Hi all,
I am new to VBA and am trying to code something that will use solver. I am asking solver to take a inputs to a binomial tree and solve for the implied volatility. Thus, I am providing the current option premium (to be overwritten with the new), time to maturity, risk-free rate, strike price, current stock price, and the *new* option premium. Solver would need to take these inputs and determine implied vol to arrive that new premium.
As I mentioned, I am new to VBA and I have no idea why this code is not working. I would really appreciate guidance!!
Sub Macro5()
SolverReset
' SolverOptions precision:=0.00000001
SolverAdd CellRef:="$b$6", Relation:=2, FormulaText:="$b$6"
SolverAdd CellRef:="$b$7", Relation:=2, FormulaText:="$b$7"
SolverAdd CellRef:="$b$9", Relation:=2, FormulaText:="$b$9"
SolverAdd CellRef:="$b$10", Relation:=2, FormulaText:="$b$10"
SolverOk SetCell:="$b$5", MaxMinVal:=3, ValueOf:="$b$13", ByChange:="$b$12"
SolverSolve True
SolverSolve UserFinish:=False
MsgBox ("Theo vol solved")
End Sub
In my file,
b5 = current option premium (to be changed to be equal to the value in b13)
b6 = underlying price
b7 = strike
b9= time to maturity
b10 = risk free rate
b13 = my desired option premium
b12 = the current vol, to be changed solved for such that the option premium in b5 changes to b13
Right now I get the error:
Compile error:
Expected: .
Please let me know if you need more information to assist. Sample excel file attached.
Thank you!
Bookmarks