In: Finance
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 information, please calculate the total paid,
total interest paid and house value you can afford (assuming a down
payment of 20%).
- Scenario #3: Use function “RATE” to calculate the interest rate
given a payment of $1,500 and a loan amount of $400,000. Please
also calculate the total amount paid and total interest paid
assuming a house value of $500,000 and down payment of 20%.
- Scenario #4: Assume that you plan to pay an extra $300 per
month on top of your mortgage payment (vs. Scenario #1 – use that
scenario’s assumptions, not any other scenario). Calculate how long
it will take you to pay off the loan given the higher payment (Use
an interest rate of 3.99% and the loan amount you could borrow in
Scenario #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 or not you should save some of
your money and put only 10% down on your house. Since you are only
putting 10% down, lenders require that you purchase private
mortgage insurance (PMI). Assume that the PMI payment is assessed
annually as 1% of the current mortgage and that your house does not
change in value over the life of the mortgage. You will pay the PMI
on a monthly basis until the PMI payments are cancelled when your
equity reaches 20% of the house value. Use the rest of your data
from Scenario #1 (e.g., the $480,000 house value) as a base
comparison.
- Calculate your total monthly payment (mortgage payment plus
PMI).
- Calculate the total cost of financing your home purchase
(interest plus PMI).
- Calculate the total cost of the home purchase; that is, the
down payment plus principal (loan amount) plus interest plus
PMI.
- Compare this to the costs associated with a 20% down
payment