Question

In: Finance

Mortgage Analysis You are planning to purchase a house that costs $4 80,000. You plan to...

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.

  1. Use function “PMT” to calculate your mortgage payment.
  2. Use function “PV” to calculate the loan amount given a payment of $1550 per month. What is the most that you can borrow?
  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.)
  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 data from #1). Calculate how much interest you will pay in total? Compare this to the value that you calculated for #1.

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. Calculate your total monthly payment (mortgage payment plus PMI).
  2. Calculate the total cost of financing your home purchase (interest plus PMI). (remember that you only pay PMI for 8 years)
  3. Calculate the total cost of the home purchase. (Down payment plus principle (loan amount) plus interest plus PMI.)
  4. Compare this to the costs associated with a 20% down payment (use data from #1).

Solutions

Expert Solution

#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


Related Solutions

Mortgage Analysis You are planning to purchase a house that costs $480,000.  You plan to put 20%...
Mortgage Analysis You are planning to purchase a house 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.99% on a 30-year mortgage. Use function “PMT” to calculate your mortgage payment. Use function “PV” to calculate the loan amount given a payment of $1700 per month. What is the most that you can borrow? Use function “RATE” to calculate the interest rate given a payment of...
You are planning to purchase a house that costs $480,000. You plan to put 20% down...
You are planning to purchase a house 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.99% on a 30-year mortgage. Use function “PMT” to calculate your mortgage payment. Use function “PV” to calculate the loan amount given a payment of $1700 per month. What is the most that you can borrow? Use function “RATE” to calculate the interest rate given a payment of $1700...
You plan to purchase an $80,000 house using a 15-year mortgage obtained from your local bank....
You plan to purchase an $80,000 house using a 15-year mortgage obtained from your local bank. The mortgage rate offered to you is 8.00 percent. You will make a down payment of 20 percent of the purchase price. Calculate your monthly payments on this mortgage. Calculate the amount of interest and, separately, principal paid in the 4th payment. Calculate the amount of interest paid over the life of this mortgage. (use bank rate.com, mortgage amortization table What will be your...
You plan to purchase an $80,000 house using a 15-year mortgage obtained from your local bank....
You plan to purchase an $80,000 house using a 15-year mortgage obtained from your local bank. The mortgage rate offered to you is 8.00 percent. You will make a down payment of 20 percent of the purchase price. Calculate your monthly payments on this mortgage. Calculate the amount of interest and, separately, principal paid in the 4th payment. Calculate the amount of interest paid over the life of this mortgage.
Using excel and show formulas You are planning to purchase a house that costs $480,000. You...
Using excel and show formulas You are planning to purchase a house 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.99% on a 30-year mortgage. Base scenario (#1): Use function “PMT” to calculate your mortgage payment. Scenario #2: Use function “PV” to calculate the loan amount given a payment of $1,500 per month. What is the most that you can borrow? Based on that...
8.Problem 4 and 5-7 House Appreciation and Mortgage Payments Say that you purchase a house for...
8.Problem 4 and 5-7 House Appreciation and Mortgage Payments Say that you purchase a house for $260,000 by getting a mortgage for $230,000 and paying a $30,000 down payment. If you get a 30-year mortgage with a 6 percent interest rate, what are the monthly payments? (Do not round intermediate calculations and round your final answer to 2 decimal places.)   PMT   $    What would the loan balance be in ten years? (Round the payment amount to the nearest cent but...
You purchase a house that costs $625,000 with an 8%, 30-year mortgage. You make a 20%...
You purchase a house that costs $625,000 with an 8%, 30-year mortgage. You make a 20% down payment to avoid PMI insurance. 1. What is your monthly payment? 2. Amortize the first and second payments. 3. What is the mortgage balance after 5 years? 4. What percentage of the principal is paid off after 5 years? 5. Suppose after 5 years you refinance at 6% the remaining balance at a cost of $10,000, for 30 years. What is your new...
: Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in...
: Mortgage Loan Analysis: A resident in Victoria is planning to buy a new house in March 2018. The sale price of the house is $336,000. He plans to pay 20% down payments and borrow additional 80% from Wells Fargo with a 30-year, 4.375% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from April 2018 for a total of 30 years. (1) Calculate the required monthly mortgage payment for Mr. Davidson. (2) Construct the 2018~2047...
please answer in excel PART 1 You are planning to purchase a house that costs $420,000....
please answer in excel PART 1 You are planning to purchase a house that costs $420,000. You plan to put some money down and borrow the remainder with a 30-year mortgage. Based on your credit score, you believe that you will pay 3.50% interest if you put 20% down. Use function “PMT” to calculate your mortgage payment. Based on your credit score, you believe that you will pay 3.50% interest. Use function “PV” to calculate the loan amount given a...
4. Billy is considering the purchase of a rental house. The house costs $240,000 and it...
4. Billy is considering the purchase of a rental house. The house costs $240,000 and it will generate annual revenues of $15,000 and annual expenses of $3,000. Nevertheless, Billy will need to borrow $240,000 at an interest rate of 7% per year in case he decides to make this investment. Should Billy purchase this house? A) No, he will lose money. B) Yes, his profits will be zero. C) No, his profits will be positive but close to zero. D)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT