Athabasca University



Slide #1: Lecture 6 – Internal rate of return (IRR) Calculation

Welcome to Lecture 6: Internal rate of return (IRR) calculation.

Slide #2: Topics covered

We will cover the following 7 topics in this lecture:

1. The definition of IRR and the formula used to calculate IRR.

2. The type of problem that we are trying to solve with IRR.

3. The variables that we must know in order to calculate IRR and make decisions based on it.

4. The process of calculating IRR.

5. The IRR rule, which will help us make capital budgeting decisions based on the calculated IRR.

6. We will then run through a numerical example of IRR calculation.

7. Then you are on your own – you will be given a practice question and asked to calculate the IRR and come to a decision based on the calculated IRR.

Slide #3: Definition of IRR

Now, the definition of Internal rate of return, or the IRR. IRR is defined as the discount rate that makes the NPV (Net Present Value, see below) of a project/asset equal to 0. The general formula used to derive IRR is:

NPV = Σ[CFt / (1 + IRR)t] = 0

Where

NPV = net present value of the project

CFt = cash flow at time t

IRR = internal rate of return on the project

This is the standard NPV formula. The only difference in this case is that the formula says that the NPV of the sum of all cash flows incremental to the project, discounted by the IRR, is equal to zero.

Slide #4: The Problem we want to solve

An IRR-based problem usually starts like this: You have a potential project or asset. It will generate a cash flow stream of CF1, CF2, etc, up to CFN, where the ‘N’ refers to the length of life of the project or asset. The project or asset will cost CF0, with all the cash outflows occurring at time 0. You will usually have to ask some variation of the question: “What is the internal rate of return, or IRR, on this project or asset?”

A problem statement can then be written down as: Should we invest in this project or asset if the required rate of return is given as x%?

Slide #5: Timeline of cash flows

Given the cash outflow of CF0 and cash inflows of CF1, CF2, …, up to CFN, where N is the length of life of the project or asset, we now have enough information to draw the following timeline:

t=0 1 2 3 …… N ∞

-CF0 CF1 CF2 CF3 …… CFN

Given that the IRR is the discount rate that causes the NPV to equate 0, we can write out the formula as:

[pic]

This formula shows that NPV is calculated as the present value of all the cash inflows minus the cash outflows. When we set this formula equal to 0, we are then solving for IRR using this formula.

Slide #6: Known variables

So, looking at the formula we have for solving for IRR, we can identify the known variables that will allow us to solve for IRR. These known variables are:

- The Cash outflow, CF0

- The Cash inflows, CF1, CF2, …, and CFN

- The length of life of the project or asset, N

Thus, so long as we know or can derive these numbers, we can solve for IRR.

Slide #7: IRR calculation approaches

There are three ways to solve for IRR. We can either use trial and error or a financial calculator, or we can write a spreadsheet program. Since these are best described using an example, we will get to these methods later.

Slide #8: The IRR Rule

For now, let’s discuss the IRR rule for capital budgeting decisions. The IRR rule is: reject a project if its IRR is less than its required rate of return of x%, and accept the project if conditions are otherwise.

Slide #9: Numerical Example

And now, finally, we get to a numerical example. As promised previously on Slide #7, let’s use this example to demonstrate the three methods of calculating the IRR.

So, let’s say that Adam Smith is deciding on whether to buy a new computer for $1300. This computer will generate for him a stream of cash inflows of $500, $600, and $700 in the next three years. So, we have $1300 as the CF0, $500 as the CF1, $600 as the CF2, $700 as the CF3, and N is 3.

We ask “what, then, is the internal rate of return on this computer?” and the problem statement would be: If the required rate of return on this computer is 15%, should Adam buy it?

Slide #10: Method 1 – Trial and error

The first method we listed above is trial-and-error. Trial-and-error means that we try different values of IRR to get the formula as close to 0 as possible. I usually start with IRR of 10% or 0.1. Plug IRR = 0.1 into the following formula and calculate the NPV:

[pic]

If the NPV is equal to 0, we can say then that the IRR is equal to 10%. In this way, if the NPV is greater than 0, we know that 10% is NOT the IRR. We also know that the IRR must be greater than 10%, because a higher discount rate is needed to get the NPV down to 0. So, we try a larger IRR of, say, 11%. In contrast, if the NPV is less than 0, we start by trying a smaller IRR, say 9%.

