You are trying to decide whether or not to refinance your current loan. You have already used the traditional method you recall from your FINC 429 class and found that you would benefit by $2587.30 if you decide to refinance, but since you took FINC 429 you know that this is not the end of your decision to refinance. You decided to see whether or not you should refinance now or wait a year and revisit the decision to refinance. Below is the information for your current loan and the scenarios you project for the loan market a year from now:

CURRENT LOAN

• $2,000,000
• 6% int rate compounded monthly
• 30 year amortization
• 10 year balloon payment
• 3 years into the loan (7 years left)

NEW LOANS ONE YEAR FROM NOW

• 5.5% or 7% int rate compounded monthly with a 50/50 probability
• 30 year amortization
• 6 years until balloon payment (1 year from now)

You must calculate the following in an Excel file, then e-mail it to me by the due date with FINC 429 HW #5 Your Name in the file and e-mail title.

1) Calculate the refinance amount for the new loans one year from now.

2) Calculate the 5.5% loans YTM.

3) Calculate the value of the current 6% loan using the 5.5% YTM as the discount rate.

4) Calculate the 7% loans YTM.

5) Calculate the value of the current 6% loan using the 7% YTM as the discount rate.

6) Calculate the Prepayment Option Value and decide whether to refinance now or wait on year and revisit the refinance scenario. Explain?

snapshots of excel showing functions thank you