ApPENDIXA INTRODUCTION TO SPREADSHEETS
command is easy to use and understand. For example, the formula =EXP(1.9) would be
copied unchanged to a new location. However,cell addresses are usually part of the range
being copied, and their absolute and relative addresses are treated differently.
Anabsolute addressis denoted by adding $ signs before the column and/or row..
For examplein FigureA-la,$A$4is the absoluteaddressfor the interestrate. Whenan
absolute address is copied, the column and/or row that is fixed is copied unchanged. Thus
$A$4 is completely fixed, $A4 fixes the column, and A$4 fixes the row. One common Use
for absolute addresses is any data block entry, such as the interest rate. When entering or
editing a formula,changing between A4, $A$4, A$4, $A4, and A4 is most easily done using
the F4 key, which scrolls an address through the choices.
In contrast arelative addressis best interpreted as directions from one cell to another.
For example in Figure A-I a, the balance due in yeartequals the balance due in yeart- 1
minus the principal payment in yeart.Specifically for the balance due in year 1, DlO
contains =D9-ClO. From cell DlO, cell D9 is one row up and ClO is one column to the
left, so the formula is really (contents of 1 up) minus (contents of 1 to the left). When a
cell containing a relative address is copied to a new location, it is these directions that are
copied to determine any new relative addresses. So if cell DlO is copied to cell F14, the
formula is =F13-EI4.
Thus to calculate a loan repayment schedule, as in Figure A-I, the row of formulas is
created and then copied for the remaining years.Table 3-1 shows four repayment schedules for a loan of $5000 to be repaid over 5 years at an
interest rate of 8%. Use a spreadsheet to calculate the amortization schedule for the constant
principal payment option.SOLUTION~ ..
The first step is to enter the loan amount, number of periods, and interest rate into a data block in
the top left part of the spreadsheet. The next step is to calculate the constant principal payment
amount, which was given as $1252.28 in Table 3-1. The factor approach to finding this value is
given in Chapter 3 and the spreadsheet function is explained in Chapter 4;
The next step is to identify the columns for the amortizatiqu schedule. These are the year,
interest owed, principal payment, and balance due. Because some of these labels arewider than a
normal column, the cells are formatted so that the text wraps (rowheight increases automatically).
The initial balance is shown in the year 0 row.
Next, the formulas for the first year are written, as shown m.figure A-I a. The interest owed
(cellBlO) equals the interest rate ($A$4) times the balance due for year 0 (D9). The principal
payment (cell ClO) equals the annual payment ($A$6) minus the interest owed and paid (B1O).
Finally, the balance due (cell DlO) equals the balance due for the previous year (D9) minus the
)?rin<:ipalpaym€?nt.(C~O):T~e re~ults are shown in Figure A-la. ... ...- '= Now ~ells AlO toMDlOare selecred for yefu-1. Brdra~ginglloWn d"nthe right comer'ofD10,
the entire row can be copied for years 2 through 5. Note if cut andpaste is used,thenitis necessary
toc()I11pletethe year column separately (dragging increme,ntsthe year, b1Jtcuttinga,ndpasting
does not). The results are shown in FigureA-Ib..
.,j.