Calculating the IRR in MS Access using a query

[BertDick]

This is new to me. I'm trying to calculate the IRR from a cash flow statement.
My database fields are Fund ID, Capital calls, Distributions,management fees, net asset value and total cash flow. The cash flow column is the total of (capital call+distributions+management fees-Net asset value) I need to add a column in my query to calaulate the IRR. How do I go about that?.

Please help

Thanks

[Rabbit]

What is IRR? How do you calculate IRR?

[BertDick]

it's a function to calculate the Internal Rate of Return.

[jaxjagfan]

Aren't you going to need Rate, NPV, Span (DateRange). I think there's a couple of other values. It's been about 6-7 years since I touched this one.

[ADezii]

This is new to me. I'm trying to calculate the IRR from a cash flow statement.
My database fields are Fund ID, Capital calls, Distributions,management fees, net asset value and total cash flow. The cash flow column is the total of (capital call+distributions+management fees-Net asset value) I need to add a column in my query to calaulate the IRR. How do I go about that?.

Please help

Thanks

Thanks[/quote]

The easiest Method by far of calculating the IRR is within Excel, since it contains a Financial Function designed specifically for this calculation. Assume the following grid where Column A contains +/- cash flows for consecutive years indicated by Rows 2 through 8:

____________Column A    Column B
Row 1       Data         Description
Row 2       -70,000         Initial Cost of a business
Row 3       12,000         Net Income for the first year
Row 4       15,000         Net Income for the second year
Row 5       18,000         Net Income for the third year
Row 6       21,000         Net Income for the fourth year
Row 7       26,000         Net Income for the fifth year
 

Cell Formulas and their Yield (%):

=IRR(A2:A6) - would return the IRR after 4 years (-2%)
=IRR(A2:A7) - would return the IRR after 5 years (9%)
=IRR(A2:A4, -10%) - calculates the IRR after 2 years, notice an Optional guess (-10%) would be required for this calculation (-44%).
 

NOTE: You probably could perform the Internal Rate of Return calculations entirely within Access using Automation code to access Excel's IRR() Function. If you are interested in this approach, let me know and I'll see what I can do.

[BertDick]

I will be greatful for your help. This function needes to be done in access.I can use the excel function but I can't convert it to access.

I'm trying the code below.

Function MyIRR(mygroupid, myguess) as double
Dim Values(4) as double' an array with 5 elements
Dim x
 
dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Myquery where myid =" & mygroupid)
rs.movefirst ' should only be one record
 
for x = 0 to 4
Values(x) = rs(x+1)
next x
 
MyIRR = IRR(Values(),myguess)
 
rs.close
set rs = nothing
 
End Function
 

In this procedure I am using a query to grouped on the first column; (FundID).

I Created a second query using the group query as source and include all fields plus an extra column to calculate your IRR.
The extra column looks like:
IRRVal:MyIRR(FundID, 0.10)

1st Query fields names are : FundID,CapCallAmt,Distributions,NetAssetValue,Cals flow

Hope I'm explaining this correct.

[Rabbit]

Please use code tags.

I don't know how your query is set up but you only need to return one column of data with 5 rows of information. They need to be sorted in the following order.

Business start-up cost.
Positive cash flows reflecting income for four successive years.

You also don't increment your recordset in your code. You need to use rst.MoveNext to move from record to record. You also need to make sure your recordset returns 5 rows of data.

The guess parameter is optional.

[ADezii]

I will be greatful for your help. This function needes to be done in access.I can use the excel function but I can't convert it to access.

I'm trying the code below.

Function MyIRR(mygroupid, myguess) as double
Dim Values(4) as double' an array with 5 elements
Dim x
 
dim rs as dao.recordset
set rs = currentdb.openrecordset("Select * from Myquery where myid =" & mygroupid)
rs.movefirst ' should only be one record
 
for x = 0 to 4
Values(x) = rs(x+1)
next x
 
MyIRR = IRR(Values(),myguess)
 
rs.close
set rs = nothing
 
End Function
 

In this procedure I am using a query to grouped on the first column; (FundID).

I Created a second query using the group query as source and include all fields plus an extra column to calculate your IRR.
The extra column looks like:
IRRVal:MyIRR(FundID, 0.10)

1st Query fields names are : FundID,CapCallAmt,Distributions,NetAssetValue,Cals flow

Hope I'm explaining this correct.

1. Are you sure that the IRR() Function will handle an Array of values in the same manner as it handles a Range of contiguous Excel entries?
2. It is my understanding that the Optional guess Argument would only need to be implemented when only 2 years of Cash Flows exist. I could be wrong because Finance is not my Field, do you know the answer to this?
3. When I get a chance, I'll work on the coding. I figure with a worse case scenario, the data would have to copied over to an Excel Worksheet via Automation and the IRR() Function operate on the Cell Range as opposed to Recordset values.
4. The more information that you can provide us up front, the easier the task will be.
5. I'll be keeping in touch.

[ADezii]

This is new to me. I'm trying to calculate the IRR from a cash flow statement.
My database fields are Fund ID, Capital calls, Distributions,management fees, net asset value and total cash flow. The cash flow column is the total of (capital call+distributions+management fees-Net asset value) I need to add a column in my query to calaulate the IRR. How do I go about that?.

Please help

Thanks

I do believe that we have arrived at a solution, but first some Assumptions:

1. Table Name: tblTestIRR
2. Relevant Field Name containing Cash Flow values: [Cash Flow]

Important Notes:

1. Values must contain at least 1 Positive and 1 Negative value to calculate the Internal Rate of Return.
2. The sequence of the values entered into the Table will be used by IRR() to interpret the order of Cash Flows. Be sure to enter values in the proper sequence and do NOT apply a Sort Order on the SQL String used to create the Recordset.
3. If an Element of the Array passed to the IRR() Function contains Text, Boolean values, or Nulls, those values should be ignored.
4. Almost left without posting the code, should you have any questions, please feel free to ask.
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
   Dim intNumOfCashFlows As Integer, intCounter As Integer
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset("Select [Cash Flow] From tblTestIRR", dbOpenSnapshot)
    
   MyRS.MoveLast: MyRS.MoveFirst
    
   intNumOfCashFlows = MyRS.RecordCount
    
   'Not sure what Excel expects, ergo the Variant
   ReDim avarFlowValues(1 To intNumOfCashFlows) As Variant
    
   For intCounter = 1 To intNumOfCashFlows
     avarFlowValues(intCounter) = MyRS![Cash Flow]
     MyRS.MoveNext
   Next
    
   Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")
    
   'The IRR() Function will accept an Array of values, how lucky for us!
   MsgBox "The Internal Rate of Return is: " & Format$(objExcel.Application.IRR(avarFlowValues()), "Percent")
    
   objExcel.Quit
   Set objExcel = Nothing
   MyRS.Close
   Set MyRS = Nothing