1. Firm Valuation
a. Obtain the cost of equity capital and the perpetuity growth rate from the two stage dividend discount model for Boeing. Modify the spin button to show two decimals. Keep all other information from the April 3 exercise and obtain the value of the firms through the five methods with and without horizon.
2. Loan Amortization.
a. To purchase a house, you take out a 30 year mortgage. The PV of the mortgage is $217,832 with an interest rate of 9.27%. What is the annual payment required by this mortgage? How much of each year's payment goes to paying interest and how much reducing the principal balance?
b. In purchasing the house, you need to obtain a mortgage with a PV of $175,000. You have a choice of (A): a 30 year mortgage at an interest rate/year of 9.74% or(B) a 15 year mortgage at an interest rate/ year of 9.46%. What is the annual payment required by the two alternative mortgages? How much of each year's payment goes to paying interest and how much reducing the principal balance by the two alternative mortgages? Which mortgage would you prefer?
c. Consider a 30 year mortgage for $442,264 as in the previous section. What would happen if the interest rate dropped from 9.21% to 7.95%. How much of each year's payment goes to paying interest vs. how much goes to reducing the principal under the two interest rates?
3. Cost-Reduction
a. Suppose a firm is considering a labor-saving investment. In year 0, the project requires a $11,700 investment in equipment (all figures are in thousands of dollars). This investment is depreciated using the straight-line method over five years and there is salvage value in year 5 of $4,500. With or without the cost-reducing investment, all cash flows start in year 1 and end in year 5. The inflation rate is 2.6% in year 2 and declines to 1.4% in year 5. The real growth rate is 21.3% in year 2 and declines to 9.5% in year 5. The tax rate is 41% in all years. The real cost of capital is 8.7% in year 1 and declines to 7.5% in year 5. Without the cost-reducing investment, the firm's existing investments will generate year 1 labor costs, other cash expenses, and depreciation of $15,200, $4,100, $5,300, and $3,300 respectively. Whit is the cost-reducing investment, the firm's year 1 labor costs will be $1,600 and revenues and other cash expenses will remain the same. What is the cost-reducing project's NPV?
b. For the same cost-reducing project as problem 1, analyze the sensitivity of the Project NPV to the assumed With Investment Labor Costs.
4. Option Buttons
b. On the same work sheet show three option buttons modifying one cell and two different option buttons modifying a different cell.
5. Scenario Analysis
a. Use the following information and all other information from the March 20th file to construct a data validation dropdown, combo dropdown, and data table (What if Analysis) with the following three cases. Please create each method on a different worksheet.