CAGR Vs IRR Vs XIRR Methods to calculate Returns on Investments

After interacting with thousands of freedom seekers from across the globe, I have seen enough of them struggling to find out the returns from their investments. 

Sounds strange? It is not. 

We invest our hard earned money, and continue to stay invested, without even knowing what is the return we are getting on our investments. 

This is basic, and simple as well. 

So, thought of dedicating this post to all those who want to inculcate this niche skill of calculating returns on their investments :) 

Simple Annual Returns or Absolute Returns do not make a lot of sense for a retail investor. What we need is an annualised form of return so that we can effectively compare returns from one asset to another, and take informed decisions.

So, here are 3 most common methods to calculate annualised returns:

Method - I

CAGR (Compounded Annual Growth Rate)

The concept of CAGR is relatively straightforward and requires only three primary inputs:
- an investment’s beginning value
- an investment's final / ending value
- the time period for which the investment was done. 

CAGR is superior to Simple Annual Returns because it considers the assumption that the investment is compounded over time. This is practical for most investments we do today.

Formula:

CAGR = (End Value/Start Value)^(1/Years) - 1  

Example: 

Real Estate is a typical example where you would do an investment and would not disturb the investment for many years before you liquidate it.

Suppose a person invested INR 10,00,000 to purchase a real estate property on 1-Jan-12 and sold the property on 1-Jan-18 at INR 20,00,000. So here CAGR would turn out to be:

CAGR = (20,00,000/10,00,000)^(1/6) - 1 = 0.122 = 12.2%                            

So, while the investment has doubled over 6 years time, the Annual ROI on this investments is 12.2%, and this is what matters when you compare two investments.

Here we have assumed that investment is done only at the beginning and also redeemed only once. In between these six years, no investment is made and nothing is redeemed. 

Shortcomings of CAGR:

What if investment is not made at lump-sum but made periodically, as in the case of Mutual Fund SIPs? In such cases, CAGR turns out to be incapable to calculate the annualised return, and we need a more effective method to perform such a calculation.

Method - II

IRR (Internal Rate of Return)

IRR is the formula we use to calculate returns typically in case of investments where we are investing in, or redeeming from our investments on a regular basis, rather than one time.

IRR Formula:

The IRR formula is rather complex as shown below:

IRR function in MS Excel:

Microsoft Excel comes to our rescue here. 

The MS Excel IRR function returns the internal rate of return for a series of periodic cash flows represented by positive and negative numbers.

The function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

Excel IRR Syntax:

The syntax of the Excel IRR function is as follows:

=IRR(values, [guess])

where:

Values (required) – an array or a reference to a range of cells representing the series of cash flows for which you want to find the internal rate of return.

Guess (optional) – your guess at what the internal rate of return might be. It should be provided as a percentage or corresponding decimal number. If omitted, the default value of 0.1 (10%) is used.

Example:

Assumptions / Precautions before using the IRR Formula:

- The values argument must contain at least one positive value (representing income) and one negative value (representing outlay).

- There should be equal time intervals between all cash flows - monthly in the above example.

- The cash flows do not necessarily have to be even, but they must occur at regular intervals, for example monthly, quarterly or annually.

- If the investment in a particular month is missing, you still need to add a row with 0 amount.

- Only numbers in the values argument are processed; text, logical values, or empty cells are ignored.

- All cash flows are assumed to occur at the end of a period.

- Because IRR in Excel interprets the order of cash flows based on the order of values, the values should be in chronological order.

- In most situations, the guess argument is not really needed. However, if the IRR equation has more than one solution, the rate closest to the guess is returned. So, your formula produces an unexpected result or a #NUM! error, try a different guess.

- In event of monthly cash flows, the IRR function produces a monthly rate of return. To get an annual rate of return for monthly cash flow, you can multiply the monthly return by 12 to get the approximate value of your Annual ROI, or you can use the XIRR function (explained next).

- The formula assumes that the profits generated by the project are reinvested at the internal rate of return, which means it is a compounded return.For the result to display correctly, please make sure the Percentage format is set for the formula cell (usually Excel does this automatically).

- For the result to display correctly, please make sure the Percentage format is set for the formula cell.

Excel IRR function not working?

If you have run into some problem with IRR in Excel, the following tips might give you a clue for fixing it:

A #NUM! error may be returned because of these reasons:

- The IRR function fails to find the result with up to 0.000001% accuracy on the 20th try.

- The supplied values range does not contain at least one negative and at least one positive cash flow.

- Blank cells in the values array. Blanks not allowed. Zeros are acceptable.

Shortcomings of IRR Formula:

The IRR function in Excel is designed to work with regular cash flow periods such as weekly, monthly, quarterly or annually. If your inflows and outflows occur at unequal intervals, IRR would still consider the intervals equal and returns a wrong result. In this case, use the XIRR function instead of IRR.

Method - III

XIRR (Extended Internal Rate of Return)

The Excel XIRR function returns the internal rate of return for a series of cash flows that may or may not be periodic.

The function was introduced in Excel 2007 and is available in all later versions of Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Office 365.

Excel XIRR Syntax:

The syntax of the XIRR function is as follows:

XIRR(values, dates, [guess])

where:

Values (required) – an array or a range of cells that represent a series of inflows and outflows.

Dates (required) – dates corresponding to cash flows. Dates may occur in any order, but the date of the initial investment must be first in the array.

Guess (optional) – an expected IRR supplied as a percentage or decimal number. If omitted, Excel uses the default rate of 0.1 (10%).

Example:

Important things you should know about XIRR function:

- XIRR in Excel is designed for calculating the internal rate of return for cash flows with unequal timing. 

- The range of values must contain at least one positive (income) and one negative (outgoing payment) value.

- All dates are truncated to integers, meaning that the fractional part of a date that represents time is removed.

- Dates must be valid Excel dates entered as references to cells containing dates or results of formulas such as the DATE function. If dates are input in the text format, error may occur.

- XIRR in Excel always returns an annualized IRR even when calculating monthly or weekly cash flows.

- Generally, if you know the exact dates of the payments, it is advisable to use XIRR because it provides better calculation accuracy.

Excel XIRR function not working?

If you have run into a problem with the XIRR function in Excel, below are the main points to check.

A #NUM error may occur because of the following reasons:

- The values and dates ranges have different lengths (different number of columns or rows).

- The values array does not contain at least one positive and one negative value.

- Any of the subsequent dates are earlier than the first date.

- A result is not found after 100 iterations. In this case, try a different guess.

A #VALUE error may be caused by the following:

- Any of the supplied values are non-numeric.

- Some of the supplied dates cannot be identified as valid Excel dates.

Hope the above methods will help you calculate your Annualised returns of your Assets and Sub-Assets. Knowing these simple formulas give you more control over your investments and this is one of the keys to massive wealth generation.

Regards

Manoj Arora

Official Website