Suppose you take out a mortgage so you can buy a house. The loan is for...

  

Transcribed Image Text:

Suppose you take out a mortgage so you can buy a house. The loan is for $100,000 at 6% interest for 30 years. The bank has calculated your payment to be $599.55 per month. (Note: This is the payment on the loan only. Banks usually include escrow in your monthly payments. Escrow is an account that pays the property taxes and insurance. In reality, you would have to pay more than $599.55 a month.) 1) Create an Excel spreadsheet similar to the one below, also called an amortization table, to analyze this loan. Fill in the top three rows and the bottom three rows of the table below. * Recall: I-P* APR/n Month Remaining Principal Monthly Interest Payment Payment Principal** Payment ** Principal Payment- Monthly Payment- Interest Payment 3 subtract each month's 358 principal payment from the remaining principal at the 359 beginning of that month to 360 find what is still owed at the beginning of the next month After you have completed the spreadsheet, highlight the cell at the bottom of the monthly payment column. If you click on the button at the top of the page that is marked , Excel will add the column up for you. Use this button to find the sum of the monthly payments. 2) How much did you pay in order to pay off the $100,000 loan? 3) Repeat the same procedure to find the sum of the interest payment column. How much did you pay in interest over the 30 years? 4) Notice some patterns in the mortgage spreadsheet. At the beginning of the loan, your monthly payment is mostly interest. In the first month of the loan, what percent of your monthly payment is interest? Suppose you take out a mortgage so you can buy a house. The loan is for $100,000 at 6% interest for 30 years. The bank has calculated your payment to be $599.55 per month. (Note: This is the payment on the loan only. Banks usually include escrow in your monthly payments. Escrow is an account that pays the property taxes and insurance. In reality, you would have to pay more than $599.55 a month.) 1) Create an Excel spreadsheet similar to the one below, also called an amortization table, to analyze this loan. Fill in the top three rows and the bottom three rows of the table below. * Recall: I-P* APR/n Month Remaining Principal Monthly Interest Payment Payment Principal** Payment ** Principal Payment- Monthly Payment- Interest Payment 3 subtract each month's 358 principal payment from the remaining principal at the 359 beginning of that month to 360 find what is still owed at the beginning of the next month After you have completed the spreadsheet, highlight the cell at the bottom of the monthly payment column. If you click on the button at the top of the page that is marked , Excel will add the column up for you. Use this button to find the sum of the monthly payments. 2) How much did you pay in order to pay off the $100,000 loan? 3) Repeat the same procedure to find the sum of the interest payment column. How much did you pay in interest over the 30 years? 4) Notice some patterns in the mortgage spreadsheet. At the beginning of the loan, your monthly payment is mostly interest. In the first month of the loan, what percent of your monthly payment is interest?