Calculating the intrinsic or extrinsic value of an option with a spreadsheet is not difficult. In fact, the most difficult part is getting the option price into your spreadsheet in the first place. In this article we’ll go over how to automatically calculate intrinsic/extrinsic values using our Sheets Add-On and also how to do this manually if you’re looking to work without an Add-on. We’ll also explain what exactly is the intrinsic and extrinsic value of an option.
Calculate Intrinsic / Extrinsic Value Automatically in Google Sheets
If you already know how to calculate intrinsic and extrinsic values, just use our OPTIONDATA formula. You can automatically calculate the intrinsic or extrinsic value of an option as long as you have the option symbol available. Here’s an example with the Jan 19, 2024 AAPL $140 Call:
- Intrinsic Value In A Single Cell:
=OPTIONDATA("AAPL240119C00140000","intrinsic") - Extrinsic Value In A Single Cell:
=OPTIONDATA("AAPL240119C00140000","extrinsic") - Intrinsic/Extrinsic Values Plus Option Price & Underlying Price:
=OPTIONDATA("AAPL240119C00140000","price,underlying price,intrinsic,extrinsic")
If you’re not yet familiar with intrinsic and extrinsic calculations, keep reading to learn how these values are calculated.
Option Concepts: Intrinsic / Extrinsic Value
Have you ever wondered what exactly is the intrinsic or extrinsic value of an option? Keep reading to learn about these two key concepts and how to calculate them.
What is the intrinsic value of an option?
The intrinsic value is the difference between the option’s strike price and the underlying price. If you exercised your option today (instead of selling it), the intrinsic value is the value you would obtain.
The calculation varies depending on whether we are looking at Calls or Puts. If the option is a Call, you subtract the strike price from the underlying price. If it is a Put, its the opposite, you subtract the underlying price from the strike price.
This is why to calculate the intrinsic value of an option, you need to first have the strike price of the option plus the option’s current trading price. If you’re not using Market Data’s Sheets Add-on, there’s not really a good way to get option prices into your spreadsheet. You’ll need to copy the price from your broker or from the web.
What is the extrinsic value of an option?
The extrinsic value is the time value of the option. You calculate it by subtracting the intrinsic value from the option price. Most options will have some extrinsic value before they expire and will gradually lose their extrinsic value as the expiration date is reached. For both in the money and out of the money options, extrinsic value will always slide towards zero near expiration. This gradual decay of extrinsic value over time is called theta decay. Learn how to calculate theta decay in your spreadsheet.
