Nowadays, while interest rates are low, many individuals contemplate refinancing their mortgage. But with refinancing comes costs. Some homeowners are considered “refinance junkies” and continually jump from one low interest rate to the next, but these individuals may not factor refinancing costs into the total benefit associated with a lower interest rate.

There are several different goals when refinancing a mortgage. A majority of homeowners seek to reduce their interest expense, but there are others who appreciate the ability to extend the term of the mortgage, effectively reducing their periodic payment. On the other hand, individuals may seek to lower their loan repayment period if they are in a position to afford a higher periodic payment. Another goal might be to consolidate debt. If you have an initial mortgage as well as a home equity loan, combining the two mortgages into one may level out the payments and simplify the repayment process.

Much of the analysis presented on mortgage refinancing stresses the fact that an individual should only refinance if they plan on being in their home for a while. This is because homeowners are urged to consider how many months of lower payments it will take to recoup the closing costs of the new mortgage. Individuals should also evaluate how long they intend to stay in a particular home in order to estimate the principal balance when it comes time to sell the property and compare that with what the balance will be if the mortgage is refinanced.

The CI Mortgage Refinance Calculator is meant to be a simple illustration of how refinancing your mortgage could affect your cash flow, whether positive or negative. This is a topic that has many intricate and elaborate caveats, and we advise that you consult a mortgage professional before making any decisions.

In this spreadsheet you can calculate the difference in periodic payments, cumulative interest and net present value between your current mortgage and your prospective mortgage. Refinancing costs and taxes are factored into the equation, as well as a simplified version of mortgage discount points and their related tax benefits.

There are several tabs on the mortgage refinance calculator spreadsheet. The first one is called Input, where you will input a vast majority of the information the spreadsheet requires to perform its calculations. If you have a fixed-rate mortgage and you are contemplating switching to a fixed-rate refinancing mortgage, all your inputs will be on this page. If you have an adjustable-rate mortgage (ARM), or are moving to an adjustable-rate mortgage, you will need to input periodic (typically monthly) interest rates on the amortization tables designated to ARMs.

We realize that inputting monthly interest rates for an ARM requires a large amount of estimation and subjectivity. However, many individuals have or want to consider an ARM. Although significant estimation is involved, this subjectivity allows the user to see the scenarios that would come about from different changes in interest rates in the future. Remember also that ARMs usually have a fixed component as well, so you only have to estimate the rate after the fixed period is over.

On the Input tab of the CI Mortgage Refinance Calculator, the first thing you input is the current year at the top in cell E1.

Next you begin entering your mortgage assumptions. The sheet begins with your current mortgage information. In cell D5 is a drop-down menu consisting of six different options: fixed 15-year, fixed 30-year, 10/1 ARM, 7/1 ARM, 5/1 ARM and 3/1 ARM (note that you won’t see the drop-down arrow until you click in cell D5). Next, in cell D6, input your current mortgage rate. The interest rate should be entered as a whole number and not a decimal. For example, if your interest rate is 4.25%, enter 4.25. It should represent the annual percentage rate on the loan. If you have an adjustable rate mortgage (ARM), input the interest rate per period on the “ARM Mortgage Amort. Schedule” tab. Cell D7 asks you to enter your discount points.

Discount mortgage points, in this case, refers to points paid to acquire your principal residence. Some individuals have a choice to pay points in order to obtain a lower annual interest rate on their mortgage. These refer to tax-deductible discount points. If you are not sure whether your points are tax-deductible or if you don’t have points on your mortgage, input zero in cell D7. To read more about points, or to decide if your points are considered tax-deductible, look through the information offered by the Internal Revenue Service (IRS).

According to the IRS, mortgage points are deductible in the year paid if you meet certain criteria. Taxpayers then have the choice to fully deduct the points in the year paid or to deduct them over the life of the loan. For the original mortgage, we assume that you would deduct the mortgage points in “payment zero” of the loan. Regarding refinancing, discount points are generally not deductible in full in the year you pay them. However, if you use part of the refinanced mortgage proceeds to improve your main home and you meet the criteria specified on the IRS website, you can fully deduct the part of the points related to the improvement in the year you paid them with your own funds. Then you deduct the rest of the points over the life of the loan. For simplicity, we assume that points would be deducted over the life of the loan.

