Hi all, So for background, I have a continuous form that consists of 60 records per one record. These 60 records are for cashflow entries over the period of 60 months.
We recently had a deal that we need to hold longer than 60 months, so I added 24 more months to it to extend the total to 84 records for the one record (Asset)
The IRR formula I have will calculate for the first 60 months, but after the 60 months, any cash after that entered, does not get factored into the IRR. I dont know what I am doing wrong, as I did not originally write the script. Any help would be appreciated. Thank You!
Code:
'do the IRR for recommended strategy
On Error GoTo ErrRecommended
qryString = "Select * from tblCashFlow where LoanID = " & Me.ID.Value & "and Strategy = 'Recommended' order by CFDate"
Set rsCF = db.OpenRecordset(qryString, dbOpenDynaset, dbSeeChanges)
rsCF.MoveFirst
Do While Not rsCF.EOF
Values(i) = (rsCF("TotalIncome").Value - rsCF("TotalExpenses").Value)
rsCF.MoveNext
i = i + 1
Loop
RetRate = XLIrrCalc(Values())
rsResults.Edit
rsResults("BusPlan_RecIRR").Value = RetRate
rsResults.Update
RetRate = 0