0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
0tts
  • 1
  • 1
  • 1
    Just to get your sample data to match your code, can you give us 3 rows of data (22 to 24)? – BigBen May 29 '20 at 18:54
  • I can't repro based on what you've provided. – BigBen May 29 '20 at 19:02
  • with XIRR the first value should be negative, see: https://support.office.com/en-us/article/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d – Scott Craner May 29 '20 at 19:06
  • ^ Which is why I asked for sample data that matches your code (and I ran on sample data with a negative value... at least one value must be negative. – BigBen May 29 '20 at 19:08
  • Thanks for all the comments. Obviously I'm new to this format. I'll try and add a little color to the scenario: There are only two rows containing data. The initial investment and the ultimate liquidation. The formula allows for three rows, and historically I've not had any problem with the third row being empty. The data set above has a typo. Cell Q22 should be -999.07. It is negative in the actual sheet. – 0tts Jun 03 '20 at 04:10
  • Another scenario just occurred tonight. Dates: 01/31/2011, 02/18/2011, 03/9/2011; cash flows: -1326, 1364, 1702 Same error as in original post. The only thing I can think of is that the VBA version of the formula errs out on extremely low values or extremely high values (-99.9% or +7mm%). The dozen other cases this was run on worked fine. @BigBen – 0tts Jun 03 '20 at 04:27
  • I cannot reproduce this error at all given the sample data you've provided: https://i.stack.imgur.com/LkStQ.png and https://i.stack.imgur.com/hHDn7.png – BigBen Jun 03 '20 at 12:34

0 Answers0