In: Finance
Mortgage Analysis
You are planning to purchase a house that costs $4 80,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 3.25% on a 30-year mortgage.
You want to determine whether you should only 10% down on your house. Because you are only putting 10% down, lenders require that you purchase private mortgage insurance (PMI). Assume that PMI is 1% of the mortgage amount. Assume that you will pay PMI for 8 years in total (the assumption is that you will have 20% equity at that time so PMI will no longer be needed).
#1]
Monthly payment is calculated using PMT function in Excel :
rate = 3.25% / 12 (converting annual rate into monthly rate)
nper = 30*12 (30 year loan with 12 monthly payments each year)
pv = 384000 (loan amount = $480,000 * (1 - 20%) = $384,000 )
PMT is calculated to be $1,671.19
#2]
The loan amount is calculated using PV function in Excel :
rate = 3.25% / 12 (converting annual rate into monthly rate)
nper = 30*12 (30 year loan with 12 monthly payments each year)
pmt = -1550 (Monthly loan payment. This is entered with a negative sign because it is a payment)
PV is calculated to be $356,152.92
#3]
Interest rate is calculated using RATE function in Excel :
nper = 30*12 (30 year loan with 12 monthly payments each year)
pmt = -1550 (Monthly loan payment. This is entered with a negative sign because it is a payment)
pv = 384000 (loan amount)
RATE is calculated to be 0.22%. This is the monthly rate. To get annual rate, we multiply by 12. Annual interest rate is 2.66%
#4]
With monthly payments of $1,671.19, total interest paid = total payments - loan amount
total interest paid = ($1,671.19 * 30 * 12) - $384,000 = $217,629.22
With monthly payments of $1,550, total interest paid = total payments - loan amount
total interest paid = ($1,550 * 30 * 12) - $384,000 = $174,000