How to Use the IRR Function in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you’re in business, you need to start familiarizing yourself with the IRR (Internal Rate of Return) function in Excel. 

This function will help you save a lot of time and make wise investment decisions.

Some project managers are using IRR to help them assess the most profitable investments based on a regular cash flow, which is measured through the value of IRR (the higher, the better).

In this article, we’ll show you how to calculate the IRR on an investment using Excel. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Usage of IRR

The IRR Function on Excel provides quick key access in computing the internal rate of return for a series of cash flow periodically (i.e. monthly, annually). 

There is a plethora of use with IRR function in Excel. From getting a foresight if you will have a profitable purchase, comparing better purchases or investments to worthwhile financial expenditures.

Many business investments run through it. Mastering this will give you a prudent insight and more sound financial decisions for your company.

We will show some examples and walk you through the spreadsheet so you can get a better grip on how easy and simple it is to use in the future. 😊

Kasper Langmann, Co-founder of Spreadsheeto

But before that, let’s get familiarized with some terms we’re going to use.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Simple terms to remember

  • Initial Cost: This will be an initial investment which means it should be negative as the cash will flow out of business. (Remember that IRR formula should always have at least one negative value which represents the initial cash that went out of your pocket and was invested.
  • Revenue: The positive cash earned after investment.
  • Scrap Value: The physical assets’ value when it’s not in a usable state.

How to use the IRR function

IRR function seems daunting and confusing at first.

With its highly-technical terms and intimidating formulas when you try to google IRR, it seems like an impossible task to complete.

But in Excel, you can calculate the IRR in seconds.

First off, here’s the syntax of the IRR function in Excel:

=IRR (values, [guess])

Parameters:

  • ‘values’ – array or reference to cells with the values
  • ‘guess’ – optional; your own estimated IRR (by default, the value is .1 or 10%)

What this function will return is a percentage value that represents the internal rate of return.

Kasper Langmann, Co-founder of Spreadsheeto

We’ll show you how to use the IRR function step-by-step using two examples below.

Example 1: Purchasing and Investment Decision

Let’s say, you’re deciding whether to buy a machine to perform a particular task. 

Before buying this particular machine, it’s better for you to calculate if it will be a profitable decision by running it through an IRR computation.

The machine you’re planning to buy costs $7,000. You estimate the machine to last 9 years, generating more or less $2,500 in revenue annually. In the 10th year, you’re planning to sell the machine for its scrap value of $1,500.

For this example, let’s just say that a worthwhile investment should have an IRR higher than 17%.

So in this scenario, 18% and above IRR will be an excellent investment.

To calculate the IRR, simply input the formula and provide the appropriate parameters:

purchasing and investment decision irr example

For this scenario, buying a $7000-machine will yield profitable years for your business.

Example 2: Comparison of Better Purchase and Investment

This time you are torn between two machines. 

The one initially costs $7,000. But another salesperson approached with a better performing machine which costs $10,000 that promises more output ($2,800) with a scrap value of $2,000.

data set overview of irr function's second example

Using the IRR function in Excel, you’ll be able to have a better judgment on which one is the better investment.

Kasper Langmann, Co-founder of Spreadsheeto

To calculate the IRR of the second machine, use the following formula:

purchasing comparison of better purchase and investment irr example

In this scenario, although the $10,000 machine yield more revenue every year and a higher value after being deemed unusable (scrap value), the $7,000 machine still proved to be a better purchase and investment among the two options.

Wrapping things up…

Whether you’re a business person trying to make the best decisions or a mother determining which blender or air-condition should you buy, try considering using IRR for better investment decisions.

If you need a sample data, feel free to download and use the exercise file we included. 😊

Kasper Langmann, Co-founder of Spreadsheeto