Loan Amortization Template in Excel

[husoway]

Does anyone have the formulas used in Excel's Loan Amortization
Template? I need plain formulas, not the ones like PMT etc... Given the
LOAN AMOUNT, INTEREST RATE, NUMBER OF YEARS and NUMBER OF PAYMENTS PER
YEAR, I need to be able to calculate all the valus that the sheet
produces in its Summary box and the payment schedule but I will do this
in Visual Basic. I would really appreciate your help.

 

[Gary Brown]

Not sure why you would want the formula on how the pmt function works
because Visual Basic (as well as Visual Basic for Applications (VBA))
supports the pmt function....

Syntax: Pmt(rate, nper, pv[,fv[,type]])

rate = interest rate per period
nper = total number of payment periods in an annuity
pv = present value (amount to be borrowed)
fv = future value ( usually -0-)
type = 0 - payments made at end of payment period
1 - payments are due at beginning of period
default is -0-

In Excel, you can make an amortization table by following the example below.

ASSUME MONTHLY AMORTIZATION TABLE
A1 = "Principal"
B1 = The amount to be amortized [assume $100,000]
A2 = "Annual Rate"
B2 = Annual Percentage rate of loan [assume 10%]
A3 = "Period in Years"
B3 = Period in years over which loan will be amortized [assume 30 years]
A5 = "Period"
B5 = "Payment"
C5 = "Interest"
D5 = "Principal"
E5 = "Add'l Payments"
F5 = "Balance"
F6 formula =B1
A7 thru A366 = 1 to 360 [# of periods in amortization of 30yrs x 12 months]
B7 thru B366 formula =-PMT($B$2/12,$B$3*12,$B$1,0)
C7 thru C366 formula =ROUND(F6*($B$2/12),2)
D7 thru D366 formula =B7-C7
F7 thru F366 formula =F6-D7-E7

HTH,
Gary Brown
(e-mail address removed)
take out the '-NoSpam' to reply

 

[husoway]

Thank you for replying to my post.
I am actually a business analyst defining amortization for developers
therefore I need to provide them the full mathematical formulas. I
cannot use any built in functions of any application. They need to know
the exact formulas.

Let me know if you know what the PMT function involves. Thanks for the
sample, it works nicely.

 

[Gary Brown]

Huseyin,
The formula you are looking for is...
-((P*(i/12)*(1+(i/12))^(n*12)))/(((1+(i/12))^(n*12))-1)
where
P = Principal amount
i = Annual interest rate
n= Period of loan in years

Example:
Principal $100,000
Interest 10%
Period - 30 years

-((100000*(0.1/12)*(1+(0.1/12))^(30*12)))/(((1+(0.1/12))^(30*12))-1) =
-877.571570088799

In Excel, -PMT(0.1/12,30*12,100000) = 877.571570088799

Identical to at least 12 places! If that isn't good enough for your
clients, you will never satisfy them.

I got this information off the web. Check out...
http://invest-faq.com/articles/analy-int-rate-return.html

Hope this helps and good luck.
Gary Brown