Assignment Instructions:
She currently owes $131,600 on an original $136,193 15-year FRM loan that she originated 2years ago at 4.375%. Unlike earlier assignments, you are responsible to use your ownknowledge of Excel to build spreadsheets to answer each of the questions listed below. In order to help facilitate grading, please download the Excel file listed on Blackboard and place your answers to the listed questions in the space provided on the first worksheet of that file.
Questions:
1. Your aunt has already received several loan quotes from different banks. Determine the monthly payment and effective borrowing cost for each loan below by creating a separate worksheet for each $131,600 loan given she prepays after 8 years (96 months):
a. 30-year FRM loan at 3.624% with $2,367 in origination fees
b. 30-year FRM loan at 3.75% with $1,617 in origination fees
c. 15-year FRM loan at 2.88% with $1,859 in orig. fees and a 2% prepayment penalty applied towards any principal repaid beyond that is scheduled.
d. 30-year 5/1 Hybrid ARM with an initial interest rate of 2.75% and $2,193 in origination fees. After the 60 th payment, you assume the interest rate will immediately increase to 6.75% for the remaining payments.
2. Each lender has offered to ‘roll-in’ any origination fees to the loan’s principal, but requires the loan-to-value (LTV) to be less than or equal to 80% at origination. Calculate the required appraised value of the property assuming she does and does not “roll-in” the origination fees for each loan option.
3. What is the net present value (NPV) of the refinance decision if she chooses loan Option B, prepays after 8 years, does not roll-in fees, and has a monthly discount rate of 0.1%?
4. Your aunt currently earns $64,000 per year and pays $741 and $259 per year in property taxes and hazard insurance, respectively. Given that the lender requires that her monthly debt-to-income ratio must be less than 45%, what is maximum size of loan she can obtain if she chooses loan option A?
5. In addition to the NPV of the decision, explain in words 1 additional factor discussed in class she should take into consideration before deciding to refinance.