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
