Question

You are planning to purchase a house that costs $620,000. You plan to put 20% down and borrow the remainder. You have been pre-approved, based on your credit score and income, for a 30-year loan with an interest rate of 4.53%.
1. Use function “PMT” to calculate your mortgage payment. 2. Use function “PV” to calculate the loan amount given a payment of $2650 per month. What is the most that you can borrow? 3. Use function “RATE” to calculate the interest rate given a payment of $2400 and a loan amount of $496,000. 4. For each scenario, calculate the total interest that will have been paid once the loan is paid off. (There is not a function for this, enter the formula into the cell, just like we did in class.) 5. Assume that you plan to pay an extra $400 per month on top of your monthly payment, calculate how long it will take you to pay off the loan given the higher payment. (Use interest rate of 4.53% and the payment you calculated in #1). Calculate how much interest you will pay in total. Compare this to the total interest value that you calculated for #1.
6. For each scenario, calculate the total cost of the home purchase. (Down payment plus principle (loan amount) plus interest.)

Memo
In a word document, summarize your analysis and the results of each of your calculations. Discuss the interest savings associated with an extra payment of $400 per month.

Answer 1

Answer 1:

Cost of house = $620,000

Loan amount = Cost of house - Down payment = 620000* (1 - 20%) = $496,000

Interest rate = 4.53%

Loan duration = 30 years = 30 *12 = 360 months

Monthly payment = PMT (rate, nper, pv, fv, type)

= PMT (4.53%/12, 360, -496000, 0, 0)

= $2522.0082

= $2,522.01

Mortgage payment =$2,522.01

Answer 2:

Monthly Payment = $2650

Most you can borrow = PV (rate, nper, pmt, fv, type)

= PV (4.53%/12, 360, -2650, 0, 0)

= 521,171.9715

= $521,171.97

Most you can borrow = $521,171.97

Answer 3:

RATE (nper, pmt, pv, fv, type)

= RATE (360, 2400, -496000, 0,0)

= 0.3426%

Yearly Interest rate = 0.3426% * 12 = 4.112%

Answer 4:

Total Interest paid = Monthly mortgage payment * 360 - Loan amount

Answer 5:

Assume that you plan to pay an extra $400 per month on top of your monthly payment

Monthly payment = 2522.0082 + 400 = $2922.0082

= NPER (rate, pmt, pv, fv, type)

= NPER (4.53%/12, 2922.0082, -496000, 0, 0)

= 271.73 months

Time it will take you to pay off the loan = 271.73 / 12 =22.64 Years

Time it will take you to pay off the loan = 22.64 Years

Total Interest paid = 2922.01 * 271.73 - 496000 = $297997.78

Total interest payment in this option in lesser than option #1 by = 411922.96 - 297997.78 = $113,925.18

Answer 6:

As per Chegg's policy 4 sub parts need to be answered. I have already answered 6 sub parts.