I believe I've found an Access bug...can anyone confirm?

[James]

For the past month I've been working on a solution for the calculation of
internal rate of return in Access with VBA with the help of a few of you
(thanks!). Everything seems to calculate fine but only under certain
conditions. For instance, if you use the IRR function in Access your term
can not be more than 24 months or it simply won't return an answer. For
accessing the Excel function from Access, you can't go past 48 months or it
too won't return a result. I originally setup two boxes on a form to show me
the results side by side for both calculations and when they did return an
answer they both return the proper answers.

So here is the code. I've placed some test information at the top of the
VBA code for you to experiment with and see if you can observe the same
issue(s). I've also included what the correct answer should be as
calculated directly from Excel 2007. Normally, there would be variables
intermingled in the code instead of just numbers as indicated below. The
Access calculation is at the top and the near duplicate Excel code with late
binding is towards the bottom.

I'd GREATLY appreciate any comments as if this is a Microsoft issue or
something else. (Environment is Vista64, Excel/Access 2007 with latest
service packs).

Thank you very much for your assistance!!


'When testing for 24 months use IRRValuex(0) = -18468.83, IRRCashFlowx =
957.55, IRRFinalCashFlow = 100, Termx = 24
'=============>>Correct IRR for 24 months should be: 18.64%
'When testing for 36 months use IRRValuex(0) = -18917.23,
IRRCashFlowx = 657.55, IRRFinalCashFlow = 100, Termx = 36
'=============>>Correct IRR for 36 months should be: 13.83%
'When testing for 42 months use IRRValuex(0) = -19044.14,
IRRCashFlowx = 572.64, IRRFinalCashFlow = 100, Termx = 42
'=============>>Correct IRR for 42 months should be: 12.66%
'When testing for 48 months use IRRValuex(0) = -19139.93,
IRRCashFlowx = 508.49, IRRFinalCashFlow = 100, Termx = 48
'=============>>Correct IRR for 48 months should be: 11.76%
'When testing for 54 months use IRRValuex(0) = -19214.71,
IRRCashFlowx = 458.49, IRRFinalCashFlow = 100, Termx = 54
'=============>>Correct IRR for 54 months should be: 11.06%
'When testing for 60 months use IRRValuex(0) = -19273.93,
IRRCashFlowx = 418.87, IRRFinalCashFlow = 100, Termx = 60
'=============>>Correct IRR for 60 months should be: 10.56%
'When testing for 72 months use IRRValuex(0) = -19362.77,
IRRCashFlowx = 359.43, IRRFinalCashFlow = 100, Termx = 72
'=============>>Correct IRR for 72 months should be: 9.80%



'===========================================================================
'Access IRR Calculation

Dim GuessA, FmtA, IRRFinalCashFlowA, CountA, IRRCashFlowA, TermA
Dim IngSizeA As Long
Dim IRRValuesA() As Double ' Set up array
TermA = 24 ' Set term of loan -- Normally this would be a variable
from a form--used for testing for next row
IngSizeA = TermA + 1 ' Set array size
ReDim IRRValuesA(IngSizeA)
IRRCashFlowA = 957.55
IRRFinalCashFlowA = 100
GuessA = 0.125 ' Guess starts at 12.5 percent
FmtA = "#0.00" ' Define percentage format
CountA = 0 ' Zero out the count amount for the cashflow
assignment loop
'IRRValuesA(0) is the first value of the array and is always a
negative value
'IRRCashFlowA are all of the cashflows from 1 to one minus the term
of the loan -- if the term of the
' loan is 48 then it would be the values from 1 to
47
'IRRFinalCashFlowA is the last cashflow of the array which is always
going to be a sum of other variables
' that is less than a normal cashflow
IRRValuesA(0) = -18468.83
Do While CountA <= (TermA - 2) ' compensates for starting at 0
instead of 1
CountA = CountA + 1
IRRValuesA(CountA) = IRRCashFlowA
Loop
IRRValuesA(TermA - 1) = IRRFinalCashFlowA ' compensates for starting
at 0 instead of 1

