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
take out the '-NoSpam' to reply
wrote in message
oups.com...
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.