Question

In: Finance

Solve Using excel spreadsheet: A young couple take out a 30-year home mortgage of $134,000.00 at...

Solve Using excel spreadsheet:

A young couple take out a 30-year home mortgage of $134,000.00 at 8.7% compounded monthly. They make their regular monthly payment for 5 years, then decide to up their monthly payment to $1,250.00.

A. What is the regular monthly payment? Correct answer: $1049.40

B. What is the unpaid balance when they begin paying the accelerated monthly payment of $1,250.00?

C. How many monthly payment of $1,250.00 will it take to pay off the loan?

D. How much interest will this couple save?

Solutions

Expert Solution

A.

30 year mortgage means 30*12=360 monthly payments

Using excel function PMT

Hence, regular monthly payment = $1049.40

B)

Unpaid balance when they begin paying the accelerated monthly payment of $1,250.00

Unpaid balance = FV (after 5 years) of the mortgage amount - FV of monthly payments of $1049.40 after 5 years

Hence, Unpaid balance = $128170.15

C.

Need to find how many monthly payment of $1,250.00 will it take to pay off the loan

We use excel nper function

nper ie. the number of periods = 188.29 ie approximately 189 monthly payments are required

D.

Interest saved is the difference between the cumulative interest paid on 188.29 monthly payments of $1250 and the the cumulative interest paid on 25*12 = 300 monthly payments of $1049.40

Using CUMIPMT function in excel

Interest couple will save = $186648.03-$107191.60 = $79456.43


Related Solutions

A young couple wishes to take out a sizeable home loan and, at the insistence of...
A young couple wishes to take out a sizeable home loan and, at the insistence of the bank, your client, asks the husband's elderly Chinese parents to guarantee the loan. The parents have very limited spoken and written English. Under what circumstances would you advise your client, the bank, that they could successfully rely on this guarantee should the young couple default on their loan?
A family needs to take out a 30​-year home mortgage loan of $140,000 through a local...
A family needs to take out a 30​-year home mortgage loan of $140,000 through a local bank. Annual interest rates for 30-year mortgages at the bank are 8.1 % compounded monthly. ​(a) Compute the​ family's monthly mortgage payment under this loan. ​(b) How much interest will the family pay over the life of the​ loan?
A couple took out a 30-year mortgage 10 years ago. At that time, the mortgage was...
A couple took out a 30-year mortgage 10 years ago. At that time, the mortgage was $306,800.00, with 7.44% APR and monthly compounding of interest. Today, the couple has been offered $327,700.00 for their house. If the couple accepts the offer, how much cash will they take from the deal? The cash will be the difference between the sell price and what is owed on the loan.
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change: the April 1 work-in-process costs were $66,000 for direct material and $18,000 for conversion. Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$ 22,000 Conversion: 20% complete, cost of ................................................................................................................. 4,500 Balance in work in process, April 1 ..................................................................................................................$  26,500 Units started during April ......................................................................................................................................100,000 Units...
You take out a 30-year $200,000 mortgage with a 4.5% mortgage rate. a. What are the...
You take out a 30-year $200,000 mortgage with a 4.5% mortgage rate. a. What are the month payments? b. How much of the first payment was interest and principal?  
Solve using excel: A. Suppose a potential home buyer is interested in taking a $500,000 mortgage...
Solve using excel: A. Suppose a potential home buyer is interested in taking a $500,000 mortgage loan that has a term of 30 years and a fixed mortgage rate of 5.25%. What is the monthly mortgage payment that the homeowner would need to make if this loan is fully amortizing? 
 B. You have taken out a $350,000, 3/1 ARM. The initial rate of 6.0% (annual) is locked in for 3 years. Calculate the outstanding balance on the loan after 3...
A young couple takes out a RM80,000 mortgage to buy a new condominium. They finance the...
A young couple takes out a RM80,000 mortgage to buy a new condominium. They finance the loan at 7% p.a. compounded quarterly for 20 years and the quarterly payment will be made at the end of period. How much interest will they pay over the 20-year life of the loan? pls attach the formula used.
You have just taken out a 30‑year mortgage on your new home for$103,104. This mortgage...
You have just taken out a 30‑year mortgage on your new home for $103,104. This mortgage is to be repaid in 360 equal monthly installments. If the stated (nominal) annual interest rate is 13.35 percent, what is the amount of each of the monthly installments? (Note: The convention when periodic payments are involved is to assume that the compounding frequency is the same as the payment frequency, unless stated otherwise. Thus this implies 13.35 % APR, compounded monthly for this...
Use excel spreadsheet to solve the following problems Problem 1: A young engineer’s stating salary is...
Use excel spreadsheet to solve the following problems Problem 1: A young engineer’s stating salary is 55,000. The engineer expects annual raises of 3%. The engineer will deposit 10% of the annual salary at the end of each year in a savings account. What is the savings interest rate must be so that there will be $150, 000 in savings for a business start-up after 15 years. Your spreadsheet include at least columns for the year, the year’s salary, the...
Suppose you take out a 30 year mortgage for $460000 at an annual interest rate of...
Suppose you take out a 30 year mortgage for $460000 at an annual interest rate of 3.5%. You plan to sell the house after 10 years. Question 1 How much do you owe on the house after five years? Question 2 How much do you owe on the house after ten years? After five years you have the opportunity to refinance what you owe at an interest rate of 3.25% for 30 years. Question 3 How much would you gain...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT