## Next Live Training Date:

20-22 January 2014

# IRR Calculator

We have developed an IRR Calculator spreadsheet for easy calculation of a series of cash flows IRR. To see a fully functional online version click here.

Below is a short explanation of how IRR works in layman’s language. This explanation and more examples involving house prices or car purchases are available in the premium IRR calculator.

Related Links: Learn IRR in Excel and build your own, NPV Excel function

## What is IRR?

IRR (Internal Rate of Return) is increasingly becoming a requirement when dealing with financial transactions and more importantly in the lives of individuals who have loans or are trying to start a business. The exact mathematical explanation is available in numerous web sites, but here we try and explain it in basic terms that is understandable to everyone.

Firstly, lets ignore IRR for now and think about a simple loan from a bank. Lets say you borrow 100 from the bank at an interest rate of 9% per year. You will pay 9 at the end of each year and then pay back the 100 at the end of year 5.

The cash flows look as follows:

 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Money Borrowed 100.00 InterestPayments -9.00 -9.00 -9.00 -9.00 -9.00 Loan Repayment -100.00 Net Cash Flows 100.00 -9.00 -9.00 -9.00 -9.00 -109.00

This represents a 9% loan. The IRR of this loan is shown below (look at the formula):

• IRR – 9%

So in this case the IRR is the same as the interest rate that the bank charges.

We like to constantly keep in mind that the IRR is the equivalent of what a bank would charge for certain cash flows Lets try another calculations using bank loans.

Lets assume again 100 at 9% but this time we need to pay the loan back during the period (as well as the interest). The bank has told us we need to repay 25.71 per year.

Below is what the debt would look like over the 5 years:

 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Debt Balance 0 100.00 83.29 65.08 45.23 23.59 Money Borrowed 100 Interest Charge 9.00 7.50 5.86 4.07 2.12 Loan Repayments -25.71 -25.71 -25.71 -25.71 -25.71 Closing Balance 100.00 83.29 65.08 45.23 23.59 0.00

In cash flow terms the following is relevant:

 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Money Borrowed 100.00 Repayments made -25.71 -25.71 -25.71 -25.71 -25.71 Net Cash Flows 100.00 -25.71 -25.71 -25.71 -25.71 -25.71

So you receive 100 and pay back 25.71 over the next 5 years. What is the IRR of this?

• IRR – 9%

Again it is 9% which equates to what the bank is charging you.

The IRR is calculating what interest rate would apply in order to make the inflows and outflows of money make mathematical sense so that they effectively cancel out.

If I told you that an investment cash flow would look as follows:

 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Money Invested -100.00 Money returned 0.00 0.00 50.00 25.00 67.02 Net Cash Flows -100.00 0.00 0.00 50.00 25.00 67.02

What do you think the IRR would be? Guess what, it is also 9% (see below):

• IRR – 9%

So the following net cash flows all equate to the same IRR of 9%

 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Scenario1 100.00 -9.00 -9.00 -9.00 -9.00 -109.00 Scenario2 100.00 -25.71 -25.71 -25.71 -25.71 -25.71 Scenario3 -100.00 0.00 0.00 50.00 25.00 67.02

This is why IRR is so useful. It allows you to compare different cash flows against each other to see which one is better. As long you tell the computer what cash is leaving you, and what you are getting back, or alternatively what cash you are receiving and how much you need to pay back you can compare.

## Lets try a practical example

• You need money and have approached 2 banks and they have given you the following options. Bank1- They will give you 100 at 9% per year.
• In return they expect to be paid 25.71 per year for 5 years plus an admin fee of 2 per year. Bank2- They will give you 100 at 8% per year.
• In return they expect to be paid 21.63 per year for 6 years plus an admin fee of 3 per year.
 Bank 1 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Loan Received 100.00 Annual repayments -25.71 -25.71 -25.71 -25.71 -25.71 Admin Fee -2.00 -2.00 -2.00 -2.00 -2.00 Net Cash Flow 100.00 -27.71 -27.71 -27.71 -27.71 -27.71
 Bank 2 Year 0 (today) Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Loan Received 100.00 Annual repayments -21.63 -21.63 -21.63 -21.63 -21.63 -21.63 Admin Fee -3.00 -3.00 -3.00 -3.00 -3.00 -3.00 Net Cash Flow 100.00 -24.63 -24.63 -24.63 -24.63 -24.63 -24.63

What are these banks actually charging you on an interest basis? Using IRR you can assess this. See the results below:

• IRR Bank 1 – 11.95%
• IRR Bank 2 – 12.45%

So even though the repayments for Bank2 are less than Bank1, the effective rate you are paying is higher. Without IRR it is difficult to be able to accurately compare there 2 streams of money.

If you can afford to make the repayments you should go for Bank1 or better yet negotiate harder with bank2 to give you the equivalent by dropping their admin (and any other fees)

The key for calculating IRR is that you must look at the actual cash flows i.e. the actual money that either leaves or comes into your bank account. It doesn’t matter how much you are told is owed to you, only what ends up in the bank account and more importantly when it will arrives in your bank account or back pocket.

## Handy Tips & Tricks

2014 World Cup Spreadsheet With the draw for the Brazil 2014 Soccer World Cup due on the 6th of December, we are ready and waiting with our 2014 World Cup Spreadsheet. As with the other prediction spreadsheets we have created … Continue reading

## Excel round to nearest 1000

Excel round to nearest 1000 Most people are aware of how to use the ROUND functions to round numbers to various decimal levels e.g. 5.5678 to either 5.568 or 5.57 or 5.6 or 6 depending on what you specify the … Continue reading

Just a quick note to say your blog is brilliant. Full of useful bits of info. After your courses, I am by far the best modeller in our office, and we regularly get comments from our business partners on the quality of our models. Thanks for all the help,

– Mike

## Solver Excel 2013

• 04:49
• Skill Level:
• What's my skill level

## Offset Excel 2013

• 06:17
• Skill Level:
• What's my skill level

## Vlookup Approximate Match

• 04:55
• Skill Level:
• What's my skill level

## Vlookup Exact Match

• 06:31
• Skill Level:
• What's my skill level