'Have Access 2007 Calculate the Internal Rate of Return (IRR)
Me.txtACCESSIRR = IRR(IRRValuesA(), GuessA) * 12



'===========================================================================
'Excel IRR Calculation

Dim GuessE, FmtE, IRRFinalCashFlowE, CountE, IRRCashFlowE, TermE
Dim IngSizeE As Long
Dim IRRValuesE() As Variant ' Set up array.
TermE = 24 ' Set term of loan -- Normally this would be a variable
from a form--used for testing for next row
IngSizeE = TermE + 1 ' Set array size.
ReDim IRRValuesE(IngSizeE)
IRRCashFlowE = 957.55
IRRFinalCashFlowE = 100
GuessE = 0.125 ' Guess starts at 12.5 percent.
FmtE = "#0.00" ' Define percentage format.
CountE = 0 ' Zero out the count amount.
'IRRValuesE(0) is the first value of the array and is always a
negative value
'IRRCashFlowE are all of the cashflows from 1 to one minus the term
of the loan -- if the term of the
' loan is 48 then it would be the values from 1 to
47
'IRRFinalCashFlowE is the last cashflow of the array which is always
going to be a sum of other variables
' that is less than a normal cashflow
IRRValuesE(0) = -18468.83
Do While CountE <= (TermE - 2) ' compensates for starting at 0
instead of 1
CountE = CountE + 1
IRRValuesE(CountE) = IRRCashFlowE
Loop
IRRValuesE(TermE - 1) = IRRFinalCashFlowE ' compensates for starting
at 0 instead of 1

'Have Excel 2007 Calculate the Internal Rate of Return (IRR) with
late binding so the Excel version doesn't matter
Dim IRRref As Reference
'Late Binding
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
On Error Resume Next
Set IRRref = References!Excel
If Err.Number = 0 Then
References.Remove IRRref
ElseIf Err.Number <> 9 _
Then 'Subscript out of range meaning not reference not
found
MsgBox Err.Description
Exit Sub
End If
Me.txtEXCELIRR = objXL.IRR(IRRValuesE(), GuessE) * 12
objXL.Quit
Set objXL = Nothing

 

[david]

TermA = 24
ReDim IRRValuesA(0 To TermA)

IRRCashFlowA = 957.55
IRRFinalCashFlowA = 100
GuessA = 0.125 ' Guess starts at 12.5 percent

IRRValuesA(0) = -18468.83

For CountA = 1 To TermA - 1
IRRValuesA(CountA) = 957.55
Next CountA

IRRValuesA(TermA) = 100


Debug.Print IRR(IRRValuesA(), GuessA) * 12

0.18643636986208

(david)

 

[david]

In VBA and Pascal, if you find your self writing
" compensates for starting at 0 "
then you've done it wrong, and you can re-write it in
a form that is more intelligible and less error prone.

The from-to array declaration syntax is such an improvement
that you would think that it would make it into other languages,
but it was a religious issue.

(david)

 

[James]

David,

Thank you for your input. I do like your simplified code much better,
however, there are still issues. I receive the same identical answer that
you do for the 24 month term variables, however, if you try anything above 24
then Access refuses to return an answer. In fact, looking in the immediate
window with your print.debug line, nothing is returned--just for the 24 month
information. Of course in the real world, one could have a loan for much
longer than just 24 payments. Any further ideas?

Thanks!!

James

 

[david]

longer than just 24 payments. Any further ideas?

It's not actually restricted to 24 payments. It just fails if it
can't get adequate convergence. I don't know if it's different
in 2003-2007, but isn't the default guess 10%? Try that
instead of 12.5% -- I think the convergence routine is
optimised for starting at the default value. Also, it doesn't
converge well if the PV of the trailing payments doesn't
approach zero. I can easily get 60 payments, but it's a
bit hit-or-miss.

Anyway, we wrote our own. In the finance area, having an
output that is "exactly the same" as Excel is important, and
you can't do that using the VBA function: it's only accurate
to something like .000001%, which means the last couple
of numbers are different from the Excel value. The last couple
of digits are meaningless to Treasury people, but not to Accountants.
When you are dealing with billions, it comes to a couple of cents,
and Accountants look for figures that match exactly.

