Trouble with MIRR

[bytreeide]

This is probably a pretty simple answer, but I have been having some trouble with Access' built in Financial Function: MIRR



I have the following code:

Private Sub ROI_Button_Click()
Static CFA(6) As Double 'Define array
CFA(0) = -14875 'Initial Investment
CFA(1) = 4635 'Positive Cash flows for five years
CFA(2) = 6180
CFA(3) = 6180
CFA(4) = 6180
CFA(5) = 6180
Me.T6ROI = MIRR(CFA(), 0.07, 0.09) 'Calculated Modified Internal Rate of Return

End Sub

The result that I get is 16.89%

When I do the same thing in Excell:

=MIRR({-14875, 4635, 6180, 6180, 6180, 6180}, 0.07, 0.09)

I get 18.53%

I have also alternatively tried to write the equation out for the MIRR function

Private Sub ROI_Button_Click()
Dim I As Long
Dim F As Long
Dim R As Long
Dim Y1 As Long
Dim Y2 As Long
Dim Y3 As Long
Dim Y4 As Long
Dim Y5 As Long
F = 1.07 'Loan Rate
R = 1.09 'Reinvest Rate
I = -14875 'Initial Investment
Y1 = 4635 'Positive Cash flows for five years
Y2 = 6180
Y3 = 6180
Y4 = 6180
Y5 = 6180
Me.T6ROI = (((((Y1 / (R ^ 1)) + (Y2 / (R ^ 2)) + (Y3 / (R ^ 3)) + (Y4 / (R ^ 4)) + (Y5 / (R ^ 5))) * (R ^ 5)) _
/ (I / F * F)) ^ (1 / 5)) - 1
End Sub

I get 14.56%

What am I missing? What have I done wrong?

Thanks

bytreeide

[June7]

Similar to my experience with programming matrix multiplication.

I tested 2 methods (one using Excel functions and one not) in Access against the Excel functions in worksheet. 3 different results.

[bytreeide]

I just feel like something is wrong with my code somewhere.

My instinct is to trust the Excel calculated number.

I would like to make them all equal the same thing, any insights?

[June7]

None. I never did get the various matrix calc methods to agree.

I wondered if invoking Excel function would return a 4th result. I am trying but can't get VBA to accept the {} characters.

Excel.WorksheetFunction.MIRR({-14875, 4635, 6180, 6180, 6180, 6180}, 0.07, 0.09)

And this returns the same as not invoking WorksheetFunction

Excel.WorksheetFunction.MIRR(CFA(), 0.07, 0.09)

[bytreeide]

I figured out what I had wrong. I think I was informed incorrectly on one of the help site I looked at. I was defining my array of entries as ARRAY(n) and then assigning values to my array starting with ARRAY(0) and ending with ARRAY(n-1). This yield an inconsistent answer from what I had been getting in Excel (which I considered to be correct, as I understand how excel works better than access).

The solution that I found is to have an array defined as ARRAY(n) and assign values from ARRAY(0) to ARRAY(n). It would appear that the way that I was doing it before I was automatically throwing a "zero" value in at the end of my array and that was causing a problem.

So lesson learned, when using arrays, if you want to have "n" values stored in the array, then your array should be defined as "n+1".

I hope this helps someone.

bytreeide

[June7]

I don't work with arrays often and I always have to relearn some things when I do. I changed the array declaration to:

Static CFA(5) As Double

And that will return 18.53%

That's because the array is 0 based (the default). Can change the array to 1 based with code in module header:

Option Base 1

Then the (6) declaration would work with indexes 1 through 6, not using 0.

[orange]

I agree with June ---CFA(5) has 6 elements

Sub AWF_MIRR()
Static CFA(5) As Double 'Define array
CFA(0) = -14875 'Initial Investment
CFA(1) = 4635 'Positive Cash flows for five years
CFA(2) = 6180
CFA(3) = 6180
CFA(4) = 6180
CFA(5) = 6180
Debug.Print MIRR(CFA(), 0.07, 0.09) 'Calculated Modified Internal Rate of Return

End Sub

Returns 0.185321001727451