Microsoft Office Tutorials and References

In Depth Information

**To Copy the Formulas to Fill the Amortization Schedule**

3

•
With cell H4 active, drag the ﬁ ll

handle down through row 20 to

copy the formula in cell H4 (=I3) to

the range H5:H20 (Figure 4–42).

What happens after the ﬁ ll

operation is complete?

Because the cell reference I3 is

relative, Excel adjusts the row

portion of the cell reference as

it is copied downward. Thus,

each new beginning balance in

column H is equal to the ending

balance of the previous year.

with beginning balances

for each year calculated,

remaining values in

amortization schedule

now are correct

cell H4 copied to

range H5:H20

Auto Fill

Options

button

ending balance

for year 18 is 0

Figure 4–42

To Enter the Total Formulas in the Amortization Schedule

The next step is to determine the amortization schedule totals in rows 21 through 23. These totals should

agree with the corresponding totals in the Loan Payment Calculator section (range B1:E6). The following steps

show how to enter the total formulas in the amortization schedule.