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
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%
8 2988
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
-
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%
@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.
@FinanceJunky:
Thanks for the education in Finance. I assumed, obviously incorrectly, that the Excel IRR() Function would have returned reliable results.
zmbd 5,501
Expert Mod 4TB
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.
NeoPa 32,556
Expert Mod 16PB
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.
Thanks guys, this helped me quite a lot!
@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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Steve D |
last post by:
I've looked all over but can't find a solid answer. I've got a
function that runs from a View and when the function runs the first
time it is calculating a Temperature for a group of Formulas. ...
|
by: Maroon |
last post by:
Hi,
I want to write a standard java user defined function, like this
example:-
select db_fun("roll"), roll from student;
**db_fun() will work for all tables of the all databse
here db_fun is...
|
by: CapeCoder |
last post by:
I'd like to use the Excel.WorksheetFunction library to compute median
and percentiles in a user-defined function. I'd like to use the data
from the calling report as the function argument.
...
|
by: chreo |
last post by:
I have user-defined function in MSSQL which returns Table (with 10 columns)
(sorry for Polish names)
CREATE FUNCTION PACZKI_Z_AKCJI
(@AKCJA_ID int)
RETURNS TABLE
RETURN
SELECT TOP 100...
|
by: karthi |
last post by:
hi,
I need user defined function that converts string to float in c.
since the library function atof and strtod occupies large space in
my processor memory I can't use it in my code.
regards,...
|
by: JLupear |
last post by:
I have written a code (a translation of my first one) that is not working. I did a user defined function that is used on 'onsubmit'. It should total the value from the form selections and...
|
by: jeff_d_harper |
last post by:
I've run into a problem with text encoding in the Sqlite3 module. I
think it may be a bug. By default sqlite3 converts strings in the
database from UTF-8 to unicode. This conversion can be...
|
by: qwedster |
last post by:
Hola!
In the following code snippet, I am creating User Defined Function (T-SQL) programmatically into database from C# Code:
BTW, this code originally I wrote for creating StoredProcedure...
|
by: buyoy |
last post by:
I am creating a database for the purpose of calculating the length of time that had passed between two milestones.
--------------------------------------------------------
1 ...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |