Unformatted text preview: Homework 7 Rate of Return For these problems, when the problem asks for a formula, show the formula with equivalency factors (i.e. (P/A, i, N) using numerical values for i and N). Evaluate the formulas using tools from the Tool Box, Excel, the Economic add-in, or your personal calculator. Compute all numerical results to two decimal places. If you use the add-in use the Freeze Solutions option of the Links command before submitting your homework. Use the Homework 7 form to submit your answers. P1 You are about to purchase a $250,000 house. You have $50,000 for the down payment, and you will finance the remaining balance with a mortgage from a bank. The bank offers a 6% nominal annual interest rate on a 30 year mortgage. Payments are monthly. The first payment is one month after the loan is issued, the end of month 1. a. What is the monthly payment associated with this loan? Formula: A = 200000(A/P, 0.05, 360) Value: 1199.10 b. Say you plan to pay off the loan after 5 years (60 months). What is the remaining balance of the loan principal when you pay off the loan? Formula: F= 200000(F/P, 0.05, 60) - 1199.10(F/A, 0.05, 60) = 1199.10(P/A, 0.05, 300) Value: 186108.71 c. What is the effective interest rate for this loan? Value: 0.0617
Note that the add-in does not correctly enter the end date of the cash flows on the default form. Change them to the correct values. If you make a mistake creating a form, construct a new form with the same name (Proj1) directly over the form with the error. A1 Project Definition Name Proj1 Life (yrs) Repetitions Comp./yr Study Period Periods 30 1 12 360 MARR(/yr) MARR(/per) Rates (%) 6.% 0.5% Present (Life) Uniform (Life) Present (SP) Nom. IRR Eff. IRR Worth($) (0) (0) (0) 6.% 6.17% Net Investment Investment Data - Amounts Negative for Investments Index Description 1 Loan Amount($) Type Start 0 End 360 Salvage 0.% Factor 1.0000 Fin. NPW($) (200000) (200000) Investment Cash Flow Data - Amounts Negative for Expenditures and Positive for Revenues Index Description 1 Payments 2 CF5 Amount($) Type Start 1 1 End 360 30 Parameter 1 1 Factor 166.7916 27.7941 CF. NPW($) 200000 0 1199 Uniform 0 Uniform 1 P2 Consider again the loan situation of problem 1. Because this is your first house, the bank decides to give you a special payment plan. There will be no payment at the end of month 1. At the end of month 2, the payment is $100. At the end of month 3, the payment is $200. The payments continue to increase in this linear fashion until the payment reaches $1,100 at the end of month 12. Starting with month 13 the payments are fixed at $1,200 a month.
a. Write the formula for the NPW of this loan to the bank. Again the bank's nominal MARR is 6%. b. Use the add-in to construct a Project form describing the cash flow from the loan from the bank's (the investor) point of view. Use the name Proj2. Be sure to check the Multiple Compound box on the dialog. Use 6% for the bank's nominal MARR. From the form determine the present value of this loan to the bank. (In order to get two place decimal accuracy you will have to change the format of the NPW cell.) c. What does this value of the NPW indicate to the bank? d. What nominal and effect IRR does this plan yield to the bank? e. The bank decides to change the payments starting in month 13 so that its return will be 6%. Using the same form, find the value of the payments that provides this return. (Suggestion: Use Solver to find the value. Change the format of the payment cell to get 2-place accuracy.) A2 a a. Write the formula for the NPW of this loan to the bank. Again the bank's nominal MARR is 6%. Formula: NPW = -200000 + 100(P/G, 0.05, 12) + 1200(P/A, 0.05, 348)(P/F, 0.05, 12) b. NPW Value: -7471.42 c. What does this value of the NPW indicate to the bank? The negative value of the NPW indicates that the loan does not meet the banks MARR. To meet the MARR, the bank would have tlo loan 7471. 42 less. d. What nominal and effect IRR does this plan yield to the bank? Nominal 5.66% Effective 5.81% e. The bank decides to change the payments starting in month 13 so that its return will be 6%. Using the same form, find the value of the payments that provides this return. Payment: 1248.15 2 Project Definition Name Proj2 Life (yrs) Repetitions Comp./yr Study Period Periods 30 1 12 360 MARR(/yr) MARR(/per) Rates (%) 6.% 0.5% Present (Life) Uniform (Life) Present (SP) Nom. IRR Eff. IRR Worth($) 0.00 0.00 0.00 5.66% 5.81% Net Investment Investment Data - Amounts Negative for Investments Index Description 1 Loan Amount($) Type Start 0 End 360 Salvage 0.% Factor 1.0000 Fin. NPW($) (200000) (200000) Investment Cash Flow Data - Amounts Negative for Expenditures and Positive for Revenues Index Description 1 Payments 2 Initial gradient Amount($) Type Start 13 2 End 360 12 Parameter 1 1 Factor 155.1727 63.2136 CF. NPW($) 193679 6321 1248.15 Uniform 100 Grad 3 P3 Use the add-in to create models (forms) for the three alternatives described below. Name the models First, Second and Third. Show costs as negative. Use the rate of return method to compare the alternatives. Show the sequence of comparisons required for this method. Use the default names for the comparisons (i.e. Comp1, Comp2, etc.). On comparisons, check the Compute IRR box, check the Show Cash Flow, and do not check the Dynamic box. If you make a mistake with a comparison, define a new comparison with the same name. It may be placed directly over the old comparison. A company must buy a heavy-duty earth-moving machine. It has three alternatives. The first involves a $45,000 initial investment with no maintenance costs and a four-year life. The resale value of this machine after four years is $9,000. The second alternative involves a $30,000 initial investment, a $20,000 overhaul expense after four years, and $2,000 per year operating expense. The alternative is discarded with no salvage value after six years. The third alternative involves renting the machine. The rental cost is $8,500 per year, payable at the beginning of the year. The operating cost is $2,500 per year. The machine can be rented for any number of years. Compare the alternatives with the rate of return method. The company's MARR is 8%. Make the end of the year assumption regarding operating costs. Rental or investment costs occur at the beginning of the year. The company has a need for the machine for an indefinite period. How does the decision change with a 9% MARR? A3 a. Order the alternatives for the IRR analysis. Third, Second, First b. Comparison 1 Challenger: Second Defender: Third IRR: 8.12% Decision: Second Comment on the Cash Flow: Cash flow has six periods. It is mixed with 3 sign changes. c. Comparison 2 Challenger: First Defender:Second IRR: 8.51% Decision: First Comment on the Cash Flow:Cash flow has 12 periods. It is mixed with 5 sign changes. d. Decision with 8% MARR e. Decision with 9% MARR Choose First Choose Third 4 P4 An analyst is given the problem of selecting between two mutually exclusive projects using the rate of return method. One and only one of the projects must be selected. The data for the projects is shown below. The minimum acceptable rate of return is 9%. Using the ROR method which should you choose? Project Initial Investment Ann. Net Revenue Salvage Value Life A 10000 2500 5000 20 B 15000 3000 10000 20 Do this problem without the add-in. Write the formulas for the NPW of the incremental investment. Find the IRR of the incremental investment. (Note that this is one of the easy ROR cases.) A4 a. Formula for the NPW of the incremental investment NPW: NPW=-5000+500(P/A, i, 20)+5000(P/F, i, 20) b. IRR: 10% c. Decision Accept B over A. The incremental investment is entirely recovered from the incremental salvage. The IRR is the incremental revenue divided by the investment, or 10%. 5 ...
View Full Document
- Spring '10
- Net Present Value, Internal rate of return, NPW