 PERSONAL FINANCIAL PLANNING

February 2003

The Mortgage Refinancing Decision: Updated Spreadsheet

By I. Richard Johnson and Paul A. Randle

This article updates the spreadsheet model described in “The Mortgage Refinancing Decision: A Break-Even Approach” (The CPA Journal, February 1996). Exhibits 1 and 2 depict the structure of the spreadsheet and Exhibit 3 illustrates the solver parameters. The Exhibits were prepared using Microsoft Excel 2000.

The spreadsheet model, as constructed, requires variables to be entered in the “data input” section of the spreadsheet. Based upon these values in these fields, the model computes the following:

• The remaining term (in months) of the existing loan
• The new loan amount (assumed to be the existing loan balance plus all refinancing costs)
• The new principal and interest payment
• The break-even point, in months, of the existing and new loan payments.

The break-even number of months is determined with the solver function by naming cell F31 the “target cell” and setting its value at zero. Cell D27 is the cell designated as the change cell. An illustration of the actual “solver parameters” screen used with the model is included as Exhibit 3. The solver option is an add-in feature that must be installed before it can be used. Once this is done, by selecting “add-ins” under the Tools menu, the function will appear in the Tools menu.

Exhibit 1 provides an example of the model’s capabilities that can also be used to test the model after it has been built. This example assumes that a borrower obtained a 15-year (180-month) mortgage in the amount of \$200,000 15 months ago, at an annual interest rate of 9.5%. The monthly principal and interest on the loan is \$2,088.45 and the current balance on the loan is \$191,988.63. The balance of the loan will be amortized over the remaining 165 months. The borrower wishes to analyze the break-even holding period associated with a new loan, at 7.375%, for an amount equal to the current balance on the existing loan plus refinancing costs of \$4,400 (a total of \$196,988.63). The new loan will have a 15-year (180 month) term. The resulting 13.8 month break-even point can be seen in cells D27 and E27.

The formulas used to construct the model are included in Exhibit 2. Corresponding rows and column numbers identify the position of each formula in the model. The rows and columns omitted from Exhibit 2 do not contain formulas.

I. Richard Johnson, PhD, CPA, is the Laurette G. Hale Professor of Accounting at Utah State University, Logan, Utah. Paul A. Randle, PhD, is an emeritus professor of finance at Utah State University.

Editor:
William Bregman, CFP, CPA/PFS 