i$igJiJiJiJi/ H
d i0{ # gi( f$%&'((
B)'*-qq6'LOAN.WK1 - Loan Amortization Table - 2May01/JRA$'Copyright 2001, Joel Anderson<'Caveat: LOAN.WK1 is not a spreadsheet tutorial. It isA' incumbent on you to know how to use a spreadsheet.H' That said, this is an easy spreadsheet to modify and use.!'What is an amortized loan?@ ' An amortized loan, from an analysis point-of-view, is a E
'series of loans of limited duration, each in a smaller amount.B' The interest due on each subsequent loan is calculated byE'applying the periodic rate (the rate for the limited duration,D
'calculated from the APR, Annual Percentage Rate) to the then-%'outstanding principal balance.?' The remaider of the payment left over after paying theA'interest due is applied to the principal balance, creatingA'a new principal balance to which the periodic rate is next'applied.E' This scheme is iterated until the principal balance is zero.D'Caveat: While the periodic rate is fixed by the APR, the rateB' that is applied to the principal balance may not beD' fixed. The periodic rate may fixed on a daily basis, E' and the payments fixed on a days-in-the month, monthlyB' basis. The rate applied to the payment would be theC' product of the daily periodic rate and the number ofG' days in the month. In addition, payments may be adjustedC' to a standard start date, say the 1st or the 10th ofB' the month, or calculated on days-between dates for B' periods beginning on the 15th, 18th, 22nd, etc., ofA' each month. Payments may be every 2 weeks, twice aG ' month, etc. See spreadsheet: LOAN_365 for one variation.-"'How to use the Loan Amortization TableF$'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 any formulas.C(' The table is extended by copying formulas in Pmt#2 row9)' to as many rows below as there are payments.A+'2. Copy the "Pmt, min" to all column 2 "MinimumPmt" cells.A,' 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.)'0'Caveats on the use of the table:>2'The table below is based on 12 equally-spaced payments 83'during the year(s). The periodic rate, applied to(4'the principal balance, is APR/12.?6'Careful! this is the tricky part: "monthly" payments areC7'NOT always based on 12 equally-spaced payments. Lenders can ?8'define months by the actual number of days in them, then?9'apply a variable periodic rate, APR/365*(days in month),,:'to the principal balance outstanding.B<'Loan rates are legally[1] denoted in APR, a "nominal" rate;>='"Nominal" means that the rate is a rate "in name only."A>'No financial analyst uses nominal rates - for good reason.A@'1. Regulation Z, Truth-in-Lending, 12 CFR 226; as amended.@A' Official Staff Commentary on Regulation Z; as amended.BB' These two documents are 145 and 185 pages, respectively.BC' They are available, free, from any Federal Reserve Bank.:I'Loan Amortization Table (for a plain vanilla loan):J\=J\=J\=J\=J\=J\=K'Input data:L'Principal
"L N
L'Notes:
M'APR
2M
ףp=
?M"EstTotPmt!MF@M'w/o added PrincipalN'Years
NN"EstTotIntN`ߨ@
N'w/o added PrincipalO'Periods/Yr
OO"Yield/O7"@ d
O'APY (banker's view)P'Pmt, min,PN >T@8QER'NOTE: payment cells #5 and below contain additional principal.FS' Additional principal payments are for demonstration only.FT' Copying the formulas for payment #2, down, will overwriteKU' the demo. No harm done, you can always reenter new pmt values.W'NOTE: na=not applicableY"MinimumPmtY"AppliedY"AppliedZ"PaymentZ"toAmortizeZ"toInterestZ"toReducingZ"PrincipalZ"Cumulative
["number["(+addnlPrin)["onPrinBal["PrinBal["Balance['InterestPaid\\=\\=\\=\\=\\=\\=]'Initialize ]"na ]"na ]"na]@ ]"na
^^N >T@P!^b@^N >@
'^k>@
^b@
__N >T@P,_);@a@_5,@
'_Lo|e@
_Or@
``N >T@P,`i,{_@M`'qk\@
'`̟k@
`[iy@
aaN >T@P*a4fp\@MaH@
'aoPTm@
at@9w}@
b
b *b-W&_Y@MbCE@
'b8@
b:+\@
c
c *cghT@Mc%b@
'c`\F@
cJ.WE@
d
d *d٣WP@Mdr@@
'd쉸@
d"?UP@
e
e*eM>G@Me
6ӊ@
'e0c @
edJ@ɉ@
f
f*fp;@@Mfy @
'fis@
fQ̊@
g
g=
ףp@*g%r1@Mgts@
'g`}e
g,u
Z@
h*hMh
'h`}e
h,u
Z@
i*iMi
'i`}e
i,u
Z@