Skip to content
All library documents

Reconciling Excel XIRR with Monthly IRR Calculations

Article Quant Q&A · Author: mbmast
Read the original

Summary

The document examines a mismatch between an Excel XIRR result and a manually estimated return from dated cash flows. The user has multiple inflows and outflows on the same dates, all on the first day of a month, and discounts them using month numbers to find a rate where the present values nearly sum to zero. That estimated rate differs from the XIRR output, prompting a question about the calculation.

The answer says to calculate a monthly IRR and then annualize it, and reports that this method agrees with the XIRR result. It also gives a one-step IRR result that is close but not identical. The figures provide a numerical check for the particular example, but the document does not reproduce the spreadsheet or explain the precise compounding and date conventions behind the small difference. Its lesson is that monthly and date-based annualized return calculations need to be compared on consistent terms; the example alone is not a general treatment of XIRR behavior.

Key ideas

  • Cash flows on the same date can be netted before calculating a conventional IRR.
  • A monthly IRR can be annualized to compare it with an annual XIRR result.
  • The answer reports that the annualized monthly calculation agrees with the example’s XIRR.
  • A one-step IRR calculation is reported as close, but not identical, in this example.
  • The document does not explain the detailed date and compounding conventions behind the difference.

Tags

Statistics
Full text
# Excel XIRR function producing unexpected IRR


# Excel XIRR function producing unexpected IRR












I'm using the Excel XIRR function because I have inflows and outflows on the same dates. To use the IRR function instead, I would have to compute the net inflow or outflow on a given date and then have a single row for that date's net inflow/outflow. All of my dates are always on the first of the given month.

The problem I'm having is the XIRR function is not giving the IRR that I have guessed. To illustrate this, in the spreadsheet below is a list of inflows and outflows (column C) and the corresponding dates (column A). The IRR computed by Excel's XIRR function is 39.098%. You can see this on the spreadsheet and you can see the function I used to compute it.

I then compute the month number, where the first month is month number zero. The formula to compute the month number is also shown.

I then compute the present value of the inflow/outflow (from column C) using the month number and a rate that I guessed at (cell C31). I then add up all of the present values, getting 1.97 in cell I29 (all the formulas are shown). I just fiddled around with the rate (cell C31) trying to get a sum of zero. I stopped when I got to 1.97, figuring that was close enough. As this sum is nearly zero, the rate of 33.430% is the IRR.

There is a fairly significant difference between Excel's XIRR and my IRR. Why is this?

## Answer by Chris Degnen (score 3, accepted)

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

You can calculate the monthly IRR then annualise. Both IRR and XIRR = 39.1% pa.

Confirming the XIRR, as you calculated in Excel. XIRR = 39.098% pa.

You can also calculate the IRR in one step. IRR = 39.062% pa.

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.