Strange XIRR error

[Tat]

I am having strange problems with XIRR in a spreadsheet in Mac Excel 2004 (11.3.7). I can enter the formuala fine in using =XIRR(values,dates) and it returns an answer. However when I recalc, I get a #Value! error. I went to similar spreadsheets that used XIRR and hit recalc, and they work fine. However, if I change anything or re-enter the formula, the same thing happens.

First I figured there was a bad date in the string of dates, but that was not the case. To experiment, I pulled both the dates and cash flows into another part of the sheet. I then copied the CF and pasted as values and hard coded the dates. When I ran the combinations, it seems that the cash flows are the issue. It works fine with the pasted values as the CF, with either date string, but gives me an error upon recalculating if I use the cash flows that are actually calculating.

Any thoughts? I need to change the cash flow waterfalls on a transaction and it is killing me!

 

[JE McGimpsey]

I am having strange problems with XIRR in a spreadsheet in Mac Excel 2004
(11.3.7). I can enter the formuala fine in using =XIRR(values,dates) and it
returns an answer. However when I recalc, I get a #Value! error. I went to
similar spreadsheets that used XIRR and hit recalc, and they work fine.
However, if I change anything or re-enter the formula, the same thing
happens.

First I figured there was a bad date in the string of dates, but that was not
the case. To experiment, I pulled both the dates and cash flows into another
part of the sheet. I then copied the CF and pasted as values and hard coded
the dates. When I ran the combinations, it seems that the cash flows are the
issue. It works fine with the pasted values as the CF, with either date
string, but gives me an error upon recalculating if I use the cash flows that
are actually calculating.

Any thoughts? I need to change the cash flow waterfalls on a transaction and
it is killing me!

Since your Cash Flows are calculated, are you sure that none of those
CF's return #VALUE! when you recalculate? If they do, they'll pass the
#VALUE! error to XIRR(), which will then display the error.

Are you using any named ranges/formulas in your calculations that could
be causing the error?

If you copy the used range of your sheet to another sheet (via
CMD-c/CMD-p, not Edit/Move or Copy sheet), does the problem stop
happening).

 

[Tat]

There are no errors in the CFs. The dates do start with a name, but even if I change this it doesn't fix and I have (through the test I mentioned).

If I open it in Excel for windows, enter in the function, it calculates correctly. I can then save it, open it in Mac Excel and, as long as I don't edit the function, it will calculate correctly. If I change the function, it will bring the error.

 

[JE McGimpsey]

There are no errors in the CFs. The dates do start with a name, but even if I
change this it doesn't fix and I have (through the test I mentioned).

If I open it in Excel for windows, enter in the function, it calculates
correctly. I can then save it, open it in Mac Excel and, as long as I don't
edit the function, it will calculate correctly. If I change the function, it
will bring the error.

Hmmm... strange.

What about the other question I asked - does it happen with a new
workbook?