CODEWITHSUNDEEP
excelexcel-formuladerivativevba
Jayesh Kurup
Jayesh Kurup

Reputation: 57

Difference in Macro vs Manual Output on same formula using LOG

Well its rather a very strange question

I have a macro which generates the delta of a Option(d1):

Function dOne(UnderlyingPrice, ExercisePrice, Time, Interest, Volatility, Dividend)

    dOne = (Log(UnderlyingPrice / ExercisePrice) + (Interest - Dividend + (0.5 * Volatility ^ 2)) * Time) / (Volatility * (Sqr(Time)))

End Function

When I pass the the values to it, it generates the desired output:

Delta value using macro

However when I try to replicate this in Excel, it gives an entirely different output

Delta when values are passed manually

I know that the calculations for output generated manually are correct. However the desired values are those generated from VBA.

Please suggest what am I missing here.

Upvotes: 2

Views: 139

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The Log function in VBA is the natural log: ln(x).
The LOG function in the formula is log base 10: log10(x).

If you want log base 10 in VBA you will have to use the logarithmic identity for converting bases:

Log(x)/Log(10)

In your case

dOne = (Log(UnderlyingPrice / ExercisePrice) / Log(10) + (Interest - Dividend + (0.5 * Volatility ^ 2)) * Time) / (Volatility * (Sqr(Time)))

Upvotes: 2

Related Questions