HOW TO CALCULATE ROI [ RETURN ON INVESTMENT ] 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’ll be clear when we can find out the RETURNS from that investment.

We call this ROI i.e. RETURN ON INVESTMENTS.

In this article, we’ll find out the way to find out the return on your investment in google sheets with the given template.

WHAT IS ROI [ RETURN ON INVESTMENTS ] ?

ROI is the return on investment.

It tell us the return from our different types of investments say it’s property, mutual funds, stocks etc. . We can compare the different investments on the basis of ROI for different instruments.

The ROI is usually expressed as the percentage.

It is simply the RATIO between the Return to the original investment [ in a fixed period of time if we want to compare ] i.e .NET PROFIT/ INVESTMENT.

So ROI becomes NET PROFIT/TOTAL INVESTMENT X100. [Simple percentage of the Net profit to the total investment ]

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

If ROI is negative, the investment is not good for you and you are losing from the investment.

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

Suppose, we invested $1000 and the amount became $1500 in one year [ for the first case ] but the amount became $2000 in the second case.

So, ROI for the first case will be (500/1000 x100)= 50% whereas

ROI for the second case will be (1000/1000 x 100 ) = 100%

HOW ROI IS CALCULATED IN GOOGLE SHEETS?

Google Sheets is a spreadsheet type of application.

The formula for the ROI is NET PROFIT/TOTAL INVESTMENT X100 , the duration is specified too.

THE DURATION NEVER COMES IN THE FORMULA BUT IS VERY IMPORTANT WHILE COMPARING TWO DIFFERENT INVESTMENT INSTRUMENTS.

ROI will be calculated in the google sheets by simply taking the input from the user for

INITIAL INVESTMENT and the FINAL AMOUNT.

The formula will be

ROI FORMULA

It’ll return the ROI in percentage.

ROI provides us the net return on our investment but CAGR or COMPOUNDED ANNUAL GROWTH RATE provides us a better scenario for analyzing different investment returns. CLICK HERE TO LEARN ABOUT CAGR.

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

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. 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.

COPY THIS SHEET AND USE THE CALCULATOR

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 ROI CALCULATOR

Leave a Reply

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