- Assume that a family is purchasing a typical house by making a $30,000 down payment and then financing a $250,000 mortgage at an annual interest rate of 4.25% (a typical rate for a 30-year loan). The size of their monthly payment will depend on the term of the mortgage.
The formula
or the Excel function “pmt” can be used to compute these monthly mortgage payments. If using Excel the 3 arguments of function “pmt” are:
Rate (the monthly interest rate): 0.0425/12
Nper (the total number of monthly payments) and
Pv (the mortgage amount)
-
- Find the monthly payments if the $250,000 was financed over 15 years.
- Find the monthly payments if the $250,000 was financed over 30 years.
- Multiply your answer to part (a) by the number of payments to find how much the family would need to pay in total over the life of the 15-year loan. Subtract the principal amount from this to give the amount of interest paid over the life of the loan.
- Multiply your answer to part (b) by the number of payments to find how much the family would need to pay in total over the life of the 30-year loan. Subtract the principal amount from this to give the amount of interest paid over the life of the loan.
- A standard rule for lenders is that a family’s house payment should not exceed 28% of their monthly income. For a family making $5500 per month, this would equate to $1540 per month. Assuming monthly costs of $250 for property tax and homeowner’s insurance, this would allow for a $1290 monthly mortgage payment.
The formula
or the Excel function “pv” can be used to compute the mortgage a family could afford. If using Excel the 3 arguments of function “pv” are:
Rate (the monthly interest rate): 0.0425/12
Nper (the total number of monthly payments) and
Pmt (the monthly mortgage amount)
-
- Assuming that a family wants to make a $1290 monthly payment, give the mortgage that a family could afford at an annual interest rate 4.25% for a 15-year mortgage.
- Assuming that a family wants to make a $1290 monthly payment, give the mortgage that a family could afford at an annual interest rate 4.25% for a 30-year mortgage.