Microsoft Office Tutorials and References
In Depth Information
Creating an Amortization Schedule
4
Select cell E2 and then enter 7.25
as the interest rate (Figure 4–32).
5
Enter 5.75 in cell E2 to return the
Loan Payment Calculator section
and Interest Rate Schedule section
to their original states as shown in
Figure 4–31.
What happens when the interest
rate is changed from 5.75?
Excel immediately displays the
cell containing the new rate
with a white font on a green
background and displays cell B15
with black bold font on a light red
background (Figure 4–32). Thus,
the white font on a green
background serves as a pointer in
the data table to indicate the row
that agrees with the input cell
(cell E2). When the loan ofﬁ cer
using this worksheet enters a
different percent in cell E2, the
pointer will move or disappear. It
will disappear whenever the interest
rate in cell E2 is outside the range of the data table or its decimal portion is not a multiple of
0.25, such as when the interest rate is 8.25% or 5.80%.
cell contents displayed
as black font on
light red background
because value in
cell B15 no longer
equals value in cell E2
cell entry is white
font on green
background because
value in cell B21
equals value in cell E2
Figure 4–32
Other Ways
1. Press ALT + O , D
The next step in this project is to create the Amortization Schedule section on the right
side of Figure 4–33. An amortization schedule shows the beginning and ending balances
of a loan, and the amount of payment that applies to the principal and interest for each
year over the life of the loan. For example, if a customer wanted to pay off the loan after
six years, the Amortization Schedule section tells the loan ofﬁ cer what the payoff would
be (cell I8 in Figure 4–33). The Amortization Schedule section shown in Figure 4–33 will
work only for loans of up to 18 years. You could, however, extend the table to any number
of years. The Amortization Schedule section also contains summaries in rows 21, 22, and
23. These summaries should agree exactly with the corresponding amounts in the Loan
Payment Calculator section in the range B1:E6.
Amortization
Schedules
Hundreds of Web sites
offer amortization
schedules. To ﬁ nd these
Web sites, use a search