Calculating IRR

[standrd]

Can anyone help me calculate Internal Rates of Return using the IRR function in Access?

The job is perfectly straightforward in Excel, but I can't work out how to assign the values in the relevant fields to the 'array' that Access requires to perform the calculation.

Attached is a sample file ... ideally I would like a query to go through the table one record at a time and calculate the IRR for each one, based on the cashflows M00, M01, M02, M03.

I'm stuck !

Many thanks.

 

[dan-cat]

Here is the code to navigate your recordset and update each row at a time

Dim Guess, Fmt, RetRate, Msg
Static Values(5) As Double
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("Test Data")

Do Until rec.EOF

Guess = 0.1

Values(0) = rec.Fields("M00")
Values(1) = rec.Fields("M01"): Values(2) = rec.Fields("M02")
Values(3) = rec.Fields("M03")
RetRate = IRR(Values(), Guess)
rec.Edit
rec.Fields("IRR") = RetRate
rec.Update
rec.MoveNext
Loop
rec.Close

Set db = Nothing
Set rec = Nothing

I've attached your db with the code but I had to convert from 97 to 02 and then back again to 97 so hopefully will still be ok.

 

[dan-cat]

 

[Mile-O]

calculate the IRR for each one, based on the cashflows M00, M01, M02, M03.

I'm stuck !

The reasons you are stuck is because you have a table design flaw. Search on normalisation and the normal forms to understand why.

As for the IIR, I posted a function for it on this thread.