Car Loan Calculator in Excel Sheet (with Easy Steps)

Are you going to buy a car with a bank loan? And worried about the loan-related calculation? Then you have come to the right place. Hope this article will be a quick guide to making a car loan calculator in an Excel sheet.

The above image is an overview of a car loan calculator for 6-month tenure.

What Is Car Loan EMI?

EMI means Equated Monthly Installment.
It involves repayment of the main amount and payment of the interest on the unpaid amount of your loan.
A longer loan period will help in decreasing the EMI but will increase the interest amount.
Always choose a higher car loan EMI to decrease your interest amount and period.

Things to Consider about Loan Interest Rate

First, compare the interest rates from different banks and other financial institutions before taking a loan.
Understand the difference between fixed and floating interest rates.
Opt for floating interest rates to get the benefits of decreasing interest rates.
The interest amount in every EMI will depend on this interest rate.

Uses of Car Loan Calculator in Excel Sheet

The car loan calculator will help you to know your monthly EMI.
You will be able to know how much interest you’ll pay.
If you prepaid some of the principal amounts then you can find the decrease in principal amount.
It will help you to manage your other plans according to your EMIs and prepayments.

Car Loan Calculator in Excel Sheet: Easy Steps

We’ll use the following dataset to make a car loan calculator in Excel.

Now we’ll calculate the 6 installments in a table as the loan is taken for 6 months.

For the first month, you haven’t paid any installment so your principal will remain the same. So, in cell B9 type:

=F4

And hit the Enter button.

Now we’ll calculate the EMI using the PMT and ABS functions. The PMT function will return a negative result because it represents an outgoing payment. That’s why we used the ABS function to make it positive.

Write the following formula in cell D9.

=ABS(PMT($F$5/12,$F$6-C9,B9))

By clicking the Enter button you will get the first installment.

Now we’ll calculate the interest for the first installment. For that, we’ll use the formula-

Monthly Interest = Interest Rate/12 ✕ Amount

In cell F9 type the following formula and press the Enter button.

=$F$5/12*B9

After finding the interest, we can calculate the principal for the first installment. It’s simple, just subtract the interest from the corresponding EMI.

So we’ll use the following formula in cell E9.

=D9-F9

Hit the Enter button to get the output.

For the second installment, the remaining principal will change.

To calculate it use the following formula in cell B10.

=B9-E9

Press the Enter button.

Drag the Fill Handle icon to copy the formula for the other cells.

Also apply the Fill Handle tool for the EMI, Principal, and Interest columns.

Then you will get the total data for the 6 installments like the following image below.

Now let’s calculate the total interest you will have to pay using the SUM function.

For that insert the following formula in cell F16.

=SUM(F9:F14)

Hit the Enter button to get the output.

Finally, to find the total amount you will have to add the total interest and principal using the following formula.

=F4+F16

Press the Enter button to finish.

Things to Know About Car Loan EMI

In EMI there are 2 parts: Principal amount and Interest amount.
The Interest amount will be high at first during your car loan period.
The Principal amount will be less at first during your car loan period.
You should make a big amount of prepayment of your principal amount to reduce the maximum interest amount.
An increase in the car loan period will increase the interest amount you’ll have to pay throughout your home loan period.

Tips to Save Car Loan Interest

If you pre-pay most of your principal amount then you can easily save the car loan interest amount.
The car loan interest amount is calculated based on the unpaid principal amount remaining. So to decrease the interest, decrease your unpaid principal amount.
The quicker you reduce your unpaid principal amount initially of the loan period, the more interest you’ll save.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Car Loan Calculator.xlsx

Conclusion

I hope the procedures described above will be good enough to make a car loan calculator in an Excel worksheet. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Loan Calculator | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!