Table of Contents
- INTRODUCTION
- WHAT IS ROI [ RETURN ON INVESTMENTS ] ?
- HOW RETURN ON INVESTMENT [ ROI ] IS CALCULATED IN GOOGLE SHEETS?
- STEPS TO CREATE TEMPLATE
- STEP 1: PUT THE LABELS AND FIX THE INPUTS
- STEP 2: PUT THE FORMULAS
- STEP 3: USING THE RETURN ON INVESTMENT [ ROI ] CALCULATOR [FREE TEMPLATE ]
- EXAMPLE 1: FIND OUT THE ROI AND CAGR WHEN WE INVESTED $10000 IN MUTUAL FUNDS AND THE VALUE AFTER THREE YEARS IS $19000.
- EXAMPLE 2: FIND ROI AND CAGR WHEN A HOUSE WAS BOUGHT FOR $100000 AND SOLD FOR $550000 AFTER FIFTEEN YEARS.
INTRODUCTION
In this article, we’ll learn about how to calculate ROI in google sheets with the help of an included template.
We all make investments through different instruments such as stocks, bonds, saving accounts and more. But how would we compare the different instruments if which one is the best?
It is very obvious that it’ll be clear when we can find out the RETURNS from that investment and compare them with one another.
There are many types of different kinds of returns. The simplest one is simple return which we call as ROI i.e. RETURN ON INVESTMENTS.
WHAT IS ROI [ RETURN ON INVESTMENTS ] ?
ROI is the return on investment.
It tells us the return from any investment 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 a 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 RETURN ON INVESTMENT [ 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
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.
- Enter Total Investment.
- The amount returned.
- Net profit
- ROI
- CAGR
- 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 RETURN ON INVESTMENT [ ROI ] CALCULATOR [FREE TEMPLATE ]
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.