# Calculate XIRR

How do I calculate an XIRR in  ACCESS (not IRR) without using EXCEL
LVL 46
###### Who is Participating?

Commented:
Here is a coded version of EXCEL's XIRR function. It has been tested against the example in Excel and is correct.

[For those reading this who don't know what XIRR, it's the internal rate of return for non-periodic payments. Access supports the IRR function, but this only works for periodic payments, like mortgages, etc. Excel uses the same iterative approach to solve the IRR function as is displayed herein]

The payment history is in a table name TableXIRR
The fields are PayDate and Payment

The function mimic's the Excel function

1. Does an iterative calculation to find the internal rate of return until % difference of last 2 residuals from the XIRR function is <10^-6
2. Takes earliest payment date as the first date
3. Will stop after 100 cycles

You can modify function by increasing the number of cycles, changing the precision of the estimate.

Errors are not reported. You may want to add one if the result doesn't converge to the desired precision.

Output is sent to the debug window. You'll want to change this too.

**************************************

Function XIRR(aXIRR As Variant, nRate As Double, nPayments As Integer) As Double
Dim i As Integer
XIRR = 0                                        ' residual of function
For i = 1 To nPayments
XIRR = XIRR + aXIRR(i, 2) / (1 + nRate) ^ (aXIRR(i, 1) / 365)
Next i

End Function

Private Sub CommandRunXIRR_Click()
Dim dbs As Database
Dim rstXIRR As Recordset
Dim aXIRR() As Double
Dim nPayments As Integer
Dim dFirstPayDate As Date
Dim i As Integer, j As Integer
Dim nRate As Double, nLastRate As Double, nRateStep As Double
Dim nXIRR As Double

Dim nResidual As Double, nLastResidual As Double

Set dbs = CurrentDb
Set rstXIRR = dbs.OpenRecordset("TableXIRR", dbOpenDynaset)

nPayments = DCount("PayDate", "TableXIRR")
ReDim aXIRR(nPayments, 3)
dFirstPayDate = DMin("PayDate", "TableXIRR")

nRate = 0.1                                                                     ' initial guess
nRateStep = 0.1                                                              ' arbitrary guess

i = 1
With rstXIRR
While Not .EOF
aXIRR(i, 1) = DateDiff("d", dFirstPayDate, !PayDate)
aXIRR(i, 2) = !Payment
i = i + 1
.MoveNext
Wend
End With

nResidual = 10
nLastResidual = 1
nLastRate = nRate
i = 0

While i < 100 And Abs((nLastResidual - nResidual) / nLastResidual) > 10 ^ -8
nLastResidual = nResidual
nResidual = XIRR(aXIRR, nRate, nPayments)
nLastRate = nRate
If nResidual >= 0 Then
nRate = nRate + nRateStep
Else
nRateStep = nRateStep / 2
nRate = nRate - nRateStep
End If
i = i + 1
Wend

nXIRR = nLastRate

Debug.Print "The internal rate of return is "; Format(nXIRR, "#.##%")

End Sub

This should do the trick for you.

Richard

0

Commented:
Access does not have this function and I'm not certain of you can add the Analysis ToolPak to Access as this is the product that contains the actual function.

A company, Entisoft, has a DLL which contains this (and other) financial functions.  Here is the link to their evaluation copy download page of this DLL:
http://www.entisoft.com/sharereg.htm

These microsoft links may be of some assistance:
http://msdn.microsoft.com/library/officedev/office97/SEC74.htm
http://support.microsoft.com/support/kb/articles/Q90/7/28.ASP

Regards,
Grant.
0

Commented:
You said "without using Excel" but I don't think you can do this in Access without writing a very complicated custom function.  I don't even see this type of function available in the Total Access Sourcebook that contains lots of custom functions for math.

You can use Excel functions from within Access  by using the Tools, References menu (available when you have a module open) and add the reference to the Excel object library.  Using this method is transparent; the user never sees Excel open and close.  Read the following article.  It also explains how to automate the process of opening the Analysis Took Pak add-in (which is required for the XIRR function) and then using the LCM (least common multiple) function.

http://support.microsoft.com/support/kb/articles/Q153/7/48.asp?LNG=ENG&SA=ALLKB&FR=0

The XIRR function will be more difficult since it uses arrays.
0

Author Commented:
Thank you mgratten but when I said without using EXCEL I meant just what you described.
I've already built the function you describe,but first of all it's heavy and second of all leaves EXCEL open in memory at the end.
0

Author Commented:
Thank you mgratten but when I said without using EXCEL I meant just what you described.
I've already built the function you describe,but first of all it's heavy and second of all leaves EXCEL open in memory at the end.
0

Commented:
Actually, it doesn't leave Excel open in the end if you use the .Quit command.  The KB article mentioned previously uses the command "obj.Quit" to close the Excel application.  In addition, you could add "Set obj = Nothing" to free the memory Access set aside for the Excel application object.
0

Commented:
I just KNEW a statistics expert would eventually jump into this one and write a custom function.  Good job Richard!
0

Commented:
mgrattan:

Were you able to get this function working in your application?
If you need any additional help with this, let me know. If it's ok, you might want to accept the comment as an answer.

Richard

Richard
0

Commented:
Richard,

The question wasn't mine.  It belonged to tbsgadi.  Hopefully, he will return and grant you the points!
0

Commented:
to mgrattan:

OOPS! That will teach me to review the question history before posting a comment.

Richard
0

Author Commented:
I'm on holiday at the moment but I'll test Repstein's code next week .If it works Well done you'll get the points!
0

Commented:
Sounds good.

