HOW TO CALCULATE IRR [INTERNAL RATE OF RETURN ] USING GOOGLE SHEETS ?

Table of Contents

INTRODUCTION

IRR or Internal Rate of Return is another instrument in taking the smart business decisions based on the financials.

INTERNAL RATE OF RETURN IS THE RETURN RECEIVED ON AN INVESTMENT WITHOUT TAKING ANY EXTERNAL FACTOR INTO CONSIDERATION.

Google Sheets is a spreadsheet application offered by Google.

Spreadsheet application provides many functionalities which help us in the analysis of the data in any way we desire.

Similarly , if we want to calculate the IRR or Internal Rate or Return on any investment, we have got a builtin function for this calculation.

The function is know as IRR function.

In this article, we’ll learn the process of using IRR function to get the INTERNAL RATE OF RETURN on any investment.



WHAT IS IRR [ INTERNAL RATE OF RETURN ] ?

Although we are here just to learn the usage of IRR FUNCTION and find out the INTERNAL RATE OF RETURN for an investment, it is always necessary to know the basics of the terms so that we can always keep an eye on the results if some absurd value is not showing up.

So, it is necessary to know the basics of INTERNAL RATE OF RETURN.

Internal Rate of Return is simply the rate of return which an investment will be giving to you without taking into account any external factors such as inflation, financial risk etc.

In this article, we’ll learn the process of finding out IRR with the help of IRR FUNCTION provided in GOOGLE SHEETS.

The standard formula of INTERNAL RATE OF RETURN is



SYNTAX OF IRR FUNCTION

The syntax of the IRR FUNCTION [ The way function is written ] is

=IRR( CASHFLOW / PAYMENTS, GUESS RATE )

CASHFLOW is the payments which you get or you pay. Positive values are the returns you are getting and negative values are the ones which you are paying.

RATE GUESS [ optional ] is the estimate of IRR. [ 0.1 by default ]. [DISCUSSED LATER IN THE SECTION ]



Let us understand the method using a few examples.

EXAMPLE 1: FIND OUT IRR FOR THE GIVEN CASHFLOWS.

The invested amount is $50000.

The cash flow for the first, second, third and fourth year are $2000,$4000, $5000 and $10000 respectively.

SOLUTION:

We can simply solve this problem by using the IRR FUNCTION.



FOLLOW THE STEPS TO CALCULATE IRR FOR THE GIVEN DATA

  • Select the cell where you want the result.
  • Enter the function as =IRR( CASH FLOWS, RATE GUESS ).
  • For our example the function will be =IRR(C3:C7,0.1)
  • The result will appear as -24%.
  • The Rate guess is taken as 0.1

EXAMPLE SHOWING IRR CALCULATION

EXPLANATION:

Let us understand the formula used for the calculation.

The formula used is =IRR(C3:C7,0.1).

The first argument is the range containing our cashflow including the investment.

The INVESTMENT is shown with a NEGATIVE SIGN as it is outflow.

Inflows are taken as positive values.

The second argument is simply the rate guess.

The result will appear as soon as Google Sheets is able to find out the result.



WHAT IS THE ROLE OF RATE GUESS IN IRR FUNCTION?

You must be wondering about the importance of RATE GUESS which is the second argument in the function.

The fact is that the calculation of IRR function is iterative and if you provide a guess, the calculation will start with the guess.

If the number of iterations is more than 20 and no result is found NUM! error appears.

Even if we omit this argument, most of the times , the result is achieved.



CALCULATE THE IRR USING THE FREE TEMPLATE

  • We have created a simple template for you to use the IRR calculation.
  • Simply click the link given below.

CLICK HERE TO COPY IRR CALCULATION TEMPLATE

  • Click MAKE A COPY after you reach the copying option.
  • If you are not logged in to google account, you’ll need to login first.
  • If you don’t have a google account, you’ll need to create one.
  • After the copy is created, it’ll open in Google Sheets.
  • Go to the sheet IRR CALCULATION TEMPLATE

IRR TEMPLATE SCREEN

DEMONSTRATION:

Let us try the template and enter the same values.

The result can be seen in the animation below.

DEMONSTRATION OF TEMPLATE USAGE

STEPS TO USE THE TEMPLATE

The steps are quite simple.

  • Enter the RATE GUESS first.
  • Enter the Cashflows in the sequence as shown in the picture above.
  • The negative cashflows are the investments.
  • Positive cashflow is the return.
  • The result will appear instantly.

NOTE* IF YOU FIND #NUM! ERROR, CHANGE THE RATE GUESS VALUE.