Dear Matthew:
The IRR function is available from Excel. However, your database
probably stores the payments (the negative values) and the income
values in separate rows of the table. Thus, the function you need is
somewhat of an aggregate function over a group, where that group is a
profit center (or similar accouting entity).
Creating new aggregate functions is not going to be possible for a Jet
engine database. However, a function returning IRR could be created
in which you pass values to the function that specify how to filter
the table(s) to obtain the data. That is, you could pass to the
function the column(s) that specify a profit center as well as
(optionally) a range of dates or other essential information. This
function could then gather an indefinite number of payment/income
values and pass these to the IRR function and return the IRR for that
profit center. In addition, it might need to provide for any missing
values, since IRR requires that you provide data for every month,
quarter, or year interval within the range. It might be inconvenient
to always put these in when entering data if they are zero.
This would be done by having the function open a recordset filtered to
that profit center and then construct the call to the IRR function
according to its rules. A final detail is to set up references to the
actual function.
I have made some assumptions, such as the ability to identify a single
profit center for the function. Hopefully these are reasonable given
your situation.
Please let me know if this helped, and if I can be of any further
assistance.
I am interested in using the IRR function in an
Access 97 database. I would like to use a series of
payments and the period which they occured (fields in a
table) to calculate the internal rate of return. How
would I go about creating an array that the function will
recognize from a table including year and payment data?
Is there another way to do this?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
