Excel VBA XIRR not funtioning correctly

  • 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.

  • Welcome to Ozgrid. Please take a few minutes to read the Forum Rules to understand how to use the Forum correctly and get prompt answers.

    I have added Code Tags for you.

  • Roy: Thanks for the links and for editing my post. I printed the forum rules and will follow them. I noticed a typo in my data. The 999.07 should be -999.07, in keeping with requirements of the XIRR function that the first number needs to be negative. I see now that I should have posted the data set as an attachment, correct? Thanks again. 0tts

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!