On the Input tab, under your current mortgage assumptions you also have to input the original loan amount in cell D8, the original loan term, in years, in cell D9, the number of payments per year in cell D10 and the remaining periodic payments left on the loan in cell D11. The term “periodic” is used throughout the loan; for most individuals this means monthly.

Regarding your new mortgage, choose the loan type from the drop-down menu located in cell D15 (again, you won’t see the drop-down until you click in the cell). The new loan amount is the remaining balance on your current mortgage based on the inputs in the current mortgage area of the Input tab. In cell D18, input the new loan term in years. Only those who are evaluating a fixed-rate refinancing option will enter the applicable interest rate in cell D19. The interest rate should be entered as a whole number and not a decimal. For example, if your interest rate is 4.25%, you should enter the numeral 4.25. It should represent the annual percentage rate on the loan. You will also enter your new mortgage points in cell D20, payments per year in cell D21 and your federal income tax rate in cell D23. The spreadsheet recognizes the tax rate as a percentage, so you only need to enter the number. So if your tax rate is 33%, only enter 33 in cell D23.

The next step is to input estimated refinancing closing costs. Under the refinancing section, there are several different types of refinancing costs that you might incur. If you want to estimate them separately you have that option. If you prefer to estimate the refinancing costs as a lump sum, you can enter the estimated amount into any of the input cells in the range D27:D34. For example, if you wish to estimate that total refinancing costs will be approximately $4,000, enter $4,000 into cell D33 and that figure will be reflected as the total refinancing costs.

The last input requires you to estimate a discount rate for the net present value calculation. Net present value is the difference between the present value of cash inflows and the present value of cash outflows. Net present value allows individuals to evaluate a project, or in this case a mortgage, by discounting the cash flows over time back to the present. This attempts to capture the time value of money. When choosing a discount rate, a typical route is to use a long-run average of expected inflation over the term of your mortgage. If the inflation rate is increasing year to year, it is decreasing your purchasing power (or cash flows) by that amount as well. Although projecting inflation is not something anyone can do with certainty, the general range is typically between 2% and 4% for long-term evaluation purposes.

The goal of the net present value (NPV) calculation in this situation is to show users the value of their investment after the time value of money has been accounted for. The general rule with net present value is to accept positive NPV projects and reject negative NPV projects. The higher the NPV, the better.

Once you have input everything required on the Input tab (cells highlighted in yellow), you can move onto the Analysis tab. For those with adjustable rate mortgages, either for a current or new mortgage, go to the amortization table for that designated loan and input the monthly interest rate. If you currently have an adjustable rate mortgage (ARM), you can do this on the ARM Mortgage Amort. Schedule tab in column I and if you are switching to an ARM you input prospective interest rates on the ARM Refinance Amort. tab in column I.

The Analysis tab displays several comparisons at the top of the spreadsheet. First is the year that your loan will be paid off for both the current mortgage and the new mortgage, the cumulative interest paid before and after taxes and current periodic payment before and after taxes. Again, when we say “periodic,” for most individuals this typically means monthly. The first chart titled Savings on Current Periodic Payment displays your current periodic payment before and after tax for your current mortgage as well as for the hypothetical new mortgage. To the right of the chart you can see the actual difference between the payments.

The next chart represents the cumulative interest paid before and after taxes. Of course you want to pay less interest over the life of your loan, so this chart allows you to compare which option will require you to pay more interest over the term of the mortgage. Again, to the right of the chart you can see the actual difference in cumulative interest paid before and after tax.

