Return on Investment (ROI): Measuring Returns Using CAGR & XIRR Formula

By Trader Pit

July 8, 2021


CAGR, or the simple average annual growth rate of a stock, is one of the most common ways to measure a portfolio’s performance. The calculation is pretty simple: the current value of a portfolio is subtracted from the beginning value and divided by the year-end value. For example, if your portfolio is worth $100,000 and you started with that same $100,000 at the beginning of the year, then at the end of the year, your portfolio would be worth $125,000 ($100,000 – $100,000 x 1.25 = $125,000).

The common question people ask themselves is – How much money could I make investing in a stock?

In general, we invest money in two forms. The first is a one-time investment. We also invest systematically, in small amounts each month. How can the return on investment (ROI) be measured in all cases?

We all know how to do that, right? In general, we use the following formula to measure performance: Return = Profit / Investment x 100. It’s a simple formula that works in most cases.

However, there are cases where this formula cannot be applied. For example, how do you measure B. the return on investment (ROI) of stocks that provide both dividend income and capital gains?

Another example is to measure the performance of a property that produces monthly rental income and capital gains.

Suppose someone invests in mutual funds under a SIP program. How do you measure the return on such an investment?

In this article, we will attempt to calculate the return of this type of investment using the CAGR and XIRR formulas. But first, let’s get some basic knowledge about TCAC and XIRR.

Watch the video: Calculate the yield

XIRR and CAGR formula

CAGR stands for Compound Annual Growth Rate. This is the mathematical formula for calculating the return on investment (ROI). The CAGR is used as a formula to calculate the return on lump sum investments.

The CAGR value indicates the annual rate at which the investment (acquisition cost) must grow to reach its ultimate value. Below is the CAGR formula:

How should the CAGR be interpreted? Suppose you have Rs 1,00,000 for a long term investment. You want to invest in a bond fund with virtually no risk. A debt-based mutual fund can give you a return of 7.5%.

But you decide to invest in stocks, gold, real estate, etc. as an alternative. If the expected future CAGR of the alternative investment is well above 7.5%, you would consider it an investment.

XIRR

To better understand the use of XIRR, we must first become familiar with the discount rate. But explaining the concept of discount rate here would lead to confusion rather than clarity. So let’s consider XIRR at this point as another form of CAGR. But XIRR is used to calculate the return when multiple cash flows occur in an investment.

We can better understand what I mean by multiple cash flows if we look at specific examples. So let’s start with a few examples.

Examples

#1. CAGR – lump sum for investments in mutual funds

In this example, a one-time investment of Rs. 60,000 was made in a mutual fund. The investment was made on 01. January 2019 traded. At the time of acquisition, the net asset value of the scheme was Rs. 574.84 million. A total of 104.38 units were purchased (=60,000/574.84).

After 11 months of program operation, 02. December 2019, all 104.38 units sold. The net asset value of the scheme at the time of sale was Rs. 614.86 lakhs.

How do you calculate the return on investment for this example? Since this example is a typical one-time investment with only two cash flows, we can use the CAGR formula to calculate the return.

Let’s understand these two streams of money:

  • Outflow of funds : Rs 60,000 crore – On 01. January 2019 Rs 60,000 crore was paid for the purchase of units of mutual funds (104.38 units).
  • Cash inflows : Rs 64,177.16 crore – 02-Dec-2019 all 104.38 number of units redeemed. A sum of Rs 64,177 crore was received on redemption of all units.

We can use the CAGR formula to calculate the return.

As can be seen from the formula above, the CAGR, the return on investment, will be 7.59% per annum.

#2. XIRR – Investment funds SIPs

In this example, every month Rs. 5,000 is invested in a mutual fund. The investments take place regularly from 01/01/2019 to 02/12/2019. The total number of units collected in 12 transactions was 100.61#.

