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

LCzerw7570

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.

darrentheturkey

04-23-2002, 02:37 PM

Do you need to know what percentage of each payment goes toward interest and what toward prin.?

LCzerw7570

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

simple.

Thanks

darrentheturkey

04-24-2002, 10:28 PM

Well, if it is simple interest then the solution is basic.

Interest=AmountBorrowed * Annual Interest Rate * Years

I=PRT

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.

LCzerw7570

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 .

chris.wallace

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.

-Chris

a_slosh

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

chris.wallace

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.

chris.wallace

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;

decimals++;

}

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;

}

jahasloth

10-07-2004, 09:23 PM

Wow. This thread is frightening. My brain hurts from thinking back to Microeconomics in university.