2

I am trying to understand the IRR, XIRR and CAGR functions better. See the link below for my examples.

I am looking at a very simple example of 20K invested and 50K returned at the end of an investment. This equates to a 150% return using IRR, XIRR and CAGR for aeriod of 1 year Of particular interest is XIRR in 1 month vs CAGR in 1 month I get 2 different answers. 4846338% v 5960364%

Here is the link with my examples(see sheet2):
https://docs.google.com/spreadsheets/d/1Gt-3vaitcDnIuEpJLB8NHgBjaifFjEs0S-0DTBBLMFM/edit?usp=sharing

I know I can just do this, but here I just wanted to use an example to provide some better context to help me understand it better.

adding a picture of the link:

enter image description here

Improve this question

2 Answers 2

Reset to default
2

IRR works simply on periodic amounts, so you need to annualise the result yourself, e.g.

for your simple example where one month periodic IRR = 150%

∴ annual IRR = (1.5 + 1)^12 - 1 = 59603.6 = 596.036 % per annum

This is the same as your one month CAGR result. In these examples the months of the year are treated as equal periods of time.

They are slightly different from your XIRR return because XIRR annualises on a daily basis to more accurately account for mid-period cashflows. The calculation to annualise on a daily basis calculates the daily rate first, then annualises:

From 01/01/2015 to 01/02/2015 is 31 days

annualised XIRR = ((1.5 + 1)^(1/31))^365 - 1 = 48463.4 = 484.634 % per annum

The XIRR rate is less than the IRR because January is a longer month than the average.

In your more complicated example the difference is the same. IRR is calculating a periodic return (your periods are years), and XIRR is calculating an annualised return based on daily resolution.

enter image description here

The calculations can be reproduced as follows. Discounting each amount to net present value (NPV) the sum of all the amounts is zero; solve for r:

First, calculating IRR (for the whole 4 year period) - ref. link

-50000/(1 + r)^(0/4) + 5000/(1 + r)^(1/4) + 5000/(1 + r)^(2/4) + 
  5000/(1 + r)^(3/4) + 50000/(1 + r)^(4/4) = 0

∴ r = 0.349104

annualised IRR = (r + 1)^(1/4) - 1 = 0.0777334 = 7.77334 % per annum

XIRR

From 01/01/2009 to 01/01/2010 is 365 days
From 01/01/2009 to 03/01/2011 is 732 days
From 01/01/2009 to 04/01/2012 is 1098 days
From 01/01/2009 to 05/01/2013 is 1465 days

-50000/(1 + r)^(0/1465) + 5000/(1 + r)^(365/1465) + 
  5000/(1 + r)^(732/1465) + 5000/(1 + r)^(1098/1465) + 
  50000/(1 + r)^(1465/1465) = 0

∴ r = 0.349174

annualised XIRR = ((r + 1)^(1/1465))^365 - 1 = 0.077472 = 7.7472 % per annum
Improve this answer
3
  • when i do irr() as i do in c11, is this not the return after 1 year, as it works out the same as xirr() for 1 year 1/1/2015-1/1/2016 in c14, being equal to 150%? for instance cagr(1year) = irr(period not specified) = xirr(1year) = 150%
    – HattrickNZ
    Jan 21, 2016 at 0:35
  • In Excel, the IRR function e.g. IRR(A2:A7) only references a range of values. It doesn't know anything about timescales. So the help (linked) says: "the cash flows must occur at regular intervals, such as monthly or annually." The result is the monthly or annual rate accordingly. If it's monthly and you want annual you have to annualise it yourself.
    – Chris Degnen
    Jan 21, 2016 at 9:03
  • tks, i think the _ regular intervals_ are yearly in IRR(C11) as it gives 150% which is what to XIIR for 1 year(C14), and CAGR for 1 year(C18) gives.
    – HattrickNZ
    Jan 21, 2016 at 19:09
1

in laymans terms(from Chris Degnen answer above)

this explains IRR - XIRR difference:
IRR is calculating a periodic return (your periods are years), and XIRR is calculating an annualised return based on daily resolution.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .