Amortization Schedule for Auto Loan

[Tennisguuy]

I am trying to create a amortization schedule for an auto loan where the monthly payment changes based on the previous payment. I found a template that creates a amortization schedule but the payment is fixed throughout the schedule. My first payment when I first started paying was $360 for 60 months. However after I made the first payment of $360 the next payment due was slightly less that $360. I always paid the $360 every month and each month the next payment was less. I know they applied the extra amount towards the principle. I even created a schedule with an extra payment column but struggling with how do create a schedule with an extra payment column even if I don't make an extra payment but just keep the $360 and have my schedule match the payment stub I get each month so I can see how much interest I paid.

These are the formulas I have in the columns

C1 Loan Amount
C2 # of Months. I would like this to go to 360 even though most auto loans are less than 60 months
C3 Interest Rate
C4 =round(C3/12,7)
C5 PMT(C4,C2-C1,0)

In row 7 I have the following heading

Months(A7) Begining(B7) Payments(C7) Interest(D7) Interest(E7) Ending Balance(F7) Extra Payment(G7)
1 =C1 =C5 =B8*C4 =C8-D8+G8 B8-E8


In cells B8 I have the formula =F8 and in cell C8 I have IF(F8<PMT($C$4,$C$2,-$C$1,0),F8*(1+$C$4),PMT($C$4,$C$2,-$C$1,0)). Then I copied formulas down beginning in row 8 to row 368. I formatted rows A7:F368 with the following customizted format $#,##0.00;$#,##0.00;; so that the schedule will put a blank when the principal has been paid.