Calling xirr in VBA

I am trying to write a vba macro that calls xirr on an array constructed from arguments. However it always return 0. It works perfectly if I have a cell formula with xirr on the exact same data.

A somewhat minimal version of the macro follows:

Please Login or Register  to view this content.

I am calling it on the following spreadsheet -

1-Jan	1000
1-Feb	100
1-Mar	100	=myXirr(-B1,A1,B2,A2,30,2,B4,A4,0.1)
1-Apr	-1233.31	=Xirr(b1:b4,a1:a4)

The Xirr cell correctly shows .127 but the myXirr always shows 0 regardless of its arguments (I know the 2 shouldn't be exactly the same in my simplified version with the 30 argument above, but they should be close enough).
Thanks for any help.

Originally Posted by coder7

Please Login or Register  to view this content.

[....]
=myXirr(-B1,A1,B2,A2,30,2,B4,A4,0.1)
=Xirr(b1:b4,a1:a4)
[....]
The Xirr cell correctly shows .127 but the myXirr always shows 0

Actually, myXirr returns about 2.98E-09, which you would see if you format the cell as General or as Scientific.

In part, this is due to a defect in the XIRR function: it always returns about 2.98E-09 if the first cash flow is zero.

But the root cause of the problem is your ReDim statements. At the very least, they should be

Please Login or Register  to view this content.

Otherwise, the default first index is zero. Since you never set vals(0), it is zero, which stumbles over the aforementioned defect.

However, with that correction, myXirr returns about 3.77038295269012, whereas XIRR returns about 0.127191597223282.

Some other mistakes:

1. You pass -B1 (-1000) to myXirr, but you pass B1 (1000) to XIRR. And you set vals(numInt+2)=-endVal (1233.31) in myXirr, whereas you pass -1233.31 in B4 to XIRR. In effect, your cash flows for myXirr are -1000, 100, 100, 1233.31, whereas your cash flows for XIRR are 1000, 100, 100, -1233.31.

2. Also, by adding deltaDays (30) to B2, the third date in myXirr is 2-Mar instead of 1-Mar. Consequently, when you correct #1, myXirr returns about 0.127324062585831 instead of 0.127191597223282.

Thanks very much joeu. I hadn't realized that vba arrays start at 0.

Originally Posted by coder7

I hadn't realized that vba arrays start at 0.

By default. You can change that by putting Option Base 1 near the top of the module. But I prefer to specify the lower and upper bounds in the Dim and ReDim statements. When in doubt, use LBound and UBound to determine the limits of an array, at least during development.