Understanding Option Theta in Excel

Option’s theta is a measure of how much an option loses its value over time as it approaches its expiration date. It is also known as the time decay of an option. Theta is usually expressed as a negative number for long options and a positive number for short options.

Theta reflects the fact that options are only good for a limited period of time, and their value decreases as they get closer to their expiration date. The longer the time until expiration, the more likely the option will be in-the-money, meaning that it has intrinsic value based on the difference between the strike price and the underlying asset’s price. Therefore, an option with more time to expiration has more potential to increase in value than an option with less time.

Basic Theory

Theta quantifies how much the option’s price decreases as time passes, assuming all other factors remain constant. It
is a crucial metric for option traders as it helps them understand the impact of time on the option’s value. The
formula for Theta is generally expressed as:

$\text{Theta} = -\frac{\partial V}{\partial t}$

Where:

$\text{Theta}$ is the option’s time decay.
$V$ is the option’s price.
$t$ is time.

Procedures for Calculating Theta in Excel

To calculate Theta in Excel, you can use the numerical derivative approach. Here’s a step-by-step guide:

Define the Option Price Function: Create a column for the option prices at different time points.
Calculate Time Differences: In another column, calculate the time differences between consecutive time points.
Compute Theta: Apply the formula for numerical differentiation to find the rate of change of option
prices concerning time. Use the following Excel formula:

$\text{Theta} = -\frac{\text{D2-D1}}{\text{T2-T1}}$

Where D1 and D2 are option prices, and T1 and T2 are the corresponding time values.
Graphical Representation (Optional): Create a line graph to visualize how Theta changes over time.

Scenario

Let’s consider a call option on XYZ stock with the following details:

Initial option price (t=0): $5.00
Final option price (t=1): $4.50
Initial time (t=0): 30 days
Final time (t=1): 29 days

Now, let’s apply the formula:

$\text{Theta} = -\frac{4.50 - 5.00}{29 - 30} = 0.50$

So, the Theta for this scenario is $0.50 per day.

Excel Table

Time (days)	Option Price ($)
30	5.00
29	4.50

Result

Theta = $0.50 per day

Other Approaches

Black-Scholes Model: For more sophisticated calculations, consider using the Black-Scholes model, which
incorporates various factors like volatility and interest rates.
Use Excel Functions: Utilize built-in Excel functions like $\text{NORM.DIST}$ and $\text{EXP}$ to
calculate Theta based on the Black-Scholes model.