In: Finance
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. Suppose the loan calls for equal monthly payments.
1. Set up a monthly amortization schedule table for the loan (Input: 20%, creating and copying formulas and Excel finance functions: 30%). Use 360 months (30*12=360)
2. 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)
3. What is the balance of the loan AFTER ten years? (build-in Finance function 10%)
Show ALL of your work through excel functions (show excel functions for everything) please!