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 months tenure.

**Table of Contents**hide

**Download Practice Workbook**

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

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

**How to Make a Car Loan Calculator in Excel Sheet**

To make a car loan calculator in Excel weâ€™ll use the following data.

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 type-

`=F4`

And hit the **Enter **button.

Now weâ€™ll calculate the **EMI **using **the PMT function** and **the ABS function**. 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**

So in **Cell F9 **type the following formula-

`=$F$5/12*B9`

Then just press the **Enter **button.

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.

After that, for the second installment, the remaining principal will be changed.

To calculate it use the following formula in **Cell B10**â€“

`=B9-E9`

And press the **Enter **button.

Later, drag the **Fill Handle** icon to copy the formula for the other cells.

And 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 pay, just add the total interest and principal using the following formula-

`=F4+F16`

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

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