VBA code for Theta of a european Call

[Aber]

I have been using so much time to make this function to work but I can't seem to find the problem. In world of no interest rate and no dividend I want to compute the theta of a call option

here is my code

Function theta(spot, strike, vol, T)

d1den = vol * Sqr(T)
d1num = Log(spot / strike) + (0.5 * vol ^ 2) * T
d1 = d1num / d1den

theta = -((spot * Application.NormDist(d1, 0, 1, False) * vol) / (2 * Sqr(T)))


What goes wrong here? I cant make it return any correct number.

for instance: spot = 100, strike = 105, vol = 0.1 and T =1 then the answer should be : -0.004965323. I can't replicate that number.

 

[Daniel Duffy]

Just use the formula from Espen Haug's book.
BTW the implementation of the formula look wrong.

https://wikimedia.org/api/rest_v1/media/math/render/svg/311fb0f35d17dde4d4f970cdbb7400960fecf96a

Am I missing something?

 

[Aber]

Just use the formula from Espen Haug's book.
BTW the implementation of the formula look wrong.

https://wikimedia.org/api/rest_v1/media/math/render/svg/311fb0f35d17dde4d4f970cdbb7400960fecf96a

Am I missing something?

I am using that exact formula but I operate in world with interest rate r=0 and no dividend payments so the last terms will be zero..

 

[Andy Nguyen]

I did something similar years ago. Take a look, plug in the appropriate numbers in the theta call function and see if it gives the expected answer. Then double check your formula.
How to create UDF in Excel using C# and Visual Studio 2008