Why XIRR Results Differ Across Software

Summary

The note compares XIRR outputs from Google Sheets, LibreOffice Calc, Excel, and a separate implementation. The first three results are nearly identical, while Excel differs slightly. The response attributes this to Excel's iterative solver, which stops when its result meets a built-in accuracy tolerance or reports failure after a fixed number of attempts.

The practical lesson is that implementations can return different values because they use different stopping criteria; a custom solver can choose its own tolerance. More precision is not always the most useful objective: for some workflows, matching a market reference such as Bloomberg may matter more than solving to a tighter numerical tolerance. The comparison is a single example and does not establish that Excel generally loses precision after a particular decimal place.

Key ideas

• XIRR implementations can produce slightly different values because their numerical solvers use different stopping criteria.
• Excel applies a fixed tolerance and attempt limit to its iterative calculation.
• A custom implementation can select a stopping rule suited to its intended use.
• Matching a market reference may be more valuable than maximizing numerical precision.

Tags

# Different XTIR results between Excel, Google Spreadsheets, LibreOffice Calc and my algorithm


# Different XTIR results between Excel, Google Spreadsheets, LibreOffice Calc and my algorithm












I am developing an algorithm to calculate XIRR, and I found some differences between excel and my algorithm, so I decided to compare other software that have the XIRR formula.

I found the following differences

```
 -0.0014968440379778700 Google Sheets
 -0.0014968440379778900 My algorithm
 -0.0014968440379778800 LibreOffice Calc
 -0.0014968425035476700 Excel
```

Apparently Excel "loses" precision after the 8 decimal.

Has anyone had this problem?

## Answer by Dimitri Vulis (score 1)

https://quant.stackexchange.com/a/55718

According to Microsoft's documentation

> Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned...

So, if you want to specify your own criteria for when you want to stop iterating in your own implementation, you can; but the criteria in Excel are hardcoded.

In practice "matching Bloomberg yield exactly for the same cash flows" may be more desirable than "solving for more precise yield".

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.