2

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.

Community
  • 1
  • 1
Jayesh Kurup
  • 57
  • 5
  • 2
    Check [this](http://stackoverflow.com/questions/31618472/logarithm-is-different-using-vba-and-excel-function). – Sangbok Lee Mar 13 '17 at 08:10
  • 1
    If "the desired values are those generated from VBA." then the exact equivalent as Excel formula will be `=(LN(E1/E2)+(E4-E6+(0.5*E5^2))*E3)/(E5*(SQRT(E3)))`. – Axel Richter Mar 13 '17 at 08:16

1 Answers1

2

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)))
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73