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.

Caveat:
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 daysbetweendates 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 TruthinLending
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
TruthinLending. 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 (1530 years). Lenders use discounted cash flow to analyze the
prospective return on loans.
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 equallyspaced payments during
the year. The periodic rate applied to the principal balance is APR/12. "Monthly"
payments are not always based on 12 equallyspaced 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

