Formula for forward rates using 3 arrays

[maxoliver100]

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

 

[StephenCrump]

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:

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.

 

[maxoliver100]

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:

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.

 

[StephenCrump]

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.