IRR calculation as an user defined function of aggregate domain

[financedoubt]

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.

Function My_IRR(Dates() As Date, Amounts() As Double) AS Double
For
...
Do
End Function

[FinanceJunky]

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

Public Function tadEFFECT(ByVal rate As Double, ByVal compounding As Double)
If compounding = 0 Then
tadEFFECT = Exp(rate) - 1
Else
tadEFFECT = (1 + rate * compounding) ^ (1 / compounding) - 1
End If
End Function

Public Function tadPVIF(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double)
tadPVIF = (1 + tadEFFECT(rate, compounding)) ^ (-N)
End Function

Public Function tadPVIFbar(ByVal rate As Double, ByVal N As Double, ByVal compounding As Double)
If (compounding = 0) Then
tadPVIFbar = -N * tadPVIF(rate, N, compounding)
Else
tadPVIFbar = -N / compounding * tadPVIF(rate, N, compounding)
End If
End Function

Public Function tadXNPV(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double

Dim i As Long
Dim t As Double
Dim npv As Double

npv = 0

For i = 0 To UBound(ValuesArr)
t = (DatesArr(i) - DatesArr(0)) / 365
npv = npv + ValuesArr(i) * tadPVIF(rate, t, compounding)
Next i

tadXNPV = npv

End Function

Public Function tadXNPVbar(ByVal rate As Double, ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal compounding As Double) As Double

Dim rCell As Range
Dim i As Long
Dim t As Double
Dim npv As Double

npv = 0

For i = 0 To UBound(ValuesArr)
t = (DatesArr(i) - DatesArr(0)) / 365
npv = npv + ValuesArr(i) * tadPVIFbar(rate, t + compounding, compounding)
Next i

tadXNPVbar = npv

End Function

Public Function IsRealPower(ByRef DatesArr() As Long) As Boolean
Dim i As Long
Dim N As Double
Dim IsReal As Boolean

IsReal = False

For i = 1 To UBound(DatesArr)
N = (DatesArr(i) - DatesArr(0)) / 365
If (N - Int(N)) <> 0 Then
IsReal = True
Exit For
End If

Next i
IsRealPower = IsReal
End Function

Public Function EducatedGuessXIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double

Dim B As Double
Dim C As Double
Dim i As Long
Dim N As Double
Dim HPR As Double
Dim AHPY As Double

B = 0
C = 0

For i = 0 To UBound(ValuesArr)
If ValuesArr(i) > 0 Then
B = B + ValuesArr(i)
Else
C = C + Abs(ValuesArr(i))
End If
Next i

N = (DatesArr(UBound(DatesArr)) - DatesArr(0)) / 365

If ((B <> 0) And (C <> 0)) Then
HPR = B / C
AHPY = HPR ^ (compounding / N) - 1
AHPY = AHPY / compounding
EducatedGuessXIRR = AHPY
Else
EducatedGuessXIRR = guess
End If

End Function

Public Function EducatedGuessIRR(ByRef ValuesArr() As Double, ByRef DatesArr() As Long, ByVal guess As Double, ByVal compounding As Double) As Double

Dim B As Double
Dim C As Double
Dim i As Long
Dim HPR As Double
Dim HPY As Double

B = 0
C = 0

For i = 0 To UBound(ValuesArr)
If ValuesArr(i) > 0 Then
B = B + ValuesArr(i)
Else
C = C + Abs(ValuesArr(i))
End If
Next i

If ((B <> 0) And (C <> 0)) Then
HPR = B / C
HPY = HPR - 1
HPY = HPY / compounding
EducatedGuessIRR = HPY
Else
EducatedGuessIRR = guess
End If

End Function

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

Dim f As Double
Dim fbar As Double
Dim x As Double
Dim x0 As Double
Dim i As Integer
Dim found As Integer
Dim rCell As Range
Dim ValuesArr() As Double
Dim DatesArr() As Long
ReDim ValuesArr(Values.Count - 1)
ReDim DatesArr(Values.Count - 1)

i = 0
For Each rCell In Values.Cells
ValuesArr(i) = rCell.Value
i = i + 1
Next rCell

i = 0
For Each rCell In Dates.Cells
DatesArr(i) = rCell.Value
i = i + 1
Next rCell

found = 0
i = 1

If guess = 0.1 Then
If IsRealPower(DatesArr()) Then
x0 = EducatedGuessXIRR(ValuesArr(), DatesArr(), guess, compounding)
Else
x0 = EducatedGuessIRR(ValuesArr(), DatesArr(), guess, compounding)
End If
Else
x0 = guess
End If

Do While (i < 100)

f = tadXNPV(x0, ValuesArr(), DatesArr(), compounding)
fbar = tadXNPVbar(x0, ValuesArr(), DatesArr(), compounding)

If (fbar = 0) Then
tadXIRR = (0) ^ (-1)
Else
x = x0 - f / fbar
End If

If (Abs(x - x0) < 0.000001) Then
found = 1
Exit Do
End If

x0 = x
i = i + 1

Loop

If (found = 1) Then
tadXIRR = x
Else
tadXIRR = (-1) ^ (0.5)
End If

End Function

[ADezii]

Since Excel is so adept at performing these types of Calculations, why not let it perform the bulk of the work, as in:

'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:

Internal rate of Return: -4.78%

[FinanceJunky]

@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.

[ADezii]

@FinanceJunky:
Thanks for the education in Finance. I assumed, obviously incorrectly, that the Excel IRR() Function would have returned reliable results.

[zmbd]

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.e du/.../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.

[NeoPa]

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 :

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.

[financedoubt]

Thanks guys, this helped me quite a lot!

[twinnyfo]

@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!