# Capital Budgeting End of Chapter Questions by kma38350

VIEWS: 0 PAGES: 6

Capital Budgeting End of Chapter Questions document sample

• pg 1
```									Chapter 1, BOC Question 2 (ch01boc-model)                                   8/1/2005

2. Finance is all about valuation—how to estimate asset values and what to do to increase
them. We develop and use Excel models throughout the book. We start this process in this
chapter with simple models used to value bonds, stocks, and capital budgeting projects.
Working through the model will give you a refresher in valuation plus a refresher on (or
preview of) Excel. The model can be accessed on iLrn Finance under the Beginning-of-
Chapter Models section, and its filename is ch01-M. If you have never used Excel at all, then
you should not attempt to use it to help answer this question, or if you do, you should not get
frustrated if you have trouble with it.

Click on the tab at the bottom, "Spreadsheet Model," to access the model itself.
o do to increase
s process in this
ting projects.
fresher on (or
eginning-of-
d Excel at all, then
you should not get
A              B           C            D            E              F        G               H             I              J
1    Worksheet for Chapter 1 BOC Questions (ch01-M)                                                  7/28/2005

a. Use Excel to find the value of an 8%, annual payment, \$1,000 par value, 5 year bond if the going
2
3   Principal amount of bond:         \$1,000               Years to maturity:                 5
4   Coupon interest rate:              8.0%                Annual interest:                 \$80
5   Market interest rate:              7.0%
6
7     Time line of payments:          1            2            3               4       5
8   Interest                         \$80          \$80          \$80             \$80     \$80
9   Principal                                                                         1,000
10   Total CF                         \$80          \$80          \$80             \$80    \$1,080
11
12   The bond's value can be found in several ways, as shown below:
13
14   Method #1. Find PV of interest and PV of principal, and sum them.
15
16   PV of interest payments:         \$328.02               Click fx > Financial > PV and then fill in as shown in dialog box below.
17   PV of maturity payment:          \$712.99               Just use algebra to find PV.
18   Value of bond:                 \$1,041.00
19
20                                                                                                 Notes:
21                                                                                                 1. We could have used numbers,
22                                                                                                    but we used cell references to
23                                                                                                    provide greater flexibility.
24                                                                                                 2. We could have left FV and Type
25                                                                                                    blank, but we put in 0.
26                                                                                                 3. The result shows as a negative
27                                                                                                    number, so we pt a minus sign
29                                                                                                    to convert to positive value.
30
31
32
33
34
35   Method #2. Use the same PV function, but insert \$1,000 as the FV:
36
37   Click fx > Financial > PV and then fill in as shown in dialog box below.
38
39   Value of bond:                \$1,041.00
40
41                                                                                                 Notes:
42                                                                                                 1. All the inputs are the same as
43                                                                                                    above except for FV, where we
44                                                                                                    inserted C3.
45                                                                                                 2. Note that this Excel function is
46                                                                                                    identical to the procedure that
47                                                                                                    would be used to find the value of
48                                                                                                    the bond with many financial
49                                                                                                    calculators.
50                                                                                                 3. Again, we inserted a minus sign
51                                                                                                    after the equal sign to convert
52                                                                                                    the answer to a positive number.
53
A   B   C   D   E   F   G   H   I   J
54
55
A             B           C             D            E             F           G            H             I            J
56   Note that if you change any of the input values, the bond's value as shown in cells C18 and C39 will instaneously change to
57   reflect the new conditions. To see this, change C5 to 15% and watch the bond's price fall. As we will see in later chapters, the
58   process for making such changes is called "sensitivity analysis," or, if several variables are changed at the same time,
59   "scenario analysis." Furthermore, Excel has very useful features called "Data Tables" and "Scenario Manager" that
60   automate this process. We will use these features often.
61
62   Note too that in our bond valuation problem we assumed that the bond will make its next interest payment exactly one year from
63   today. We could use the Excel bond "Price" function to find the bond's value between interest payment dates, and we will do so
64   in Chapter 4.
65
66   Stock Valuation
b. Use Excel to find the value of a stock if the last dividend was \$1, the expected growth rate for the
next 3 years is 20%, the long-run growth rate is 5%, and the required rate of return is 12%. Again, you
67
68   Last dividend (D0):                     1                  Growth rate next 3 years (g1-3):              20%
69   Required rate of return (r):          12%                  Growth rate thereafter (gn):                   5%
70
71     Time line of payments:            0             1             2          3            4
72   Dividend                          \$1.00         \$1.20         \$1.44      \$1.73        \$1.81
73   Stock value end of Year 3:                                               \$25.92
74   Total cash flow:                  \$1.00         \$1.20         \$1.44      \$27.65
75
76
77   PV of cash flow:                                   \$1.07        \$1.15       \$19.68
78   Value of the stock:                 \$21.90
79
80   Notes:             1. The first dividend that will be received is the \$1.20, because the \$1 has been paid.
81                      2. The growth rate drops to 5% after Year 3.
82                      3. The PV of the dividends from Year 4 to infinity is found as D4/(r - gn) = \$25.92.
83                         The calculation assumes that the stock is, or can be, sold at the \$25.92 at the end of Year 3.
84
85   Again, we could change the inputs and see instantly how the stock price varies. For example, if you change r to 10%
86   and gn to 7%, the stock price will more than double, rising from \$21.90 to \$49.88. Thus, if management could lower
87   the firm's risk (and thus r) and raise the growth rate, this would greatly enhance shareholder value.
88
89   Capital Budgeting

c. Use Excel to find the NPV and the IRR of a project that is expected to cost \$10,000 and to
provide net cash flows of \$2,000 per year for the next 10 years. The cost of capital is 11%.
90
91   Excel is excellent for analyzing capital budgeting problems. Here is an example:
92   Project cost:                     -\$10,000             Project life (years):      10
93   Annual net cash flow:               \$2,000             Cost of capital:          11%
94
95   Time line:              0           1             2            3            4           5             6            7          8
96
97   Cash flow             -\$10,000      \$2,000        \$2,000       \$2,000       \$2,000      \$2,000         \$2,000      \$2,000          \$2,000
98 Net Present Value (NPV):                       \$1,778
99 Internal Rate of Return (IRR):                  15.1%
100
101 Notes:          1. The completed dialog boxes shown below illustrate how to use Excel to calculate the NPV and IRR.
102                 2. You get the dialog boxes by clicking fx > Financial > NPV or IRR.
103                 3. Excel's NPV function assumes that the first cash flow occurs at the end of the year. However, the cost
104                 occurs at the beginning of the period. Therefore, to use the NPV function we first calculate the NPV of the
105                 flows that occur after Period 0, and we add to the value thus calculated the (negative) cost.
106                 4. Excel's IRR function assumes that the cash flows occur at the beginning of each period, so when we
A             B             C           D            E            F           G            H              I           J
107                  calculate the IRR we can simply specify the entire range (B96:L96). Also, we leave "guess" blank and let
108                  Excel starts the iterative process with a 10% cost of capital discount rate.
109
110 Again, we could vary the inputs and see how the NPV and IRR change, and we will be doing a lot of this later in the book.
111 As we will see, using Excel to analyze capital budgeting projects is an easy way to evaluate potential projects, and it can
112 also be used by management to help design projects so that they maximize the firm's value.

```
To top