3.2 General Discount Rate
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97
Problem. A project requires a current investment of $100.00 and yields future expected cash flows of
$21.00, $34.00, $40.00, $33.00, and $17.00 in periods 1 through 5, respectively. All figures are in
thousands of dollars. For these expected cash flows, the appropriate discount rate starts at 8.0% in period
1 and declines to 7.0% in period 5. What is the net present value of this project?
Solution Strategy. We will calculate the Net Present Value of this project using a Time Line. This is the
only possible way to calculate the project NPV in the general case where the discount rate changes over
time. Excel’s NPV function can not be used because it is limited to the special case of a constant discount
rate. And there is no simple formula for NPV, short of typing in a term for each cash flow.
FIGURE 3.2 Spreadsheet for Net Present Value - General Discount Rate.
How To Build Your Own Spreadsheet Model.
1. Inputs. Enter the Current Investment in B6, the Future Cash Flows in the range C7:G7, and the
Discount Rates in the range C8:G8.
2. Net Present Value using a Time Line. Create a time line from period 0 to period 5. Calculate a
cumulative discount factor. Determine the project cash flows in periods 0 through 5. Calculate the
present value of each cash flow and sum the present values as follows.
o Period. Enter 0, 1, 2, …, 5. in the range B11:G11.
o Cumulative Discount Factor. Enter 0.0% in the cell B12. The (Cumulative Discount
Factor on date t) = (1 + Cumulative Discount Factor on date t-1) * (1 + Discount Rate on
date t) - 1. Enter =(1+B12)*(1+C8)-1 in cell C12 and copy it across.
o Cash Flows. The current investment is a negative cash flow. Enter =-B6 in cell B13.
Future cash flows are positive cash flows. Enter =C7 in cell C13 and copy it across.
o Present Value of Each Cash Flow = (Cash Flow on date t) / (1+ Cumulative Discount
Factor on date t). Enter =B13/(1+B12) in cell B14 and copy it across.
o Net Present Value = Sum over all periods of the Present Value of Each Cash Flow.
Enter =SUM(B14:G14) in cell B15.
The Net Present Value of this project is $17.42. This spreadsheet can handle any pattern of discount rates.
For example, it can handle the special case of a constant discount rate.
FIGURE 3.3 General Spreadsheet Implementing a Constant Discount Rate.
The Net Present Value of this project is $16.17. Notice this is the same answer as the previous
spreadsheet for the Net Present Value - Constant Discount Rate. The general discount rate spreadsheet is
the most general way to do discounting and is the approach we will use throughout this book.
Problem. A project requires a current investment of $100.00 and yields future expected cash flows of
$21.00, $34.00, $40.00, $33.00, and $17.00 in periods 1 through 5, respectively. All figures are in
thousands of dollars. For these expected cash flows, the appropriate discount rate starts at 8.0% in period
1 and declines to 7.0% in period 5. What is the net present value of this project?
Solution Strategy. We will calculate the Net Present Value of this project using a Time Line. This is the
only possible way to calculate the project NPV in the general case where the discount rate changes over
time. Excel’s NPV function can not be used because it is limited to the special case of a constant discount
rate. And there is no simple formula for NPV, short of typing in a term for each cash flow.
FIGURE 3.2 Spreadsheet for Net Present Value - General Discount Rate.
How To Build Your Own Spreadsheet Model.
1. Inputs. Enter the Current Investment in B6, the Future Cash Flows in the range C7:G7, and the
Discount Rates in the range C8:G8.
2. Net Present Value using a Time Line. Create a time line from period 0 to period 5. Calculate a
cumulative discount factor. Determine the project cash flows in periods 0 through 5. Calculate the
present value of each cash flow and sum the present values as follows.
o Period. Enter 0, 1, 2, …, 5. in the range B11:G11.
o Cumulative Discount Factor. Enter 0.0% in the cell B12. The (Cumulative Discount
Factor on date t) = (1 + Cumulative Discount Factor on date t-1) * (1 + Discount Rate on
date t) - 1. Enter =(1+B12)*(1+C8)-1 in cell C12 and copy it across.
o Cash Flows. The current investment is a negative cash flow. Enter =-B6 in cell B13.
Future cash flows are positive cash flows. Enter =C7 in cell C13 and copy it across.
o Present Value of Each Cash Flow = (Cash Flow on date t) / (1+ Cumulative Discount
Factor on date t). Enter =B13/(1+B12) in cell B14 and copy it across.
o Net Present Value = Sum over all periods of the Present Value of Each Cash Flow.
Enter =SUM(B14:G14) in cell B15.
The Net Present Value of this project is $17.42. This spreadsheet can handle any pattern of discount rates.
For example, it can handle the special case of a constant discount rate.
FIGURE 3.3 General Spreadsheet Implementing a Constant Discount Rate.
The Net Present Value of this project is $16.17. Notice this is the same answer as the previous
spreadsheet for the Net Present Value - Constant Discount Rate. The general discount rate spreadsheet is
the most general way to do discounting and is the approach we will use throughout this book.