We have developed an IRR Calculator spreadsheet for easy calculation of a series of cash flows IRR. To see a fully functional free online version click here.
To obtain the full IRR Calculator spreadsheet as an excel download 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.