3.1 Constant Discount Rate

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 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.