Mortgage Analysis You are planning to purchase a house that costs $480,000. You plan to put 20% down...

60.1K

Verified Solution

Question

Finance

Mortgage Analysis
You are planning to purchase a house that costs$480,000.  You plan to put 20% down and borrow theremainder. Based on your credit score, you believe that you willpay 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 paymentof $1700 per month. What is the most that you can borrow?
Use function “RATE” to calculate the interest rate given apayment of $1700 and a loan amount of $384,000.
For each scenario, calculate the total interest that you willhave paid once the mortgage is paid off.  (There is not afunction for this, enter the formula into the cell.)
For each scenario, calculate the total cost of the homepurchase.  (Down payment plus principle (loan amount)plus interest.)
Assume that you plan to pay an extra $300 per month on top ofyour mortgage payment, calculate how long it will take you to payoff the loan given the higher payment. (Use the data from #1).Calculate how much interest you will pay in total? Compare this tothe value that you calculated for #1.
You want to determine whether or not you should save some ofyour money and put only 10% down on your house. Because you areonly putting 10% down, lenders require that you purchase privatemortgage insurance (PMI).  Assume that PMI is 1% of themortgage amount.  Assume that you will pay PMI for 8years in total (the assumption is that you will have 20% equity atthat time so PMI will no longer be needed).
Calculate your total monthly payment (mortgage payment plusPMI).
Calculate the total cost of financingyour homepurchase (interest plus PMI).
Calculate the total cost of the home purchase. (Downpayment plus principle (loan amount) plus interest plus PMI.)
Compare this to the costs associated with a 20% down payment(use data from #1).
Memo
Summarize the results of each of your calculations.
Discuss the interest savings associated with an extra paymentof $300 per month.
Discuss Private Mortgage Insurance.  What is it? Whydo lenders require it?  What is the benefit to theborrower?
Compare the costs and benefits of a 10% down payment versus a20% down payment.
Simply answer the 10 questions and focus on the MEMO questions.If you can please use excel vision to answer my questions.Thank somuch

Answer & Explanation Solved by verified expert

3.9 Ratings (410 Votes)

Home Price 480000 Mortgage Downpayment 20 02 x 480000 96000 Interest Rate 399 and Tenure 30 years or 30 x 12 360 months Mortgage 48000096000 384000 a Using PMT Function the monthly payment will be calculated as Input See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

Mortgage AnalysisYou are planning to purchase a house that costs$480,000. You plan to put 20% down and borrow theremainder. Based on your credit score, you believe that you willpay 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 paymentof $1700 per month. What is the most that you can borrow?Use function “RATE” to calculate the interest rate given apayment of $1700 and a loan amount of $384,000.For each scenario, calculate the total interest that you willhave paid once the mortgage is paid off. (There is not afunction for this, enter the formula into the cell.)For each scenario, calculate the total cost of the homepurchase. (Down payment plus principle (loan amount)plus interest.)Assume that you plan to pay an extra $300 per month on top ofyour mortgage payment, calculate how long it will take you to payoff the loan given the higher payment. (Use the data from #1).Calculate how much interest you will pay in total? Compare this tothe value that you calculated for #1.You want to determine whether or not you should save some ofyour money and put only 10% down on your house. Because you areonly putting 10% down, lenders require that you purchase privatemortgage insurance (PMI). Assume that PMI is 1% of themortgage amount. Assume that you will pay PMI for 8years in total (the assumption is that you will have 20% equity atthat time so PMI will no longer be needed).Calculate your total monthly payment (mortgage payment plusPMI). Calculate the total cost of financingyour homepurchase (interest plus PMI).Calculate the total cost of the home purchase. (Downpayment plus principle (loan amount) plus interest plus PMI.)Compare this to the costs associated with a 20% down payment(use data from #1).MemoSummarize the results of each of your calculations.Discuss the interest savings associated with an extra paymentof $300 per month.Discuss Private Mortgage Insurance. What is it? Whydo lenders require it? What is the benefit to theborrower?Compare the costs and benefits of a 10% down payment versus a20% down payment.Simply answer the 10 questions and focus on the MEMO questions.If you can please use excel vision to answer my questions.Thank somuch