"All of these questions need to be done in Excel PROBLEM # 1: You have just turned 22, and you intend to start saving for your retirement. You plan to retire in 40 years when you turn 62. During your retirement you would like to have an annual income of $140,000 per year for the next 30 years (until age 92). Calculate how much you would have to save annually between now and age 62 in order to finance your retirement income. Make the following assumptions: ? Assume that the relevant compounded interest rate is 9 percent per year. ? You make the first payment today and the last payment on the day of your turn 62. ? You make the first withdrawal when you turn 62 and the last withdrawal when you turn 92. PROBLEM # 2: You are offered an asset that costs $8,000 and has cash flows of $500 every three month (end of period) of the next 10 years. a) If your cost of capital is 8 percent, should you purchase it? b) What is the IRR of the asset? c) What is the NPV of the asset? (Setup cash flows in Excel spreadsheets and use the following Excel Financial functions, IRR and NPV, to derive your answers. PROBLEM # 3: Use Excel to construct an amortization table for the following mortgage. In the amortization table, provide all the information listed below. (Assuming interest is compounded monthly and payments are due at the end of the month). For a 10-year variable-rate-level-payment mortgage (VRM) of $480,000 with the following mortgage rates: Years 1-2: 4.20%, Years 3-5: 5.45%, Years 6-10: 6.75% Compute and illustrate the following in an amortization table: ? Monthly Payment of the mortgage. ? Mortgage Balance Remaining at the end of each month (Total 120 months) ? Principal Repayment for each month. ? Interest Expenses for each month and the life of the loan.
Paper#4140 | Written in 18-Jul-2015Price : $25