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

XIRR(values, dates, [guess])
Values   Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
Dates    Required. A schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .
Guess    Optional. A number that you guess is close to the result of XIRR.

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

=XIRR( $C$2:C33 , $B$2:B33 , 1)

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)

=XIRR( $C$2:C77 , $B$2:B77 , 1)

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.