Please verify that my XIRR calculation is correct

[LearnmeXL]

<tbody>
</tbody>

Trying to calculate an IRR on a piece of buy and hold real estate. Using the XIRR function I am coming up with a 5% IRR. Would someone be kind enough to run the formula and verify my calculation? Thanks in advance for Your help.


Dates Cash Out/In
5/28/15 -44766.72
6/18/15 1000.00
8/1/15 354.85
9/1/15 354.85
10/1/15 354.85
11/1/15 354.85
12/1/15 354.85
1/1/16 354.85
2/1/16 354.85
3/1/16 354.85
4/1/16 354.85
5/1/16 354.85
6/1/16 354.85
7/1/16 354.85
8/1/16 354.85
9/1/16 354.85
10/1/16 354.85
11/1/16 354.85
12/1/16 354.85
1/1/17 354.85
2/1/17 354.85
3/1/17 354.85
4/1/17 354.85
5/1/17 354.85
6/1/17 354.85
7/1/17 354.85
8/1/17 354.85
9/1/17 354.85
10/1/17 354.85
11/1/17 354.85
12/1/17 354.85
1/1/18 354.85
2/1/18 354.85
3/1/18 354.85
4/1/18 354.85
5/1/18 354.85
6/1/18 354.85
7/1/18 354.85
8/1/18 354.85
9/1/18 354.85
10/1/18 354.85
11/1/18 354.85
12/1/18 354.85
1/1/19 354.85
2/1/19 354.85
3/1/19 354.85
4/1/19 354.85
5/1/19 354.85
6/1/19 354.85
7/1/19 354.85
8/1/19 354.85
9/1/19 354.85
10/1/19 354.85
11/1/19 354.85
12/1/19 354.85
1/1/20 354.85
2/1/20 354.85
3/1/20 354.85
4/1/20 354.85
5/1/20 354.85
6/1/20 354.85
7/1/20 354.85
8/1/20 354.85
9/1/20 354.85
10/1/20 354.85
11/1/20 354.85
12/1/20 354.85
1/1/21 354.85
2/1/21 354.85
3/1/21 354.85
4/1/21 354.85
5/1/21 354.85
6/1/21 354.85
7/1/21 354.85
8/1/21 354.85
9/1/21 354.85
10/1/21 354.85
11/1/21 354.85
12/1/21 354.85
1/1/22 354.85
2/1/22 354.85
3/1/22 354.85
4/1/22 354.85
5/1/22 354.85
6/1/22 354.85
7/1/22 354.85
8/1/22 354.85
9/1/22 354.85
10/1/22 354.85
11/1/22 354.85
12/1/22 354.85
1/1/23 354.85
2/1/23 354.85
3/1/23 354.85
4/1/23 354.85
5/1/23 354.85
6/1/23 354.85
7/1/23 354.85
8/1/23 354.85
9/1/23 354.85
10/1/23 354.85
11/1/23 354.85
12/1/23 354.85
1/1/24 354.85
2/1/24 354.85
3/1/24 354.85
4/1/24 354.85
5/1/24 354.85
6/1/24 354.85
7/1/24 354.85
8/1/24 354.85
9/1/24 354.85
10/1/24 354.85
11/1/24 354.85
12/1/24 354.85
1/1/25 354.85
2/1/25 354.85
3/1/25 354.85
4/1/25 354.85
5/1/25 354.85
6/1/25 354.85
7/1/25 354.85
8/1/25 354.85
9/1/25 354.85
10/1/25 354.85
11/1/25 354.85
12/1/25 354.85
1/1/26 354.85
2/1/26 354.85
3/1/26 354.85
4/1/26 354.85
5/1/26 354.85
6/1/26 354.85
7/1/26 354.85
8/1/26 354.85
9/1/26 354.85
10/1/26 354.85
11/1/26 354.85
12/1/26 354.85
1/1/27 354.85
2/1/27 354.85
3/1/27 354.85
4/1/27 354.85
5/1/27 354.85
6/1/27 354.85
7/1/27 354.85
8/1/27 354.85
9/1/27 354.85
10/1/27 354.85
11/1/27 354.85
12/1/27 354.85
1/1/28 354.85
2/1/28 354.85
3/1/28 354.85
4/1/28 354.85
5/1/28 354.85
6/1/28 354.85
7/1/28 354.85
8/1/28 354.85
9/1/28 354.85
10/1/28 354.85
11/1/28 354.85
12/1/28 354.85
1/1/29 354.85
2/1/29 354.85
3/1/29 354.85
4/1/29 354.85
5/1/29 354.85
6/1/29 354.85
7/1/29 354.85
8/1/29 354.85
9/1/29 354.85
10/1/29 354.85
11/1/29 354.85
12/1/29 354.85
1/1/30 354.85
2/1/30 354.85
3/1/30 354.85
4/1/30 354.85
5/1/30 354.85
6/1/30 354.85
7/1/30 354.85

 

[joeu2004]

Trying to calculate an IRR on a piece of buy and hold real estate. Using the XIRR function I am coming up with a 5% IRR. Would someone be kind enough to run the formula and verify my calculation?

Yes: about 5.42888849973679%.

You can verify the IRR yourself using XNPV.

If C1 contains =XIRR(B1:B182,A1:A182), then =XNPV(C1,B1:B182,A1:A182) returns about -2.66E-06, which is close to zero.

Is there some reason why you doubt the results?

For the future, it would behoove you to post 4-digit years. A date of the form m/d/30 is interpreted as year 1930 on my computer. Of course, it should be 2030.

