In: Finance
SHOW CLEAR WORK ON EXCEL
A 30-year fully amortizing mortgage loan was made 10 years ago for $75,000 at 6 percent interest. The borrower would like to prepay the mortgage balance by $10,000.
a. Assuminghecanreducehismonthlymortgagepayments,whatisthenewmortgagepayment?
b. Assuming the loan maturity is shortened and using the original monthly payments, what is the new loan maturity?
Loan Amount = $75000
Interest Rate = 6% i.e. 0.5% for monthly payments
Loan Term = 30 years i.e. 360 months
Monthly payment amount can be calculated as below:
P = 75000*0.5%/[1-(1+.5%)^(-360)] = $449.67 (refer cells G2, H2 & I2)
Hence After making the payment for 10 years, outstanding loan amount can be calculated as below:
Loan amount = PV of future payments = 449.67 * [(1-(1+.5%)^(-240))/.5%] = $62765.29
(same could be calculated using excel formula for present value as shown in the attached image, refer cells H6, I6)
After making the prepayment of $10000 at the end of year 10,
Loan balance outstanding = $62765.29 - $10000 = $52765.29
Assuming he can reduce his monthly mortgage payments, the new mortgage payment is
P = 52765.29*0.5%/[1-(1+.5%)^(-240)] = $378.03 (refer cells L11 & M11)
Assuming the loan maturity is shortened and using the original monthly payments, new loan maturity is = 177.16 months (refer cells L12 &M12).