NPV and IRR Return Measures PDF

Preview

Summary

Discounted Cash Flow (DCF) analysis and NPV/IRR Calculations inExcel.NPV and IRR Excel FunctionsThis lab introduces you to the use of net-present value (NPV) and internal rate of return (IRR)functions in excel.NPV is a multi-period return measures that compares the present value of outflows from ani...

Description

Discounted Cash Flow (DCF) analysis and NPV/IRR Calculations in Excel. NPV and IRR Excel Functions This lab introduces you to the use of net-present value (NPV) and internal rate of return (IRR) functions in excel. NPV is a multi-period return measures that compares the present value of outflows from an investment schemes to the present value of the inflows. The inflows and outflows are discounted at the investors expected rate of return which compensates the investor for risk inherent in the project over the investment horizon. NPV is expressed in dollar terms and if NPV is positive the project increases the investor’s wealth, however if the NPV is negative then the investor’s overall wealth decreases if he chooses to invest in the project. An NPV of zero is still okay so long as the investment is not mutually exclusive with other investment options with positive NPV. The zero-NPV deals means that the investor will just realize their expected rate of return. The internal rate of return measures the return on funds invested in the project and by definition it is the discount rate applied to a projects cash flows resulting in NPV of zero. The investment IRR is compared with the investors expected rate of return and an IRR more than the investor’s expected rate of return indicates a good deal whilst IRR lower than the investor’s expected rate of return indicates a bad investment deal. The NPV and IRR functions on our calculator and Excel allow us to enter different values of payments for cash flows, thus overcoming a limitation imposed by the basic TVM calculations available on our calculator. NPV and IRR Functions in Excel: The Basics

Suppose we have an investment with the following projected cash flows (including initial investment, operating and reversion) over an assumed 5 year holding period:

0 Operating Cash Flow (CF) Equity Investment Net Sale Proceeds

-1500

Net Cash Flow

-1500

1

2

3

4

5

112

115

117

120

122

2100 112

115

117

120

2222

Given this info we want to calculate NPV and IRR. IRR: You want to use the =IRR(range,guess) function, where "range" is the cells that contain the estimated cash flows, INCLUDING the initial investment. "guess" is simply a rough estimate of what you think the IRR is close to - it is just a starting point for EXCEL. So, in a cell you type in the following, using a guess of 10% or 0.10: =IRR(B14:G14,0.10) This is what is in cell C27 - you should try it in another cell to make sure you get the same thing IRR=

13.84%

NPV: Now we use the =NPV(discount rate, range) function, remembering a quirky thing about EXCEL, that the NPV function assumes the first number in the range of cash flows is paid or received at the end of the first period. In most cases we will use NPV to determine PV, so the range DOES NOT INCLUDE the initial equity investment. Assuming a discount rate or required total return on equity of 12% you type the following in a cell to determine NPV: =NPV(.12,C14:G14)+B14 This is what is in cell C36 - again you should try this.

NPV = PV =

$112.04 $1,612.04

*** If you find that the decimals either don't appear or that you have way too decimals, click on the cell that contains the formula then click on "Format" in the top toolbar, then "Cells", then under "Format" click number or percent, whatever you want then adjust the decimals.

OK now you do one to make sure you know what the heck is going on…. For the following pro forma cash flows, determine the IRR and the NPV assuming a discount rate of 11.5%

0 Operating Cash Flow Equity Investment Net Sale Proceeds

-1150

Net Cash Flow

-1150

1

2

3

4

5

6

7

98

104

106

84

100

110

120

1390 98

104

106

Answers: IRR=11.02% , NPV=-$27.59

84

100

110

1510

You can prove the above using your calculator: Important to clear any previous cash flows from your calculator by 2nd F CA. If this is not done the new cash flows follow on from the previous ones in time, are further away from time zero, and are therefore discounted more heavily. HP10B: 2ND F CA, -1150 CF, 98 CF, 104 CF, 106 CF, 84 CF, 100 CF, 110 CF, 1510 CF then 11.5 I/YR and 2NDF NPV (in red), 2nd F IRR (in red)

Problems to solve using excel and your calculator: 1. Suppose a certain property is expected to produce net operating cash flows annually as follows, at the end of each of the next 5 years: $35,000, $37,000, $45,000, $46,000 and $40,000. In addition, at the end of the 5th year, assume that the property will be sold for $450,000. a) What is the NPV of a deal in which you would pay $400,000 for the property today assuming the required expected return or discount rate is 12% per year? b) What is the IRR of the deal and comment on whether this is a good investment? c) If you could get the property for only $375,000, what would be the expected IRR of your investment? 2. Consider a property with expected future cash flow of $25,000 per year for the next 5 years (starting one year from now). After that, the operating cash flow should step up 20% to $30,000 for the following 5 years. If you expected to sell the property 10 years from now at a price 10 times the next cash flow at the time, what is the value of the property if the required rate of return is 12%? 3. In the previous question, suppose the seller of the building wants $260,000. (a) Should you do the deal? Why not? (b) What is the IRR if you pay $260,000? How does this compare with the required rate of return of 12%? (c) What is the IRR if you get the seller to accept $248, 075 for the property? What is the NPV at that price?...