PS: Note that 5.42888849973679% is an approximation. Not only because of the nature of the (X)IRR algorithm (which is why XNPV is not exactly zero), but also because XIRR always assumes a 365-day year, whereas leap years are actually 366. Whether or not to use 366 in leap years depends on regional regulations as well as industry practices for the region.

 

[joeu2004]

PS....

PS: Note that 5.42888849973679% is an approximation. Not only because of the nature of the (X)IRR algorithm (which is why XNPV is not exactly zero), but also because XIRR always assumes a 365-day year, whereas leap years are actually 366.

Taking leap years into account, the (X)IRR is about 5.43267810983446%.

I used Solver to derive that. Enter the following formula into C5 to calculate the (X)NPV using the annual IRR to be derived in C4.

=SUMPRODUCT(B1:B182/(1+C4)^((A1:A182-A1)/(365+(MOD(YEAR(A1:A182),4)=0))))

Solver set-up
Set Objective: C5
To Value Of: 0
By Changing: C4
Constraint:  C4>=-99.99%
Method: GRG Nonlinear
Options:
    All Methods Constraint Precision: 0.000000000001
    GRG Nonlinear Convergence: 0.000000000001

With the derived (X)IRR above, C5 is about 1.12E-10; again, close to zero.

Note: The simplified expression to account for leap years works for years 1901 through 2099.

 

[LearnmeXL]

joeu2004,

Thanks for the help. I've been going round and round with this thing for a couple of evenings. Had seen posts the talk about potential inaccuracies of IRR and XIRR functions when the number of cash flows were as high as 180. Questioned my calc because I intuitively thought the IRR would be better on this deal. And, thanks for the tip on the date format when using the XIRR function.

So, this XIRR result is expressed as an annualized percentage or monthly percentage? I am more or less pulling these cash flows from an amortization table (does the formula see it as 180 years, or 180 months?). When using simple IRR (without dates) is that result expressed as a monthly or annual IRR? If monthly, is simply mulitplying by 12 a valid conversion to annual?

Thanks again.

 

[joeu2004]

Had seen posts the talk about potential inaccuracies of IRR and XIRR functions when the number of cash flows were as high as 180.

The Excel XIRR implementation is indeed unreliable. Sometimes it returns a bogus number, when it should have returned a #NUM error. Sometimes it returns a #NUM error when a single IRR is indeed computable. But I don't believe the number of cash flows makes a difference.

Conceptually, on the other hand, the number of cash flows might affect the accuracy of the Excel IRR result simple because of numerical limitations.

But Excel IRR works with the 182 cash flows you presented.

Mathematically, any IRR implementation is adversely affected by the number of sign changes in the cash flows. There can be more than one IRR that causes the NPV to be zero. And the IRR might not be computable.

Your cash flows are what we want to see: a series of one sign (negative) followed by a series of the other sign (positive).

Questioned my calc because I intuitively thought the IRR would be better on this deal.

In my experience, the Excel IRR implementation is indeed more reliable.

However, Excel IRR should be used only when all cash flows are equally spaced -- monthly, in your case.

Technically, in your case, we cannot use Excel IRR because the cash flows dated 5/28 and 6/18 are not a month apart from each other and from the remaining sequence of monthly cash flows starting on 8/1.

If we use Excel IRR, we are treating the 5/28 and 6/18 cash flows as if they occur on 6/1 and 7/1. In this particular case, the numerical error is relatively small. Compared with Excel XIRR, the difference is about 0.0140%, and the relative error is about 0.2574%.

So, this XIRR result is expressed as an annualized percentage or monthly percentage? I am more or less pulling these cash flows from an amortization table (does the formula see it as 180 years, or 180 months?). When using simple IRR (without dates) is that result expressed as a monthly or annual IRR? If monthly, is simply mulitplying by 12 a valid conversion to annual?

Excel XIRR always returns a compounded annual IRR.

Excel IRR returns a periodic IRR. The length of the period depends on the dates of the cash flows -- which, again, should be equally spaced. In your case, the IRR is monthly.

If you want to compare the monthly IRR result to the annual Excel XIRR result, the monthly IRR should be compounded. That is, =(1+IRR(...))^12-1.

There will always be a difference, even if the cash flows are indeed monthly. That difference is because a monthly IRR treats all months as the same, whereas Excel XIRR uses the actual days between cash flows.

On the other hand, sometimes we should annualize the periodic IRR simply by multiplying by 12; for example, if you want to calculate a US APR based on monthly cash flows. This is dictated by regional regulations -- Appendix J of the Truth in Lending regulations for the US.

But in that case, we should also "nominalize" the annual Excel XIRR result. That is, =NOMINAL(XIRR(...),12).

And, thanks for the tip on the date format when using the XIRR function.

My comment was about the presentation of dates in this forum, not so much in the Excel worksheet and not just "when using" Excel XIRR.

It was a problem only because when I copy-and-pasted your data into Excel, Excel interpreted m/d/30 as m/d/1930.

I presume you entered the dates correctly because you said that Excel XIRR returned about 5%. In that case, the format of the dates has no effect on Excel XIRR.

If Excel had interpreted your last 7 dates as in 1930, Excel XIRR would have returned an Excel error (#NUM).

 

[joeu2004]

PS....

If you want to compare the monthly IRR result to the annual Excel XIRR result, the monthly IRR should be compounded. That is, =(1+IRR(...))^12-1.

Or equivalently: =EFFECT(12*IRR(...),12).