Arrays in VBA -- PV cash flows

J

jdmc45

Board Regular
Joined
May 8, 2011
Messages
146
Office Version
  1. 365
Platform
  1. Windows
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.

Code: 
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
 
Sort by date Sort by votes

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Provide some sample data (a small set - not 100's of periods, but 5 or 10) and where on the spreadsheet it is (Such as In B1:10 we have the values ... and in C1:C10 we have the values ...). If possible, also the expected result.

Any reason not to just use a normal excel formula on a worksheet?

ξ
 
Upvote 0
xenou said:
Provide some sample data (a small set - not 100's of periods, but 5 or 10) and where on the spreadsheet it is (Such as In B1:10 we have the values ... and in C1:C10 we have the values ...). If possible, also the expected result.

Any reason not to just use a normal excel formula on a worksheet?

ξ
Click to expand...


A B C
<TABLE style="WIDTH: 171pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=228 border=0><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=72 height=19>CFtimes</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=84>CFamounts</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72>r </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>5%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>3</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>4</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>5</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>6</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>7</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>8</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>9</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>10</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">105</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Answer should be $100. This example is the present value of a coupon paying bond but any cash flows could be used. I know this is easily implemented in a spreadsheet, but I need to know how to program this sort of thing for more complicated applications. I have used MATLAB for this sort of program before as it easily accomodates vectors and matrices, but would also like the implementation in VBA.

Any help is appreciated!

The code so far is

Code: 
Sub TestPV_discrete()
    
    Dim CFtimes As Variant
    Dim CFamounts As Variant
    Dim r As Double
    Dim x As Double
    CFtimes = Worksheets("Bond").Range("A2:A11")
    CFamounts = Worksheets("Bond").Range("B2:B11")
    r = Worksheets("Bond").Range("C2")
    
    x = PV_discrete(CFtimes, CFamounts, r)
    
    MsgBox ("Answer is " & "x")
    
End Sub
Function PV_discrete(CFtimes As Variant, CFamounts As Variant, r As Double) As Variant
    
Dim t As Integer
If WorksheetFunction.Count(CFtimes) <> WorksheetFunction.Count(CFamounts) Then PV_discrete = 0 Else
    For t = LBound(CFtimes) To UBound(CFtimes)
        PV_discrete = Worksheets("Bond").Range("B" & t) / (1 + Worksheets("Bond").Range("C2")) ^ (Worksheets("Bond").Range("A" & t))
    Next t
End Function

I think it's close
 
Upvote 0
I know this is easily implemented in a spreadsheet ...
Click to expand...

Could you post an example where a worksheet function returns the value you want for a series of cash flows? Chances are it can be implemented directly in VBA.
 
Upvote 0
shg said:
Could you post an example where a worksheet function returns the value you want for a series of cash flows? Chances are it can be implemented directly in VBA.
Click to expand...

If CFtimes are column (A2:A11), CFamounts are column B (B2:B11) and r is C2, then it's simply in cell D2 for instance,

Code: 
=B2*(1+$C$2)^(-A2)

and fill down.
 
Upvote 0
Hi,
Try this:
The code:
Rich (BB code): 

' This function can be used as UDF in cell formula:
' =PV_discrete(A2:A11,B2:B11,C2)
Function PV_discrete(CFtimes As Range, CFamounts As Range, r As Double) As Double
  Dim i&, Times, Amounts
  Times = CFtimes.Value
  Amounts = CFamounts.Value
  If Not IsArray(Times) Then PV_discrete = Amounts / (1 + r) ^ Times: Exit Function
  If UBound(Times) <> UBound(Amounts) Then Exit Function
  For i = 1 To UBound(Times)
    PV_discrete = PV_discrete + Amounts(i, 1) / (1 + r) ^ Times(i, 1)
  Next
End Function

' Vba calling: x = PV_discrete(CFtimes, CFamounts, r)
Sub Test()
  Dim CFtimes As Range
  Dim CFamounts As Range
  Dim r As Double
  Dim x As Double
  Set CFtimes = Worksheets("Bond").Range("A2:A101")
  Set CFamounts = CFtimes.Offset(, 1)
  r = Worksheets("Bond").Range("C2")
  x = PV_discrete(CFtimes, CFamounts, r)
  Worksheets("Bond").Range("D2").Value = x
  'MsgBox ("Answer is " & x)  ' <-- For debug only
End Sub


Sheet layout:
Excel Workbook
ABCDE
1CFtimesCFamountsrxComment
2155%100Sub
325100UDF
435
545
655
765
875
985
1095
1110105
Sheet


Regards,
 
Last edited:
Upvote 0
ZVI said:
Hi,
Try this:
The code:
Rich (BB code): 
' This function can be used as UDF in cell formula:
' =PV_discrete(A2:A11,B2:B11,C2)
Function PV_discrete(CFtimes As Range, CFamounts As Range, r As Double) As Double
 Dim i&, Times, Amounts
 Times = CFtimes.Value
 Amounts = CFamounts.Value
 If Not IsArray(Times) Then PV_discrete = Amounts / (1 + r) ^ Times: Exit Function
 If UBound(Times) <> UBound(Amounts) Then Exit Function
 For i = 1 To UBound(Times)
   PV_discrete = PV_discrete + Amounts(i, 1) / (1 + r) ^ Times(i, 1)
 Next
End Function
 
' Vba calling: x = PV_discrete(CFtimes, CFamounts, r)
Sub Test()
 Dim CFtimes As Range
 Dim CFamounts As Range
 Dim r As Double
 Dim x As Double
 Set CFtimes = Worksheets("Bond").Range("A2:A101")
 Set CFamounts = CFtimes.Offset(, 1)
 r = Worksheets("Bond").Range("C2")
 x = PV_discrete(CFtimes, CFamounts, r)
 Worksheets("Bond").Range("D2").Value = x
 'MsgBox ("Answer is " & x)  ' <-- For debug only
End Sub


Sheet layout:
Excel Workbook
ABCDE
1CFtimesCFamountsrxComment
2155%100Sub
325100UDF
435
545
655
765
875
985
1095
1110105
Sheet


Regards,
Click to expand...

Thanks ZVI. I knew I was missing something and you sorted it. Cheers!

PS. how do you copy and paste a snippet of the spreadsheet like you did in that post?
 
Upvote 0
You are welcome!

To post the sheet’s layout & formulas try one of these tools/methods:
Richard Schollar's HTML Maker
Excel Jeanie
Border Copy Paste

It seems that CFtimes is unnecessary:
The code:
Rich (BB code): 

' This function can be used as UDF in cell formula:
' =PV_discrete(B2:B11,C2)
Function PV_discrete(CFamounts As Range, r As Double) As Double
  Dim i&, Amounts
  Amounts = CFamounts.Value
  If Not IsArray(Amounts) Then PV_discrete = Amounts / (1 + r): Exit Function
  For i = 1 To UBound(Amounts)
    PV_discrete = PV_discrete + Amounts(i, 1) / (1 + r) ^ i
  Next
End Function

' Vba calling: x = PV_discrete(CFamounts, r)
Sub Test()
  Dim CFamounts As Range
  Dim r As Double
  Dim x As Double
  Set CFamounts = CFtimes.Offset(, 1)
  r = Worksheets("Bond").Range("C2")
  x = PV_discrete(CFamounts, r)
  Worksheets("Bond").Range("D2").Value = x
  'MsgBox ("Answer is " & x)  ' <-- For debug only
End Sub

Sheet’s layout:
Excel Workbook
ABCDE
1CFtimesCFamountsrxComment
2155%100from Sub
325100from UDF
435
545
655
765
875
985
1095
1110105
Sheet


Cheers :)
 
Last edited:
Upvote 0
ZVI said:
You are welcome!

To post the sheet’s layout & formulas try one of these tools/methods:
Richard Schollar's HTML Maker
Excel Jeanie
Border Copy Paste

It seems that CFtimes is unnecessary:
The code:
Rich (BB code): 
' This function can be used as UDF in cell formula:
' =PV_discrete(B2:B11,C2)
Function PV_discrete(CFamounts As Range, r As Double) As Double
 Dim i&, Amounts
 Amounts = CFamounts.Value
 If Not IsArray(Amounts) Then PV_discrete = Amounts / (1 + r): Exit Function
 For i = 1 To UBound(Amounts)
   PV_discrete = PV_discrete + Amounts(i, 1) / (1 + r) ^ i
 Next
End Function
 
' Vba calling: x = PV_discrete(CFamounts, r)
Sub Test()
 Dim CFamounts As Range
 Dim r As Double
 Dim x As Double
 Set CFamounts = CFtimes.Offset(, 1)
 r = Worksheets("Bond").Range("C2")
 x = PV_discrete(CFamounts, r)
 Worksheets("Bond").Range("D2").Value = x
 'MsgBox ("Answer is " & x)  ' <-- For debug only
End Sub

Sheet’s layout:
Excel Workbook
ABCDE
1CFtimesCFamountsrxComment
2155%100from Sub
325100from UDF
435
545
655
765
875
985
1095
1110105
Sheet


Cheers :)
Click to expand...

Great, thanks. I'll be sure to check out your website. If the cash flow times are not in integer intervals however (for example, 0.25, 1.5, 3.75) then it would be necessary to include them, is this correct?

Cheers
 
Upvote 0
jdmc45 said:
If the cash flow times are not in integer intervals however (for example, 0.25, 1.5, 3.75) then it would be necessary to include them, is this correct?
Click to expand...
Yes, it's absolutely correctly!

As the case, CFtimes can be used as an optional argument to meet both conditions:
Rich (BB code): 

' 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 :beerchug:
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Building Custom Function with Array Loop
Replies
3
Views
101
FormR
FormR
I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
520
ipbr21054
I
L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
117
Legacy 519704
L
I
  • Question Question
Unable to run VBA code
Replies
6
Views
141
RoryA
RoryA
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
107
Mazurofgod
M

Forum statistics

Threads
1,224,592
Messages
6,179,787
Members
452,942
Latest member
VijayNewtoExcel

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top