Question

Jennifer and Jason are buying a house. They have $30000 for a down payment. The house price is $177000. They have secured a 30 year loan with an annual interest rate of 5.45% compounded monthly, Use Excel to determine the size of the monthly payments they must make over the next 30 years to pay off the house. How much will they pay total over the 30 years? How much will they pay in interest of the 30 years? Express your answer rounded to the nearest cent! Payments: $ Total paid: $ Total interest:

Answer 1

To calculate the monthly payments, you can use the PMT function in Excel. It's a financial function that returns the periodic payment for a loan.

The formula is: PMT(interest rate/compounding periods, total number of payments, loan amount).

In your case, the interest rate is 5.45% (or 0.0545 as a decimal), the loan is compounded monthly (so 12 times per year), and the loan will be paid off over 30 years (or 360 months). The loan amount is the house price minus the down payment, or $177,000 - $30,000 = $147,000.

So the formula would be: PMT(0.0545/12, 360, 147000).

To calculate the total paid over 30 years, you multiply the monthly payment by the total number of payments (360).

To calculate the total interest paid over 30 years, you subtract the initial loan amount from the total amount paid.

Please note that the PMT function in Excel gives a negative result because it represents money out (your payments). To make it positive, you can put a minus sign before the formula.

Remember to round to the nearest cent as you requested!