As a banker, I know how difficult it can be to determine
whether to refinance your home and what option is the best.
I developed these spreadsheets for my own use in the past few
weeks. The rates are relatively current, but your should
update them based on current rates when you do your analysis.
The zip file contains the following Lotus 1-2-3 spreadsheet
REFI1.wk1 This sheet calculates all required information
for an industry standard one year ARM mortgage.
REFI3.wk1 This sheet calculates all required information
for an industry standard three year ARM mortgage.
REFI7.wk1 This sheet calculates all required information
for an industry standard Seven/twenty-three mortgage.
REFIFIX.wk1 This sheet calculates all required information
for an industry standard 30 year fixed rate mortgage.
REFICURR.wk1 This sheet is used to input your current loan
parameters for comparison
REFISUM.wk1 This sheet will import selected summary data
from the input sheets above and compare the various plans to
the current loan. It provides a format which allows you to
see the length of time it takes to break even under the
The sheets were created on a 386DX 25 w/4 mb of memory and
in order to have them run at an acceptable speed, I have
limited the inbuilt amortization tables to 10 years or 120
payments, which is the horizon for the worksheets. Also in
the zip file are worksheets with the bak extension. They are
identical to the wk1 extensions except that they have 360
payment amortization schedules and some run real slow
(notably REFI7.bak). They will work fine on a standalone
basis or in the linked environment if you change the
extensions to wk1, and are happy with the speed.
How to use the sheets:
1. Load ALL sheets into the same directory on your disk.
The program uses the LINK spreadsheet feature (releases
2.2 and above) and REFISUM retrieves data from the other
sheets. If you have an earlier release of Lotus, all
dependent sheets will work, but you will have to print
them individually and make manual comparisons.
2. Input the various parameters on each dependent sheet
(REFIFIX, REFI1, REFI3, REFI7, REFICURR) first. The
Margin and cap parameters are industry standard and
should not need to be changed. They will calculate
appropriately however if you change them.
3. After entering the parameters on the dependent sheets,
simply load REFISUM and it will import the pertinent
information from them and do the analysis. Be careful
not to modify the import formulas or they calculations
will be messed up. I would suggest copying REFISUM to
another name for customization. The dependent sheets
will still pass their information.
4. If you run multiple copies of 1-2-3 simultaneously, via
a multi-task program, and update and "/F S Replace" the
dependent sheets while REFISUM is loaded, use the command
"/F A L" to refresh the data from the dependent sheet.
5. Answering yes (Y) to the "FINANCE POINTS AND SETT. COSTS"
will cause the sheet to add in the indicated points and
dollar amount of settlement costs to the loan amount and
yield the financed amount. Answering no (N), will leave
the loan amount equal to financed amount, and indicate
the settlement costs are paid in year 1.
Good Luck and I hope you Save some money !