Calculating finance charge on auto loans

I've been trying to figure out how to calculate finance charges on auto loans for over a month, and I still have yet to figure out how to make a formula work. Has anyone ever done this before? Auto loans calculate interest on a daily basis, so days to first payment come into account. I cannot seem to get a functioning formula. Any help would be really appreciated, or even a place to start. Here is some data I have to work with to build a formula:

contract date: 05/03/11
first pmt date: 06/17/11
Term: 60 months
Payment: 472.01
Interest Rate: 17.99
Amount Financed: 18453.57

And finally, I know that the finance charge for this loan is $9867.03. What I'm trying to do is calculate participation, which means I need to calculate what the finance charge would be at 15.99%, and so forth. All the information is there, I just can't seem to figure it out and I'm pretty frustrated at it. Now, I know that the actual interest rate is closer to 18.02% from my company loan calculator, but I can't get it perfect.

Originally Posted by day92

Are you looking for something like this?

Well, that is the basic calcuation, yes. However I'm trying to do a more complex and exact calculation. When dealers contract people, they had something called days to first payment, usually it's between 30 and 45 days. Because of this, those simple interest calculators (like the one you posted) are calculating the term, but not the days to first payment, which cause all of the numbers to be incorrect. What I'm trying to do is get a calculation down to the penny. The problem I'm having is how to write the formula. I need to calculate the days to first payment, which is the contract date - first payment date, then I need to add all the additional payments in the term. However, if I do a simple day calculation, it causes problems because the payments always remain on the same day, when I try doing a daily calculation excel always seems to calculate the final payment 6-10 days after the actual final payment date. I need to reverse the data on a contract to find out how I need to make a formula. Here is another contract for reference:

Contract Date: 4/29/11
First Payment: 6/13/11
Term: 54mo
Interest rate: 16.99%
Amount Financed: $16,300.72
Payments: $436.93
Finance Charge: $7,293.50
Total of Payments: $23,594.22

All of the data is here to form the calculation but I can't seem to figure it out and it's really frustrating! :X I know I will not get it perfect, because the dealership's systems do tend to have a very slight variance in the actual interest rate vs. the calculated rate, but regulation Z compliance allows some variance. I can't even seem to get within 500 bucks of the actual finance charge. I've just about given up out of frustration, I might have tried 100 different formulas. If anyone has the time or the interest to help, I would really appreciate it.

I think you need to find out how they calculate the daily interest rate from the annual rate.

A daily rate of 0.046209% liquidates the loan at 54 months, with interest computed daily starting from the contract date, and payments made on the same day of the month as the first payment date, but the interest totals $7287.29, not $7293.50

How are you getting those figures? The variance there is most likely the APR, it will vary slightly, and that's well within a thousandths of apr over the term of the loan. I could figure out the rest working backwards if I could see how you got that daily rate.

It's a larger difference than that.

Please Login or Register  to view this content.

F3 is loan amount, G3 is contract date, G4 is first payment date.

F4 and down: =F3 - $C$4 + H4

H4 and down: =((1+$C$3)^(G4-G3) - 1) * F3

G5 and down: =EDATE(G4,1)

Yeah, that's the problem I've run into it seems. The calculation is correct per state/federal requirements and guidelines, I just can't figure out the formula to get it.