The last chart displays the net present value, or cost, of your current mortgage as well as your new mortgage. This calculation is based on the aftertax expense of the mortgage over the term. On the individual amortization tables there is a column titled “total expense.” For your current mortgage, this takes into account your points (if applicable) as well as the interest deduction you receive from having discount points. The details of these calculations are explained later in this article when the formulas are addressed. This particular net present value calculation shows the total expense (the adjusted principal amount) as an outflow and the periodic payments as inflows. This was done solely because the amortization tables are “easier on the eyes” if there aren’t several negatives throughout the table. However, it is worth noting that the upfront payment and the periodic cash flows must have an opposite sign (positive or negative) and you can choose which way you prefer if you recreate the calculation.

Creating a Drop-Down Menu

On the input page, the first thing that was created was the drop-down menu in cell D5. If you go to the data tab at the end of the worksheet tabs and scroll to column AI, you will see six values starting in cell AI2: Fixed 15-year, Fixed 30-year, 10/1 ARM, 7/1 ARM, 5/1 ARM and 3/1 ARM. These are the options that are in the drop-down menu located on the input page. To create a drop-down menu, you type the list of items you want in the drop-down list, as I did on the data worksheet. Then highlight them, or select the cells that contain the names you want in the drop-down list. At the top of the Excel spreadsheet, click on the tab titled Formulas. Then click Name Manager and New at the top of the Name Manager box. You decide on the name of your drop-down list, the scope of the list, comments, as well as the cells the drop-down list will reference. The name can’t have a space in it. So, for example, I named my drop-down menu “MortgageType.” I chose to set the scope as “workbook” just in case I wanted to use the drop-down list anywhere throughout the spreadsheet. The comments section is optional. If you had previously selected the cells with the names you want in the drop-down menu (fixed 15-year, fixed 30-year, etc.) then those cell references should appear in the “refers to” box. If not, adjust the cell range accordingly. Once you are finished composing the name for the drop-down menu, click “OK.” Also, keep in mind we are using Excel 2013, so the location of the name manager might differ slightly if you are using a different version of Excel.

In order to put the drop-down menu in a specific cell, click on the cell you want it in (in our case cell D5 on the Input tab). Then click on the Data menu at the top of the Excel worksheet and choose the Data Validation option. Click it and a separate data validation box should appear. On the settings tab of the box you will see a couple of options for “validation criteria.” In the first drop-down list, you have to choose what type of value you will allow in the cell you have selected: Choose list, and check the boxes for “ignore blank” and “in-cell dropdown.” Then in the box that asks for a source, type in the name of the drop-down list that you previously created. In our example, I named my list “MortgageType.” Then click OK. You have now made a drop-down list!

Formulas on the Input Tab

On the Input tab there are a couple other formulas that were used. For example, in cell D17, we calculated the refinanced mortgage principal with the points included. You will see the formula:

=$D$16-($D$16*($D$20/100))

The dollar signs in the formula are used to make the cell an “absolute cell reference.” When a formula contains an absolute reference, no matter which cell the formula occupies the cell reference does not change: If you copy or move the formula, it refers to the same cell as it did in its original location.

The calculation is done because mortgage discount points are considered to be prepaid interest on your mortgage loan. The more points you pay, the lower the interest rate on the loan, and vice versa. A point is a fee equal to 1% of the loan amount. So if you have a $300,000 mortgage, one point is equivalent to $3,000. This type of mortgage point is tax-deductible. The formula is essentially saying:

New loan amount – (new loan amount × (loan points ÷ 100))

Again, this is done because paying points reduces the amount of interest you will have to pay over the life of the loan.

Another formula used on the Input tab can be found in cell G8:

=IF(ISNUMBER(SEARCH("ARM",$D$5)),"", PMT((($D$6/100)/$D$10),($D$9*$D$10),$D$8, 0,0))

This formula begins with an IF function. The syntax is:

IF(logical_test, “value_if_true”, “value_if_false”)

Then you will see an ISNUMBER function. The syntax is:

ISNUMBER()

where the value is what you want to test. It will return a “true” for numbers and a “false” for anything else.

The SEARCH function syntax is:

SEARCH(find_text, within_text, start_number)

