# Formula for forward rates using 3 arrays

#### maxoliver100

##### New Member
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

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Unfortunately, I can't check your statistical add-ins, but it is clear that there are several other things here that will need tidying up ...

Here's my first attempt, with changes marked up:

Code:
``````Function FwdRate(Tau As Range, f1 As Range, dtau As Range) As Double()  'Double() because you want to return an array

Dim i As Integer
Dim dt As Double
Dim m() As Double   'Array?
Dim X1 As Double, X2 As Double, X3 As Double    'Each variable needs a type specified, otherwise defaults to Variant
Dim vol1 As Double, vol2 As Double, vol3 As Double
Dim dblFwdRate() As Double     'Temporary working variable
Dim lCount As Long              'Temporary working variable

dt = 0.01

X1 = BoxMuller()    'Does BoxMuller return a single value or a pair?
X2 = BoxMuller()
X3 = BoxMuller()
vol1 = 0.029216
vol2(i) = VolFit(Tau(1, i), Sheet3.[H23:K23])   'vol2 and vol3 aren't declared as arrays?
vol3(i) = VolFit(Tau(1, i), Sheet3.[H36:K36])   'Should these two lines be inside the loop?
'If so, declare as vol2() and vol3() and ReDim later

lCount = Tau.Columns.Count
ReDim dblFwdRate(1 To lCount)        'Now we know the right dimensions
ReDim m(1 To lCount)

For i = 1 To lCount

m(i) = drift(Tau(1, i))

dblFwdRate(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
'Does f1 have lCount+1 values, ie so that f1(1, i + 1) won't produce an error?

Next i

FwdRate = dblFwdRate

End Function``````

Let me know how this goes? For further debugging, it would be useful to have:

1. The code that calls the FwdRate Function
2. More information on the error message(s) you're getting, which line the code breaks down on etc.

Thanks Stephen
This is very helpful in improving my understanding of what is required to get future code working ! In the end I ran out of time so included all the vols and m as range inputs into the actual formula at the top hence I did not need to redim them .... Thanks for taking the time to reply like I said next time I will have more of a clue on what to do. I am happy to send you the data for your interest , let me know ? Cheers Max

Unfortunately, I can't check your statistical add-ins, but it is clear that there are several other things here that will need tidying up ...

Here's my first attempt, with changes marked up:

Code:
``````Function FwdRate(Tau As Range, f1 As Range, dtau As Range) As Double()  'Double() because you want to return an array

Dim i As Integer
Dim dt As Double
Dim m() As Double   'Array?
Dim X1 As Double, X2 As Double, X3 As Double    'Each variable needs a type specified, otherwise defaults to Variant
Dim vol1 As Double, vol2 As Double, vol3 As Double
Dim dblFwdRate() As Double     'Temporary working variable
Dim lCount As Long              'Temporary working variable

dt = 0.01

X1 = BoxMuller()    'Does BoxMuller return a single value or a pair?
X2 = BoxMuller()
X3 = BoxMuller()
vol1 = 0.029216
vol2(i) = VolFit(Tau(1, i), Sheet3.[H23:K23])   'vol2 and vol3 aren't declared as arrays?
vol3(i) = VolFit(Tau(1, i), Sheet3.[H36:K36])   'Should these two lines be inside the loop?
'If so, declare as vol2() and vol3() and ReDim later

lCount = Tau.Columns.Count
ReDim dblFwdRate(1 To lCount)        'Now we know the right dimensions
ReDim m(1 To lCount)

For i = 1 To lCount

m(i) = drift(Tau(1, i))

dblFwdRate(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
'Does f1 have lCount+1 values, ie so that f1(1, i + 1) won't produce an error?

Next i

FwdRate = dblFwdRate

End Function``````

Let me know how this goes? For further debugging, it would be useful to have:

1. The code that calls the FwdRate Function
2. More information on the error message(s) you're getting, which line the code breaks down on etc.

Thanks Max, I'm glad you got it working one way or another.

Yes, it would be interesting to see your data if you are able to post this.

Replies
2
Views
424
Replies
1
Views
4K
Replies
0
Views
4K

1,196,426
Messages
6,015,183
Members
441,882
Latest member
LostinExcelHelp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back