CODEWITHSUNDEEP
excelvbaxirr
0tts
0tts

Reputation: 1

XIRR function with extremely low ending value

I have a macro that calculates XIRR; used to tell me the rate of return on an investment.

The relevant code is:

Dim CFArray() As Variant
CFArray = Range("Q22:Q24").Value

Dim DateArray() As Variant
DateArray = Range("L22:L24").Value

ROR = WorksheetFunction.Xirr(CFArray, DateArray, 0.1)

The code works most of the time.

But when the ending value is extremely low, say zero, or a penny, I get

"Run time error '1004': Unable to get the XIRR property of the worksheet function class".

Here is my data:

Row  Col L         Col Q
22   6/30/2009     999.07
23   5/1/2010         .01

When I change the final value (cell Q23) to 5.01 or 4.01, I get an answer. 3.01 returns the same error as above.

Using the data in a plain old Excel formula (in a cell) yields a correct answer... something like -99.99%.

Some series will have multiple payments and withdrawals, so IRR is not an option. I believe I can write code to literally write the Excel function in the cell, but it seems like VBA should be able to accommodate this essential financial function.

Thanks for any insights.

Upvotes: 0

Views: 252

Answers (0)

Related Questions