The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

How do I calculate an XIRR in ACCESS (not IRR) without using EXCEL

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.

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.

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.

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.

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

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

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

Richard

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

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,gu

“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

∑ [ 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.

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

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

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

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

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

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

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

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

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?

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

John

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

[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("TableXI

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