How do you calculate the return on investment for this example? Since this example is an investment with multiple cash flows, we can use the XIRR formula in Excel to calculate the return. The XIRR formula is as follows:

There were 13 payment streams in total. The payouts amounted to Rs 12 (5 trillion from January 19 to December 19). There was only one cash inflow in the form of redemption of units on 02.12.19 (Rs. 61,859.2).

How do you calculate the return on investment for this example? For the calculations we will use the XIRR formula in Excel. Look at the screenshot below, which shows the 13 cash flows and how to apply the XIRR formula to them.

As shown in the calculation above, the XIRR, the return on investment, is 6.81% per year.

#3. XIRR – investment inshares

In this example, a one-time investment of Rs. 25,000 was made in shares. The investment was made on 01. January 2010. At the time of purchase, the share price was Rs. 100. A total of 250 shares were purchased (=25.000/100).

After holding the shares for 10 years, the 250 shares were sold on 02. January 2020 sold. At the time of sale, the price was Rs 315 per share.

During the holding period from 01.01.2010 to 02.01.2020, the stock has also generated dividend income for nine consecutive years. This means that this investment has multiple cash flows at specific times. Thus, this example is appropriate for using the XIRR formula to calculate return on investment (ROI).

There were a total of 11 cash flows. There was only one withdrawal of Rs 25,000 on 01.01.10. During the holding period, there were numerous investments in the form of dividends and sales of shares. The calculation of the total dividend income for the year is as follows

How do you calculate the return on investment for this example? We will use the XIRR formula. Look at the screenshot below which shows the 11 cash flows and applies the XIRR formula to calculate the return.

As shown in the calculation above, the XIRR, the return on investment (ROI), is 13.06% per year.

#4. XIRR – unique real estate investment

In this example, a one-time investment of Rs 50,00,000 was made in real estate. The investment was made on 01. January 2010. After owning the property for 10 years, it was sold on 02. January 2020 sold for Rs 1.25 crore.

The property has also generated rental income for nine years, from 01-Jan-2010 to 02-Jan-2020. This is another example of multiple cash flows. Therefore, the application of XIRR formula is suitable to calculate the return on investment (ROI).

There were a total of 11 cash flows. There was a one-time withdrawal of Rs 50,00,000 on 01.01.10. There was a large cash inflow in the form of rents and property sales. The calculation of the total rental income for the year is as follows

How do you calculate the return on investment for this example? We will use the XIRR formula. Look at the screenshot below which shows the 11 cash flows and applies the XIRR formula to calculate the return.

As the above calculation shows, the XIRR, the return on investment (ROI), is 11.91% per year.

Supplement

Knowing how to calculate the return on an investment is a skill. We should all be aware of that. CAGR and XIRR are two formulas that can do this job for us. The XIRR is the more versatile of the two. It can calculate the return of even complex investments (with multiple cash flows).

Frequently Asked Questions

Is CAGR and ROI same?

CAGR is calculated on a yearly basis while ROI is calculated on a monthly basis.

How do you calculate ROI Return?

ROI Return is a calculation of the return on investment. ROI Return is calculated by dividing the total profit by the total investment.

What does the CAGR tell you?

The CAGR is a measure of the compound annual growth rate. It tells you how quickly the stock price is increasing over a given period. The CAGR for the company is 6.2%.

Related Tags:

Feedback,how to use cagr to forecastcagr return calculatorreverse cagr calculatorwhat is a good cagr for an industrycagr formulacagr formula excel,People also search for,Privacy settings,How Search works,Compound annual growth rate,CAGR formula,how to use cagr to forecast,cagr return calculator,reverse cagr calculator,what is a good cagr for an industry,cagr formula excel,annual growth rate formula,sip cagr calculator

About the author

Trader Pit

A yogi who like Finance and Technology. I have been in Indian Stock market for over 12 years now as financial analyst, portfolio manager, trader. Now, I focus on Yoga, Financial Education & Long term investing. 

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}