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

=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.

"Rich Rosier" wrote:

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