We plug the new IRR into the formula again, and calculate the NPV. If the NPV does not equal 0, we try another number, and so on, and so forth, until we get to an IRR that gets us as close to a zero NPV as possible. This is obviously why we call this method "trial and error".

With this numerical example, we have

CF0 = 1300

CF1 = 500

CF2 = 600

CF3 = 700

N = 3

When we plug these known numbers into the formula, and we get

NPV = -1300 + (500/(1+IRR)1) + (600/(1+IRR)2) + (700/(1+IRR)3)

Slide #11: Numerical Example (cont.) – Trial and error

First, we try IRR = 0.1. Plugging 0.1 into the formula as IRR, we do the calculations:

NPV = -1300 + (500/(1+0.1)1) + (600/(1+0.1)2) + (700/(1+0.1)3)

= -1300 + 454.55 + 495.87 + 525.92

= 176.33 ˃ 0

We get an NPV of 176.33, which is not equal to 0, and is greater than 0.

Since the NPV is greater than 0, we then try a higher IRR, say, 15%. Plugging 0.15 into the formula as IRR, we get

NPV = -1300 + (500/(1+0.15)1) + (600/(1+0.15)2) + (700/(1+0.15)3)

= -1300 + 434.78 + 453.69 + 460.26

= 48.73 ˃ 0

We find that the NPV is 48.73, which is still greater than 0. This means that the IRR must be higher than 15%.

We repeat this exercise until we get the NPV as close to 0 as possible. If you try this out on your own, you will eventually find that NPV = 0 occurs at an IRR of 17.12.

Slide #12: Method 2 – Financial calculator

Wow! That was a lot of work, which is why nobody likes to do trial and error stuff, especially now that we have financial calculators to complete such tedious work for us within seconds.

This brings us to the second method of calculating IRR: Using the financial calculator. There are many brands and models of financial calculators, and it is not possible for us to discuss how to use all of them. And so, we choose the model pictured below: The Sharp EL-738C.

[pic]

