Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
Descriptive named ranges are used to make the formulas more readable. More
information on named cells and ranges is in Chapter 3.
The other calculation necessary to complete the schedule is the monthly payment. The formula in
The PMT function is used to determine the monthly payment amount. The rate (B7) is divided by
12, and the term (B8) is multiplied by 12, so that the arguments are on a monthly basis. This
ensures that the result of PMT is also on a monthly basis.
The ROUND function rounds the result of PMT to two decimal places. It’s tempting to avoid
rounding so that the result is accurate to the penny. However, because you will not be paying the
bank fractions of pennies, you shouldn’t have them in your schedule.
The first line of the schedule, after the header information, contains summary formulas. In this
example, only the totals are shown. However, you could include totals by year, quarter, or any
other interval you like. The formula in B13, and copied across, is
Placing the summary information above the schedule itself eliminates the need to scroll
to the end of the worksheet.
The schedule starts in row 14 with the amount financed as the beginning balance. The first
payment is made exactly one month after the loan is initiated. The first payment row (row 15) and all
subsequent rows contain the same formulas, which are described below. The formula in E14 is
To increment the date for the payment rows, the DATE function is used. The formula in A15 is
The DATE function constructs a date from the year, month, and day arguments. The arguments
are derived from the cell above, and the month is incremented by one.