Question

I have to do this in Excel. Please Provide the formulas step by step and a screenshoot of output how it looks in the end.
Thank you.
Question is about the MARKET vs the CONTRACTUAL value of a loan with refinancing, and cost of a loan and cost of refinancing in terms of points. Has to be in monthly terms.

You want to buy a house of a million dollars, and the bank is giving you a loan with 1.5 points. The interest for the loan is 7.5% for twenty years annually. Evaluate the ammonization table for the loan. At the 25th month, you want to refinance the loan with 5.25% annual interest. The bank in order to process the refinancing process is charging 2% on the value to be refinanced. You have to evaluate if you will have a benefit from refinancing paying the 2%. For this case, you calculate again the new amortization table. What do you benefit evaluate at the time you make the decision to refinance the loan?

Answer 1

During this solution only one formula for PMT has been used. Fv is assumed nil as it is fully amortizing loan and default type as payment (EMI) is assumed to be made at the start of month.

-          Step 1. Calculate monthly interest rate by dividing No. of months with Annual interest rate (7.50%/12). Same for refinancing schedule

-          Step 2. Calculate EMI with formula of PMT as =-PMT($G$3,$G$5,$G$2). Whatever you get will be your Installment in your original schedule. Same with Refinancing Schedule PMT =-PMT($I$4,$I$6,$I$2)

-          Step 3. Insert your Opening Balance as given; Interest Component = 0.63% * $1,000,000; Principal Component = Installment - Interest Component; Closing Balance = Opening Principal - Principal Component.

-          Step 4. Your Opening Balance for month 2 would be your closing balance for month 1. Copy paste formulas for Int Comp, Principal compnent, closing balance from month 1. Select cell c17-f17 and scroll down to apply same formula till your last month i.e. 240th.

-          Step 5. Prepare your Refinancing schedule accordingly. Keep in mind -> you have to take different interest rate, Your month should start from 25th and opening balance would be closing balance of 24th month.

-          Step 6. Now you have to prepare Expected Out Come… Total expense in original schedule would be summation of Interest Payments + Cost of financing i.e. 1.5%*$1,000,000 {1.5 point is 1.5% on loan amount as financing cost}

-          Step 7. Calculate Total cost after refinancing as [Interest expense during 24 months in original shedule + Interest expense during refinanced schedule + Refinancing cost 2% on Opening balance of Refinancing schedule 2%.

-          Step 8. Calculate Net Benefit from refinancing by taking difference of above two.

Best wishes!!