In this post we are going to explain you, in an easy way, how to create a useful application in Excel to calculate your own car loan payments and an amortization schedule.
But, what is a car loan calculator? This is a basic financial configuration that allows you to know how much money you have to pay monthly with a specific loan amount and interest rate. (See: Bankrate)
At last, we are going to make an amortization schedule that shows the interest rate and balance for each month of the duration of the loan.
In the following picture you can see the table and use that picture to understand the exercise.
Convert the Interest Rate
The first thing that we are going to do is convert the interest rate. The rate in most of the cases is given in % per year, but you must make the payments monthly, for that reason the rate should be converted to monthly rate. To do that we are going to put in the cell D7 the following excel formula: =NOMINAL(D6,12)/12
This excel formula calculates the Nominal interest rate, taking in count the rate given (Cell D6) and this value is divided by the number of periods in a year (is 12 because a year has 12 months).
Calculate the monthly payment
The second part is to calculate the monthly payment. To do that we are going to use the following formula in the cell D8: =PMT(D7,D5,D4)*-1
This excel function calculates the monthly payment taking the loan amount, the loan term and the converted interest rate. As payment is a negative value so we make a multiplication by -1.
Now you can know, how much you should pay monthly for a specific loan. You can change, the loan amount, the loan term and the interest rate per year and you will know the amount of cash you have to pay each month.
Create the Amortization Schedule
The last part is to create the amortization table. It is used to see in each month how much payment is for interest, for principal and the balance of the loan. Let’s see how we construct it.
You must create an excel table with the following fields: Month, Initial Balance, Payment, Interest Paid, Principal Paid, Final Balance. In the first column the months, it is a list of numbers from 1 to the number of months of the loan term.
The next column is the Initial Balance for the first row, it is the Loan amount (cell D4), for the following rows is the Final Balance of the previous row. For the cell C14 the formula is =G13. You can select the C14 cell and drag to the last row.
The next field is the Payment. It’s the payment previously calculated. For the cell D13 the formula is =$D$8 (the $ is used to keep fixed the cell D8 when you drag the cell value D13). You select the D13 cell and drag it to the last row.
At last, the Interest Paid is the amount of interest that contains the payment. For the cell E13 the formula is: =C13*$D$7 It is the amount of the initial balance multiplied by the converted interest rate. After that you can select the E13 cell and drag it to the last row.
The Principal Paid is the amount of principal that contains the payment. For the cell F13 the formula is: =D13-E13 It is the subtraction between the Payment and the Interest Paid. You select the F13 cell and drag it to the last row.
The last column is the Final Balance, it is the loan amount that remains to pay after the present payment. For the cell G13 the formula is: =C13-F13 It is the subtraction between the Initial Balance and the Principal Paid. You select the G13 cell and drag to the last row.
After all, you can proof that all the calculation are good if the Final Balance in the last month is cero (0).
You can see that the interest rate is decreasing as Principal paid increases.
This excel table is really helpful to know the loan amount that remains in a specific month. If you want to cancel or finish the payments of a loan, you see how much is the final balance in the month and that is the amount that you have to pay.
Now you have your Car Loan Calculator with an Amortization Schedule.
If you have any questions, suggestions or simple comments, please don’t hesitate to do it below that post.