XIRR - weird results

I have a set of dates and values and need to calculate the XIRR - however I have picked up a weird discrepancy, and in an attempt to try and decipher the issue I have replicated the same data set

• MSoft Excel
• Google Sheets
• LibreOffice Calc
• PHP Code.

MSoft Excel gets stuck at some point, and seems to be disregarding the cashflow from a certain point, resulting in a 148.15 XIRR value
ALL the others return -0.09 XIRR value.

The sheet is attached showing columns E,F and G showing what values are returned for each platform.
Column I shows the live calculation - which 'should' be your system's interpretation.
On each row - I calculated the XIRR from row 1 up-to-including the formula row.
Can anyone explain to me what is wrong here ?

Thanks

Found a solution

Microsoft use a default of 10% (0.1) in the 3rd variable (guess) of the XIRR formula in Excel.

The other platforms use 0 as default. When I adjusted to 10% the results were much closer.

It's mostly GIGO. Your XIRR formula is malformed, if column I truly represents the formula.

If Column C are the cash flows (i.e. external funds added or withdrawn), the last "cash flow" should be the net value, namely =C77+D77, not simply C77.

That requires some finess in order to create the progressive XIRR that you want to calculate. For example, array-enter the following in N2 and copy down:

=XIRR(IF(ROW($C$2:C2)=ROW(C2), C2+D2, $C$2:C2), $B$2:B2)

That presumes a "normal" interpretation of the cash flow model, namely: a series of external cash flows and corresponding end balance.

However, if that is the case, the cash flows are extreme, if not incredulous.

And the balances are calculated incorrectly. For example, if -8041.43 on 2012-12-31 (YMD) represents an investment of 8041.43, the balance should be =-C2 (8041.43), not =C2.

Be that as it may, the calculations that I added might help you understand what is happening, based on your (incorrect) XIRR usage.

See the attached Excel file. LMK if you want an explanation.

I do not agree (or actually - I do not have an opinion, but MicroSoft do not agree with you)

According to support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d the formula is simply

Please Login or Register  to view this content.

So in my sheet the XIRR calculation on each line is perfect for the entire series UP TO THAT POINT IN THE SERIES
eg the XIRR up to (including) 2016-02-26 (Line 33) is correct as

Please Login or Register  to view this content.

resulting in 148.15

... also the XIRR formula for the entire series ending on line 77 is correct (so is the formula on each line)

Please Login or Register  to view this content.

resulting in 148.15

I do understand why the first 5 lines throw errors, it is because there are only negative values up to that point, and the formula requires at least one positive value in the range as well.
... and it also does that with your formulas.