Internal Rate of Return (IRR) Calculator

IRR Explained- 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 which is available if you try out the Free IRR calculator :

IRR Explanation

IRR (Internal Rate of Return) is increasingly becoming a requirements 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
Interest Payments		-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.

Using the IRR function for investment decisions

Very few people are aware of the added features that are available from Microsoft Excel which can simplify your life. Excel has a wide variety of functions and features that simplify your Excel usage experience and which enables you to complete many more tasks in less time. One of these functions is the internal rate of return calculator which assists you in answering financial queries on the feasibility of an investment.

The internal rate of return calculator function in Excel returns the internal rate of return for a series of cash flows. It is not necessary for the cash flows to be equal to one another, however it is necessary that the cash flows occur at regular intervals. Therefore we can say that the internal rate of return calculator calculates the interest rate received for an investment which consists of negative values, payments, and positive values such as income which occurs at regular intervals.

The internal rate of return calculator is closely linked to the net present value function, the rate of return as calculated by the internal rate of return calculator function is the interest rate corresponding to a zero net present value. Before you can use the internal rate of return calculator you will be required to set up a series of cash flows.

To learn more about the internal rate of return calculator you can visit www.auditexcel.co.za or you can book a training course. These functions are useful functions for everyday business and will assist your decision making when it comes to investment opportunities.