Question

A fully amortizing mortgage loan is made for $200,000 at 6 percent interest for 30 years. Payments are to be made monthly. (Ignore origination fee and other fees).
a.      Using Excel, construct fully amortizing mortgage loan table including beginning balance, payment, interest, principal, and ending balance. (Please check your ending balance by using PV function in Excel)
b.      Interest and principal payments during month 1.
c.       Total Principal and total interest paid over 30 years.
d.      The outstanding loan balance if the loan is repaid at the end of year 5.
e.      Total monthly interest and principal payments through year 15.
f.        What would be the breakdown of interest and principal be during month 50.
g.   What is the interest paid over 30 years if the interest is changed to 7%?

Answer 1

a) Using Excel to solve for the PMT Function

= PMT ( Rate/12, NPER, -Present Value, 0,0) Since payment is Monthly.

Rate = 6%, Years = 30, NPER = 30× 12 = 360, Present Value = 200000

Principal Part of the Payment = PPMT( Rate/12, Period Number of the Payment, NPER, -PV,0,0)

Interest Part of the Payment = IPMT( Rate/12, Period Number of the payment, NPER, -PV,0,0)

Using these Excel Function we can find the Monthly Payment , Principal and Interest and Balance remainder for the month.

b) Interest and Principal Payment during month 1 has been attached in the picture.

It can be calculated using the functions above

Principal Payment for the month 1 = 199.10, Interest Payment = 1000

c) Total Principal paid for the total Period = 200000

In the picture attached loan schedule till 40 Periods have been given. It can be extended to 360 Periods, there by calculating the total interest to be paid for 360 periods comes out to be 231676.38

d) Outstanding Loan Balance if the loan is repaid 60 months

186108.71

e) total Monthly principal payment till 15 Years = 57902.31

Total Interest Payment till 15 Years = 157935.88