1. Registered User
Join Date
Oct 2003
Posts
18

I am trying to calculate the IRR using the built in function within access.

I currently have a query with has 5 columns...

1. Investment
2. year 1 return
3. year 2 return
4. year 3 return
5. year 4 return

From what i can gather, i need to make these values in to an array so that i can use the IRR function but have no idea how to do this.

Ideally i'd like to return the value off the IRR function in to a column in the same query.

Any ideas how to acheive this ?

It seems so simple in excel, this is really frustrating.

Thanks

Dave

2. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
From the access help:
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.

So if you paste this into a module
Code:
```Public Function myIRR(Investment, Return1, Return2, Return3, Return4, Percentage) As Double
Dim x(4) As Double
x(0) = Investment
x(1) = Return1
x(2) = Return2
x(3) = Return3
x(4) = Return4
myIRR = IRR(x, Percentage)
End Function```
Then from your query call this function with the needed fields, it should return your IRR.

Regards

3. Registered User
Join Date
Oct 2003
Posts
18
I tried this but it returns a compile error:

Compile error !!

Expected variable or proceedure, not module

Any idea whats going wrong ?

4. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
You have named your module the same as the function, DONT.

Try keeping to a naming convention.

mdl = module
mdlIRR for instance

frm = from
tbl = table
qry = query
etc

Regards

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•