Loan Amortization, How It Works - Loan.WK1

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.

Applying APR APR isn't used directly.    The devil is in the details. APR fixes the periodic rate, but the rate that is applied to the loan's principal balance is often variable. Here's why: The periodic rate may be a daily rate, but the payments monthly based on the number of days in that month. (The rate applied is the daily periodic rate multiplied by the number of days in the month.) The daily periodic rate may also reflect the number of days in the year in leap years (or not, at the lender's option). APR/365(or 366)*(days in month). Lenders can define months by the actual number of days in them. They can optionally define years by the actual number of days in them. The rate applied to the principal balance can be different for each month depending on the number of days in the month, or optionally different when the months have the same number of days because of the difference in the number of days in the year (during leap years). Some lenders only apply a leap-year adjustment to the month of February in leap years, and some apply a leap-year adjustment to the entire year, and some don't adjust for leap-year at all. Payments can 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, creating rate periods that aren't sync'd with the normal month. Payments may be every 2 weeks, or twice a month which could lead to 1st or 2nd payments in a month having fewer or more days, etc.. Payments may the same but the amount of interest different depending on the number of days in the payment period. [See Loan_365 for a variation.] Finding the 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 difficult to calculate the actual rate of interest. Reverse engineering the lender's loan disclosure to the formula they use is 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 loan, an amortization table is a good place to start.

APR - Annual Percentage Rate,
a nominal rate.

"Nominal" means "in name only." No financial analyst uses nominal rates - for good reason - they don't disclose the true cost of money.

APR is not a true rate. APR is the rate quoted on loans. 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 an accurate disclosure. The only time APR is accurate 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.

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 Applying APR sidebar.]

The interest due is subtracted from the payment, and whatever is left over, the balance of the payment, is subtracted from the outstanding principal. This creates a new principal balance. This scheme - first pay the interest, then pay down the principal - is iterated until the principal balance is zero.

How is the periodic interest rate determined from the APR?

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 Applying APR 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 to the lender.

Some lenders will use a monthly period.

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

Lenders don't use APR

For lenders, APR is only the legal rate disclosure required to be revealed to borrowers. They don't use APR to evaluate loans. They want to know the rate of interest a loan really pays. A loan's return can be estimated from the payment scheme, points, fees and prepayment penalties, and an informed guess at the lenght of the loan. Few loans run to term (15-30 years), most are paid off early. How long a loan runs before it's paid off can mean a large difference in the rate of return. From a lender's point-of-view, shorter is usually better. The sooner they have their money in hand the sooner they collect up-front fees on another loan. Lenders use discounted cash flow to analyze the potential return on loans under consideration, not APR.

Cash is not an asset - to a bank

Strange though it may sound to the average retail borrower, lenders look on cash as a liability, not an asset. Cash in the bank, to a bank, is not money making money. Loans are a bank's assets. Large borrowers are courted assiduously by banking Vice Presidents whose job is selling money. Their bonuses depend on making sales and not bring in borrowers who will default. The mortgage broker industry's sole purpose is to sell money, converting banking's cash liabilities into loan assets.

---

Spreadsheet

Download spreadsheet    

How to use the Loan Amortization Table

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.

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 Applying APR sidebar.]

Loan Amortization Table (for a plain vanilla loan)

Input data:
Principal	$20,000.00	|	Compute		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 (lender'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.

na=not applicable

Pmt_#	MinPmt to Amortize (+addnlPrin)	AppliedTo Interest onPrinBal	AppliedTo Reducing PrinBal	Principal Balance	Cumulative Interest Pd
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