Using the Loan amortization and Loan analysis templates

Applies to
Microsoft Excel 2002
Microsoft Office Excel 2003

You're about to sign on the dotted line for a home mortgage or car loan. Naturally, you're having second thoughts. Is this the best interest rate or term for this loan? What's the payment schedule going to look like? Did I do all my homework?

Before you ink the deal, take a look at the following templates. The Loan Amortization and Loan Analysis templates are based on the PMT function, which calculates a loan payment with a fixed interest rate and constant payments. Together, these templates can provide you with the wealth of numbers you need to compare rates and terms, review monthly principle and interest payments, and do a "what-if" analysis just like the suits up in finance.

Loan amortization schedule template

The Loan amortization template calculates loan payments and interest on a loan and then displays a loan summary along with a list of monthly principle and interest payments for the life of the loan. It also calculates extra payments, a common practice for reducing loan costs.

Download the Loan amortization schedule template from Templates on Microsoft Office Online.

You can also open a version of this template directly in Excel:

  1. On the File menu, click New.
  2. In the New Workbook task pane:
    • In Excel 2003, click On my computer.
    • In Excel 2002, click General Templates.
  3. In the Templates dialog box, on the Spreadsheet Solutions tab, double-click Loan Amortization.

Loan analysis template

The Loan Analysis template contains a loan summary and a reference table comparing different monthly payments based on two variables: the interest rate (from 4% to 18% in ¼ point increments) and the loan term (for 3, 5, 10, 15, 20, 25, or 30 years). Rather than enter a formula for each possible cell, which would be time-consuming and error-prone, the template uses a data table to automatically generate the results.

Data tables are part of a suite of "what-if" analysis tools provided by Excel. In general, a data table takes a formula, plus one or two variables in the formula, varies the values based on the rows and columns you define in the data table, and then displays all of the formula results at once. In this case, the Loan Analysis template uses a two-variable data table to calculate how different interest rates (the first variable) and loan terms (the second variable) affect the monthly mortgage payment.

Loan Analysis template

Callout 1 Enter the loan numbers…
Callout 2 …to display the loan summary.
Callout 3 Data tables require the formula to be in the upper left-hand cell of the data table.
Callout 4 Compare different rates and terms in the data table.

Download the Loan analysis worksheet from Templates on Microsoft Office Online.

For more information on data tables, see About data tables and Create a two-variable data table.

More information

For training on how to use the PMT function and other related functions, see Plan payments and savings by using financial formulas.

To examine other loan and mortgage templates, see the following related templates:

 
 
Applies to:
Excel 2003