{"id":2367,"date":"2020-04-18T23:56:53","date_gmt":"2020-04-18T15:56:53","guid":{"rendered":"https:\/\/www.fabianmoa.com\/?p=2367"},"modified":"2020-04-25T15:53:10","modified_gmt":"2020-04-25T07:53:10","slug":"finding-option-implied-volatility-using-goal-seek-in-excel","status":"publish","type":"post","link":"https:\/\/fabianmoa.com\/2020\/04\/18\/finding-option-implied-volatility-using-goal-seek-in-excel\/","title":{"rendered":"Finding Option Implied Volatility using Goal Seek in Excel"},"content":{"rendered":"\n[et_pb_section fb_built=&#8221;1&#8243; _builder_version=&#8221;4.4.1&#8243; custom_padding=&#8221;16px|||||&#8221;][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_text _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;]<p>When we model a problem, we will usually have a set of inputs, feed it into the model and we get the output. There are also times, when we have a target output and we want to know what the input value (out of all the other inputs) would be in order to achieve that output value.<\/p>\n<p>Take the example where we compute the value of a European-style call option using the Black-Scholes formula. Often times, traders will want to know the implied volatility given the option price.<\/p>\n<p>Well, you could estimate the implied volatility if you implement the Newton-Raphson method or other numerical methods, but a quick and easy way to do this in Excel will be to use<span>\u00a0<\/span><strong>GoalSeek<\/strong>.<\/p>\n<p>Excel file can be found at: https:\/\/drive.google.com\/file\/d\/1fY6Og1zIhJq1PvTZftAbC7zQ0teXQlYi\/view?usp=sharing<\/p>\n<p>For illustration, I will assume:<\/p>\n<ul>\n<li>Underlying price = 50<\/li>\n<li>Strike\/Exercise price = 52<\/li>\n<li>Volatility = 20% (Initially)<\/li>\n<li>Time to Maturity = 6 months (0.5 years)<\/li>\n<li>Risk-free rate = 3% p.a.<\/li>\n<\/ul>\n<p>Using the Black-Scholes formula, we would get the call option price as 2.28.<\/p>\n<p>But, if the call option price is 5.20, then what would the implied volatility be?<\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=&#8221;4.4.1&#8243;][et_pb_column _builder_version=&#8221;4.4.1&#8243; type=&#8221;4_4&#8243;][et_pb_video src=&#8221;https:\/\/youtu.be\/NRqXYpJj6Pc&#8221; _builder_version=&#8221;4.4.1&#8243; hover_enabled=&#8221;0&#8243;][\/et_pb_video][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"<p>When we model a problem, we will usually have a set of inputs, feed it into the model and we get the output. There are also times, when we have a target output and we want to know what the input value (out of all the other inputs) would be in order to achieve that [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2393,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29],"tags":[21,36,50,51],"_links":{"self":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2367"}],"collection":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/comments?post=2367"}],"version-history":[{"count":0,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/posts\/2367\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media\/2393"}],"wp:attachment":[{"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/media?parent=2367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/categories?post=2367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabianmoa.com\/wp-json\/wp\/v2\/tags?post=2367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}