If you have a different model of financial calculator, the logic for using these financial calculators is fairly similar and should not be too much trouble. If you do not know how to use the NPV and IRR functions on your financial calculator, please consult your calculator’s operation manual. Another easy way to do this is to google the make and brand of your financial calculator, and you will usually be able to find an online version of the calculator`s operation manual. If the online manual is in PDF format, you will even be able to use the keyword search function by pressing the Ctrl and F keys simultaneously.

And now, onwards and upwards. To the numerical example.

First, a little bit of taking care of business. Before you start calculations on any new problem, it is advisable to clear the memory on your financial calculator. To clear the memory on the Sharp EL-738C calculator, simply press the buttons: 2ndF ALPHA 0 0.

To enter the cash flows from our numerical example, we do the following:

Type out the number: 1300

Press the button: +/-

Press the button: ENT

This enters the number -1300 as the cash flow in time 0.

To enter the next three cash inflows, we do the following:

Type out the number: 500

Press the button: ENT

Type out the number: 600

Press the button: ENT

Type out the number: 700

Press the button: ENT

To check that you have entered the correct cash flow numbers, press the CFi button, and then use the up and down arrow buttons to check the cash flows numbers.

Slide #13: Numerical Example (cont.) – Financial calculator

To access the NPV function on the Sharp EL-738C financial calculator, we simply press the buttons: 2ndF

CFi.

To check that the NPV is set to 0, press the down arrow button. You should see the NPV and the number 0 displayed on the screen.

To calculate the IRR, press the up arrow button. You should see IRR displayed on the screen. Then press the button: COMP. It will take a moment, and then the answer should pop up. In this case, the answer is 17.12181066, which is approximately the answer we obtained previously by using the trial and error approach.

Slide #14: Method 3 – Spreadsheet program

And now, we come to Method 3: using a spreadsheet program. For our numerical example, I have constructed the following table:

| |A |B |C |

|1 |Time period |Cash Flow |Present value |

|2 |0 |-1300 |=B2/((1+B7)^A2) |

|3 |1 |500 |=B3/((1+B7)^A3) |

|4 |2 |600 |=B4/((1+B7)^A4) |

|5 |3 |700 |=B5/((1+B7)^A5) |

|6 | |NPV = |SUM(C2:C5) |

|7 |IRR |0.1 | |

|8 | | | |

|9 | | | |

The most important thing with writing a spreadsheet program is the placement of the known variables. In this example, I placed the time periods in the first column, in cells A2 to A5. I then placed the cash flows in the column to the right of the time periods, in cells B2 to B5. I have also placed the plug cell for IRR in cell B7.

Next, I placed the present value calculations in the column to the right of the cash flows, in cells C2 to C5. Notice that these formulas are all based on cells with known values. For example, in the formula in cell C2, B2 refers to the cash flow in cell B2, which is -1300; B7 refers to the cell containing the IRR plug number; and, A2 refers to the cell containing the time period associated with the cash flow of -1300, which is time 0. The formula in cell C2 then gives us:

-1300/(1+IRR)^0

Cell C3 contains the formula:

500/(1+IRR)^1

Cell C4 contains the formula:

600/(1+IRR)^2

Cell C5 contains the formula:

700/(1+IRR)^3

The NPV is calculated in cell C6, which is equal to the sum of the present values of all the cash flows.

Slide #15: Spreadsheet function – Goal Seek

To solve for the IRR that causes NPV to be equal to 0, we use the Goal Seek function in Excel. Simply click on the top menu item: Data, then click on What-If Analysis, then click on Goal Seek.

A new window will pop up, asking you to fill in the blank spaces for "Set cell:". "To value:", and "By changing cell:".

In the "Set cell:" blank, put in the plug cell for the NPV, which in our program is cell C6.

In the "To value:" blank, put in the value we want to set for NPV, which is 0.

In the "By changing cell:" blank, put in the plug cell for the IRR, which in our program is cell B7.

Then click OK, and then click OK again.

Slide #16: Numerical Example (cont.) – Spreadsheet solution

Your spreadsheet solution should look something like this:

| |A |B |C |

|1 |Time period |Cash Flow |Present value |

|2 |0 |-1300 |-1300 |

|3 |1 |500 |426.906 |

|4 |2 |600 |437.3969 |

|5 |3 |700 |435.6971 |

|6 | |NPV = |5.76E-07 |

|7 |IRR |0.171218 | |

We get an IRR of 0.171218, which is 17.1218%, and this is the same answer that we obtained before using the trial and error method and the financial calculator.

Slide #17: The way to the heart of finance

And now, since the way to the heart of finance is through practice, practice, practice, here’s a practice question for you. Try to see if you can figure out the IRR for this project.

A company is considering investing in a project that will cost $2,500,000. This project will generate before-tax cost savings of $500,000 for the next 10 years. If the depreciation is straight-line, with no residual value at the end of the 10th year, and the tax rate is 30%, what is the IRR of this project? Should the company invest in this project if its required rate of return is 15%?

We first write down the information given:

CF0 = $2,500,000

BTCS = $500,000

N = 10

T = 0.3

r = 0.15

Depreciation = straight-line for 10 years

Guided answers are provided on the next two slides. Have fun!

Slide #18: Hints

Step 1: Identify cash flows:

Cash outflow:

Initial cost = CF0 = $2,500,000

Cash inflows:

Annuity #1:

After-tax cost savings

= Before-tax cost savings x (1 – Tax rate)

= $500,000 x (1 – 0.3)

= $350,000

Annuity #2:

Depreciation tax shield

= Annual depreciation x Tax rate

= (Initial cost / N) x Tax rate

= ($2,500,000 / 10) x 0.3

= $75,000

Total cash inflow annuity = Annuity #1 + Annuity #2 = $350,000 + $75,000 = $425,000

Slide #19: Check Answers

|Year |Cash Flow |

|0 |-2,500,000 |

|1 |$350,000 + $75,000 = $425,000 |

|2 |$425,000 |

|3 |$425,000 |

|4 |$425,000 |

|5 |$425,000 |

|6 |$425,000 |

|7 |$425,000 |

|8 |$425,000 |

|9 |$425,000 |

|10 |$425,000 |

The IRR for this project is: 11.02788231%, and the conclusion is to reject the project.

Slide #20: End of Lecture 6

Here ends the lecture on IRR calculation.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download