Using the IRR function in Access

[Mark]

I have a query concerning the IRR function within Access 97:

What I would like to do is have a database that calculates the IRR based on
a range of dates and cashflows over 40 years.

I have set up something similar in Excel using the XIRR function in a cell
which was straightforward [XIRR(<range of cash flows>,<range of dates>)
but it appears to be more complex to replicate this in Access.

The data I wish to use in the calculation is held in one table.
An example of the data I have would be:

YEAR CASH FLOW
01-Jun-03 -2,415,056
01-Oct-03 114,031
30-Jun-04 206,001
30-Jun-05 158,617
30-Jun-06 135,803
30-Jun-07 150,409
29-Jun-08 169,167

Does anyone have any suggestions as to how I can do this in Access 97?

many thanks
Mark

 

[Jeff Boyce]

Mark

Using the Access/VBA HELP, here's the IRR Example:

IRR Function Example
In this example, the IRR function returns the internal rate of return for a
series of 5 cash flows contained in the array Values(). The first array
element is a negative cash flow representing business start-up costs. The
remaining 4 cash flows represent positive cash flows for the subsequent 4
years. Guess is the estimated internal rate of return.

Dim Guess, Fmt, RetRate, Msg
Static Values(5) As Double ' Set up array.
Guess = .1 ' Guess starts at 10 percent.
Fmt = "#0.00" ' Define percentage format.
Values(0) = -70000 ' Business start-up costs.
' Positive cash flows reflecting income for four successive years.
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
RetRate = IRR(Values(), Guess) * 100 ' Calculate internal rate.
Msg = "The internal rate of return for these five cash flows is "
Msg = Msg & Format(RetRate, Fmt) & " percent."
MsgBox Msg ' Display internal return rate.