I'd suggest testing it against data entered into Excel using the XIRR function there since I used the Excel formula listed in Help to generate the function I wrote. It should also give the same result as the IRR function in access (and excel) if you enter periodic payments, since XIRR is just a more generalized version of IRR.

Richard
0

Commented:
Here is another possible solution:

Public Function XIRR_VB(cashflow As Range, dates As Range, guess)

Dim k, m, i As Long
Dim calc, sumcalc, rate, calc2, sumcalc2, next_rate

i = cashflow.Count

ReDim arcash(i) As Double
ReDim ardate(i) As Date

next_rate = guess

Dim final As Boolean

Do While final = False
sumcalc = 0
sumcalc2 = 0
rate = next_rate

'This calculate the formula as described in the text.

For k = 1 To i
arcash(k) = cashflow.Cells(k, 1)
ardate(k) = dates.Cells(k, 1)
calc = arcash(k) / (1 + rate) ^ ((ardate(k) - ardate(1)) / 365)

sumcalc = sumcalc + calc
Next

If Abs(sumcalc) < 0.000001 Then Exit Do

'this calculate the sum of the differential with respect to "rate"

For m = 1 To i
arcash(m) = cashflow.Cells(m, 1)
ardate(m) = dates.Cells(m, 1)
calc2 = -1 * ((ardate(m) - ardate(1)) / 365) * arcash(m) * (1 + rate) ^ (-1 * ((ardate(m) - ardate(1)) / 365) - 1)
sumcalc2 = sumcalc2 + calc2
Next

'this give the next guess rate as per Newton-Rapson method

next_rate = rate - sumcalc / sumcalc2

Loop

XIRR_VB = rate

End Function

----------------------------------------
Here is a small explanation of the above function:
The function presented returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

Syntax
XIRR_VB(cashflows,dates,guess)

“cashflows”   is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. All succeeding payments are discounted based on a 365-day year.

“Dates” is a schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.

Internal rate of return formula
The internal rate of return is the rate that satisfy the following formula:

n
&#8721; [ Pi  / ( 1 + rate)^((di – d1) / 365) ]      = 0
i = 1

with

di = the ith, or last, payment date.

d1 = the 0th payment date.

Pi = the ith, or last, payment.

5. Solution

The above formula needs to be solved for the “rate”. The iterative technique that is used is the Newton-Raphson method.

Regards,
Grant.
0

Commented:

FYI, the iterative technique I used to create subsequent guesses is a version of Simplex. Simplex adjusts subsequent step sizes based on the differences generated by a function between successive guesses. If the difference is positive, the same step size is added to the next guess. If the difference is negative, then 1/2 the previous step size is subtracted from the previous guess. This rapidly converges as 1/2^n once the first value is reached where the difference between successive guesses is negative.

Simplex is applicable to a wide variety of problems that need to be solved iteratively, and is very "simple" to implement. It can be described as a "divide and conquer" approach.

Richard
0

Author Commented:
Well done.Have you got a web site ,with useful code or functions?
0

Commented:
Thanks. Its really ridiculous that Microsoft doesn't just include the Excel library functions in Access.

If  you need other missing functions, pulling down help in Excel is a reasonable place to look to see how they were coded. Unfortunately it sometimes is a bit vague as to exactly what to code. My approach is to do the test coding in Excel (usually on the spreadsheet), and then once the coded answer = excel answer, move the code over to access.

I don't have a web site with code posted. There are a number of books available with such code. The best is a series called "Numerical Recipes in X" where x= Fortran, Pascal, C

Hope this information is of further use to you.

Good luck.

Richard

0

Commented:
Richard,

I have converted your XIRR code to be used with Visual Basic .NET.  However, I am having getting the correct values to come out for IRR's that would have a negative value.  Do you know why this is?

Thanks,
Jason
0

Commented:
Richard,

I have converted your XIRR code to be used with Visual Basic .NET.  However, I am having getting the correct values to come out for IRR's that would have a negative value.  Do you know why this is?

Thanks,
Jason
0

Commented:
Richard,

I have converted your XIRR code to be used with Visual Basic .NET.  However, I am having getting the correct values to come out for IRR's that would have a negative value.  Do you know why this is?

Thanks,
Jason
0

Commented:
Richard,

I have converted your XIRR code to be used with Visual Basic .NET.  However, I am having getting the correct values to come out for IRR's that would have a negative value.  Do you know why this is?

Thanks,
Jason
0

Commented:
Richard,

I've tried to copy your code and use it in an Access form using VBA. I've got a table as you discribed. The table contains:
31-dec-00      173971,88
12-jan-01      20209,28
31-dec-04      -117523,65
in the fields paydate and payment.

The outcome in Excel is -11,79%

Your code is giving me 999,999999999999998 or something like that.

What did I do wrong?

eBaster
0

Commented:
eBaster:

The first item in a cash flow is usually the payment (negative number) followed by incomes (positive or negative).

I don't quite get your example.

The # you got is an overflow.  I would test the code you converted with a more usual example (initial payment then series of incomes) vs Excel. If that doesn't work, there is likely an error in the conversion.

If you step through the function, you should be able to see where it is blowing up. You might try a different initial guess.

Richard
0

Commented:
Dear Grant,

I've tried your solution and in several way it is good, but there are some troubels.
When in de formula:

calc = arcash(k) / (1 + rate) ^ ((ardate(k) - ardate(1)) / 365)

the next values occur:

1+rate = -0.409
((ardate(k) - ardate(1)) / 365) = 0.48

Then things are goimg wrong. Do you know why?
0

Commented:
Dear Grant,

I have found out that the base (1+rate) can't be less then zero. Have you find a way around this problem?

John
0

Commented: