Home Loan Refinance Calculations Using Intuitive Method Using Excel

Susan wrote:
I got the question below in my BBA exam and my answer was marked wrong. “Lisa bought an apartment five years ago for $400000. The bank agreed to finance 80% of the cost of her apartment. She took the loan at a fixed rate of 8% for 15 years and decided to pay in monthly instalments. She has just completed the payment of her 60th instalment. Now her friend informs her that her bank offers a lower annual interest rate of 7% for a 10 year loan. However, she’ll also have to pay $50 every month as transfer cost. She is not sure whether she should transfer the balance of the loan to her friend’s bank. She wants to know whether she would save or lose if she decides to go ahead with the refinance.”
Earlier also we had shown how to calculate the gain/loss in a practical home loan refinace situation and we had used only loan amortization tables. Today we compare the loan amortization method with an ‘intuitive’ method to perform the refinance home loan calculations quicker and easier. We can calculate the equal monthly payments on a home loan if we have the loan amount, interest rate per year and the period of the loan using the straight-forward PMT function. Now we can draw a time line between 0 and let’s say 180 months because the loan is for 15 years and payments are made monthly. If we imagine that we are standing at 0 (our decision point) and looking at 180 our future value at month 180 will be our present loan amount i.e. 80% of $400000. Now since Lisa has paid her 60th instalment and we look again forward we’ll find that we are standing at 120 payments away from the final payment at 180. Also 180 minus 60 gives us 120, isn’t it? Using the PV function in Excel we can calculate the balance amount at point 61 or beginning of the 61st payment. For refinancing calculations we can use this amount as the starting loan for the new finance option and calculate the equal monthly instalments (EMIs) or equal monthly payments. Based on the difference of what we would have paid at 8% interest rate per annum and the new interest rate per annum of 7% in the new option we can find our savings or loss. Now the gain in this case will be for the next 120 months or 10 years. We can calculate the present value of the 120 instalments using the PV function again to find the total gain in today’s $ value. Since Lisa would have to pay $50 every month as transaction fees for the next 120 months or 10 years, we need to calculate its present value and subtract it from the earlier gain to arrive at the ‘ACTUAL’ gain in today’s $ terms. Our final result is $7024 and this should be the correct answer for Susan.

One thought on “Home Loan Refinance Calculations Using Intuitive Method Using Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.