PDA

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.