3.1 Constant 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 is 8.0%. What is the net
present value of this project?
Solution Strategy. We will calculate the net present value of this project in two equivalent ways. First,
we will calculate the net present value using a time line, where each column corresponds to a period of
calendar time. Second, we use Excel’s NPV function for the net present value.
FIGURE 3.1 Spreadsheet for Net Present Value - Constant Discount Rate.
How To Build Your Own Spreadsheet Model.
1. Inputs. Enter the Discount Rate in B5, the Current Investment in B7 and the Future Cash Flows
in the range C8:G8.
2. Net Present Value using a Time Line. Create a time line from period 0 to period 5. 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 Cash Flows. The current investment is a negative cash flow. Enter =-B7 in cell B12.
Future cash flows are positive cash flows. Enter =C8 in cell C12 and copy it across.
o Present Value of Each Cash Flow = (Cash Flow) / ((1 + Discount Rate) ^ Period). Enter
=B12/((1+$B$5)^B11) in cell B13 and copy it across. The $ signs in $B$5 lock the
column and row when copying.
o Net Present Value = Sum over all periods of the Present Value of Each Cash Flow.
Enter =SUM(B13:G13) in cell B14.
3. Net Present Value using the NPV Function. The Excel NPV function is used to calculate the
net present value of a cash flow stream using the following format =-(Current Investment) +
NPV(Discount Rate, Future Cash Flows). Enter =-B7+NPV(B5,C8:G8) in cell B17. An oddity
of the Excel NPV function is that it only discounts cash flows starting in period 1 and going
forward. You must add the present value of the period 0 cash flow separately, which explains the
negative cash flow term: -(Current Investment).
The Net Present Value of this project is $16.17. Notice you get the same answer both ways: using the
time line or using the NPV function.
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 is 8.0%. What is the net
present value of this project?
Solution Strategy. We will calculate the net present value of this project in two equivalent ways. First,
we will calculate the net present value using a time line, where each column corresponds to a period of
calendar time. Second, we use Excel’s NPV function for the net present value.
FIGURE 3.1 Spreadsheet for Net Present Value - Constant Discount Rate.
How To Build Your Own Spreadsheet Model.
1. Inputs. Enter the Discount Rate in B5, the Current Investment in B7 and the Future Cash Flows
in the range C8:G8.
2. Net Present Value using a Time Line. Create a time line from period 0 to period 5. 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 Cash Flows. The current investment is a negative cash flow. Enter =-B7 in cell B12.
Future cash flows are positive cash flows. Enter =C8 in cell C12 and copy it across.
o Present Value of Each Cash Flow = (Cash Flow) / ((1 + Discount Rate) ^ Period). Enter
=B12/((1+$B$5)^B11) in cell B13 and copy it across. The $ signs in $B$5 lock the
column and row when copying.
o Net Present Value = Sum over all periods of the Present Value of Each Cash Flow.
Enter =SUM(B13:G13) in cell B14.
3. Net Present Value using the NPV Function. The Excel NPV function is used to calculate the
net present value of a cash flow stream using the following format =-(Current Investment) +
NPV(Discount Rate, Future Cash Flows). Enter =-B7+NPV(B5,C8:G8) in cell B17. An oddity
of the Excel NPV function is that it only discounts cash flows starting in period 1 and going
forward. You must add the present value of the period 0 cash flow separately, which explains the
negative cash flow term: -(Current Investment).
The Net Present Value of this project is $16.17. Notice you get the same answer both ways: using the
time line or using the NPV function.