Car Loan EMI Calculation Formula in Excel

To calculate a car loan EMI in Excel, use `=PMT(annual_rate/12, years*12, -loan_amount)`, where the annual rate is entered as a percentage such as 8%. This calculator estimates the monthly payment and total interest from the loan principal, annual APR, and term; it does not include lender fees or vehicle costs.

Formula

The standard fixed-payment formula is:

EMI = P × r × (1 + r)n / ((1 + r)n − 1)

It is equivalent to P × r / (1 − (1 + r)−n), the expression used by the calculator. Here:

• EMI is the monthly payment, in your currency.
• P is the amount borrowed, in your currency.
• r is the monthly interest rate as a decimal: annual APR divided by 12 and then by 100. For an APR entered as a percentage, r = APR / 1200.
• n is the number of monthly payments: loan term in years multiplied by 12.

Total interest in this estimate is monthly payment × number of payments − principal. The monthly payment is rounded to two decimal places before the total interest is calculated, as payments are normally stated to the smallest currency unit. In Excel, enter the loan amount, APR, and term in separate cells. If they are in A2, B2, and C2, respectively, use =PMT(B2/12,C2*12,-A2) when B2 contains an Excel percentage such as 8%. The negative sign makes the displayed payment positive under Excel’s cash-flow sign convention.

Worked Example

Using the calculator’s defaults—principal 20,000 in your currency, annual APR 8%, and term 5 years—first convert the inputs to monthly units:

• Monthly rate: r = 8 / 1200 = 0.0066666667, or about 0.6667% per month.
• Number of payments: n = 5 × 12 = 60.
• Substitute: EMI = 20000 × 0.0066666667 / (1 − (1 + 0.0066666667)^−60).
• Monthly payment, rounded to two decimals: 405.53 in your currency.
• Total paid using the rounded payment: 405.53 × 60 = 24,331.80.
• Estimated total interest: 24,331.80 − 20,000 = 4,331.80.

Small differences can arise if a spreadsheet retains extra decimal places in the payment when computing totals. To match this calculator, calculate the payment to cents first, then multiply that rounded value by the number of payments.

Assumptions

• The rate is fixed throughout the term.
• Payments are equal, made monthly, and occur at the end of each monthly period.
• Interest is modelled monthly at APR divided by 12; the APR is treated as a nominal annual rate for this calculation.
• The estimate excludes fees, taxes, insurance, down payment, trade-in value, add-ons, and any other amount not entered as principal. It also does not model changing payment dates or a final adjusted payment.

Using the Result in an Excel Sheet

Excel’s PMT function calculates a level payment. If the APR cell contains 8%, Excel stores it as 0.08, so divide it by 12—not by 1,200. If instead you type the number 8 to represent 8%, use =PMT(B2/1200,C2*12,-A2). Mixing those two input conventions is a frequent source of incorrect results.

For a total-interest estimate in a separate cell, use the rounded payment cell multiplied by the number of months, then subtract the principal. For example, if D2 holds the payment and A2 the principal, a five-year loan’s total interest can be calculated as =ROUND(D2,2)*C2*12-A2. Make sure the payment cell itself is rounded to two decimals if you want its total to match the displayed monthly amount.

What Changes the EMI

For a fixed rate and term, a larger principal increases the monthly payment. A higher APR also increases it. Extending the term usually lowers the required monthly payment because repayment is spread over more months, but it can increase total interest because interest accrues for longer. The calculator shows the payment and interest estimate together so these effects are not confused.

The principal should represent the amount actually financed, not automatically the car’s advertised price. A down payment or trade-in may reduce the amount borrowed, while an amount financed for optional products or fees may increase it. Use the principal and APR supplied in the lender’s loan disclosure or quote rather than assuming that a vehicle price is the loan balance.

Limits and Common Calculation Errors

• APR is not the monthly rate: Convert an annual percentage to a monthly decimal using APR divided by 1,200.
• Years are not months: Multiply a term in years by 12 to get the payment count.
• Do not omit the sign convention: In Excel, use a negative present value, such as -A2, to show the payment as positive.
• Do not treat every charge as interest: Fees, insurance, taxes, and other amounts may affect a real bill but are not included unless they are financed into the principal or entered separately.
• Rounding matters: Displayed payments are rounded to cents; a lender may use its own payment schedule and rounding approach, which can affect the final payment or total.

This formula assumes a constant rate and regular monthly periods. It does not reproduce every contract’s day-count method, payment dates, fee treatment, or variable-rate changes. For a zero-interest loan, the formula’s division by the rate is not defined; the payment is simply principal divided by the number of payments, provided there are no other financed amounts. The calculator’s minimum APR is 0.01% to keep its stated formula defined.

Frequently Asked Questions

What is the Excel formula for car loan EMI?
Use =PMT(APR/12, term_in_years*12, -loan_amount) when APR is stored as a percentage, such as 8%. If you enter 8 as a plain number rather than 8%, divide by 1,200 instead.

Does Excel’s PMT function return the total interest?
No. It returns the periodic payment. Multiply the payment rounded to cents by the number of monthly payments, then subtract the principal to estimate total interest under the same assumptions.

Should I include the down payment in the loan amount?
No. A down payment is generally money paid toward the vehicle rather than borrowed. Use the financed principal after accounting for any down payment or trade-in, as shown in the actual loan figures.

Why does my Excel payment differ from this estimate?
Check whether the APR is entered as 8% or as the number 8, whether the term is in years or months, and whether the principal is the financed amount. Differences can also come from rounding and contract-specific payment timing or fees.

Does this EMI include insurance and taxes?
No. It estimates principal and interest only. Insurance, taxes, fees, and other charges are excluded unless included in the principal you enter.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• PPF Account Interest Rate: How It Is Set
• PPF Interest Rate: Current Rate and How It Works
• PL Rate of Interest: Calculate a Personal Loan Payment

in p | Loan amount | - | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | - | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | - | pmt * yrs * 12 - p | 2