Loan Formula discussion from "Ask A Loan Officer" (Thread split by request)

[Averett]

Hey NoSoup, are there any simple formulas out there that I can use to calculate my debt? Basically I want to know how long it will take to pay off my credit card debt using the current balance, set amount of minimum payments and the APR. There are plenty of websites out there that have calculators, but I haven't found one that shows the actual formula.

I'm frustrated.

[Yakk]

Originally posted by Averett
Hey NoSoup, are there any simple formulas out there that I can use to calculate my debt? Basically I want to know how long it will take to pay off my credit card debt using the current balance, set amount of minimum payments and the APR. There are plenty of websites out there that have calculators, but I haven't found one that shows the actual formula.

I'm frustrated.

<- not NoSoup!

Edit: My conclusions about how to do this are in the post starting with "how is it useful" below. Feel free to skip to that post, the rest are less important, and might confuse you. =)

But, after playing around with a bunch of math, and finally google, I found something.

The morgage formula![1]

P = (r*D*(1+r)^m) / ((1+r)^m-1)
P is "payment per period"
r is "interest rate per period"
m is "number of payments"
D is "starting debt"

there is no difference between morgages and other debt really, so this should work.

(a^b is a to the power b. Ie, 2^3 is 2 * 2 * 2, or 2 cubed).

NoSoup, that look rightish? I tried to solve the continuous case using ODE's, but my calculus is very rusty: kept on getting junk.

Footnotes:
[1]: Link is to a PDF, you'll need acrobat reader to look at it. It's just a cheat sheet for some undergrad actuary course I'm guessing.

[NoSoup]

Regarding the above formula, yup, that's correct, providing that you never pay your bill a single day ahead of the due date. If you do, you will be paying less in interest that the amount calculated via the formula. Thanks Yakk for looking that up & typing it out

Some other common formulas that are used in the realm of lending.

Debt-to-Income Ratio

Minimum Monthly Payments/Income = DTI

Generally, the debts used for calculating this are debts that report to your credit bureau (ie Vehicle loans, Credit Cards, School loans) as well as Homeowners Insurance and Property Taxes.

It is best to try and keep this number below 50%, even better if you can keep it below 35%.

Loan-to-Value Ratio

Values of all Liens on Collateral Item/Value of Item= LTV

Generally, when speaking of LTVs, mortgages on a home are involved, although you could certainly use the term when speaking about vehicle or other collateralized loans. The lower the LTV, the more equity that you have in that specific collateral. Especially when it comes to mortgages, the lower the LTV, the better potential financing you may qualify for. A Low LTV is especially important when purchasing/refinancing non-owner occupied property, as it is usually more difficult to obtain financing for those types of properties.

[Averett]

My head just exploded. I'll have to check this out when I get home tonight. Thanks guys

[Yakk]

The one without soup expressed:
Regarding the above formula, yup, that's correct, providing that you never pay your bill a single day ahead of the due date. If you do, you will be paying less in interest that the amount calculated via the formula. Thanks Yakk for looking that up & typing it out

Do you happen to know the "continuous" one?

Ie, instead of "interest per period" you have "continuous interest" or "effective annual interest" or "interest compounded instintaniously", and instead of having payments per period you have "payment rate".

It should be easier to play with (more continuous, less discreet), so easier to invert and calculate how payment time changes based on how payment rate changes...

I could do the ODE work. Probably wouild be a good refresher!

[NoSoup]

I do not, my apologies

I'll take a look around and see if I can find it here at work, but no guarentees....

[Yakk]

Me, Maple and a friend of mine got it.

Time = ln((payment_rate+goal*interest_rate)/(payment_rate+start*interest_rate))/interest_rate

where
"goal" is your desired goal (saving goal or pay-off-debt goal).
"interest_rate" is your interest rate per period of time (compounded instantly, which is wierd[1]).
"payment_rate" is your payment rate per period of time.[2]
"start" is the start state (how much money you have saved up, or how deep your debt is. Debt is negative.)
"Time" is the number of periods of time it will take.

