3.2 General 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 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.