Using a spreadsheet program (Microsoft Excel) to solve all the problems given below. The Excel sheet should be an active sheet ? means the formula should be enabled in each cell, DO NOT just copy & paste the values or manually input the values in the cells. PROBLEM # 1: You have just turned 26, and you intend to start saving for your retirement. You plan to retire in 40 years at age when you turn 65. During your retirement you would like to have an annual income of $140,000 per year for the next 27 years after retirement. Calculate how much you would have to save between now and age 68 in order to finance your retirement income. Make the following assumptions: -All savings draw compounded interest of 10 percent per year. - You make the first payment today and the last payment on the day of your turn 68 (43 annual end of the year payments). - You make the first withdrawal when you turn 68 and the last withdrawal when you turn 94 (26 annual withdrawals). PROBLEM # 2: You are offered an asset that costs $6000 and has cash flows of $1000 every three months (end of period) of the next 10 years. a. If your cost of capital is 9.2 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 following Excel Financial functions, IRR and NPV to obtain your answers.) PROBLEM # 3: You just took a $20,000, three-year loan. Payments at the end of each quarter are flat (equal in every quarter) at an interest rate of 8 percent. Calculate the appropriate loan table, showing the breakdown in each year between principal and interest. PROBLEM # 4: Use Excel, 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 30-year Fixed-rate-level-payment mortgage (FRM) of $350,000 with the annual mortgage rate of 9.25% Compute and illustrate the following in an amortization table: Monthly Payment of the mortgage. - Mortgage Balance Remaining at the end of each month (Total 180 months) - Principal Repayment for each month. - Interest Expenses for each month, each year, and the life of the loan. Problem # 4: HINTS: Create in Excel the following: Year-InterestRate -Monthly Int. rate- # of Periods-Loan Amount- Present Value -Future Value.
Paper#10245 | Written in 18-Jul-2015Price : $25