By plugging numbers into this, you will get ballpark values for real-life loan repayment times and the like. It won't be accurate, because in the real world you only make payments every month, and interest doesn't work quite the same.

Footnotes:
[1] if you take 12% yearly interest, and you compound it every month, you get 1% per month. This works out to 12.68% interest over a year. If you compound every instant, you end up with something like 13% interest.

[2] Ie, if your period of time is years, your interest rate and the units the answer is in will also be in years.

A prettier version:
Define X to be:
X =
payment_rate+goal*interest_rate
-------------------------------------------
payment_rate+start*interest_rate

Then the amount of time it takes to pay off your loan is:
ln(X)/interest_rate

Example:
you have 10,000 in debt at 9% interest/year. You are paying 4800/year to pay off the debt.

Your goal is 0$ of debt. You start at -10000.

X = (4800 + 0*0.09)/(4800 + -10000*0.09)
X = 1.23

So, it will take about:
ln(1.23)/0.09
or 2.3 years to pay off your debt.

Testing against the morgage equation from a few posts ago. . .
28 monthly payments, about .8% monthly interest, initial debt of 10000.

Payment per month =
0.008*10,000*(1.008)^28)/((1.008)^28-1)
= $400.06

sweetness, it worked!

Edit:
If you care, the rate of change of the repayment time, with respect to the payment rate, is:
(start*interest_rate - goal*interest_rate)/interest_rate
*
1
--------------------------------------------------------
(payment_rate+goal*interest_rate)(payment_rate+start*interest_rate)

but I doubt you care much, and it may be wrong.
Edit2:

Best version of the formula yet!

First concept: surplus. This is
payment_rate - debt*interest_rate
basically, how much you are reducing the debt by.

So, if your interest payments are 100$, and you are putting 400$ down on the debt, your surplus is 300$.

If your interest payments are 0$ and you are putting 400$ down on the debt, your surplus is 400$.

then, the amount of time it takes to pay off the debt is:
ln(desired_surplus)-ln(current_surplus)
--------------------------------------------------
interest_rate

Note you have to calculate the interest rate per payment period, not the annual interest rate.

To pay off the debt, you want the desired surplus to be the entire payment.

Example:
Your interest payments are 100$. You are paying 200$ a month to pay off the debt. Interest rates are 1%/month.

Time to pay off it:
ln(200)-ln(100)
------------------
0.01

which is 69.3 months, or about 5 years 10 months.

[NoSoup]

Lol, I found the formula here at work, but was a little hesitant to type it all out. I see that you beat me to it, and I want to thank you for doing it for me

[Yakk]

How is it Useful?

So, what does all this mean, and how is it useful?

TimeToPayOffDebt =
ln(desired_surplus)-ln(current_surplus)
--------------------------------------------------
interest_rate

where
surplus = payment_rate - debt*interest_rate


payment =
(r*D*(1+r)^m)
-------------
((1+r)^m)-1

payment is "payment per period of time"
r is "interest rate per period of time"
m is "number of payments"
D is "starting debt"
^ means "to the power of". So, (1+r)^m means "(1+r) to the power m".

In general, this is how you should use the above.

The first rule is, interest isn't a percentage: If you have 12% interest, your interest is 0.12. If you have 20% interest, your interest is 0.20.

Next, figure out your monthly interest rate. There are two ways you can do this.

Accurate way:
MonthlyInterest = (1+Annual_Interest)^(1/12)-1

Easy way:
MonthlyInterest = Annual_Interest / 12

The above is easy to calculate if you turn the windows calculator into "scientific" mode. =)

Example: 9% annual interest.
Monthly_Interest = (1+0.09)^(1/12) - 1
= 1.09^(1/12) - 1
= 1.007207 - 1
= 0.007207

Work out your monthly interest payments.
Monthly_Interest_Payments = Monthly_Interest * Debt

Suppose your debt was 9000$. Then
Monthly_Interest_Payments = 0.007207 * 9000
Monthly_Interest_Payments = 64.87

The next thing you do is decide how much you can afford to pay off your debt. Lets say 200$ / month.

Now, what is your monthly surplus? Take the amount you are paying off and subtract your Monthly_Interest_Payments.