“Find text” references the text you want to find, “within text” references the cell in which you want to search for the value of the “find_text” argument. The “start_number” reference is optional and references the number in the “within_text” argument at which you want to start searching. I did not use the “start_number” reference in the formula used.

The formula used in cell G8 is first searching for the letters “ARM” in cell D5 on the Input tab, which is the mortgage type that the user would have chosen from the drop-down menu. Then if it finds the letters ARM, I have instructed the formula to return a blank cell. This is indicated using the two quotation marks next to one another. The blank is appropriate because if the loan type is an ARM, there will be a different mortgage payment than fixed. Instead of embedding another formula into this one, I simply programmed the cell to be blank if the ARM option is chosen in cell D5. If the search does not find the letters ARM, the formula is instructed to calculate the payment amount (PMT) for the fixed-rate mortgage payer. The formula for mortgage payment is as follows:

PMT(rate,nper, pv, fv, type)

where “rate” is the rate at which the mortgage should be calculated. “Nper” represents the total number of payments for the loan. “PV” is the present value, or the total amount that a series of future payments is worth now, also known as your mortgage principal. “FV” represents the future value, or the cash balance you want to attain after the last payment is made. The “type” refers to when the payments will be made; use a 0 to represent payments made at the end of the period, and use a 1 to represent payments made at the beginning of the period. The future value and type inputs are optional in this formula.

To calculate the rate for the payment formula, I selected cell D6, which is the rate input by the user, and divided that number by 100. This is because the formula requires the rate to be input in decimal format, and in our spreadsheet rates are input as a whole number. The decimal is then divided by the payments per year in order to get a periodic rate. The periodic rate must be used in this calculation because we are looking for a periodic payment (in most users’ cases, monthly). Since we are using the periodic rate to calculate periodic payments, we must input the total number of payments in a way that correctly reflects the periodic rate. The number of payments is calculated by multiplying the value in cell D9 by the value in cell D10. The future value in the formula is input as zero because we want the mortgage to be paid off entirely by the end of the total periodic payments.

In cell F8 you will see the formula:

=IF(ISNUMBER(SEARCH("ARM",$D$5)),"","Fixed Payment Before Refinance")

This uses the same logic explained above, except it tells Excel to return a blank cell if the letters “ARM” are found in D5, and if these letters are not found, then Excel will display the words “Fixed Payment Before Refinance.” This is programmed in the worksheet so that the cell “disappears” if an ARM is chosen, and so users aren’t confused by a random phrase being displayed in the cell with no numbers to accompany it. This rationale was also used for the formula in cell F9:

=IF(ISNUMBER(SEARCH("ARM",$D$5)),"","Principal Balance")

In cell G9 you will see the formula:

=IFERROR(VLOOKUP(D12+1,’Fixed Mortgage Amort. Schedule’!B11:G371, 2, FALSE), "")

The IFERROR syntax is:

IFERROR(value, value_if_error)

“Value” is the argument that is checked for an error. The “value_if_error” represents what you want to show if in fact an error is found. The following errors types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

The VLOOKUP formula syntax is:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

The “lookup_value” represents the value to search in the first column of the table or range. It can be a number, text, or logical value. The “table_array” references the range of cells that contains the data you are trying to find. You can use eference a range or a range name. “Col_index_num” represents the column number in the table array argument from which the matching value must be returned. This value can’t be less than 1. The “range_lookup” argument is an optional argument and is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If “range_lookup” is either “TRUE” or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than the lookup_value is returned. If range_lookup is either “TRUE” or is omitted, the values in the first column of the table array must be placed in ascending order; otherwise, VLOOKUP might not return the correct value.

If you type in “FALSE” for range_lookup, VLOOKUP will only find an exact match. If there are two or more values in the first column of the table array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #NA is returned.

Regarding the formula used in cell G9, I used the IFERROR function to say, “lookup the value of the number of periods paid plus one, entered in cell D12, in the table array on tab ‘Fixed Mortgage Amort. Schedule,’ and select the value corresponding to second column in the table. If there is an error in that cell, then let cell D9 on the Input tab be blank. Otherwise, insert the cell value from the table array.”

