IRR Function

  • Thread starter Thread starter Jim S.
  • Start date Start date
J

Jim S.

I would appreciate an example for setting up the IRR
function to print in a report.

I have a col. entitled "Date" and another "Amount". I
have more than one cash flow per year. I am looking to
compute the IRR in Access 2003.

Thanks for any help you can provide.
 
Jim:

The IRR function is a VBA function that you can call, but not from within
the query itself. The way implement this varies based upon what data your
report will contain.

1.) If your report contains all of the values for the cash flow (i.e.
initial outlay and all payments,) then you can do the calc in your report by
a.) dimensioning an array in the report modules declaration (Dim
IRRValues(X) as Double) where X is a number sufficient to cover all payments
+ 1 for the inital outlay along with a counter Dim i as Integer. b.) Then
assuming you have a group header that contains say the project name and
outlay, you'd append the first value to the array in the On Print event of
that section as in: IRRValues (0) = Me!txtInitialOutlay. c.) Then in the On
Format event of each Detail section add code like this:

If FormatCount = 1 Then
i = i +1
IRRValues (i) = Me!txtPayment
End if

d.) last in the group footer for the payment stream, you'd have an unbound
control where you'd display the value and in the On Print Event of the Group
footer, you'd calc the IRR value and return it to the unbound control as in:

Dim dblReturn as Double
dblReturn = IRR(IRRValues(), .1)
Me!txtReturn = dblReturn

But that would make a lengthly report to print and a lot of repeated
information (you could use HideDuplicates to hide payment lines after the
first one to shorten the report)

2.) If your report doesn't contain the entire payment stream (e.g. it has
only this month's listing of payment for all projects) then you've got to go
about it differently. This is especially true if your db doesn't contain a
payment record for all payments, but only those upto the point of the
current report (e.g. you've got a three year payment stream monthly, but the
db only contains the records of payments through the current month and
you've got 2.5 years to go on the project.)

In this case you'd have pull into your query the periodic payment amount,
the total number of payments expected and the initial outlay in your
report's query. Then in the group footer, you'd do something like this,
assuming that there was a control for each of the above values on your
report:

Dim IRRValues() as Double
Dim dblReturn as Double
Dim intArraySize as Integer, i as Integer

intArraySize = Me!txtNumPayments + 1

ReDim IRRValues ( intArraySize)
IRRValues(0) = Me!txtOutlay
For i = 1 to intArraySize
IRRValues (i ) = Me!txtPaymentAmount
Next i
dblReturn = IRR(IRRValues, .1)
Me!txtReturn = dblReturn

HTH
 
Back
Top