Initial_Monthly_Surplus = 200 - 64.87
Initial_Monthly_Surplus = 135.13$

Eventually, you want to be paying no interest. At this point, your Interest_Payments will be 0.

End_Monthly_Surplus = 200 - 0
End_Monthly_Surplus = 200

Now, pull out the 'how long to pay off debt' equation:
TimeToPayOffDebt =
ln(desired_surplus)-ln(current_surplus)
--------------------------------------------------
interest_rate

"ln" is a function on most calculators. It is also known as "the natural logarithm".

Plug in the numbers
TimeToPayOffDebt =
ln ( End_Monthly_Surplus ) - ln (Initial_Monthly_Surplus)
---------------------------------------------------------
Monthly_Interest_Rate

(notice everything is monthly, if they are inconsistent things break)

TimeToPayOffDebt =
ln( 200 ) - ln( 135.13 )
------------------------
0.007207
=
0.39208
------------------------
0.007207
=
54.4

So, this says it will take about 54.4 months to pay off the debt at this rate.

After you have an answer, use the 2nd equation to verify it. This is important: not only did you do alot of math, the first equation isn't prerfectly accurate for the real world either. Round the number of months up.

payment =
r*D*(1+r)^m
-------------
((1+r)^m)-1
=
Monthly_Interest* Debt * (1+Monthly_Interest)^NumberOfMonths
------------------------------------------------------------
((1+Monthly_Interest)^NumberOfMonths) - 1
=
0.007207 * 9000 * (1+0.007207)^55
---------------------------------
((1+0.007207)^55) - 1
=
64.863 * 1.4843
---------------
1.4843 - 1
=
96.276
---------------
0.4843
=
198.80

which is really close to 200$, close enough that we can be confident we didn't make a serious mistake. =)

[NoSoup]

To Clarify, This thread was split from the Original "Ask The Loan Officer" Thread simply because it doesn't really follow the simple, easy to use format that we so far have been able to stick with, as it involves rather complex mathematical equations, and probably isn't the typical question asked on the previously mentioned thread.

[Averett]

I'm going to print this out and work out my figures. I'll let you guys know what I come up with. Thanks for the help!!


Yikes... My one big card which has a $6,000 balance on it at 15.99% will take me 110 months to pay off if I'm paying $100 a month on it. Sick

[Yakk]

With multiple cards, you want to focus on one at a time.

Take your "debt service per month" budget.

