Mortgage Refinance Calculator Spreadsheet
... by Mark Szekely, MA (Economics)
With mortgage rates at very low levels, your fixed rate mortgage may be charging you interest exceeding today's market rates. If so, you may be curious about the benefits and costs of refinancing early.
If you hold a fixed-rate closed mortgage, your lender will likely charge a penalty to discharge your existing mortgage early. This prepayment penalty can be sizeable, and its method of calculation will be set out in your mortgage contract.
While the costs of refinancing a mortgage early can be substantial, so too can be the financial rewards. I have refinanced my own mortgage (with help from a mortgage broker) on two separate occasions, and in both instances have enjoyed interest savings many times greater than the costs.
This three-step online guide will help you quantify the financial benefits and costs of refinancing your existing fixed-rate mortgage before the end of its term.
Step 1 will help you determine potential interest savings from an early mortgage refinance.
Step 2 addresses the costs of paying out your existing mortgage and of securing a new mortgage loan.
Step 3 explores additional factors to consider when determining whether to refinance, and provides tips on finding a mortgage that suits your needs.
Before starting, please ensure that you have the following:
1. Microsoft Excel ('97 or higher).
2. Copy of your most recent mortgage statement.
3. Copy of your existing mortgage contract.
4. Mortgage Refinance Calculator (download button below)
Step 1 - Determine Interest Savings
Refinancing a mortgage involves an investment of both your money and your time. As a first step, you will therefore need to assess whether or not the likely interest savings are sufficient to justify your investment of time and effort.
If the likely interest savings from refinancing are small (say $1,500 or less), then your time and effort can probably be more productively spent elsewhere. However, if the potential savings are several thousand dollars or more, then you may wish to take a very close look at the costs and benefits of refinancing.
To help determine potential interest savings, launch Microsoft Excel on your computer and open the Mortgage Refinance Spreadsheet.
In a few minutes, you will change the cells in Red Typeface (all located in Rows 6 to 9) in order to reflect your own personal circumstances. Before doing so, however, first review the "placeholder values" in the spreadsheet.
The two examples below demonstrate how the Mortgage Refinance Calculator spreadsheet can be used to assess the financial benefits of a mortgage refinance.
Example 1
To illustrate how the Mortgage Refinance Calculator Spreadsheet can be used to determine potential savings, consider a fictional couple (let's call them Hank and Lisa), with a fixed-rate closed mortgage.
Hank and Lisa are weighing the possible benefits and costs of a refinancing their mortgage on June 1, 2012. This value has already been entered in cell "C6" of your Mortgage Refinance Calculator Spreadsheet.
Cells "I6", "I7", "I8", and "I9" define the parameters of Hank and Lisa's existing mortgage:
- Balance of $300,000 as of the Refinance Date;
- Interest rate of 4.40% (computed on a semi-annual basis);
- Amortization period of 180 remaining months as of the Refinance Date; and,
- Term of 35 remaining months as of the Refinance Date.
Based on the above values, Hank and Lisa's required payment is shown in cell "I11" to be $2,274 per month. Total interest payable over the remaining 35-month term is shown to be $35,488 (cell "I12"), and at the end of the term Hank and Lisa's remaining balance under their existing mortgage would be $255,912 (cell "I13").
Cells "P6", "P7", and "P8" show the characteristics of the Refinance Mortgage that our fictional couple are considering. To maintain an "apples to apples" comparison, the starting balance and amortization period are assumed to be identical to those of Hank and Lisa's existing mortgage. However, the new interest rate on the refinance mortgage is 3.50% (instead of 4.40%).
Cell "P11" shows the required monthly payment for the 3.5% refinance mortgage to be $2,141 (a decrease of $133 per month). Total interest payable over the 35 months (the term remaining in the original mortgage) is shown in cell "P12" to be $28,141. This represents $7,347 in interest saved over the 35 months considered! At the end of the 35 months, Hank and Lisa will now owe $253,208, which is $2,703 less than they would have owed on their existing mortgage.
The above example assumes that the only difference between the two mortgages is the interest rate (e.g., 4.40% versus 3.50%). The principal balance and the amortization period is assumed to be identical for both loans.
Example 2
In practice, borrowers might wish to alter the principal balance and/or the period of remaining amortization as well. For example, suppose that Hank and Lisa discover that the cost for breaking their existing mortgage will be $4,000 (including pre-payment penalty, legal fees and other "soft costs").
Suppose that the interest rate on the refinance mortgage is 3.5%, and that Hank and Lisa wish to reduce their monthly mortgage payment (by increasing the amortization period) such that the amount owing after 35 months remains at $255,912 (the same amount that Hank and Lisa would have owed under their existing mortgage).
First, enter in the cell "P6" a value of $304,000. This value represents the new total amount owed under the refinance mortgage (including pre-payment penalty, legal fees and other "soft costs").
Next, successively increase the period of remaining amortization in cell "P8" until the remaining balance after 35 months (cell "P13") shows $255,912. You will discover that this happens when the amortization period is extended to about 192 months.
Now compare the monthly payments in the existing mortgage ($2,274 in cell "I11") against the monthly payments in the refinance mortgage ($2,067, approximately, in cell "P11"). By refinancing at the lower rate and adjusting their amortization period, Hank and Lisa will save $206 in monthly mortgage payments for the next 35 months, and their outstanding balance at the end of this period will be the same as for their existing mortgage.
Make it Your Own!
After having worked through the above examples, modify cells "C6", "I6", "I7", "I8", and "I9" to match your own mortgage circumstances.
Pick a planned refinance date, and enter this value in cell "C6."
Use your recent mortgage statement to estimate your mortgage balance on your chosen refinance date, and enter this in cell "I6." Then enter your current interest rate in cell "I7".
Your mortgage statement will also show your remaining amortization period and remaining term as of your statement date. Recalculate your remaining amortization period and term as of your planned refinance date, and enter these values in cells "I8" and "I9."
If you have correctly entered cells "C6", "I6", "I7", "I8", and "I9", then the monthly mortgage payment shown in cell "I11" will match the actual mortgage payment that you pay every month. If not, check to ensure that all five cells have been entered properly.
Now turn your attention to the three cells describing the refinance mortgage (cells "P6", "P7", and "P8"). Initially, I recommend that you set the Balance at Refinance Date (cell "P6") and Amortization (cell "P8") to the same values as for your existing mortgage, and that you start by adjusting the interest rate (cell "P9") only.
Consider now what happens when you set the refinance mortgage interest rate (cell "P7") to a level 0.5% or 1.0% below the rate on your existing mortgage. Run a quick Google Search and find out what approximate rate you might obtain if you were to refinance today. Enter this rate in cell "P7" and look at the resulting interest savings (cell "Q12").
By spending a few minutes running scenarios on your Mortgage Refinance Spreadsheet, you will gain a clearer perspective on the interest savings that may be possible from refinancing your mortgage.
Step 2 - Costs of Refinancing a Mortgage
If you hold a fixed-rate closed mortgage, your lender will normally charge a penalty to discharge your mortgage early. This prepayment penalty can be substantial, and its calculation will be set out in your mortgage contract.
If your mortgage was funded five years ago or less, the penalty amount will be described by language such as the following: "payment of a prepayment charge equal to the GREATER of the interest rate differential (IRD) or 3 months interest calculated on the outstanding loan amount."
In most instances, the "interest rate differential (IRD)" (which essentially means the entire shortfall between the interest your bank would have charged you until the end of your loan term versus the interest that your bank can now charge another borrower) will exceed the "3 months interest" amount, and therefore the applicable prepayment charge will be equal to the "interest rate differential (IRD)."
As a sidebar, lenders in Canada are only allowed to charge an "interest rate differential (IRD)" if the existing mortgage was funded five years ago or less. If your existing mortgage was funded more than five years ago, Canada's Interest Act (R.S.C., 1985, c. I-15) prohibits your lender from charging a prepayment amount exceeding three months of interest. For this reason, if your mortgage was funded more than five years ago, your prepayment charge to exit a mortgage early will be no higher than the "3 months interest" amount.
In addition to the pre-payment penalty, your mortgage lender will likely also charge an additional "discharge fee". Some lenders will charge additional "hidden" fees as well (for example, at the time of writing TD Canada Trust charges an additional $300 "reinvestment fee" in addition to its prepayment penalty and discharge fees).
Phone your lender and ask that they provide you with the current prepayment penalty amount if you were to pay out your mortgage today. When speaking with your lender, ensure that they properly applied all prepayment provisions that you are entitled to before calculating the prepayment penalty. Furthermore, ask your lender for a breakdown of all additional discharge fees and other "hidden" charges at the time of discharge.
In addition to the amounts paid to your existing lender, you can expect to incur other "soft costs" when refinancing, which might include legal or notary costs, appraisal fees, survey fees, title insurance, and inspection fees. Legal or notary services will be the most significant of these "soft costs", and can vary both in terms of scope of service and in terms of cost.
Step 3 - Your Next Steps
When you contacted your existing lender for payout costs, they likely serenaded you with a range of their own alternative mortgage options.
Refinancing with your existing bank (or other lender) does offer certain advantages. Most notably, your existing lender may be willing to waive certain of the "soft costs" associated with refinancing your mortgage (such as legal fees, appraisal and survey fees, title insurance, and inspection fees).
At this time, do not commit to refinancing with your existing lender (at least not just yet!) You will want to first to obtain additional quotes from other lenders.
The most effective way to obtain additional competing quotes is to contact an experienced independent mortgage broker. Mortgage brokers serve as intermediaries between lenders (banks and other financial institutions) and borrowers such as yourself. Mortgage brokers can provide you with a broad selection of products from a large number of lenders. Mortgage broker compensation is typically received in the form of commissions paid by the lenders, and therefore there is normally no direct cost to you from working with a mortgage broker.
Mortgage brokers (like other professionals) vary greatly in terms of quality of service and depth of knowledge.
I personally have refinanced the mortgage on my own home twice over the past decade, and in both cases I used the mortgage brokerage services of Karen Cameron of Meridian Mortgage Services Inc. (Karen also arranged my original mortgage back in 2001, hence making me a three-time client of hers). Karen's commitment to customer service is outstanding, and she has consistently offered me well-reasoned and impartial advice on all mortgage-related matters.
I hope that this tutorial has left you with a clearer understanding of the potential benefits and costs of refinancing your fixed-rate mortgage. For any comments or questions relating to the use of my Mortage Refinance Calculator spreadsheet (or the economics of mortgage refinancing in general), please phone me at 604-910-5457 or drop me an e-mail using the form below.