By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Calculating the IRR in MS Access using a query

P: 4
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
Jan 3 '08 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What is IRR? How do you calculate IRR?
Jan 3 '08 #2

P: 4
it's a function to calculate the Internal Rate of Return.
Jan 3 '08 #3

jaxjagfan
Expert 100+
P: 254
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.
Jan 3 '08 #4

ADezii
Expert 5K+
P: 8,638
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:

Expand|Select|Wrap|Line Numbers
  1. ____________Column A    Column B
  2. Row 1       Data         Description
  3. Row 2       -70,000         Initial Cost of a business
  4. Row 3       12,000         Net Income for the first year
  5. Row 4       15,000         Net Income for the second year
  6. Row 5       18,000         Net Income for the third year
  7. Row 6       21,000         Net Income for the fourth year
  8. Row 7       26,000         Net Income for the fifth year
  9.  
Cell Formulas and their Yield (%):
Expand|Select|Wrap|Line Numbers
  1. =IRR(A2:A6) - would return the IRR after 4 years (-2%)
  2. =IRR(A2:A7) - would return the IRR after 5 years (9%)
  3. =IRR(A2:A4, -10%) - calculates the IRR after 2 years, notice an Optional guess (-10%) would be required for this calculation (-44%).
  4.  
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.
Jan 3 '08 #5

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. Function MyIRR(mygroupid, myguess) as double
  2. Dim Values(4) as double' an array with 5 elements
  3. Dim x
  4.  
  5. dim rs as dao.recordset
  6. set rs = currentdb.openrecordset("Select * from Myquery where myid =" & mygroupid)
  7. rs.movefirst ' should only be one record
  8.  
  9. for x = 0 to 4
  10. Values(x) = rs(x+1)
  11. next x
  12.  
  13. MyIRR = IRR(Values(),myguess)
  14.  
  15. rs.close
  16. set rs = nothing
  17.  
  18. End Function
  19.  
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.
Jan 3 '08 #6

Rabbit
Expert Mod 10K+
P: 12,366
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.
Jan 3 '08 #7

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Function MyIRR(mygroupid, myguess) as double
  2. Dim Values(4) as double' an array with 5 elements
  3. Dim x
  4.  
  5. dim rs as dao.recordset
  6. set rs = currentdb.openrecordset("Select * from Myquery where myid =" & mygroupid)
  7. rs.movefirst ' should only be one record
  8.  
  9. for x = 0 to 4
  10. Values(x) = rs(x+1)
  11. next x
  12.  
  13. MyIRR = IRR(Values(),myguess)
  14.  
  15. rs.close
  16. set rs = nothing
  17.  
  18. End Function
  19.  
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.
Jan 4 '08 #8

ADezii
Expert 5K+
P: 8,638
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    2. Dim intNumOfCashFlows As Integer, intCounter As Integer
    3.  
    4. Set MyDB = CurrentDb()
    5. Set MyRS = MyDB.OpenRecordset("Select [Cash Flow] From tblTestIRR", dbOpenSnapshot)
    6.  
    7. MyRS.MoveLast: MyRS.MoveFirst
    8.  
    9. intNumOfCashFlows = MyRS.RecordCount
    10.  
    11. 'Not sure what Excel expects, ergo the Variant
    12. ReDim avarFlowValues(1 To intNumOfCashFlows) As Variant
    13.  
    14. For intCounter = 1 To intNumOfCashFlows
    15.   avarFlowValues(intCounter) = MyRS![Cash Flow]
    16.   MyRS.MoveNext
    17. Next
    18.  
    19. Dim objExcel As Excel.Application
    20. Set objExcel = CreateObject("Excel.Application")
    21.  
    22. 'The IRR() Function will accept an Array of values, how lucky for us!
    23. MsgBox "The Internal Rate of Return is: " & Format$(objExcel.Application.IRR(avarFlowValues()), "Percent")
    24.  
    25. objExcel.Quit
    26. Set objExcel = Nothing
    27. MyRS.Close
    28. Set MyRS = Nothing
Jan 4 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.