Search code examples
excelexcel-formuladerivativevba

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.

Solution

  • 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)))