Question

A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an...

A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an annual fixed rate of 5.49% (15-year term) with monthly payments of $1,881.46. They just made their twenty-fifth payment and the current balance on the loan is $208,555.87.

Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed loan. Their bank has made the following offer: 15-year term, 3.0%, plus out-of-pocket costs of $2,937. The out-of-pocket costs must be paid in full at the time of refinancing.

Build a spreadsheet model to evaluate this offer. The Excel function:

=PMT(rate, nper, pv, fv, type)

calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are as follows:

rate = the interest rate for the loan

nper = the total number of payments

pv= present value (the amount borrowed)

fv = future value [the desired cash balance after the last payment (usually 0)]

type = payment type (0 = end of period, 1 = beginning of the period)

For example, for Dave and Jana's original loan there will be 180 payments (12*15 = 180), so we would use = - PMT(0.0549/12, 180, 230415,0,0) = $1881.46. Note that since payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also, for payment calculations, we assume that the payment is made at the end of the month.

Assume that Dave and Jana have accepted the refinance offer, and that there is no prepayment penalty, so that anything above the beyond the required payment is applied to the principal. Construct a spreadsheet model in Excel so that you may use Goal Seek to determine the monthly payment that will allow Dave and Jana to pay off the loan in 9 years.

(Hint: Break each monthly payment up into interest and principal [the amount that gets deducted from the balance owed]. Recall that the monthly interest that is charged is just the monthly loan rate multiplied by the remaining loan balance.)

If required, round your answers to two decimal places. If required, round intermediate calculations to two decimal places.

Pay off loan in years Additional Payment
9 Years $

Which option for prepayment if any, would you choose and why?

The input in the box below will not be graded, but may be reviewed and considered by your instructor.

Homework Answers

Answer #1

***I hope this will hel.p you.Please LIKE.

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Question 2 2a) Suppose a risk-free bond promises to pay $2,249.73 in 4 years.  If the going...
Question 2 2a) Suppose a risk-free bond promises to pay $2,249.73 in 4 years.  If the going risk-free interest rate is 3.5%, how much is the bond worth today?   Nper Rate PMT FV PV 2b) Suppose you can buy a U.S. Treasury bond which makes no payments until the bond matures 10 years from now, at which time it will pay you $1,000. What interest rate would you earn if you bought this bond for $585.43?   Nper PMT PV FV Rate...
1. A borrower has secured a 30 year, $100,000 loan at 8%.  Fifteen years later, the borrower...
1. A borrower has secured a 30 year, $100,000 loan at 8%.  Fifteen years later, the borrower has the opportunity to refinance with a fifteen year mortgage at 7%.  However, the up-front fees, which will be paid in cash, are $2,000.   What is the monthly payment on the initial loan?   What is the loan balance at the time of refinancing?   What is the return on investment if the borrower expects to remain in the home for the next fifteen years after refinancing?  ...
I was looking at the solution to the following question on this site. I could not...
I was looking at the solution to the following question on this site. I could not understand why use 12 when working out the NPER. Since the monthly payments start 1 month after should you not use 11? Question 3. (a) A family member is thinking about funding his granddaughter’s university education in 8 years when she is expected to enrol at UWI, St. Augustine. He opens a special savings account, where he can receive a lump sum in 8...
You have purchased a home for $200,000 with a down payment of 10 percent. Prevailing mortgage...
You have purchased a home for $200,000 with a down payment of 10 percent. Prevailing mortgage interest rates are 4.5 percent. If you finance the home for 30 years, what is your monthly payment? N I/Y PV PMT FV If you finance it for 20 years, what is your monthly payment? N I/Y PV PMT FV If you finance it for 15 years, what is your monthly payment? N I/Y PV PMT FV Assuming you choose a 20-year payment period...
Suppose a home buyer took out a 75% LTV loan 9 years ago to purchase a...
Suppose a home buyer took out a 75% LTV loan 9 years ago to purchase a $190,000 home at a fixed interest rate of 8% amortized over 30 years with monthly payments. This loan had 3 discount points, a 1% origination fee, and a 4% prepayment penalty associated with it. The owner is thinking about refinancing and has decided to pay any costs incurred in the process out of his pocket. The loan-to-value ratio on the new loan is not...
Please use Excel to answer the following TVM questions. You can use this spreadsheet to set...
Please use Excel to answer the following TVM questions. You can use this spreadsheet to set up your calculations if you so desire. Unless indicated otherwise, assume that all of the problems are ordinary annuities (payment made at the end of the period).        Part 3 I am going to buy a car. I will finance the whole purchase (no down payment) with a new car loan that has a 6-year term. My monthly payments will be $392/mth and the annual...
You took out your home mortgage five years ago, and are currently considering refinancing into a...
You took out your home mortgage five years ago, and are currently considering refinancing into a loan at a lower rate and for a shorter term. Your original loan was for 30 years, at 6% interest on the $200,000 borrowed, and you pay monthly. The new loan you are considering will be for 15 years at a rate of 4%. Again, the payments will be monthly. What will your new payment be if you take on this new loan? SHOW...
You borrow $400,000 at a rate of 5% and make annual payments for 20 years to...
You borrow $400,000 at a rate of 5% and make annual payments for 20 years to repay the loan. What Excel function can be used to calculate the loan balance immediately after making the 5th payment? Question 12 options: =PMT(5%, 20, -400000) =PV(5%,15,PMT(5%,20,400000)) =PV(5%, 15, -400000) =FV(5%, 240, 400000)
You and your spouse purchased your first home immediately after the birth of your child. You...
You and your spouse purchased your first home immediately after the birth of your child. You have lived in the home for the past 18 years, paying your mortgage on time, but otherwise struggled to accumulate savings to pay for college. Your child is off to college this fall and annual cost is estimated to be $70,000. You originally paid $450,000 for your house, which has appreciated in price at 3.5% per annum. The original mortgage was a 30 year...
An engineer bought a house four years ago for $70,000. She paid cash equal to 10%...
An engineer bought a house four years ago for $70,000. She paid cash equal to 10% of the purchase price as the down payment. The rest she financed with two loans. One is a company subsidized loan of 12% for $20,000, with equal monthly payments for 20 years. The other loan (for the remainder of the money needed) was provided by a local bank, with an interest rate of 15%, also payable over 20 years, with uniform monthly payments. What...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT