Question

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.

  1. Base scenario (#1): Use function “PMT” to calculate your mortgage payment.
  2. 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%).
  3. 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%.
  4. 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.
  5. 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.

    1. Calculate your total monthly payment (mortgage payment plus PMI).
    2. Calculate the total cost of financing your home purchase (interest plus PMI).
    3. Calculate the total cost of the home purchase; that is, the down payment plus principal (loan amount) plus interest plus PMI.
    4. Compare this to the costs associated with a 20% down payment

Solutions

Expert Solution

Note: all answers are highlighted in blue and formula in orange


Related Solutions

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...
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...
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...
You are planning to purchase a condo that costs $480,000. You plan to put 20% down and borrow the remainder.
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. 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.) Use function “PMT” to calculate your...
Please use excel and show formulas You are planning to invest $10,000 per year to save...
Please use excel and show formulas You are planning to invest $10,000 per year to save for retirement. Assume you'll get an annual rate of return of 7%, and that you'll make the yearly investments at the beginning of each period. If you plan on retiring at 65, compare the difference between starting these yearly investments at 30 years old versus starting at 40 years old.
Show work (excel if possible but show formulas you used in excel): If I buy a...
Show work (excel if possible but show formulas you used in excel): If I buy a new car and pay $399 a month for 5 years and the maintenance costs are $50 the first year and increase by $200 a year for each year after that, how much money would I have to barrow today at a nominal interest rate of 9% to cover my monthly payment and my yearly maintenance bill?
Show work (excel if possible but show formulas you used in excel): If I buy a...
Show work (excel if possible but show formulas you used in excel): If I buy a new car and pay $399 a month for 5 years and the maintenance costs are $50 the first year and increase by $200 a year for each year after that, how much money would I have to barrow today at a nominal interest rate of 9% to cover my monthly payment and my yearly maintenance bill?
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells copy paper by the case to office supply stores. Each case of paper costs Parry $15. The operating costs are $30,000 per period. Each period, Parry sells approximately 15,000 cases of copy paper at $35 per case. Texas Office Emporium is requesting an order of 4,000 cases of copy paper in the next period at a price of $25 per case. Since Parry has...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells...
PLEASE ANSWER USING EXCEL. please show formulas used in excel. THANK YOU :) Parry Enterprises sells copy paper by the case to office supply stores. Each case of paper costs Parry $15. The operating costs are $30,000 per period. Each period, Parry sells approximately 15,000 cases of copy paper at $35 per case. Texas Office Emporium is requesting an order of 4,000 cases of copy paper in the next period at a price of $25 per case. Since Parry has...
***PLEASE USE EXCEL AND SHOW FORMULAS**** Barry and Samantha are starting to take their retirement planning...
***PLEASE USE EXCEL AND SHOW FORMULAS**** Barry and Samantha are starting to take their retirement planning seriously. They are both 46 and plan to retire in 20 years at the age of 66. They expect to live 20 years in retirement (a life expectancy of 86). Between their 401k and IRA accounts they currently have $84,295 in retirement savings. They currently have a combined income of $80,000 per year and expect to be able to live comfortably in retirement with...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT