In: Finance
Solve Using excel spreadsheet:
A young couple take out a 30-year home mortgage of $134,000.00 at 8.7% compounded monthly. They make their regular monthly payment for 5 years, then decide to up their monthly payment to $1,250.00.
A. What is the regular monthly payment? Correct answer: $1049.40
B. What is the unpaid balance when they begin paying the accelerated monthly payment of $1,250.00?
C. How many monthly payment of $1,250.00 will it take to pay off the loan?
D. How much interest will this couple save?
A.
30 year mortgage means 30*12=360 monthly payments
Using excel function PMT
Hence, regular monthly payment = $1049.40
B)
Unpaid balance when they begin paying the accelerated monthly payment of $1,250.00
Unpaid balance = FV (after 5 years) of the mortgage amount - FV of monthly payments of $1049.40 after 5 years
Hence, Unpaid balance = $128170.15
C.
Need to find how many monthly payment of $1,250.00 will it take to pay off the loan
We use excel nper function
nper ie. the number of periods = 188.29 ie approximately 189 monthly payments are required
D.
Interest saved is the difference between the cumulative interest paid on 188.29 monthly payments of $1250 and the the cumulative interest paid on 25*12 = 300 monthly payments of $1049.40
Using CUMIPMT function in excel
Interest couple will save = $186648.03-$107191.60 = $79456.43