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
Search JabSto ::




Custom Search