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

B9 is

=–ROUND(PMT(Rate/12,Term*12,Amount_Financed),2)

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.

Summary information

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

=SUM(B14:B381)

Placing the summary information above the schedule itself eliminates the need to scroll

to the end of the worksheet.

The schedule

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

=Amount_Financed

To increment the date for the payment rows, the DATE function is used. The formula in A15 is

=DATE(YEAR(A14),MONTH(A14)+1,DAY(A14))

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.