It's not difficult to do, you just calculate the NPR and adjust
the IRR until the NPR comes out to zero.

(david)

 

[James]

David,

Thank you again for your insight. You are correct, the default guess is 10%
(.10) and I played with different percentages and scenarios before I
accidentally came up with the improbable answer; set guess equal to zero and
use the Excel function from Access. The answer came out perfect every time.
I'm not exactly sure why this works but for the record, it does. Here is the
snippet that I finally ended up going with--note the zero guess, fourth line
from the bottom. Thanks again for your help and thoughts! jb


Private Sub CalculateIRR()

'------------------------------------------------------
'IRR Calculation from Access through Excel
'STEP 1 - Setup the array of cash flows
'------------------------------------------------------
Dim IRRFinalCashFlow, Count, IRRCashFlow, Term
Dim IngSize As Long
Dim IRRValues() As Variant ' Set up array.
Term = Me.txtInputTerm ' Set term of loan
IngSize = Term + 1 ' Set array size.
ReDim IRRValues(IngSize)
'IRRValues(0) is the first value of the array and is always a
negative value
'IRRCashFlow is all of the cashflows from 1 to one minus the term of
the loan -- if the term of the
' loan is 48 then it would be the values from 1 to 47
'IRRFinalCashFlow is the last cashflow of the array which is always
going to be a sum of other variables
' that is less than a normal cashflow
IRRValues(0) = (Me.txtInputCapitalizedCost * (-1)) +
Me.txtInputAdminFee + Me.txtCIFinalBaseMonthlyPayment _
+ Me.txtInputSecurityDeposit + Me.txtCIProRataTotal
IRRCashFlow = (Me.txtCIFinalTotalMonthlyPayment1 / (1 +
Me.txtInputInterestRate))
IRRFinalCashFlow = Me.txtInputResidual - Me.txtInputSecurityDeposit
+ Me.txtCIFinalBaseMonthlyPayment
For Count = 1 To (Term - 2)
IRRValues(Count) = IRRCashFlow
Next Count
IRRValues(Term - 1) = IRRFinalCashFlow

'---------------------------------------------------------
'Excel calculation of IRR
'STEP 2 - Have Excel 2007 Calculate the Internal Rate of
'Return (IRR) with late binding so the Excel version doesn't matter
'---------------------------------------------------------
Dim IRRref As Reference
'Late Binding
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
On Error Resume Next
Set IRRref = References!Excel
If Err.Number = 0 Then
References.Remove IRRref
ElseIf Err.Number <> 9 _
Then 'Subscript out of range meaning not reference not
found
MsgBox Err.Description
Exit Sub
End If
Me.txtEXCELIRR = objXL.IRR(IRRValues(), 0) * 12 ' <-- Use zero as
guess
objXL.Quit
Set objXL = Nothing
End Sub

 

[david]

:~)

Set objXL = CreateObject("Excel.Application")

It's amazing what the world has come to -- you have no idea
how difficult it was to make that work on Windows 98 and
(DDE on) Windows 3.11

accidentally came up with the improbable answer; set guess equal to zero
and use the Excel function from Access. The answer came out perfect

Good to know.

(david)

 

[VitaminAnalystLG]

I've finally found a discussion that addresses my problem.........I want to
calculate IRR for a 5 Year Term in MS Access Report. The only issue is that I
am not a programmer and need some help with Visual Basic. My data is setup to
Calculate the annual Contribution (Cash Inflow) of a Retail Site and combine
with the Acquired Value (Investment/Outflow) all within the Report. Here's an
example of what my data looks like in Report:

2009 Contribution - $100,000; Acquired Value ($900,000)
2008 Contribution - $120,000; Acquired Value ($900,000)
2007 Contribution - $130,000; Acquired Value ($900,000)
2006 Contribution - $200,000; Acquired Value ($900,000)

Any suggestions on how to adapt your Module to my data?

Thanks,

Larry Gray