Solved

Calculate XIRR

Posted on 2000-04-16
25
5,239 Views
Last Modified: 2012-08-13
How do I calculate an XIRR in  ACCESS (not IRR) without using EXCEL
0
Comment
Question by:tbsgadi
  • 7
  • 4
  • 4
  • +4
25 Comments
 
LVL 4

Expert Comment

by:gcs001
Comment Utility
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
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
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
 
LVL 46

Author Comment

by:tbsgadi
Comment Utility
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
 
LVL 46

Author Comment

by:tbsgadi
Comment Utility
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
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
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
 
LVL 2

Accepted Solution

by:
repstein earned 200 total points
Comment Utility
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
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
I just KNEW a statistics expert would eventually jump into this one and write a custom function.  Good job Richard!
0
 
LVL 2

Expert Comment

by:repstein
Comment Utility
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
 
LVL 14

Expert Comment

by:mgrattan
Comment Utility
Richard,

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

Expert Comment

by:repstein
Comment Utility
to mgrattan:

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

Richard
0
 
LVL 46

Author Comment

by:tbsgadi
Comment Utility
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
 
LVL 2

Expert Comment

by:repstein
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 4

Expert Comment

by:gcs001
Comment Utility
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
 
LVL 2

Expert Comment

by:repstein
Comment Utility
tbsgadi:

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
 
LVL 46

Author Comment

by:tbsgadi
Comment Utility
Well done.Have you got a web site ,with useful code or functions?
0
 
LVL 2

Expert Comment

by:repstein
Comment Utility
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
 

Expert Comment

by:jk2003
Comment Utility
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
 

Expert Comment

by:jk2003
Comment Utility
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
 

Expert Comment

by:jk2003
Comment Utility
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
 

Expert Comment

by:jk2003
Comment Utility
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
 

Expert Comment

by:jkruijt
Comment Utility
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
 
LVL 2

Expert Comment

by:repstein
Comment Utility
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
 

Expert Comment

by:jkruijt
Comment Utility
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
 

Expert Comment

by:jkruijt
Comment Utility
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
 
LVL 2

Expert Comment

by:yatin_81
Comment Utility
can i have the vb .net code of XIRR?  Please help. Its most urgent to me.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now