Question

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.

Answer #1

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

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 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 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 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 $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 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 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 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 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% 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

Get Answers For Free

Most questions answered within 1 hours.

ADVERTISEMENT

asked 1 minute ago

asked 19 minutes ago

asked 21 minutes ago

asked 1 hour ago

asked 1 hour ago

asked 2 hours ago

asked 2 hours ago

asked 2 hours ago

asked 2 hours ago

asked 3 hours ago

asked 3 hours ago

asked 3 hours ago