Generally, we invest money in two forms. The first is in lump-sum, like a one-time investment. We also invest systematically, small-small amounts each month. How to measure return on investment (ROI) in each case?
We all know how to do it, right? To measure returns we commonly use this formula: Returns = profit/investment x 100. This is a simple formula that works most of the time.
But there are cases where this formula cannot be applied. For example, how to measure return on investment (ROI) on stocks that yield both dividend income and capital appreciation?
Another example can be, the measurement of returns on residential properties that yield monthly rents and capital appreciation.
Suppose one is investing in mutual funds through the SIP route. How to measure the ROI of this type of investment?
In this article, we will try to calculate the returns of similar types of investments using the CAGR and XIRR formulas. But before that, let’s first know some basics behind CAGR and XIRR.
XIRR & CAGR Formula
CAGR is an abbreviation for the “Compound Annual Growth rate.” It is a mathematical formula used to calculate return on investments (ROI). CAGR is used as a formula to calculate ROI’s of investments made in lumpsum.
The CAGR value indicates the annual rate at which an investment (beginning value) must grow to reach the final value. Below is the formula for CAGR:
How to interpret the CAGR number? Suppose you have Rs.1,00,000 available for long-term investing. You would like to invest the money in a debt-based mutual fund that is almost risk-free. A debt-based mutual fund can earn you a return of 7.5%.
But you’ve decided to invest alternatively in equity, gold, property, etc. If the alternative investment’s expected future CAGR is considerably more than 7.5%, you will consider it for investing.
The XIRR
XIRR full form is Extended Rate of Return. To better understand the application of XIRR, we must first know about the discount rate. But explaining the concept of discount rate here will lead to more confusion than clarity. So for the moment, let’s consider XIRR as another form of CAGR. But XIRR is used to calculate returns when there are multiple cash flows happening in an investment.
We will understand more about what I mean by multiple cash flows when we take specific examples. So let’s go ahead and start with the examples.
Examples
#1. CAGR – Lump-sum Investment in Mutual Fund
In this example, a lump-sum investment of Rs.60,000 was made in a mutual fund scheme. The investment was made on 01-Jan-2019. On the purchase date, the NAV of the scheme was Rs.574.84. Total units purchased were 104.38 nos (=60,000/574.84).
After holding the scheme for 11 months, all 104.38 nos units were sold on 02-Dec-2019. On the date of sale, the NAV of the scheme was Rs.614.86.
How to calculate return on investment for this example? As this example represents a typical lump-sum investing, having only two cashflows, we can use CAGR formula to calculate returns.
Let’s understand the two cashflows:
- Cash-outflow: Rs.60,000 – On 01-Jan-2019, Rs.60,000 was paid to buy the mutual fund units (104.38 nos).
- Cash-inflow: Rs.64,177.16 – On 02-Dec-2019, all 104.38 number units redeemed. Upon redemption of all units, an amount of Rs.64,177 is received.
We can use the CAGR formula to calculate the returns.
As shown in the above formula, CAGR, return on investment, will be 7.59% per annum.
#2. XIRR – SIP in Mutual Fund
In this example, investments of Rs.5,000 each month are made in a mutual fund scheme. Investments are made regularly starting from 01-Jan-2019 and ending on 02-Dec’2019. The total number of units accumulated in 12 transactions was 100.61 nos.
How to calculate return on investment for this example? As this example represents an investment having multiple cash flows, we can use the XIRR formula of Excel to calculate the returns. XIRR formula is this:
All in all, there were 13 nos of cash flows. Cash-outs were 12 nos (Rs.5,000 from Jan’19 to Dec’19). There was only one cash-in in the form of redemption of units on 02-Dec’19 (Rs.61,859.2).
How to calculate return on investment for this example? We will use the XIRR formula of Excel to do the calculations. Check the below screenshot that shows all the 13 cashflows and how the XIRR formula is applied to it.
As shown in the above calculation, XIRR, return on investment, will be 6.81% per annum.
#3. XIRR – Lump-sum Investment in a Stock
In this example, a lump-sum investment of Rs.25,000 was made in stock. The investment was made on 01-Jan-2010. On the purchase date, the share price was Rs.100. Total shares purchased were 250 nos (=25,000/100).
After holding the stock for 10 years, all 250 nos shares were sold on 02-Jan-2020. On the date of sale, the share price was Rs.315 each.
During the period of holding from 01-Jan-2010 to 02-Jan-2020, the shares also yielded dividend income for nine consecutive years. It means multiple cash-flows are happening in this investment on specific dates. That makes this example suitable for the XIRR formula application to calculate return on investment (ROI).
All in all, there were 11 nos of cash flows. Cash-out was just one of Rs.25,000 on 01-Jan-10. There were multiple cash-ins in the form of dividends and the sale of shares happening within the holding period. The calculation for total dividend income earned in a year is shown below:
How to calculate return on investment for this example? We will use the XIRR formula. Check the below screenshot that shows all the 11 cash-flows and the application of the XIRR formula to calculate returns.
As shown in the above calculation, XIRR, return on investment (ROI), will be 13.06% per annum.
#4. XIRR – Lump-sum Investment in a real estate property
In this example, a lump-sum investment of Rs.50,00,000 was made in property. The investment was made on 01-Jan-2010. After holding the property for 10 years, it was sold on 02-Jan-2020 for a price of Rs.1.25 Crore.
During the period from 01-Jan-2010 to 02-Jan-2020, the property also yielded rental income for nine years. That is again an example of multiple cash-flows. Hence XIRR formula application will be suitable to calculate return on investment (ROI).
All in all, there were 11 nos of cash flows. Cash-out was just one of Rs.50,00,000 on 01-Jan-10. There were multiple cash-ins in the form of rents and the sale of the property. The calculation for total rental income earned in a year is shown below:
How to calculate return on investment for this example? We will use the XIRR formula. Check the below screenshot that shows all the 11 cash-flows and the application of the XIRR formula to calculate returns.
As shown in the above calculation, XIRR, return on investment (ROI), will be 11.91% per annum.
Conclusion
Knowing how to calculate return on investment is a skill. We all must know about it. CAGR and XIRR are two formula’s that can do the job for us. XIRR specifically is the more versatile formula of the two. It can calculate returns for even complex investments (having multiple cash-flows).
Very well explained. This type of calculations are must.
I continued SIP in X MF for 3-4 years. Now, I am doing STP from X to Y MF of same fund house for few months. Can I use XIRR to highlight outflow & check for X MFs once STP duration over thru XIRR? if yes, how?
The return %pa is an approximate return number per year, right ? And not exact ? Meaning, x% pa approx in all the y years , right ?
How to interpret the return % number ?
It is an average number. In some years it will be more and in some, it will be less than the average.
Good afternoon Mr. Mani(sh)
Excellent!!! Keep up the good work! God Bless.
Hoping to see more such write ups.
Thanks