Solving my problem will require digging into several layers of issues, so I really appreciate your efforts should you decide to help. I have not used solver in VBA before but due to the amount of recalculations I have to do I must this time. The 'generic' loop is the following:
Option Explicit Sub calc_implied_vol() Dim calc_cell As Range, act_data As Range, imp_vol As Range Dim i As Integer, t As Integer Set calc_cell = Range("d13") Set act_data = Range("d6") Set imp_vol = Range("d20") For t = 0 To 5 For i = 0 To 20 SolverReset SolverOptions Precision:=0.0001 SolverOk SetCell:=Range(calc_cell).Offset(t, i), ValueOf:=Range(act_data).Offset(t, i), _ ByChange:=Range(imp_vol).Offset(t, i) SolverSolve Next i Next t End Sub
If you look at the attached sheet, range D6:X11 contains market prices for options. In Range D13:X18, prices are calculated using my custom function/add-in. The custom function uses data in range d20:x25 as inputs. I would like the solver to match the calculated prices to market prices by changing data in range d20:x25. Here is the function, just in case: (i don't think it's relevant)
Function gk(CallPutFlag As String, Spot As Double, Strike As Double, _ Time_to_Mat As Double, domestic_rate As Double, foreign_rate As Double, Volatility As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(Spot / Strike) + (domestic_rate - foreign_rate + Volatility ^ 2 / 2) * Time_to_Mat) / (Volatility * Sqr(Time_to_Mat)) d2 = d1 - Volatility * Sqr(Time_to_Mat) If CallPutFlag = "Call" Then gk = Spot * Exp(-foreign_rate * Time_to_Mat) * Application.NormSDist(d1) - Strike * Exp(-domestic_rate * Time_to_Mat) * Application.NormSDist(d2) ElseIf CallPutFlag = "Put" Then gk = Strike * Exp(-domestic_rate * Time_to_Mat) * Application.NormSDist(-d2) - Spot * Exp(-foreign_rate * Time_to_Mat) * Application.NormSDist(-d1) End If End Function
When running the solver sub, the code doesn't execute but steps into the function when the code gets to -->SolverOptions. What am I overlooking? Am I missing several steps? As always, any input and guidence is greatly appreciated.