IRR Calculations

[Slayboy]

Hi,

I need to add in a function to a database that calculates IRR for each of many reference numbers, everything is in flat file at the moment but I can get around that to list cashflows in rows for each ref if I have to. I found a thread where someone wrote some VB to do this for a set or refs which all had 4 cashflows, but my data has varying amounts, some with overs 40 cashflows.
I need to write a piece of code like the one in this thread but which just uses all of the cashflows listed no matter how many, rather than a rigid formula using 4 cashflows.
It would be even better if I could keep the data in flat file format and have some code that creates a temporary query with the cashflows in a column for each ref and then iterates through all of the ref numbers?

Does anyone have any ideas?

This is the code that someone wrote in the other thread and I have attached the example database:

Public Sub pop()

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

End Sub

 

[Guus2005]

What does the flat file look like?
Where do you want the output to go to?

 

[Slayboy]

I want the IRRs to be put into a table next to the Ref numbers, the flat file looks like this:

Ref SchedDate SchedAmount
1111 10/04/06 102000
1111 10/04/07 -80000
1111 10/04/08 -80000
1111 10/04/09 -107000
1112 7/04/06 927000
1112 7/04/07 -10000
1112 7/04/08 -10000
1112 7/04/09 -10000
1112 7/04/10 -984000
1113 10/04/06 102000
1113 10/04/07 -15000
1113 10/04/08 -104000

This is just an example, but you see what I mean, I want a query to look at each set of cashflows, calculate an IRR and move on, the have a table with 2 columns: Ref and IRR

 

[Slayboy]

IRR Calc - can no one help me with this?

please can someone help me with this, I'm sure I just need some kind of itteration formula or something but I don't know how to do it