maxoliver100
New Member
- Joined
- Dec 19, 2013
- Messages
- 18
Hi Guys
Please some help !! I want to calculate 10 forward rates based on the 10 horizontal row inputs from Tau,f1 and dtau which are range inputs. I am having trouble getting VBA to read say Tau(1,i) which I want to be the ith element in the row and likewise f1(1,i) to be the ith element in the row ? These ith elements will then give me the ith fwdrate. What am I missing ? Sorry I am new to this .....
Thanks
Option Explicit
Function FwdRate(Tau, f1, dtau)
Dim i As Integer
Dim dt As Double
Dim m As Double
Dim X1, X2, X3 As Double
Dim vol1, vol2, vol3 As Double
dt = 0.01
X1 = BoxMuller()
X2 = BoxMuller()
X3 = BoxMuller()
vol1 = 0.029216
vol2(i) = VolFit(Tau(1, i), Sheet3.[H23:K23])
vol3(i) = VolFit(Tau(1, i), Sheet3.[H36:K36])
For i = 1 To Tau.Columns.Count
m(i) = drift(Tau(1, i))
FwdRate(1, i) = f1(1, i) + m(i) * dt + (vol1 * X1 + vol2(i) * X2 + vol3(i) * X3) * Sqr(dt) _
+ ((f1(1, i + 1) - f1(1, i)) / dtau(1, i)) * dt
Next i
End Function
Please some help !! I want to calculate 10 forward rates based on the 10 horizontal row inputs from Tau,f1 and dtau which are range inputs. I am having trouble getting VBA to read say Tau(1,i) which I want to be the ith element in the row and likewise f1(1,i) to be the ith element in the row ? These ith elements will then give me the ith fwdrate. What am I missing ? Sorry I am new to this .....
Thanks
Option Explicit
Function FwdRate(Tau, f1, dtau)
Dim i As Integer
Dim dt As Double
Dim m As Double
Dim X1, X2, X3 As Double
Dim vol1, vol2, vol3 As Double
dt = 0.01
X1 = BoxMuller()
X2 = BoxMuller()
X3 = BoxMuller()
vol1 = 0.029216
vol2(i) = VolFit(Tau(1, i), Sheet3.[H23:K23])
vol3(i) = VolFit(Tau(1, i), Sheet3.[H36:K36])
For i = 1 To Tau.Columns.Count
m(i) = drift(Tau(1, i))
FwdRate(1, i) = f1(1, i) + m(i) * dt + (vol1 * X1 + vol2(i) * X2 + vol3(i) * X3) * Sqr(dt) _
+ ((f1(1, i + 1) - f1(1, i)) / dtau(1, i)) * dt
Next i
End Function