Arrays in VBA -- PV cash flows

[jdmc45]

Hi all,

I have an issue with creating a simple function in VBA. The help isn't of much use. I know I am simply missing some key concept in VBA which I'm sure is easy.

I am trying to create a function to calculate the present value of future cash flows, simply CF(t)/(1+r)^(t) for all t. The arrays are the issue. I have many more problems in this format (with vectors and matrices) so if I figure out this one it will carry forward to the others.

Please look at the code below and see if you can help me. Thanks.

Function PV_discrete(Dim CFtimes(1 To n) As Integer, Dim CFamounts(1, X) As Integer, Dim r As Double)
Dim t() As Integer
PV_discrete = 0
For t = 0 To Length(CFamounts(1, X))
PV_discrete = CFamounts(t) / (1 + r) ^ (CFtimes(t))
Next t
PV_discrete
End Function

 

[jdmc45]

Yes, it's absolutely correctly!

As the case, CFtimes can be used as an optional argument to meet both conditions:

' This function can be used as UDF in cell formula:
' Excel formula: =PV_discrete(C2,B2:B11)          - for integer CFtimes
' Excel formula: =PV_discrete(C2,B2:B11,A2:A11)   - for floating CFtimes
' Vba call: x = PV_discrete(r, CFamounts)         - for integer CFtimes
' Vba call: x = PV_discrete(r, CFamounts,CFtimes) - for floating CFtimes
Function PV_discrete(r As Double, CFamounts As Range, Optional CFtimes As Range) As Double
 Dim i&, j#, UseTimes As Boolean, Times, Amounts
 Amounts = CFamounts.Value
 If Not CFtimes Is Nothing Then
   UseTimes = True
   Times = CFtimes.Value
 End If
 If Not IsArray(Amounts) Then PV_discrete = Amounts / (1 + r) ^ IIf(UseTimes, Times, 1): Exit Function
 If UseTimes Then
   If UBound(Times) <> UBound(Amounts) Then Exit Function
 End If
 For i = 1 To UBound(Amounts)
   If UseTimes Then j = Times(i, 1) Else j = j + 1
   PV_discrete = PV_discrete + Amounts(i, 1) / (1 + r) ^ j
 Next
End Function
 
' Vba calling: x = PV_discrete(r, CFamounts[, CFtimes ])
Sub Test()
 Dim CFamounts As Range, CFtimes As Range
 Dim r As Double, x As Double
 Set CFtimes = Worksheets("Bond").Range("A2:A101")
 Set CFamounts = Worksheets("Bond").Range("B2:B101")
 r = Worksheets("Bond").Range("C2")
 
 'x = PV_discrete(r, CFamounts)          ' <-- Test one
 x = PV_discrete(r, CFamounts, CFtimes)  ' <-- of these lines
 
 Worksheets("Bond").Range("D2").Value = x
End Sub

Cheers

Awesome, thanks