I. 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. Please set up a monthly amortization schedule table for the loan (Input setting: 20%, creating and copying formulas and Excel finance functions: 40%).
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? Calculate the difference of the two numbers. (Summation function 10%, please fill color in your answer cells)
c. What is the balance of the loan after ten years? (build-in Finance function 10%)
d. You are considering if taking an adjustable rate mortgage (ARM), in which your interest rate on the loan will change with a price index. In a different worksheet, please show a graph of your monthly payment as a function of APRs, varying the APR from 4% to 8% with a unit of 0.5 percentage point change (that is, show your monthly payment corresponding to APR at 4%, 4.5%, 5%, 5.5%....7.5%, 8% and graph the relationship between PMTs and APRs). (Graphing and working with multiple worksheets: 20%)