Click to See Complete Forum and Search --> : monthly payment fomula

04-21-2002, 12:44 PM
I need to know the formula that when a person enters the
amount , the number of months and interest rate, the formula will return the monthly payments.Thanks in advance.

04-23-2002, 02:37 PM
Do you need to know what percentage of each payment goes toward interest and what toward prin.?

04-23-2002, 10:15 PM
No I don't need that. The users that I am building this for
will not use that feature. I am trying to keep things real


04-24-2002, 10:28 PM
Well, if it is simple interest then the solution is basic.

Interest=AmountBorrowed * Annual Interest Rate * Years


Then you add the amount borrowe to the interest accumulated (P+I) and divide by number of months.

Compound Interest is more complicated. If this is the case, tell me how often it is compounded.

04-26-2002, 07:55 PM
I believe I understand your formula, however, the problem
is that an interest amount is deducted from a monthly payment, the balance of the monthly payment decreases the
prinicipal. So every month the interest being charged changes, as well as the principal since it also declines as
monthly payments are made. I hope I clarified the problem.
Again thanks .

10-06-2004, 05:19 PM
I had this same problem. And here is your function to calculate it correctly:

function pmt(principal, rate, periods) {
mypayment = principal*(rate/12) / (1-1/math.pow(1+rate/12,periods));
return mypayment;

Now if struggling to figure out the RATE function. Grrr.


10-07-2004, 03:29 AM
amount left to pay = A
initial amount = P
interest per period = r (eg 12%pa r=1.01)
number of periods = n
repayment per period = M

1 month:
A = Pr-M

2 months:
A = (Pr-M)r-M

n months:
A = Pr^n-Mr^(n-1)-Mr^(n-2)-....-M
A = Pr^n-sum(from k=0 to k=n-1)Mr^k

the sum is a geometric progression (the ratio from each term to the next is the same) so the sum can be written as:

sum(from k=0 to k=n-1)Mr^k = M(r^n - 1)/(r-1)

let me know if you want this explained

therefore after n months:
A = Pr^n-M(r^n - 1)/(r-1)

for complete repayment, A=0:
Pr^n-M(r^n - 1)/(r-1) = 0
Pr^n = M(r^n - 1)/(r-1)
M = Pr^n x (r-1)/(r^n - 1)

there is your equation for monthly repayments (as long as my algebra is correct all through):

payment = principal*math.pow(rate,periods)*(rate-1)/(math.pow(rate,periods)-1)

chris i don't think yours takes into account compounding interest on the principal

10-07-2004, 03:39 PM
I'm not sure, but my pmt function returns the same results as the excel pmt function. So whatever excel is doing, mine is doing.

10-07-2004, 05:03 PM
And an update for anyone that needs it, using my pmt function I was able to create a rate() function which is able to find the fixed interest rate for an annuity, based on the principal, the payment, and the number of periods. My original pmt() function is also included.

function rate(principal, periods, payment) {
myrate = 0;
increment = 0.1;
decimals = 0
while(pmt(principal,myrate,periods) != payment && decimals < 9) {
while(pmt(principal, myrate, periods) < payment) {
myrate = myrate+increment;
myrate = myrate-increment;
increment = increment/10;
myrate = math.round(myrate*100000000)/100000000;
return myrate;

function pmt(principal, rate, periods) {
if(rate == 0) {
mypayment = principal/periods;
} else {
mypayment = principal*(rate/12) / (1-1/math.pow(1+rate/12,periods));
return mypayment;

10-07-2004, 09:23 PM
Wow. This thread is frightening. My brain hurts from thinking back to Microeconomics in university.