Part V: Analyzing Data with Excel
746
With this worksheet, you can easily answer the following what-if questions:l What if I can negotiate a lower purchase price on the property?l (^) What if the lender requires a 20-percent down payment?
l What if I can get a 40-year mortgage?
l (^) What if the interest rate increases to 7.0 percent?
FIGURE 36.1
This simple worksheet model uses four input cells to produce the results.
You can answer these questions by simply changing the values in the cells in range C4:C7 and
observing the effects in the dependent cells (C10:C13). You can, of course, vary any number of
input cells simultaneously.
The mortgage calculation example, simple as it is, demonstrates an important point about spreadsheet
design: You should always set up your worksheet so that you have maximum flexibility to make
changes. Perhaps the most fundamental rule of spreadsheet design is the following:
Do not hard-code values in a formula. Rather, store the values in separate cells and use cell
references in the formula.
The term hard-code refers to the use of actual values, or constants, in a formula. In the mortgage loan
example, all the formulas use references to cells, not actual values.
You could use the value 360, for example, for the loan term argument of the pmt function in cell C11
of Figure 36.1. Using a cell reference has two advantages. First, you have no doubt about the values
that the formula uses (they aren’t buried in the formula). Second, you can easily change the value —
which is easier than editing the formula.
Avoid Hard-Coding Values in a Formula