Microsoft Office Tutorials and References

In Depth Information

All the examples in this section are available at this book's website in the workbook

loan amortization.xlsx.

User input section

The area above the schedule contains cells for user input and for intermediate calculations. The user input cells

are shaded, making it easy to distinguish between cells that can be changed and cells that are calculated by for-

mulas.

The user can enter the purchase price and the down payment. The amount financed is calculated for use in the

amortization calculation. The formula in cell B5 is

=Purchase_Priceâ€“Down_Payment

Names 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

=-PMT(Rate/12,Term*12,Amount_Financed)

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.

Because this is a simple amortization schedule, the loan term is fixed at 30 years. Using a different term would

require adding or deleting rows of formulas and also changing the summary formulas.

Summary information

The first line of the schedule (row 13) contains summary formulas. Placing the summary information above the

schedule itself eliminates the need to scroll to the end of the worksheet.

In this example, only the totals are shown. However, you could include subtotals by year, quarter, or any other

interval you like. The formula in C13 sums 360 cells and is copied across to the next two columns:

=SUM(C15:C374)

The schedule

The schedule starts in row 14, which shows the loan date and the amount financed (the beginning balance). The

first payment is made exactly one month after the loan is initiated. The first payment row (row 15) and all sub-

sequent rows contain the same formulas, which I describe later.

The Payment column simply references the Monthly_Payment cell in the user input section.

The Interest column computes a monthly interest based on the previous loan balance. The formula in D15 is