By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,933 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
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
  1. Function My_IRR(Dates() As Date, Amounts() As Double) AS Double
  2. For
  3. ...
  4. Do
  5. End Function
Jan 27 '14 #1

✓ answered by ADezii

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%

Share this Question
Share on Google+
8 Replies


P: 2
Well there are more than one way to program an XIRR function depending on how you define the underlying IRR equation. But all such methods of finding an IRR are mere approximation of the actual rate as it requires numerical methods to solve for the interest rate in the IRR equation.

I could offer help in writing a VBA based XIRR function but since my expertise are in Excel based VBA thus you would have to alter the code to adjust for input from the table rather than from a worksheet.

The XIRR function may not always find a rate thus in certain cases you would have to select a guess just as the Excel XIRR function

However, the cash flows provide enough information based on which one can have the programming code select a guess thus eliminating the need for wild guessing when a rate is not found using the default guess of 10%.

Expand|Select|Wrap|Line Numbers
  1. Public Function tadEFFECT(ByVal rate As Double, ByVal compounding As Double)
  2. If compounding = 0 Then
  3. tadEFFECT = Exp(rate) - 1
  4. Else
  5. tadEFFECT = (1 + rate * compounding) ^ (1 / compounding) - 1
  6. End If
  7. End Function
  8.  
  9. Public Function tadPVIF(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double)
  10. tadPVIF = (1 + tadEFFECT(rate, compounding)) ^ (-N)
  11. End Function
  12.  
  13. Public Function tadPVIFbar(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double)
  14. If (compounding = 0) Then
  15. tadPVIFbar = -N * tadPVIF(rate, N, compounding)
  16. Else
  17. tadPVIFbar = -N / compounding * tadPVIF(rate, N, compounding)
  18. End If
  19. End Function
  20.  
  21. Public Function tadXNPV(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double
  22.  
  23. Dim i As Long
  24. Dim t As Double
  25. Dim npv As Double
  26.  
  27. npv = 0
  28.  
  29. For i = 0 To UBound(ValuesArr)
  30. t = (DatesArr(i) - DatesArr(0)) / 365
  31. npv = npv + ValuesArr(i) * tadPVIF(rate, t, compounding)
  32. Next i
  33.  
  34. tadXNPV = npv
  35.  
  36. End Function
  37.  
  38. Public Function tadXNPVbar(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double
  39.  
  40. Dim rCell As Range
  41. Dim i As Long
  42. Dim t As Double
  43. Dim npv As Double
  44.  
  45. npv = 0
  46.  
  47. For i = 0 To UBound(ValuesArr)
  48. t = (DatesArr(i) - DatesArr(0)) / 365
  49. npv = npv + ValuesArr(i) * tadPVIFbar(rate, t + compounding, compounding)
  50. Next i
  51.  
  52. tadXNPVbar = npv
  53.  
  54. End Function
  55.  
  56. Public Function IsRealPower(ByRef DatesArr() As Long) As Boolean
  57. Dim i As Long
  58. Dim N As Double
  59. Dim IsReal As Boolean
  60.  
  61. IsReal = False
  62.  
  63. For i = 1 To UBound(DatesArr)
  64. N = (DatesArr(i) - DatesArr(0)) / 365
  65. If (N - Int(N)) <> 0 Then
  66. IsReal = True
  67. Exit For
  68. End If
  69.  
  70. Next i
  71. IsRealPower = IsReal
  72. End Function
  73.  
  74. Public Function EducatedGuessXIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double
  75.  
  76. Dim B As Double
  77. Dim C As Double
  78. Dim i As Long
  79. Dim N As Double
  80. Dim HPR As Double
  81. Dim AHPY As Double
  82.  
  83. B = 0
  84. C = 0
  85.  
  86. For i = 0 To UBound(ValuesArr)
  87. If ValuesArr(i) > 0 Then
  88. B = B + ValuesArr(i)
  89. Else
  90. C = C + Abs(ValuesArr(i))
  91. End If
  92. Next i
  93.  
  94. N = (DatesArr(UBound(DatesArr)) - DatesArr(0)) / 365
  95.  
  96. If ((B <> 0) And (C <> 0)) Then
  97. HPR = B / C
  98. AHPY = HPR ^ (compounding / N) - 1
  99. AHPY = AHPY / compounding
  100. EducatedGuessXIRR = AHPY
  101. Else
  102. EducatedGuessXIRR = guess
  103. End If
  104.  
  105. End Function
  106.  
  107. Public Function EducatedGuessIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double
  108.  
  109. Dim B As Double
  110. Dim C As Double
  111. Dim i As Long
  112. Dim HPR As Double
  113. Dim HPY As Double
  114.  
  115. B = 0
  116. C = 0
  117.  
  118. For i = 0 To UBound(ValuesArr)
  119. If ValuesArr(i) > 0 Then
  120. B = B + ValuesArr(i)
  121. Else
  122. C = C + Abs(ValuesArr(i))
  123. End If
  124. Next i
  125.  
  126. If ((B <> 0) And (C <> 0)) Then
  127. HPR = B / C
  128. HPY = HPR - 1
  129. HPY = HPY / compounding
  130. EducatedGuessIRR = HPY
  131. Else
  132. EducatedGuessIRR = guess
  133. End If
  134.  
  135. End Function
  136.  
  137. Public Function tadXIRR(ByVal Values As Range, ByVal Dates As Range, Optional ByRef guess As Double = 0.1, Optional ByRef compounding As Double = 1#) As Double
  138.  
  139. Dim f As Double
  140. Dim fbar As Double
  141. Dim x As Double
  142. Dim x0 As Double
  143. Dim i As Integer
  144. Dim found As Integer
  145. Dim rCell As Range
  146. Dim ValuesArr() As Double
  147. Dim DatesArr() As Long
  148. ReDim ValuesArr(Values.Count - 1)
  149. ReDim DatesArr(Values.Count - 1)
  150.  
  151. i = 0
  152. For Each rCell In Values.Cells
  153. ValuesArr(i) = rCell.Value
  154. i = i + 1
  155. Next rCell
  156.  
  157. i = 0
  158. For Each rCell In Dates.Cells
  159. DatesArr(i) = rCell.Value
  160. i = i + 1
  161. Next rCell
  162.  
  163. found = 0
  164. i = 1
  165.  
  166. If guess = 0.1 Then
  167. If IsRealPower(DatesArr()) Then
  168. x0 = EducatedGuessXIRR(ValuesArr(), DatesArr(), guess, compounding)
  169. Else
  170. x0 = EducatedGuessIRR(ValuesArr(), DatesArr(), guess, compounding)
  171. End If
  172. Else
  173. x0 = guess
  174. End If
  175.  
  176. Do While (i < 100)
  177.  
  178. f = tadXNPV(x0, ValuesArr(), DatesArr(), compounding)
  179. fbar = tadXNPVbar(x0, ValuesArr(), DatesArr(), compounding)
  180.  
  181. If (fbar = 0) Then
  182. tadXIRR = (0) ^ (-1)
  183. Else
  184. x = x0 - f / fbar
  185. End If
  186.  
  187. If (Abs(x - x0) < 0.000001) Then
  188. found = 1
  189. Exit Do
  190. End If
  191.  
  192. x0 = x
  193. i = i + 1
  194.  
  195. Loop
  196.  
  197. If (found = 1) Then
  198. tadXIRR = x
  199. Else
  200. tadXIRR = (-1) ^ (0.5)
  201. End If
  202.  
  203. End Function
  204.  
Jan 27 '14 #2

ADezii
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
  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%
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

ADezii
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

zmbd
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 Jelen
The 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

NeoPa
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
  1. 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

twinnyfo
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

Post your reply

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