434,933 Members | 1,243 Online
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

# IRR calculation as an user defined function of aggregate domain

 P: 2 Dear users, I'm trying to build a custom function "My_IRR()" that calculates IRR of set of dates and amounts that belong to a table. Dates are located in the field "Dates" and the amounts are located in the field "Amounts" - Both from the table called "tblFCF". It also takes the IRR value from a list that belongs to another table called "tblIRR". The field is called "dblIRR". The idea of this is to emulate XIRR function of Excel. Expand|Select|Wrap|Line Numbers Function My_IRR(Dates() As Date, Amounts() As Double) AS Double For ... Do End Function Jan 27 '14 #1

Since Excel is so adept at performing these types of Calculations, why not let it perform the bulk of the work, as in:
Expand|Select|Wrap|Line Numbers
1. 'Must set a reference to the Microsoft Excel XX.X Object Library
2. Dim appExcel As New Excel.Application
3.
4. Dim varData As Variant
5.
6. 'Load Data into an Array
7. varData = Array(-50000, 12000, 15000, 18000)
8.
9. Debug.Print "Internal rate of Return: " & _
10.             Format(appExcel.worksheetfunction.IRR(varData), "Percent")
OUTPUT:
Expand|Select|Wrap|Line Numbers
1. Internal rate of Return: -4.78%

8 Replies

 Expert 5K+ P: 8,638 Since Excel is so adept at performing these types of Calculations, why not let it perform the bulk of the work, as in: Expand|Select|Wrap|Line Numbers 'Must set a reference to the Microsoft Excel XX.X Object Library Dim appExcel As New Excel.Application   Dim varData As Variant   'Load Data into an Array varData = Array(-50000, 12000, 15000, 18000)   Debug.Print "Internal rate of Return: " & _             Format(appExcel.worksheetfunction.IRR(varData), "Percent") OUTPUT: Expand|Select|Wrap|Line Numbers Internal rate of Return: -4.78% Jan 27 '14 #3

 P: 2 @ADezii The OP is seeking a XIRR function which I suppose may just be called by using the Excel Application Object as you have done for IRR However keep in view that Excel's implementation of IRR and XIRR functions are prone to errors and may not always return the expected values regardless of the guess rate being used. This fault in Excel IRR and XIRR functions is attributed largely to the programmers who coded such function as these are technical people without any knowledge of financial math that is required to understand the IRR and XIRR calculation. On occasions, I have came across Microsoft personnel (who participate on Excel newsgroups and Microsoft Answers community and they give the assertion that any IRR or XIRR less than -100% is a false IRR. That statement is incorrect thus leading to limited IRR and XIRR functionality in Excel Look at this discussion on Microsoft Answers http://answers.microsoft.com/en-us/o...b-a4423afd5553 Excel IRR reports two positive IRR values for the last two data sets whereas the actual IRR is close to -300%. If you tried to use a guess close to -250% the IRR function for these two data sets returns #VALUE! error thus confirming my concerns that anyone who thinks they write the best of code without knowing anything about the subject matter results in program code that produces incorrect results I could just imagine how many CFOs of corporations were given IRR values from Excel by financial analysts that were incorrect and the CFO took the results as correct thus costing the company money out of its pockets. Jan 27 '14 #4

 Expert 5K+ P: 8,638 @FinanceJunky: Thanks for the education in Finance. I assumed, obviously incorrectly, that the Excel IRR() Function would have returned reliable results. Jan 28 '14 #5

 Expert Mod 5K+ P: 5,397 FinanceJunky: You may have overlooked the fact that Microsoft revamped the IRR, MIRR, and other related functions in ACC2010 ---------- Microsoft Excel 2010 In Depth By Bill JelenThe algorithm behind the IRR function is new and more accurate in Excel 2010. Be aware that Excel 2007 and Excel 2010 might produce different results ---------- Then there is this: ...[PDF]Function Improvements in Microsoft Office Excel 2010 www.kelley.iu.edu/.../Excel%20... and many other documents. As much as I like to poke fun at MS for things, in this case one should be giving credit where credit is due and not assume that nothing has changed. Indeed, since the huge scandal with the financials that occurred here in the recent past (mind you, MS took it on the chin some because of the poor programming skills and assumptions made by end users) MS has appeared to have taken on some consultants in the finance industry (I can only say appears as I don't personally know anything about the internals of MS - but one can infer a great deal from the news reports). Therefore, before dismissing ADezii's treatment of the problem, perhaps it should be verified which version of the formulas are being used - because of this, I am going to reset the best answer. Jan 28 '14 #6

 Expert Mod 15k+ P: 31,492 In view of the fact that the OP (FinanceDoubt) has stipulated in their question that they are looking for something to emulate the `XIRR()` function of Excel I have set ADezii's post as Best Answer. Many people are unaware that Excel functions can be called from other Office applications if the library is referenced. In fact, opening an instance of Excel is also unnecessary. The code can be invoked simply by using : Expand|Select|Wrap|Line Numbers Excel.WorksheetFunction.Irr() I believe the reference is still required though. I am also conscious of the contribution offered by FinanceJunky. Importantly it raised issues that many will also benefit from knowing about - particularly those who still use versions older than 2010. I hope that they will continue to offer help and support for the OP if they (the OP) ever responds indicating they are still using an older version and would like help, now they understand the situation better, in designing a function that handles the arithmetic correctly, and are now less interested in using the function as provided by (an older version of) Excel. Jan 30 '14 #7

 P: 2 Thanks guys, this helped me quite a lot! Feb 7 '14 #8

 Expert Mod 2.5K+ P: 3,284 @NeoPa, Concerning your statement in Post #7, Many people are unaware that Excel functions can be called from other Office applications if the library is referenced I will admit that I was totally unaware of this. Another tool for my kit! Thanks much to all! I enjoyed reading this entire thread and understanding a little more about something way outside my area of expertise! Jun 25 '14 #9