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 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.
William Bregman, CFP, CPA/PFS
The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.
Visit the new cpajournal.com.