1
$\begingroup$

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?

enter image description here

Improve this question
$\endgroup$
2
  • $\begingroup$ Suggestion: XIRR is based on exact day counts between dates divided by 365, your manual calc is based on month counts divided by 12. But months differ in length. Change you manual calculation to be on a day basis and see what happens $\endgroup$
    – Alex C
    Commented Jan 8, 2017 at 22:19
  • $\begingroup$ @AlexC Extremely slight improvement. I used DAYS to compute the days between dates and then used =C4/(1+C$31/365)^E4 (etc.) and the rate that gives a sum of zero (0.11 actually) was 33.016%. Still significantly different than the 39.098% given by Excel's XIRR function. $\endgroup$
    – mbmast
    Commented Jan 9, 2017 at 2:11

1 Answer 1

Reset to default
3
$\begingroup$

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

enter image description here

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

enter image description here

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

enter image description here

Improve this answer
$\endgroup$
7
  • $\begingroup$ I'm digesting this. I'm thinking my mistake was not annualizing the IRR I calculated. What tool are you using? Is this R? $\endgroup$
    – mbmast
    Commented Jan 9, 2017 at 22:36
  • $\begingroup$ It's Mathematica. $\endgroup$
    – Chris Degnen
    Commented Jan 9, 2017 at 22:57
  • $\begingroup$ After reading Degnen's answer, I believe if you replaced the formula in for example I4 with =C4/(1+C\$31)^(E4/12) and similarly for I4..I27 you would get close to the correct answer. In other words the division by 12 is in the wrong place. (The day versus months issue is minor). $\endgroup$
    – Alex C
    Commented Jan 10, 2017 at 0:36
  • $\begingroup$ @AlexC Indeed. You are correct. Instead of 33.430% I get %39.062% which is nearly the 39.098% given by XIRR. $\endgroup$
    – mbmast
    Commented Jan 10, 2017 at 3:25
  • 1
    $\begingroup$ @mbmast You can divide a nominal rate by 12 to get a monthly rate. The mathematical rate is an effective rate so you take the 12th root. See the effective interest rate calculation. You can convert the XIRR effective rate like so: ((1 + 0.390985)^(1/12) - 1)*12 = 33.4592 % nominal interest compounded monthly. It is clearer to calculate in effective rate then convert at the end if required. XIRR gives its result in effective interest. $\endgroup$
    – Chris Degnen
    Commented Jan 10, 2017 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.