what is the formula for calculating how long it will take you to pay off a debt with a given interest rate?
interest rate equation
- Thread starter StoneViper
- Start date
-
Two Factor Authentication is now available on BeyondUnreal Forums. To configure it, visit your Profile and look for the "Two Step Verification" option on the left side. We can send codes via email (may be slower) or you can set up any TOTP Authenticator app on your phone (Authy, Google Authenticator, etc) to deliver codes. It is highly recommended that you configure this to keep your account safe.
you want an amortization schedule.
i don't remember the formula off the top of my head.
but you can do this easily in excel:
have these columns:
balance | interest rate | interest | payment |
balance: initially this is how much you took out the loan for, this will decrease monthly, but not by the full amount of payment.
interest: you have to know what your interest rate was quoted as. suppose this is a car loan, they quote you an APR of 15%, that usually means the interest rate per month is 15/12, since you usually pay monthly, and it compounds, it's usually over 15% a year. but suppose this is some weird loan where you pay twice a month, the interest rate term should be 15/24 per period, assuming the interest rate quoted was an annual rate. in short, the interest here is the rate of interest PER PERIOD, whatever you define the period to be. interest rate for your purpose, will be the same every period, but if you wanted, you can have a variable rate there. in fact, you can have some fun by having the rates follow some random distribution, to simulate the effects of a variable rate loan.
interest: dollar interest value.
payment: this is how much you pay in one period, should be fixed for the whole spreadsheet.
calculation works as such:
the first balance entry is manual.
the first interest rate entry is fixed.
interest = balance * rate
the first payment entry is fixed.
second row:
balance = previous balance + interest - payment
interest = balance (the balance of that row)* interest rate.
repeat for all other rows.
have a counter on the furthest row to count how many "periods" there are, and that's the time you need.
play around with the principal a little.
there is no tidy formula to calculate the time, you need natural logarithms and such things. easier to just do it in excel.
i don't remember the formula off the top of my head.
but you can do this easily in excel:
have these columns:
balance | interest rate | interest | payment |
balance: initially this is how much you took out the loan for, this will decrease monthly, but not by the full amount of payment.
interest: you have to know what your interest rate was quoted as. suppose this is a car loan, they quote you an APR of 15%, that usually means the interest rate per month is 15/12, since you usually pay monthly, and it compounds, it's usually over 15% a year. but suppose this is some weird loan where you pay twice a month, the interest rate term should be 15/24 per period, assuming the interest rate quoted was an annual rate. in short, the interest here is the rate of interest PER PERIOD, whatever you define the period to be. interest rate for your purpose, will be the same every period, but if you wanted, you can have a variable rate there. in fact, you can have some fun by having the rates follow some random distribution, to simulate the effects of a variable rate loan.
interest: dollar interest value.
payment: this is how much you pay in one period, should be fixed for the whole spreadsheet.
calculation works as such:
the first balance entry is manual.
the first interest rate entry is fixed.
interest = balance * rate
the first payment entry is fixed.
second row:
balance = previous balance + interest - payment
interest = balance (the balance of that row)* interest rate.
repeat for all other rows.
have a counter on the furthest row to count how many "periods" there are, and that's the time you need.
play around with the principal a little.
there is no tidy formula to calculate the time, you need natural logarithms and such things. easier to just do it in excel.
thanks for info.
all i just want though is a one line thing in excel where it takes your current debt and how much you pay/month, and given a fixed interest rate will tell you how long it will take you to pay it off. i don't want to build any chart or anything.
all i just want though is a one line thing in excel where it takes your current debt and how much you pay/month, and given a fixed interest rate will tell you how long it will take you to pay it off. i don't want to build any chart or anything.
Cat Fuzz
Qualthwar's Minion. Ph34r!
i've found millions of these on the internet, but what i want is just the equation.Cat Fuzz said:
I think what you seek is
payment = balance * ( rate / (1+rate)^-T ) { T = Time (Period) }
(1+r)^-T = r*b/p
T = - [ log(r*b/p) / log(1+r) ]
The first is the formula I have, I deducted the last from it and posted all so you can verify it.
payment = balance * ( rate / (1+rate)^-T ) { T = Time (Period) }
(1+r)^-T = r*b/p
T = - [ log(r*b/p) / log(1+r) ]
The first is the formula I have, I deducted the last from it and posted all so you can verify it.
Last edited:
thanks a lot! here's what i put into excel, from the equation above, but i don't think it translated correctly to excelZarkazm said:
=LOG(D20*B20/B18)/LOG(1+D20)
d20 = rate = 3.9%
b20 = debt = $10,000
b18 = payment = $350
so..
log( rate * balance / payment) / log( 1 + rate)
log( %3.9 * $10,000 / $350 ) / log( 1 + %3.9 )
and it gives me ~3 to have it all paid off. does that mean 3 years? months? i assume months, and if so it is a few years off. note it is not negated in the excel formula.
am i doing this right?
Last edited:
Sorry about that, I made a mistake copying the formula.
payment = balance * ( rate / 1-(1+rate)^-T )
(1+r)^-T = 1-(r*b/p)
T = - [ log[1-(r*b/p)] / log(1+r) ]
You will probably notice this formula cannot work for your payment, simply because your payment is lower than the interest rate, which means you can never, ever pay off the debt this way, although I suspect a misunderstanding here:
The result is not given in months but in periods, and how long that is is irrelevant as long as both payment and interest rate relate to the same period. So if you have 3,9% annual interest rate, you also need to work with annual payments. If you want to work with monthly payments, you have to calculate the monthly interest rate.
This time I tested my formula against Cat Fuzz' calculator to make sure it works correctly.
payment = balance * ( rate / 1-(1+rate)^-T )
(1+r)^-T = 1-(r*b/p)
T = - [ log[1-(r*b/p)] / log(1+r) ]
You will probably notice this formula cannot work for your payment, simply because your payment is lower than the interest rate, which means you can never, ever pay off the debt this way, although I suspect a misunderstanding here:
The result is not given in months but in periods, and how long that is is irrelevant as long as both payment and interest rate relate to the same period. So if you have 3,9% annual interest rate, you also need to work with annual payments. If you want to work with monthly payments, you have to calculate the monthly interest rate.
This time I tested my formula against Cat Fuzz' calculator to make sure it works correctly.
Last edited:
Zarkazm said:Sorry about that, I made a mistake copying the formula.
payment = balance * ( rate / 1-(1+rate)^-T )
(1+r)^-T = 1-(r*b/p)
T = - [ log[1-(r*b/p)] / log(1+r) ]
You will probably notice this formula cannot work for your payment, simply because your payment is lower than the interest rate, which means you can never, ever pay off the debt this way, although I suspect a misunderstanding here:
The result is not given in months but in periods, and how long that is is irrelevant as long as both payment and interest rate relate to the same period. So if you have 3,9% annual interest rate, you also need to work with annual payments. If you want to work with monthly payments, you have to calculate the monthly interest rate.
This time I tested my formula against Cat Fuzz' calculator to make sure it works correctly.
at first the formula didn't work in excel then i reread your post
Zarkazm said:
once i upped the payments the equation worked, but it doesn't give m the same answer as Cat_Fuz's calculator
=-LOG(1-(D19*B19/B17))/LOG(1+D19)
-log( 1 - ( rate * balance / payment ) ) / log( 1 + rate)
Last edited:
Zarkazm said:
yes, as i had posted, you have to make sure the periods and the payments and the quoted interest rates match up. if your rate is quoted in % per year, but your payment is quoted on a monthly standpoint, you'd better adjust your rate to a monthly rate, and compound by counting the number of PERIODs in your payment schedule.
most people giving you loans or credit cards willl quote you APRs, but quote you MONTHLY payments, and it's generally unwise to try calculating anything using an annual amortization table, i.e. listening ot the loan officer and thinking "oh he said ANNUAL percentage rate, so i can go by this as a yearly payment." because in fact you're paying monthly, and it's compounded monthly, which actually means your interest rate is HIGHER than the APR he quotes you. but he has incentive ot tell you the APR and not the acutal rate (effective annual rate, or EAR) but still tell you to pay on a monthly basis.
Heh. Did you also reread the part Cactus just mentioned once more?StoneViper said:
Assuming you really work with monthly payments, the formula uses the monthly, the calculator the annual interest rate.
Except rounding I got the same result with my formula and the calculator:
monthly interest: 0,00325
My formula: 30.0349 months
Calculator: 31 months
The calculator obviously only gives full months as results. Fair enough.
Cactus:
You mean interest on interest?
cactus. i understand kind of what you mean. i'm also kind of ignorant about loans and interest rate stuff. as far as i knew, "periods" were just virtual months and there were 12 periods in a year, but that it doens't have to start/end when months do.
i don't exactly know what you mean by annual percentage rate. all i know is the rate is 3.9% and i'm penalized every "period". this rate doesn't change no matter how many years it takes to pay off.
i don't exactly know what you mean by annual percentage rate. all i know is the rate is 3.9% and i'm penalized every "period". this rate doesn't change no matter how many years it takes to pay off.
The APR is the monthly interest rate times twelve. There are actually financial contracts that use 1-year periods. However, even if a contract uses 1-month periods, it's not uncommon that people use the term APR nonetheless, although it does not correctly describe the annual interest in this case.
And I don't have practical experience with loans myself, but isn't 3,9% too high for a monthly interest rate? That would equal an APR of ~46% and effectively mean ~58% interest over a year!
That is exactly the problem. You say you don't know what the APR is. However, it's important because MY calculation requires the MONTHLY interest rate, while Cat Fuzz' calculator asks for the APR.StoneViper said:
And I don't have practical experience with loans myself, but isn't 3,9% too high for a monthly interest rate? That would equal an APR of ~46% and effectively mean ~58% interest over a year!
Last edited:
thanks for clearing what "APR" means, wow.
so i guess what i meant in my first post is APR and not interest rate. i just took the "daily periodic rate" on my cc statement and multiplied it by 30 (30 days in the period) which equals 0.1638% and plugged it into your formula, while at the same time putting 1.9% in cat fuz's calculator, and it came out the same.
i'd hate to make you go through all that trouble and work again but do you have handy the equation where you can just put the APR into the forumula?
thanks for all the help in this.
so i guess what i meant in my first post is APR and not interest rate. i just took the "daily periodic rate" on my cc statement and multiplied it by 30 (30 days in the period) which equals 0.1638% and plugged it into your formula, while at the same time putting 1.9% in cat fuz's calculator, and it came out the same.
i'd hate to make you go through all that trouble and work again but do you have handy the equation where you can just put the APR into the forumula?
thanks for all the help in this.
Zark: basically yes.
SV: that's the difference between EAR and APR.
both: example:
suppose you're quoted a 12% APR>
that means interest is 1% a month.
suppose you take a 1000 loan. 0 coupon, meaning you don't pay it off until the very end, all at once, lump sum (this will illustrate the biggest difference between what you're quioted and what you actually pay)
so the first month, your "outstanding balance" is: 1000*1.01 = 1010
second month, your outstanding balance is 1010*1.01 = 1020.1
the other months are as follows:
1030.301
1040.60401
1051.01005
1061.520151
1072.135352
1082.856706
1093.685273
1104.622125
1115.668347
1126.82503
so at the end of 12 months, when you have ot pay it off, you have to pay 1126.82503 dollars. so your original sum was 1000, that means the TOTAL INTEREST you paid for having that money for 12 months is (1126.83 - 1000)/1000 = 12.683
that means your Effective Annual Rate, EAR, is 12.683%, while your quoted APR is 12%.
when you take a home loan for 100,000, and your loan is for 15 years, that's a HUGE difference.
but 12 is lower than 12.683, and telling you, the consumer, "12%" is much more appealing than 12.683%.
that's how they get you.
remember that each month, interest is added to the principal, and the NEW interest amount is calculated from your TOTAL outstanding balance, including the previous interest rate.
that's how credit cards calculate your MINIMUM monthly payment. you usually have to pay 19% or more for a credit card rate. divide that by 12, and multiply by your bill. you'll notice that the minimum payment on your credit card is exactly that amount (so long as it's greater than 10 bucks or whatever your bank does.) because the minimum interests cancels out the interest rate payment, and your next month's bill (assuming you don't charge any more onto the card) is exactly the same as this month.
remember zark went on to talk about diverging payments. i.e. if your payment is smaller than your calculated interest rate for that month, you will NEVER, EVER pay that off, because the bill is growing faster than you're paying. that's why credit card companies have the minimum payment. anything below, pretty much means you're never going to pay it off, and not only that, the bill keeps getting bigger. if you ONLY pay the minium monthly payments, your bill is always the samve amount outstanding. if you pay anything above the minimum, there's a chance you'd pay it off eventually. that's how they do business.
SV: that's the difference between EAR and APR.
both: example:
suppose you're quoted a 12% APR>
that means interest is 1% a month.
suppose you take a 1000 loan. 0 coupon, meaning you don't pay it off until the very end, all at once, lump sum (this will illustrate the biggest difference between what you're quioted and what you actually pay)
so the first month, your "outstanding balance" is: 1000*1.01 = 1010
second month, your outstanding balance is 1010*1.01 = 1020.1
the other months are as follows:
1030.301
1040.60401
1051.01005
1061.520151
1072.135352
1082.856706
1093.685273
1104.622125
1115.668347
1126.82503
so at the end of 12 months, when you have ot pay it off, you have to pay 1126.82503 dollars. so your original sum was 1000, that means the TOTAL INTEREST you paid for having that money for 12 months is (1126.83 - 1000)/1000 = 12.683
that means your Effective Annual Rate, EAR, is 12.683%, while your quoted APR is 12%.
when you take a home loan for 100,000, and your loan is for 15 years, that's a HUGE difference.
but 12 is lower than 12.683, and telling you, the consumer, "12%" is much more appealing than 12.683%.
that's how they get you.
remember that each month, interest is added to the principal, and the NEW interest amount is calculated from your TOTAL outstanding balance, including the previous interest rate.
that's how credit cards calculate your MINIMUM monthly payment. you usually have to pay 19% or more for a credit card rate. divide that by 12, and multiply by your bill. you'll notice that the minimum payment on your credit card is exactly that amount (so long as it's greater than 10 bucks or whatever your bank does.) because the minimum interests cancels out the interest rate payment, and your next month's bill (assuming you don't charge any more onto the card) is exactly the same as this month.
remember zark went on to talk about diverging payments. i.e. if your payment is smaller than your calculated interest rate for that month, you will NEVER, EVER pay that off, because the bill is growing faster than you're paying. that's why credit card companies have the minimum payment. anything below, pretty much means you're never going to pay it off, and not only that, the bill keeps getting bigger. if you ONLY pay the minium monthly payments, your bill is always the samve amount outstanding. if you pay anything above the minimum, there's a chance you'd pay it off eventually. that's how they do business.
i've attached a sample amortization schedule.
plug in the APR
the only variables you need to adjust are the three up top.
the table isn't smart enough to end itself, but use your imagination, when the balance goes into negative, that means your debt's paid off.
plug in the APR
the only variables you need to adjust are the three up top.
the table isn't smart enough to end itself, but use your imagination, when the balance goes into negative, that means your debt's paid off.
wow Cactus, your example 2 posts above makes things so much clearer. i could really make use of the amortization table as well.
i modified zark's equation to work with APR's instead.
thanks a ton!! you answered my questions and more. it's making my debt consolidation much easier.
i modified zark's equation to work with APR's instead.
thanks a ton!! you answered my questions and more. it's making my debt consolidation much easier.