# Thread: Determining IRR's on numerous records...

1. Registered User
Join Date
Dec 2005
Posts
45

## Unanswered: Determining IRR's on numerous records...

I have a table with 700 loans, and each loan has 180 months worth of cash flows. I am trying to figure a way to calculate the IRR for each loan. I can generate an IRR with the code supplied by Microsoft:

Dim guess, Fmt, RetRate, Msg
Static Values(5) As Double
guess = 0.1
Fmt = "#0.00"
Values(0) = -70000
Values(1) = 22000: Values(2) = 25000
Values(3) = 28000: Values(4) = 31000
RetRate = irr(Values(), guess) * 100 ' Calculate internal rate.

I think I need to loop through the recordset of each loan and assign the cash flow values to an array, where I then calculate the IRR.

Any ideas would be greatly appreciated!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
move the code from the microsoft site into a function, then call that function from a Query

the function will have to be stored in a public module
HTH

3. Registered User
Join Date
Dec 2005
Posts
45
Thanks, but my monthly cash flows are not in columns, they are each a different record.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so?
do a select on the data within the function, or find another way of flattenting the data within a query before it gets passed to the fucntion.

intrinscially there is no reason why you can't run a query within a function, subject to accepting that there will be a perfomrance hit if the query and the way it is accessed isn't carefully crafted.

from what I rmember of IRR calcualtions they are failry naiv, after all they are a projection, often used by accoutnants to pretend that a project has a better return that another project.

5. Registered User
Join Date
Dec 2005
Posts
45
Thanks for the help!

The problem I'm having is that I have a table with 388 loans, and each has between 355 and 360 cash flows, giving me @ 140000 records.

I think my first step is looping through a recordset and assigning loan_ID to a variable, then using that variable in an SQL statement to set another recordset to include only that loan's cash flows. Lastly, use getrows to assign the cash flows to an array, and irr that array.

Make sense?
This may be more difficult than necessary, but its all I can come up with.

Thanks again!

6. Registered User
Join Date
Jul 2005
Posts
50
Let's say you had a table like this:

Code:
```ID	Customer	CashFlowAmount	FlowDate
1	Hayes		-70000		1/1/2005
2	Hayes		22000		2/1/2005
3	Hayes		25000		3/1/2005
4	Hayes		28000		4/1/2005
5	Hayes		31000		5/1/2005```
Here is a function call and function to handle that:

Code:
```Sub SomeAccountingSubRuoutine()

Dim dIRR As Double

dIRR = MyIRR("Hayes", 0.1)

End Sub

'The Function
Function MyIRR(sCustomer As String, dMyGuess As Double) As Double

Dim iLoop As Integer
Dim rsCashFlows As DAO.Recordset
Dim sSQL As String
Dim lRecCt As Long

sSQL = "Select CashFlowAmount From tb_CashFlow WHERE Customer = '" & sCustomer & "'"
Set rsCashFlows = CurrentDb.OpenRecordset(sSQL)

'Get accurate record count
rsCashFlows.MoveLast
rsCashFlows.MoveFirst
lRecCt = rsCashFlows.RecordCount

ReDim Values(lRecCt - 1) As Double ' an array with 15 elements

For iLoop = 0 To lRecCt - 1
Values(iLoop) = rsCashFlows(0)
rsCashFlows.MoveNext
Next iLoop
' Perform your Internal Rate of Return calculation
MyIRR = IRR(Values(), dMyGuess) * 100

rsCashFlows.Close
Set rsCashFlows = Nothing

End Function```

7. Registered User
Join Date
Jul 2005
Posts
50
If that is too simplistic, I don't know how your data is set up.

It might be more fun to try to return your answer in a query, with no VB code, but I thought I'd approach your problem from a VBA perspective, since that's where you were headed.

8. Registered User
Join Date
Dec 2005
Posts
45
mlbuie,

Looks like a great way to do the calc.

Now I just need it to pull the first customers cash flows, calc an IRR, and then move to the next Customers cash flows, until all customers are done.

Thanks for the help!

#### Posting Permissions

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