Question

You are planning to purchase a condo that costs $480,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.

1. Use function “PV” to calculate the loan amount given a payment of $1550 per month. What is the most that you can borrow? (Be sure that PV is the amount you borrow, not the cost of the house.)
2. Use function “PMT” to calculate your mortgage payment.
3. Use function “RATE” to calculate the interest rate given a payment of $1550 and a loan amount of $384,000.
4. For each scenario, calculate the total interest that you will have paid once the mortgage is paid off. (There is not a function for this, enter the formula into the cell.)
5. For each scenario, calculate the total cost of the home purchase. (Down payment plus principle (loan amount) plus interest.) (You will have to add some cells for this.)
6. Assume that you plan to pay an extra $300 per month on top of your mortgage payment, calculate how long it will take you to pay off the loan given the higher payment. (Use the rate, PMT and PV from #2). Calculate how much interest you will pay in total. Compare this to the value that you calculated for #2.

Memo

1. Summarize the results of each of your calculations.
2. Discuss the interest savings associated with an extra payment of $300 per month.
3. Discuss Private Mortgage Insurance. What is it? Why do lenders require it? What is the benefit to the borrower?
4. Compare the costs and benefits of a 10% down payment versus a 20% down payment.

Answer 1

The purchase price of the condo is $480,000.

The downpayment is 20% and the remainder of the 80% has to be borrowed.The amount to borrowed is 80% of $480,000 =$ 384,000.

3.25% is the interest rate being decided for the 30 year mortgage

Note: The repayment period calculated in spreadsheet is in months

Please note that there are three distinct scenarios, namely

1) The PV of the loan or rather the maximum amount which can be borrowed under the given mortgage payment of $1550. This is termed as Scenario 1

2) Calculating the mortgage payment required given the interest rate and required loan amount. it is termed as Scenario 2

3) Calculating an interest rate which accomodates the smaller mortgage payments but can raise the required loan amount.It is termed as Scenatio 3

We will first summarize the results of all the asked calculations and then understand their explanation in the Excel spreadsheet.

Summary

Scenario 1 PV = $356152.92

Scenario 2 MOrtgage payment = $1671.19

Scenario 3 Interest Rate = 2.66%

The total interest paid and total cost of purchase under each scenario is summarized in the table

	Total interest paid	Total cost of the home purchase
Scenario 1	$201,847	$654,000
Scenario 2	$ 217,629	$ 697,629
Scenario 3	$ 174,000	$ 654,000

Scenario 4 Adding an extra $300 monthly to the mortgage payment. Please note that the extra $300 has been added to the mortgage paymnet as calculated by us i.e $1671.19 and not $1550

So, the period of repayment in months is 277.27 months

The interest payment in that period comes to $ 162,555(rounded down)

The spreadsheet formulas given below explain the rationale

The formulas for the cells are given below

The excel formulas being used are PV,PMT,RATE and NPER

Thus all the results have been summarized

Next up, when we pay the extra $300 over our original mortgage payment of $1617.19 we have to pay a lower interest of $ 162,555 compared to a corresponding interest payment of $217,629. This is because of two reasons.

1)A higher monthly mortgage payment, so principal is recovered in larger proportion

2) Shorter period of repayment, so lesser opprtunity for interest compounding.

Investopedia writes that

"When you apply for a mortgage, the lender will typically require a down payment equal to 20% of the home's purchase price. If a borrower can't afford that amount, a lender will likely look at the loan as a riskier investment and require that the homebuyer take out PMI, also known as private mortgage insurance, as part of getting a mortgage."

So, PMI is actually an insurance to the lender for the greater risk they are bearing

Investopedia further states that "PMI protects the lender in the event that you default on your primary mortgage and the home goes into foreclosure."

A PMI lets the borrower gain access much more quickly to the housing market when faced with the improbable situation of not having the necessary loan amount.

Comparing the costs of a 10% down payment and 20% down payment

The calculations remain the same as above. It would make sense to observe the total interest paid and total cost of the house to ascertain the comparative costs and benefits.Lower cost renders greater benefit.

The key calculation difference is that 10% downpayment amounts to 48000$ rather than $96000 for a 20% down payment and this means that the loan to be borrowed for purchasing the house is greater. We can see the implications of such a situation on all the scenarios in the subsequent spreadsheet.

Key conclusions in the comparative analysis

1)Scenario 1 --->In the case of 10% downpayment.lower total cost of purchase but interest paid is the same

2)Scenario 2--->In the case of 10% downpayment. higher total cost and higher interest paid

3)Scenario 3---?In the case of 10% downpayment. lower total cost and lower interest paid

4)Scenario 4--->In the case of 10% downpayment. higher period of repayment of 284.44 months and higher interest payment for that period.