govtwork / loans / Loan amortization

Copyright 2001, 2002, Joel Anderson

Loan Amortization, How It Works - Loan.WK1

Download spreadsheet    

What is an amortized loan?

An amortized loan looks and feels like one big, long loan. But it is structured as a series of loans, each for a period - often a month, shorter than the term - years. At the end of each period, interest is paid on the principal balance. Payments are larger than the amount of interest due. The excess payment is applied to reduce the principal. As the principal is paid down, subsequent loans are for the then outstanding principal balance.

Annual Percentage Rate - APR

APR is a nominal rate. "Nominal" means "in name only." APR is not a true rate. APR is the rate quoted on loans.


Applying the periodic rate,
the devil is in the details  

The periodic rate is fixed by the APR, but the rate that is applied to the principal balance may be variable.

The periodic rate may be daily but the payments monthly, based on the number of days in a month. The rate applied is the daily periodic rate multiplied by the number of days in the month. APR/365(or 366)*(days in month). Further:

Lenders can define months by the actual number of days in them. They can define years by the actual number of days in them. Then, the rate applied to the principal balance is different for each month depending on the number of days in the month, or different when the months have the same number of days because of the difference in the number of days in the year.

Payments may be adjusted to a standard starting date, say the 1st or the 10th of the month. Payments may be calculated on days-between-dates for periods beginning on the 15th, 18th, 22nd, etc., of each month. Payments may be every 2 weeks, twice a month, etc.

[See Loan_365 for a variation.]
APR is the disclosure that the banks could agree upon at the time Truth-in-Lending was written into law. APR replaced the 6,000 ways interest was being quoted. To that extent, borrowers benefited. But APR is not often a true disclosure. The only time APR is an accurate disclosure is when payments are made on an annual basis, that is, almost never. Most loans are structured to have 12, 24, or 26 payments per year. These loans have a higher rate of return to the lender than is disclosed by APR.

No financial analyst uses nominal rates - for good reason - they don't disclose the true cost of money.

How are loan payments distributed to interest and principal?

The interest due on each (usually monthly) loan increment is determined by applying the periodic rate to the outstanding principal balance. [See: Caveat, sidebar.] The interest due is subtracted from the payment, and the balance of the payment is subtracted from the outstanding principal. This creates a new principal balance. This scheme is iterated until the principal balance is zero.

How is the periodic interest rate determined from the APR?

True rate

Use discounted cash flow to
find the true rate of interest

There are many ways to calculate interest and remain within the bounds of Truth-in-Lending. Absent the lender's formula it's hard to calculate the actual rate of interest. Reverse engineering lender' loan disclosures to the formula the lender uses remains a problem. You can ask for the formula but they aren't required by law to tell you.

Discounted cash flow determines the true rate of interest. An amortization table shows the periodic flows, though it will not show points, fees and other gimmicks that must also be included. But for any given loan, an amortization table's flows is a good place to start.
Divide the APR by the lender's period. The period is usually the day. The day yields the highest return to the bank while simultaneously disclosing the lowest APR to the borrower. [See: Caveat, sidebar.]

Periods shorter than a day (hours minutes, seconds) are legal, but difficult to justify to regulators. And they return only a little more interest.

Some lenders will use a monthly period.

Having determined the periodic rate, adjust the periodic rate to the payment period by multiplication.

Ignorance is not bliss

For lenders, APR is only the rate disclosure revealed to the borrower. Lenders want to know how much interest a loan really pays. A loan's real return can be determined by knowing the payment scheme, points, fees and prepayment penalties, and making an informed guess at the lenght of the loan - few loans run to term (15-30 years). Lenders use discounted cash flow to analyze the prospective return on loans.


Download spreadsheet    

How to use the Loan Amortization Table

  1. Extend the number of payments (and associated formula cells) to agree with the term of your loan.

    NOTE: Payment numbers are not used in the formulas. The table is extended by copying formulas in Pmt#2 row to as many rows below as there are payments.
  2. Copy the "Pmt, min" to all column 2 "MinimumPmt" cells. If you have copied Pmt#2 row formula cells, down, this will be done, automatically. (The table looks weird without payments entered into the payment cells.)

Caveat on the use of the Loan Amortization Table

The table is based on 12 equally-spaced payments during the year. The periodic rate applied to the principal balance is APR/12. "Monthly" payments are not always based on 12 equally-spaced payments. [See: Caveat, sidebar.]

Loan Amortization Table (for a plain vanilla loan)

Input data:
Principal  $20,000.00                         Notes:
APR 9.00% EstTotPmt $20,988.35 w/o added Principal
Years 1 EstTotInt $988.35 w/o added Principal
Periods/Yr 12 Yield 9.38 APY (banker's view)
Pmt, min $1,749.03

NOTE: payment cells #5 and below contain additional principal.
Additional principal payments are for demonstration only.
Copying the formulas for payment #2, down, will overwrite
the demo. No harm done, you can always reenter new pmt values.

NOTE: na=not applicable

MinimumPmt Applied Applied
Payment toAmortize toInterest toReducing Principal Cumulative
number (+addnlPrin) onPrinBal PrinBal Balance InterestPaid
Initialize na na na 20000.00 na
1 1749.03 150.00 1599.03 18400.97 150.00
2 1749.03 138.01 1611.02 16789.95 288.01
3 1749.03 125.92 1623.10 15166.84 413.93
4 1749.03 113.75 1635.28 13531.57 527.68
5 2500.00 101.49 2398.51 11133.05 629.17
6 2500.00 83.50 2416.50 8716.55 712.67
7 2500.00 65.37 2434.63 6281.92 778.04
8 2000.00 47.11 1952.89 4329.04 825.16
9 2000.00 32.47 1967.53 2361.51 857.62
10 2379.22 17.71 2361.51 -0.00 875.34
11 0.00 0.00 -0.00 875.34
12 0.00 0.00 -0.00 875.34