In this section we discuss the internal rate of return (IRR):
The IRR of a series of cash flows is the discount rate that sets the net present value of the cash flows equal to zero.
Before we explain in depth (in the next section) why you want to know the IRR, we explain how to compute it. Let’s go back to the example on page000: If you pay $800 today to your local pawnshop, the owner promises to pay you $100 at the end of year 1, $150 at the end of year 2, $200 at the end of year 3, $250 at the end of year 4 , and $300 at the end of year 5.
Discounting these cash flows at rate r, the NPV can be written:
Nomenclature—Is it a discount rate or an interest rate?
In some of the examples above we’ve used discount rate instead of interest rate to describe the rate used in the net present value calculation. As you will see in further chapters of this book, the rate used in the NPV has several synonyms: Discount rate, interest rate, cost of capital, opportunity cost—these are but a few of the names for the rate that appears in the denominator of the NPV:
(
1)
tCash flowin year t r
Discount rate Interest rate Cost of capital Opportunity cost
+
↑
( ) ( ) (
2) (
3) (
4)
5100 150 200 250 300
800 1 1 1 1 1
NPV r r r r r
= − + + + + +
+ + + + +
In cells B16:B32 of the spreadsheet below, we calculate the NPV for various discount rates. As you can see, somewhere between r = 6% and r = 7%, the NPV becomes negative.
1
r, interest rate 6.6965%
Year Payment
NPV 0.00 <-- =NPV(B2,B6:B10)+B5 IRR 6.6965% <-- =IRR(B5:B10)
Discount
rate NPV
0% 200.00 <-- =NPV(A16,$B$6:$B$10)+$B$5 1% 165.86 <-- =NPV(A17,$B$6:$B$10)+$B$5 2% 133.36 <-- =NPV(A18,$B$6:$B$10)+$B$5
3% 102.41
CALCULATING THE IRR WITH EXCEL
NPV and the Discount Rate
-250
In cell B13, we use Excel’s IRR function to calculate the exact discount rate at which the NPV becomes 0. The answer is 6.6965%; at this interest rate, the NPV of the cash flows equals zero (look at cell B12). Using the dialog box for the Excel IRR function:
Dialog box for IRR function
Notice that we haven’t used the second option (“Guess”) to calculate our IRR. We discuss this option in Chapter 4.
What does the IRR mean?
Suppose you could get 6.6965% interest at the bank and suppose you wanted to save today to provide yourself with the future cash flows of the example on page000:
• To get $100 at the end of year 1, you would have to put the present value 100 93.72
1.06965= in the bank today.
• To get $150 at the end of year 2, you would have to put its present value
( )
2150 131.76
1.06965
= in the bank today.
• And so on … (see the picture below)
The total amount you would have to save is $800, exactly the cost of this investment opportunity. This is what we mean when we say that:
The internal rate of return is the compound interest rate you earn on an investment.
Time 0 1 2 3 4 5
Save for time 1's $100
$100/(1+6.6965%) 93.72
FV=93.72*(1+6.6965%)
=$100.00 Save for time 1's $150
$150/(1+6.6965%)2 131.76
FV=131.76*(1+6.6965%)2
= $150.00 Save for time 3's $200
$200/(1+6.6965%)3 164.66
FV=164.66*(1+6.6965%)3
= $200.00 Save for time 4's $250
$250/(1+6.6965%)4 192.90
FV=192.90*(1+6.6965%)4
=$250.00 Save for time 5's $300
$300/(1+6.6965%)5 216.95
FV=216.95*(1+6.6965%)5
= $300.00 Total saving at time 0 800.00
Using IRR to make investment decisions
The IRR is often used to make investment decisions. Suppose your Aunt Sara has been offered the following investment by her broker: For a payment of $1,000, a reputable finance company will pay her $300 at the end of each of the next four years. Aunt Sara is currently getting 5% on her bank savings account. Should she withdraw her money from the bank to make the investment? To answer the question, we compute the IRR of the investment and compare it to the bank interest rate:
1
IRR 7.71% <-- =IRR(B3:B7)
USING IRR TO MAKE INVESTMENT DECISIONS
The IRR of the investment, 7.71%, is greater than 5% Sara can earn on her alternative investment (the bank account). Thus she should make the investment.
Summarizing:
In using the IRR to make investment decisions, an investment with an IRR greater than the alternative rate of return is a good investment and an investment with an IRR less than the alternative rate of return is a bad investment.
Using IRR to choose between two investments
We can also use the internal rate of return to choose between two investments. Suppose you’ve been offered two investments. Both Investment A and Investment B cost $1,000, but they have different cash flows. If you’re using the IRR to make the investment decision, then you would choose the investment with the higher IRR. Here’s an example:
1
IRR 24.74% 22.26% <-- =IRR(C3:C7)
USING IRR TO CHOOSE BETWEEN INVESTMENTS
We would choose Investment A, which has the higher IRR.
To summarize:
In using the IRR to choose between two comparable investments, we choose the investment which has the higher IRR. [This assumes that: 1) Both investments have IRR greater than the alternative rate. 2) The investments are of comparable risk.]
Using NPV and IRR to make investment decisions
In this chapter we have now developed two tools, NPV and IRR, for making investment decisions. We’ve also discussed two kinds of investment decisions. Here’s a summary:
“Yes or No”:
Choosing whether to undertake a single investment
“Investment ranking”:
Comparing two investments which are mutually exclusive NPV criterion The investment should be
undertaken if its NPV > 0:
Investment A is preferred to investment B if
NPV(A) > NPV(B)
IRR criterion The investment should be undertaken if its IRR > r, where r is the appropriate discount rate.
Investment A is preferred to investment B if
IRR(A) > IRR(B).
In Chapter 3 we discuss further implementation of these two rules and two decision problems.