The amortization figures on the Fixed Mortgage Amort. Schedule tab will show an error value if the user selects an ARM as their current mortgage. This will in turn make cell D9 on the Input tab “disappear” when ARM is selected as the current mortgage.

Formulas on the Fixed Mortgage Amort. Schedule Tab

On the Fixed Mortgage Amort. Schedule tab there are several different calculations pertaining to your current mortgage.

In cell D4 is the current periodic payment before tax. This is the same value that was on the Input tab, except multiplied by a negative one. Multiplying by negative one simply makes the value positive, which makes more sense for some users. There are also several figures carried over from the Input tab: the loan term, interest rate, payments per year, points, amount borrowed and tax rate.

In cell B10 and down, you will see the payment periods from zero to 360. In cell C11 is the beginning debt value in “time zero.” This is the principal amount that you borrowed for your original mortgage. Cell D11 calculated the interest expense, and the formula is:

=(C11*(G6/100))*(-1)

Or: (principal x (points/100)) x (-1)

This represents the amount of points that can be deducted from your mortgage. As mentioned earlier, for the current mortgage calculation, we chose to deduct interest points in “time zero” as opposed to amortizing the points deduction over the life of the loan.

In cell B12 the first payment begins, and the principal amount is carried down into cell C12. The interest expense in cell D12 is calculated as:

=$C12*(Input!$D$6/100)/$G$5

Or: principal × (annual percentage rate ÷ 100) ÷ payments per year

Interest expense after tax is calculated in cell E12 as:

=($D12*(1-$G$8/100))

Or: interest expense × (1 – (tax rate ÷ 100))

When something is tax-deductible, it reduces your taxable income. The equation above represents the “tax benefit” received from reducing your taxable income.

In cell F12, you will see the reduction of the principal:

=IF(ROUND(D12, 2)=0,0,$D$4-D12)

This formula uses the IF function. It is essentially saying “round cell D12 to two decimal places; if it is zero, then let cell F12 equal 0, otherwise, subtract cell D12 from the fixed periodic payment in cell D4." Notice the dollar sign values are around cell D4 but not cell D12 in the formula. This is because throughout the whole amortization table, the interest expense for a certain period will have to be deducted from the original fixed periodic payment. The dollar signs make sure than when the formula is “dragged down” it doesn’t change the reference to cell D4, but does change the reference to cell D12 depending on the periodic payment.

In cell G12 is calculation for total after-tax expense:

=F12+E12

This calculation accounts for the fact that a portion of your interest will be credited back to you because it is tax-deductible. There is no necessary adjustment to principal.

Cell C13 contains the remaining balance on the mortgage principal for the period. This is calculated by subtracting the reduction in principal from the prior period (cell F12) from the original loan balance. In subsequent periods, or in cell C14 for example, you will see that the beginning debt is the prior period’s beginning debt, minus the reduction in principal in the prior period.

As you can see from the amortization table, the amount of principal paid and interest paid varies from period to period. Earlier payments on a mortgage consist primarily of interest and only a small portion of principal, but throughout time this patterns slowly reverses and payments later in the mortgage term consist primarily of principal reduction as opposed to interest.

In cell I12 is the cumulative interest paid on the loan to that point. This essentially just adds all the interest payments from row D together to show the user how much interest is paid over the life of the loan. In cell J12 is the first calculation for cumulative interest paid after tax. This essentially adds all the interest amounts determined in row E together to show the user how much interest he or she has paid after taking taxes into account.

In cell L11 is the total amount of interest paid as of the current period. This shows the amount of interest you have paid thus far, according to the information that was input on the Input tab. The formula for this calculation is:

=VLOOKUP(data!B5+1,’Fixed Mortgage Amort. Schedule’!B11:J371, 8, FALSE)

