Calculating your remaining balance and other things...

[Sodium Duck]

I'm trying to build a spreadsheet to keep track of all my money.

One big part is my car payments. I'm having trouble figuring out how this gets calculated. I just want to be able to put in whatever payment I make that month, and have my remaining balance show.

What's the trick? I mean to me, if my balance was $100, and paid $50, then my remaining balance should be $50. But of course it can't be that simple. So I have to figure out what my daily interest rate is, I think - I'm not sure?

Financed: $12,530.33
APR: 8.44%
Monthly payment varies from $256.72 (standard)-$270ish, depending on how frugal I'm feeling

Bah, I just don't get this interest thing. Math gods, please shine upon me! Show unto me the ways of your gilded calculators!

[yaris-?]

I'm no math god thank goodness there are spreadsheets for that

http://spreadsheets.google.com/ccc?key=pYKaaQjbe6zE1-wyu5d4OIw

[tetzyamis]

Let's say you owe $12,530.33 now.
You are paying $256.72 this month.
$12,530.33 * 8.44% = $1,057.56 is your annual interest.
This month = one month = 1/12 year.
$1057.56 / 12 = $88.13 is the interest you pay this month.

Out of $256.72,
$88.13 goes to the bank
$168.59 gets paid off.
You will have $12,361.74 more to go.

[Sodium Duck]

Yes! That's exactly what I was looking for.

Thank you master of the numbers!

[Sodium Duck]

Oh, and one more question:

When the new year starts, is the APR multiplied by the remaining balance starting the new year?

Say I start this January owing $10,000. Would that be $10,000*8.44% = $844 divided up by 12 months? Or is it still based off my original financed amount.

[jkuchta]

I don't think the above formula is correct as it doesen't take into account the fact that the interest owed (and therefore included in your monthly payment) is calculated more than once a year. As you make payments, you reduce the principle amount that you owe. The interest you pay is based on the principle that you owe. As you make payments, the amount of that payment that gets applied to interest goes down

i.e. a lot of the money you pay in your first few payments goes to interest, whereas the bulk of the money you pay towards the end of your loan term goes to pay off principle, as the principle is small, so the accrued interest is small.

If I were any good at math, I'd figure out the formula.

[Sodium Duck]

Bah, I'll just make my brother explain this to me. Damn banks... lol

[songa]

sodium, he is saying exactly what you were saying here:

"When the new year starts, is the APR multiplied by the remaining balance starting the new year?

Say I start this January owing $10,000. Would that be $10,000*8.44% = $844 divided up by 12 months? Or is it still based off my original financed amount."

only, instead of teh interest changing every YEAR as you said, it will change after every payment. for example, after you make your FIRST payment, the interest of your second payment will be determined by your interest rate of 8.44% multiplied by (your original financed amount - the amount of your first payment).

FOR EXAMPLE: if you owe 100$ w/an interest rate of 1%, and you make a FIRST payment of 10$, then you have 90$ left to owe. of this 10$ taht you paid, 0.01 * 100 = $1 goes to interst, 9$ goes to the actual car payment. for the next payment, these numbers will be different!

to calculate the amount of interest you will be paying for your SECOND payment, you take your current balance which is 90$, multiply by 1%, or 0.01 * 90 = 0.90 cents goes to interest, and $9.10 goes to actual car payment.

And so forth...

so as you can see, interest portion of your payment go down every payment, because your principle is small. therefore a larger percentage of your 10$ payments are going towards the principle balance with the more payments you make. in our case, first payment had 9$, the second $9.10, etc.

[Sodium Duck]

Wow... This is going to be hard to make a formula for in Excel... lol

[tetzyamis]

It's re-calculated every month.
That's how you pay less interest if you pay more.

[tetzyamis]

I don't think the above formula is correct as it doesen't take into account the fact that the interest owed (and therefore included in your monthly payment) is calculated more than once a year. As you make payments, you reduce the principle amount that you owe. The interest you pay is based on the principle that you owe. As you make payments, the amount of that payment that gets applied to interest goes down

i.e. a lot of the money you pay in your first few payments goes to interest, whereas the bulk of the money you pay towards the end of your loan term goes to pay off principle, as the principle is small, so the accrued interest is small.

If I were any good at math, I'd figure out the formula.

Below is how it will be monthly for 5 years under my calculation using the amount OP posted.
It's only $6.08 off in 60 months, so it's pretty accurate, so I don't think it's wrong.

Month Amount Owed Interest Paid for that month
Dec-08 12,530.33 173.55
Jan-09 12,356.78 174.72
Feb-09 12,182.06 175.90
Mar-09 12,006.16 177.10
Apr-09 11,829.06 178.30
May-09 11,650.77 179.50
Jun-09 11,471.26 180.72
Jul-09 11,290.54 181.94
Aug-09 11,108.60 183.18
Sep-09 10,925.42 184.42
Oct-09 10,741.00 185.67
Nov-09 10,555.33 186.93
Dec-09 10,368.41 188.19
Jan-10 10,180.22 189.47
Feb-10 9,990.75 190.75
Mar-10 9,800.00 192.04
Apr-10 9,607.95 193.34
May-10 9,414.61 194.65
Jun-10 9,219.95 195.97
Jul-10 9,023.98 197.30
Aug-10 8,826.68 198.64
Sep-10 8,628.04 199.98
Oct-10 8,428.06 201.34
Nov-10 8,226.72 202.70
Dec-10 8,024.02 204.08
Jan-11 7,819.94 205.46
Feb-11 7,614.48 206.85
Mar-11 7,407.63 208.25
Apr-11 7,199.38 209.66
May-11 6,989.72 211.08
Jun-11 6,778.64 212.51
Jul-11 6,566.12 213.95
Aug-11 6,352.17 215.40
Sep-11 6,136.77 216.86
Oct-11 5,919.90 218.33
Nov-11 5,701.57 219.81
Dec-11 5,481.76 221.30
Jan-12 5,260.46 222.80
Feb-12 5,037.66 224.31
Mar-12 4,813.36 225.83
Apr-12 4,587.53 227.36
May-12 4,360.17 228.90
Jun-12 4,131.27 230.45
Jul-12 3,900.82 232.01
Aug-12 3,668.81 233.58
Sep-12 3,435.23 235.17
Oct-12 3,200.06 236.76
Nov-12 2,963.30 238.36
Dec-12 2,724.94 239.98
Jan-13 2,484.96 241.60
Feb-13 2,243.36 243.24
Mar-13 2,000.12 244.89
Apr-13 1,755.23 246.55
May-13 1,508.68 248.22
Jun-13 1,260.47 249.90
Jul-13 1,010.57 251.59
Aug-13 758.98 253.30
Sep-13 505.68 255.01
Oct-13 250.67 256.74
Nov-13 -6.08 258.48

[tetzyamis]

I don't know if this attachment to a thread works (first time trying).
But it's the Excel spreadsheet with that calculation.
You could add additional payment in "Add" column to play around and see how much extra payment can reduce the amount you owe.

Edit: I guess attachment doesn't work... I can email you the spreadsheet if you PM me your email address.