Cut off "carrying" amounts for most of your cards. Ie, pay the interest every month on every card (or, maybe less if your card is nowhere near it's limit?) Simularly, if you have a negotiated repayment on a debt, work this in here.

After this, you better have money left over, or you are in trouble.

Now, take the highest rate card. Take all the left over "debt service per month" money, and do the math on this card. That will tell you how long you have to pay it off.

Then, work out what your debt on all your cards will be at the point where you pay off your first card. Repeat the above, paying off another card.

It gets more complicated when your rates are going to change: for example, you have a card that will go up in rate in six months. What you can do here is solve for the first 6 months, then figure out your debt on all cards 6 months from now, and then solve for after 6 months, if that makes sense.

Damn, that's alot of work. =/ I'd try it on a spreadsheet.

Do you know how to figure out the answer to:
You have a credit card at X% interest and D debt. You put P dollars a month into it. After N months, how much debt do you have on the card?

[Averett]

That makes sense Yakk. I'm also done with my car payment in about 11 months. So I'll have that money to put towards my cards.

I used these formulas last night and I understand them, so I should be able to work this stuff out.

[Cynthetiq]

if people would just think before they bought... they'd see that even if they didn't have to put money down they'd see just how long it's going to take to pay that "$4.00" extra a month...

thanks for the formula

[Averett]

Yup, sometimes we've got to learn the hard way

These formulas really are a great help, I was trying to figure one out with a friend on Saturday and we were doing something wrong, I'll have to show this to him.

[Yakk]

So, a question for NoSoup.

Do you have an equation for how much debt you have left after paying B dollars each month for M months on a debt of D dollars with (monthly) interest rate R?

If not, I'll work it out, but feeling lazy!

[Averett]

I'd like to see that formula too

I'd work it out, but it'd be wrong

[Yakk]

So, people are slow at gathering for lunch. Lets see what I can work out...

D(n) is "debt after period n"

Your debt at period n is simply:
D(n) = D(n-1) + D(n-1)*R - P
ie, your old debt, plus the interest, minus your payment P (R is the monthly interest rate)

Playing around:
D(n+1) = D(n) * (1+R) - P
D(n) = D(n-1) * (1+R) - P

D(n+1) = (1+R) * (D(n-1)*(1+R) - P) - P
D(n+1) = D(n-1)*(1+R)^2 - (P * (1+R) + P)

So, going back 2 months, we start seeing a pattern. I'll bet it ends up like:

D(n) = D(n-k)*(1+R)^k - P * sum{j=0 to k-1}(1+R)^j

which, fortunetally, can be simplified[1]:

D(n) = D(n-k)*(1+R)^k - P*(1-(1+R)^k)/(1-(1+R))

set k = n
D(n) = D(0)*(1+R)^n + [1/R - (1+R)^n/R] * P

D(n) = D(0)*(1+R)^n - ((1+R)^n - 1) * P/R
which is what I was looking for.

I have no idea if that is right or not: NoSoup? Gonna go to lunch, might poke at it after, see if it behaves reasonably.

Footnote: (for those who want to know the magic trick used in that step)
[1] 1+x+x^2+x^3+... (the infinite series) is often the same as (1/(1-x)). So, 1+x+x^2+...+x^(k-1) (a finite series) works out to be (1+x+x^2+... ) - x^k*(1+x+x^2+...) (all the terms at and beyond x^k "cancel").

Which is
1/(1-x) - x^k / (1-x), or (1-x^k)/(1-x)
or, in other words:
sum{j=0 to k-1}(x^j) = (1-x^k)/(1-x)

In our case, x = (1+R). So, we get:
(1-(1+R)^k)/(1-(1+R))
= -(1-(1+R)^k)/(R)

[NoSoup]

Hmm... I do not have that formula on hand at the moment, I can check around work, but it looks like you're already on a roll, lol.

[Yakk]

A simple varient, if you know your payments, rate, etc, and want to know what happens to your debt as months pass:
D(n) = P/R - (P/R - D(0))*(1+R)^n
Fill in everything except for n.

So, for example, you are paying 300$ a month towards a 10,000$ debt with 1% monthly interest.

So,
D(0) = 10000
P = 300
R = 0.01

D(n) = 300/0.01 - (300/0.01 - 10000) * (1.01)^n
D(n) = 30000 - 20000 * (1.01)^n

D(6 months) = 8769.60
D(12 months) = 7463.50
D(24 months) = 4605.31
D(36 months) = 1384.62
D(40 months) = 222.73
D(41 months) = -75.05


If you know how long, but don't know how much you are paying, fill in everything except for P into:
D(n) = D(0)*(1+R)^n - P * ((1+R)^n - 1)/R

So, 10,000$ debt, 0.01% interest, over 6 months.
D(6 months) = 10000 * (1.01)^6 - P * (1.01^6-1)/0.01
= 10615.20 - P * 6.15

If P = 100, we end up with 10,000$. As expected: we only payed interst.

P = 200 means D(6) = 9385
P = 300 means D(6) = 8770
P = 400 means D(6) = 8154
P = 1000 means D(6) = 4460

Looking at the equation:
D(n) = D(0)*(1+R)^n - P * ((1+R)^n - 1)/R

This part: (1+R)^n - 1)/R is the "multiplier factor". That is how many dollars of debt you eat away when you put 1$/month towards your debt. In the above example, it was 6.15.

If you have 1% monthly interest, and you pay regularly for a year, every dollar reduces your debt at the end by:
12.68$

Do that for two years, every dollar/month reduces your debt by almost 27$. 3 free dollars! ;-)

Ok, I'm pretty confident the formulas work!

[Averett]

Nevermind, figured it out

But maybe others can use the math here