# Car Loan Calculator in Excel Sheet (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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

• 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.

## 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.

Read More: EIDL Loan Amortization Schedule Excel

## 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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF