Problem 1. Suppose an apartment you want to purchase costs $500,000. You can put down 20%...

  

 

Transcribed Image Text:

Problem 1. Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash and take out a 30-year fixed rate mortgage loan for the remaining. You believe that you can get an APR of 6.5% on such a mortgage loan at a local bank. A. Suppose the loan calls for equal monthly payments. Set up a monthly amortization schedule table for the loan (Input: 20%, creating and copying formulas and Excel finance functions: 30%). B. What is the sum of total payments made over the life of the loan? What is the sum of total interest paid over the life of the loan? What is the difference of the two numbers? (Summation function 10%, fill color in your answer cells) C. What is the balance of the loan AFTER ten years? (build-in Finance function 10%) There are two problems in this assignment. Put your work of two problems in two separate worksheets in the same Excel file. The due date is May 1. Instructions: Input: you need to create INPUT area to contain all key assumption data. Output: each of the output cell, e.g. payment, interest, end balance must contain a formula which performs calculations referring the number in the input cell. NEVER enter numbers directly in the output cells to compute. For instance, suppose the given interest rate is 5% in cell A1, the given PV is $100 in cell A2 and the given time period is 2 years in cell A3. To calculate the future value, you must enter =FV(A1, A3,0, -A2,0), instead of = 100*1.05^2. (30 pts deduction for not setting the input-output relationship appropriately or not using the Excel finance functions) Format: Your numerical displays must be in appropriate formats: 1000 separator, two decimal places and $, % symbols. Color: Fill color in your answer cells. ■ ■ Problem 1. Suppose an apartment you want to purchase costs $500,000. You can put down 20% in cash and take out a 30-year fixed rate mortgage loan for the remaining. You believe that you can get an APR of 6.5% on such a mortgage loan at a local bank. A. Suppose the loan calls for equal monthly payments. Set up a monthly amortization schedule table for the loan (Input: 20%, creating and copying formulas and Excel finance functions: 30%). B. What is the sum of total payments made over the life of the loan? What is the sum of total interest paid over the life of the loan? What is the difference of the two numbers? (Summation function 10%, fill color in your answer cells) C. What is the balance of the loan AFTER ten years? (build-in Finance function 10%) There are two problems in this assignment. Put your work of two problems in two separate worksheets in the same Excel file. The due date is May 1. Instructions: Input: you need to create INPUT area to contain all key assumption data. Output: each of the output cell, e.g. payment, interest, end balance must contain a formula which performs calculations referring the number in the input cell. NEVER enter numbers directly in the output cells to compute. For instance, suppose the given interest rate is 5% in cell A1, the given PV is $100 in cell A2 and the given time period is 2 years in cell A3. To calculate the future value, you must enter =FV(A1, A3,0, -A2,0), instead of = 100*1.05^2. (30 pts deduction for not setting the input-output relationship appropriately or not using the Excel finance functions) Format: Your numerical displays must be in appropriate formats: 1000 separator, two decimal places and $, % symbols. Color: Fill color in your answer cells. ■ ■