The VLOOKUP function in this cell is referencing a figure that is on the data tab (we will address this tab later). That particular cell (B5 on the data tab) references the payments made on the current loan. Essentially the formula is saying, “In the table array on the ‘Fixed Mortgage Amort. Schedule’ tab ranging from cells B11 to J371, search for one payment above the most recent mortgage payment made, in column 8 (which is the column addressing cumulative interest before tax), and return that figure.” This formula was used because each user will have a different “current period,” so therefore the cumulative interest paid as of “today” will change. This same concept is used to return the value in cell M11 for the current interest paid after tax. In cell L15 is the remaining interest on the loan before tax, and in cell M15 is the remaining interest after tax.

The amortization tables for the refinanced mortgage are identical to the original mortgage tabs in terms of set up. There is one difference. On the Refinance Fixed Amort. Schedule tab, in cell N19 is a calculation with the title “points” above it. As mentioned earlier in the article, mortgage points for the refinanced loan amount are assumed to be deducted throughout the life of the loan. This differs from the current mortgage because we took the interest deduction “up front” or at time zero.

The calculation in cell N19 is:

=(((G6/100)*G7)/(G3))*(1-(G8/100))

Or: ((points ÷ 100) × loan principal) ÷ payment periods) × (1 – (tax rate ÷ 100))

This amount is then subtracted from the figures in the “interest expense after tax” column because the calculation represents the amount your expense is reduced by due to the deductibility of interest points. This calculation is done on both refinance amortization schedules.

You will also notice that on the refinance amortization schedules, the total refinancing costs have been added into the total expense figure at the top of the amortization table. This factors into the net present value calculation by including the refinancing costs as an initial cash outflow.

Calculations on the Data Tab

The data tab requires no additional user input. This is essentially a place to aggregate information that is needed for the charts. The box toward the top of the page has all information that you have seen on previous tabs.

There are also several different boxes of calculations. Here, all the information is pulled based on where you are in terms of payment in your current mortgage and shown next to the mortgage refinance option. The four boxes compare monthly savings by switching from a fixed mortgage to another fixed mortgage, a fixed mortgage to an ARM, an ARM to fixed and, lastly, an ARM to another ARM.

Each payment is pulled using the VLOOKUP function and referencing the individual table arrays in which the payments are located. For example, the monthly savings fixed rate to fixed rate uses the VLOOKUP function to pull the respective payments based on the most recent payment paid. In cell B16 is the formula:

B5+1

Cell B5 references the amount of payments you have already made on your current mortgage. Each subsequent period after cell B16 references the cell above it and adds one. This is done so the numbers adjust in relation to where you are in your current mortgage; consequently, the VLOOKUP functions used to pull in the monthly payments adjust based on the payment number in row B.

At the bottom of each of these four tables there are additional calculations: a calculation for the total amount of payments for each mortgage from now until the end of the loan term, the period count, the average and the aftertax net present value. The total row simply sums the table values in a particular column. The period count row counts the number of values in the column that are greater than 1. This is done for averaging purposes. The COUNTIF function syntax is as follows:

COUNTIF(range, “criteria”)

The “range” represents one or more cells that are to be counted, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The “criteria” represents a number, expression, cell reference, or text string that defines which cells will be counted.

The average row divides the total for a given column by the period count.

The aftertax NPV comes into use on the Analysis tab where the charts are presented. The syntax for the net present value calculation is as follows:

NPV(rate, value 1, value 2, value 3….)

The “rate” is the discount rate over the length of the period. We discussed the discount rate earlier in the article. “Value 1” is a required input and represents the initial cash outflow or inflow, in our case the initial cash outflow. Subsequent inflows of cash include the aftertax payments to the mortgage.

Analyzing whether to refinance your mortgage or not is a difficult and complex topic. As I realized simply by writing this article, there are many caveats and adding the tax benefits complicates the issue. There are many refinance calculators available online, but most seemed to be more on the simple side of things (namely, they don’t include tax deductions). The concept of points is another difficult concept to understand, and points may or may not be the route for you.

I am open to suggestions regarding what should be changed or added. I got excellent feedback on the retirement withdrawal calculator, and I do intend to attempt to add value to that calculator over time and perhaps see if I can work in some of the requests that members made.