[h1]VBA for Excel Spreadsheet[/h1]The VBA includes two functions
- BlackScholesCall() calculates the price of a call option
- ImpliedVolatility() calculates implied volatility. It uses the bisection method to calculate volatility using BlackScholesCall()
Function BlackScholesCall( _
ByVal S As Double, _
ByVal X As Double, _
ByVal T As Double, _
ByVal r As Double, _
ByVal d As Double, _
ByVal v As Double) As Double Dim d1 As Double Dim d2 As Double
d1 = (Log(S / X) + (r - d + v ^ 2 / 2) * T) / v / Sqr(T)
d2 = d1 - v * Sqr(T)
BlackScholesCall = Exp(-d * T) * S * Application.NormSDist(d1) - X * Exp(-r * T) * Application.NormSDist(d2)
End Function Function ImpliedVolatility( _
ByVal S As Double, _
ByVal X As Double, _
ByVal T As Double, _
ByVal r As Double, _
ByVal d As Double, _
ByVal Price As Double) As Double Dim epsilonABS As Double Dim epsilonSTEP As Double Dim volMid As Double Dim niter As Integer Dim volLower As Double Dim volUpper As Double
epsilonABS = 0.0000001
epsilonSTEP = 0.0000001
niter = 0
volLower = 0.001
volUpper = 1
Do While volUpper - volLower >= epsilonSTEP Or Abs(BlackScholesCall(S, X, T, r, d, volLower) - Price) >= epsilonABS And epsilonABS = epsilonABS
volMid = (volLower + volUpper) / 2
If Abs(BlackScholesCall(S, X, T, r, d, volMid) - Price) |
转播
