HOW TO CALCULATE CAGR [ COMPOUNDED ANNUAL GROWTH RATE ] IN GOOGLE SHEETS ?

INTRODUCTION

We all make investments through different instruments but how would we decide which one is best for us.

It is very obvious that it can be clarified only with the help of few well proven instruments such as CAGR, ROI, IRR etc.

Out of the few mentioned above, CAGR is one of the most widely used instrument to check the performance of any financial scheme.

Compound annual growth rate (CAGR) is the net annual rate of return which will be invested again annually. It means if we got 5% return in one year , it’ll be again invested which will make the principle as 105% for the next year and so on.

In this article, we’ll find out the way to create a template for CAGR and find out the CAGR in google sheets with the given template.

WHAT IS CAGR [ COMPOUND ANNUAL GROWTH RATE ] ?

CAGR is compound annual growth rate.

It tell us the compounded return on different investments.

It is helpful for comparing different investment options where the returns vary such as STOCK MARKET over a period of time.

CAGR is expressed as a PERCENTAGE.

If CAGR is positive, you are gaining from the investment.

If CAGR is negative, the investment is not good for you and you are losing form the investment.

Whenever comparing the CAGR, always remember that time duration will be the same for different cases.

Suppose we invested $1000 in any financial scheme. After first year the amount became $1200. In the next year , there was lesser increment and the amount became $1250 only. In the third year the amount became $1500. Now, we can see that if we check the Returns yearly, they are different. It is the appropriate case to use CAGR.

CAGR will give us the net yearly returns, by which our money is growing [ condition applied that the amount grown is again reinvested, which we call as compounding.

The CAGR will become clearer when we take a few examples.

NOTE: For the above case CAGR stands at 14.47%

HOW CAGR IS CALCULATED IN GOOGLE SHEETS?

Google Sheets is a spreadsheet type of application.

COMPOUNDED ANNUAL GROWTH RATE provides us a better scenario for different investment returns.

The CAGR is calculated with the following formula

CAGR FORMULA

Let us now create a READY TO USE template so that we can find out ROI and CAGR instantly using google sheets.

A very simple and easy to use instrument to calculate the returns is ROI i.e. Return on investment which is perhaps the first step we try when we are deciding about the investments .

CLICK HERE TO LEARN ROI.

STEPS TO CREATE TEMPLATE

The formulas are very simple which we can use create the template easily.

Before we start working on the template, let us see the final look.

STEP 1: PUT THE LABELS AND FIX THE INPUTS

In our formula, we have just a few inputs.

Enter the labels as shown in the picture.

  1. Enter Total Investment.
  2. The amount returned.
  3. Net profit
  4. ROI
  5. CAGR
  6. DURATION

The input cells will be against the labels 1, 2 and 3 as they are the only inputs which we need.

The solution is overlapping for both CAGR and ROI. For ROI, we just need the inputs as 1 and 2 but for CAGR , we need 1 ,2 and 3.

After we have put all the labels, the calculator looks like the one in the following picture.

STEP 2: PUT THE FORMULAS

Now, it is time to put the formulas.

The formulas will be put against ROI and CAGR.

The first requirement is NET PROFIT which is given by the formula

=C9-C6

where C9 is TOTAL INVESTMENT and C6 is TOTAL AMOUNT RETURNED.

For ROI, the formula used will be

=C13*100/C6

or in the Google sheets we can simply use the formula C13/C6 press % with this to find out the percentage . It’ll get us the output we want. [ C13 contains the NET PROFIT and C6 contains the TOTAL INVESTMENT.

For CAGR, the formula used is

=(((C9/C6)^(1/C11)-1))

where C9 is the FINAL VALUE and C6 is the initial investment and C11 is the number of years.

The following animated picture shows all the formulas used in the template.

STEP 3: USING THE CALCULATOR

The use of the calculator is very simple.

Put the values in the 1,2 and 3 i.e. 1.Enter Total Investment which is the Total investment which you have invested in any financial instrument.

2, Total Amount Returned, which is the amount which you received at the maturity or till any point of time. [ The time when you want to check the performance]

and 3 which is the DURATION in years, which is the total duration for which we are finding out the results.

The calculator is ready for the use.

CLICK HERE TO COPY THE READY TO USE SHEET.

We’ll take a few examples to see how our calculator works.

EXAMPLE 1: FIND OUT THE ROI AND CAGR WHEN WE INVESTED $10000 IN MUTUAL FUNDS AND THE VALUE AFTER THREE YEARS IS $19000.

FOLLOW THE STEPS TO FIND OUT THE ROI AND CAGR.

  • Enter the Initial investment in the first input [ENTER TOTAL INVESTMENT] which is $10000 , enter the FINAL VALUE as the maturity amount which is $19000 and put the duration in years in the DURATION option which is 03 years for the example.
  • The result will instantly appear.
  • The picture shows the process.

EXAMPLE 2: FIND ROI AND CAGR WHEN A HOUSE WAS BOUGHT FOR $100000 AND SOLD FOR $550000 AFTER FIFTEEN YEARS.

FOLLOW THE STEPS TO FIND OUT THE ROI AND CAGR.

  • Enter the Initial investment in the first input [ENTER TOTAL INVESTMENT] which is the initial investment on the house i.e. $100000 , enter the FINAL VALUE as $550000 which is the price you got while selling the house as the maturity amount and put the duration in years in the DURATION option which is 15 years.
  • The result will instantly appear.
  • The picture shows the process.
EXAMPLE SHOWING THE USE OF CAGR CALCULATOR