To create a car amortization schedule, you first have to find the amount of money that the borrower has to pay every installment. As long as you know the loan amount, interest rate, repayment frequency and loan period, you can calculate the monthly installment amount using the formula: [P (r / 12)] / [1 - (1 + r / 12)-m]. If the borrower repays the loan every week, the formula becomes: [P (r / 52)] / [1 - (1 + r / 52)-w]. In these formulas, "P" stands for the loan amount or principal amount, "r" stands for interest rate, "m" stands for the loan period in months and "w" stands for the loan period in weeks.
For example, assume that the borrower makes a $1,000 US Dollar (USD) down payment for a $15,000 USD car when the annual interest rate is 7 percent and he or she has to make monthly repayments for three years. Using the formula to calculate the monthly installment amount, you will get: [$14,000 USD (0.07 / 12)] / [1 - (1 + 0.07 / 12)-36] = $432.28 USD. Note that the loan amount is the car price minus the down payment and that the interest rate is expressed as a decimal.
Next, you have to calculate the portion of each installment that goes into interest payment. The formula for this calculation is: P (r / 12) for monthly installments or: P (r / 52) for weekly installments. Using the same example as before, you can calculate the interest portion of the first installment in this way: $14,000 USD (0.07 /12) = $81.67 USD.
Using the results from the previous calculations, you can find the portion of the installment that goes into the loan principal. You simply deduce the interest payment from the total installment payment. In the example, the calculation is as follows: $432.28 USD - $81.67 USD = $350.61.
After paying the first installment, the borrower's loan amount decreases. You can find the new car loan balance by deducting the principal repayment portion of the installment from the previous balance. Using the results from the previous calculations, you can find the new balance after the first installment: $14,000 USD - $350.61 USD = $13,649.37 USD.
To make the car amortization schedule, you have to make a table with five columns and (m + 2) or (w + 2) rows. A car amortization schedule of the example in this article would have five columns and 38 rows. The first row contains the titles; the first column would say "Installment Number," the second column "Payment," the third column "Interest," the fourth column "Principal" and the last column "Balance."
Enter the initial balance in the first cell under the title "Balance," then enter the numbers 1 to m or w from the second cell onward in the "Installment Number" column and enter the same installment amount in all corresponding cells under the title "Payment." Enter the figures for the first installment as you have previously calculated.
To fill the other cells in the car amortization schedule, you only have to perform calculations similar to the ones you have previously performed, only using the new balance as the principal each time. For example, the second installment's interest portion is $79.62 USD ($13,649.39 USD (0.07 / 12)), while the principal repayment portion is $352.66 USD ($432.28 USD - $79.62 USD), making the new balance $13,296.73 USD ($13,649.39 USD - $352.66 USD). You can repeat the same calculations to fill the whole table manually or plug in the numbers and the formulas into a spreadsheet software.