How can I use black scholes formula in Excel?

manojg wrote:
How can I use black scholes formula in Excel?
Post the particular Black Scholes formula you're referring to.

Alan Beban

This is what i use:

Function dOne(Stock, Exercise, time, Interest, sigma)
dOne = (Log(Stock / Exercise) + Interest * time) / (sigma * Sqr(time)) + 0.5
* sigma * Sqr(time)
End Function

Function optionCall(Stock, Exercise, time, Interest, sigma)
optionCall = Stock * Application.NormSDist(dOne(Stock, Exercise, time,
Interest, sigma)) - Exercise * Exp(-time * Interest) *
Application.NormSDist(dOne(Stock, Exercise, time, Interest, sigma) - sigma *
Sqr(time))
End Function

Function optionPut(Stock, Exercise, time, Interest, sigma)
optionPut = optionCall(Stock, Exercise, time, Interest, sigma) + Exercise *
Exp(-Interest * time) - Stock
End Function

Put these functions in a standard module. And to find the call premium,
simply use
=optioncall(Stock, Exercise, time, Interest, sigma)
stock is the spot price, exercise in the exercise price, time is in years,
interest and volatility are the next arguments.

Use the function as you would use any other standard excel functions.

Mangesh



"manojg" wrote in message
...
How can I use black scholes formula in Excel?