Creating a Formula to Calculate Time Value in an Option

[Rich Rosier]

The relevant columns are as follows:
B=Option (2 words only in this column) Call or Put
D=Strike Price = numeric value
O=Current Stock Price = numeric value
P=Current Option Price = numeric value
Q=Time Value = numeric value

I would like to create a formula in the cell of column Q that automatically
calculates the time value left on the option (either call or put). Here's
what the formula needs to do:

1) assess whether the option is a call or put
2) if call, then calculate as follows:
If current stock price O is more than strike price D, the the option is in
the money. The time value is calculated as follows: Current Option Price P -
(Actual Stock Price O - Strike Price D)
If current stock price O is less than strike price D, the the option is out
of the money. The time value is the Current Option Price P
3) if put, then calculate as follows:
If current stock price O is less than strike price D, the the option is in
the money. The time value is calculated as follows: Current Option Price P -
(Strike Price D -Actual Stock Price O)
If current stock price O is more than strike price D, the the option is out
of the money. The time value is the Current Option Price P

Is it possible to write a formula that will do that in one cell for each row?

Thanks very much for your help.
Rich

 

[bpeltzer]

=P2-MAX(0,IF(B2="call",1,-1)*(O2-D2))
Start by calculating the stock price minus the strike price.
Multiply by -1 if the option is a put.
Take the MAX of that result and 0, since the intrinsic value cannot be
negative.
Subtract the intrinsic value from the option price to get the time value.