I'm trying to use the IRR function in Access to calculate across roughly 5 years of quarterly cash flows. Currently my data is organized in a single row with columns labled CF1 to CF20. I keep getting "Undefined function 'IRR' in expression" I am not great with Access, I know how to write SQL scripts but never really used Access for anything, but if somebody could tell me how or if it is possible to perform this function I would really appreciate it.

Thanks

IRR is available in VBA. How are you using it now that you are getting an error? Are you trying to use it in a query? If so, it won't work (I don't think.)

First of all, you need to have a Cash Flow table, not a table with 20 cash flow columns. That is not n programming. You are limited to 20 values and then you are done. Do this:

tblProject
ProjectID
ProjectName
etc

tblCashFlow
CashFlowID
ProjectID - FK
Quarter_Year(?)
CashFlow

This way your queries will work muuuuuuuch better, and you can runa project for more than 5 years - if you are working in quarters.

You will need to post the values from your table to an array (one payment and x cash returns - so must have one negative number). The array must have Doubles).

So for example, create a report with the fields CF1 - CF20 (assuming CF1 is your initial investment - if not, supply that). The fields can be Visible = No. Alternatively, have the values waiting in a query, and then package them from the query.

Then on the Detail section's On Format event, take those values into an array, then in an unbound text box, output the function.

So (I will assume you are using a ProjectID for each IRR):

Dim IRRArray As Variant
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim iProj As Integer
Dim i As Integer
Dim dIRR As Double

iProj = Me.ProjectID
i = 0
ReDim IRRArray(20)
sSQL = "SELECT * FROM qryMyCashFlow WHERE [ProjectID] = " & iProj

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs.MoveFirst
Do Until i = 20
   IRRArray(i) = rs("CashFlow")
   i = i + 1
   rs.MoveNext
Loop

dIRR = Irr(IRRArray)

Me.UnBoundTextBox = dIRR

I think that should work.







mmcdonal

Sorry, the query would have CashFlow from the CashFlow table, not CF1 - CF20. If the numbers are not available like that, then create a temp table and post the values to the temp table and work in the proper format from there.

mmcdonal

Also, if you were using the proper table structure, then you would take the record count from the query as a Count() column first (iCount variable as Integer), then pull the data into a recordset, then do this:

Do Until i = iCount + 1
   IRRArray(i) = rs("CashFlow")
   i = i + 1
   rs.MoveNext
Loop


mmcdonal