Microsoft Office Tutorials and References
In Depth Information
To Copy the Formulas to Fill the Amortization Schedule
1 Select cell I21. Enter Subtotal
as the row title. Select the
range J21:K21. Click the Sum
button on the Ribbon.
2 Select cell I22. Type Down Pymt
as the row title. Select cell K22
and then enter =c5 as the down
3 Select cell I23. Type Total Cost
as the row title. Select cell K23,
type =j21 + k21 + k22 as the
total cost, and then click the
Enter box in the formula bar
to complete the amortization
schedule totals (Figure 4–43).
What was accomplished in the
The formula assigned to cell
K23 (=j21 + k21 + k22) sums the
total amount paid on the princi-
pal (cell J21), the total interest
paid (cell K21), and the down
payment (cell K22). Excel assigns
cell J21 the same format as cell
J3, because cell J3 is the ﬁ rst
cell reference in =SUM(J3:J20).
Furthermore, because cell J21
was selected ﬁ rst when the
range J21:K21 was selected to
determine the sum, Excel
assigned cell K21 the same for-
mat it assigned to cell J21.
Finally, cell K22 was assigned
the Currency style format, because
cell K22 was assigned the formula =c5, and cell C5 has a Currency style format. For the
same reason, the value in cell K23 appears in Currency style format.
of cell C5
life of loan
total cost assigned
format of cell J21
To Format the Numbers in the Amortization Schedule
The ﬁ nal step in creating the amortization schedule is to format it so it is easier to
read. The formatting is divided into two parts: (1) formatting the numbers and (2) adding
borders and background.
When the beginning balance formula (=c6) was entered earlier into cell H3, Excel
automatically copied the Currency style format along with the value from cell C6 to
cell H3. The steps on the next page use the Format Painter button to copy the Currency
style format from cell H3 to the range I3:K3. Then the Comma style will be assigned to
the range H4:K20.