EMI Calculator Google Sheets: Formula and Example

An EMI calculator in Google Sheets estimates the fixed monthly instalment on a loan from its principal, annual interest rate and term. Enter those three figures to see the monthly payment and total interest; this calculator uses the standard fixed-rate, monthly-payment formula.

Formula

The equal monthly instalment, or EMI, is calculated by dividing the monthly interest charge by the discount factor for all scheduled payments:

EMI = P × r / [1 − (1 + r)⁻ⁿ]

• P is the amount borrowed, in your currency.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
• n is the number of monthly instalments: loan term in years × 12.

The calculator rounds the resulting EMI to two decimal places, then calculates total interest as rounded EMI × number of payments − principal. The rate input is an annual percentage; for example, 8 means 8% per year, not 0.08%. The formula assumes a positive APR, which is why the input starts at 0.01%.

Worked Example

Using the calculator’s defaults, the principal is 20,000 in your currency, APR is 8%, and the term is five years.

1. Convert the annual rate to a monthly decimal: 8 ÷ 12 ÷ 100 = 0.0066667.
2. Find the number of instalments: 5 × 12 = 60.
3. Substitute into the formula: EMI = 20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰].
4. The unrounded payment is approximately 405.53, so the monthly EMI is 405.53 in your currency.
5. Total of the rounded payments: 405.53 × 60 = 24,331.80. Estimated total interest: 24,331.80 − 20,000 = 4,331.80.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are equal, made once a month, and paid at the end of each monthly period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI and escrow are not included.

Using an EMI Calculator in Google Sheets

To reproduce this estimate in Google Sheets, place the principal, annual rate and term in separate cells. For example, use B2 for the loan amount, B3 for APR entered as a percentage such as 8%, and B4 for the term in years. A formula for the EMI is:

=-PMT(B3/12,B4*12,B2)

Google Sheets’ PMT function returns a negative number when the loan amount is entered as a positive present value, because it treats the payment as money flowing out. The minus sign makes the displayed payment positive. Format the result as currency if useful. To calculate interest using the rounded payment, use a formula such as =ROUND(EMI_cell,2)*B4*12-B2, replacing EMI_cell with the cell holding the payment. This calculation uses the payment rounded to cents, just as the calculator does.

If the APR cell contains the number 8 rather than a percentage-formatted 8%, use B3/1200 as the monthly rate in PMT: =-PMT(B3/1200,B4*12,B2). These two approaches are equivalent only when the cell is entered in the corresponding format. Check the cell value and formatting before relying on a result.

How to Read the Result and Where It Differs

EMI is the scheduled payment amount, not the principal portion alone. Early in a standard amortizing loan, a larger share of each payment goes toward interest; over time, the interest share generally falls as the outstanding balance is repaid. The total-interest figure here is the sum of rounded payments minus the original principal, not an additional amount due on top of every instalment.

In Sheets, PMT is a convenient way to calculate a periodic payment, but it does not automatically account for every feature of a lender’s repayment schedule. Check that its rate is the monthly rate, its number of periods is the number of monthly payments, and its present value is the principal. If payments are due at the start rather than the end of each period, the timing setting changes the result; the formula above assumes end-of-month payments.

Common Mistakes and Limits

• Mixing rate formats: An entered 8% and an entered 8 are different cell values. Use APR ÷ 12 for a percentage-formatted rate, or APR ÷ 1200 for a whole-number rate.
• Using years as periods: A five-year monthly loan has 60 periods, not five. Convert years to months.
• Rounding inconsistently: The displayed payment is rounded to cents. Multiplying an unrounded payment can produce a slightly different total from multiplying the rounded EMI.
• Assuming all charges are included: The estimate covers principal and interest under the stated assumptions. Add-on charges or a variable rate can make actual payments or total cost different.

This formula is for a conventional fixed-rate loan with equal monthly instalments and a positive periodic rate. It is not a complete payoff schedule, and it does not model rate resets, irregular payment dates, fees financed into the balance, payment holidays or lender-specific rounding. For a variable-rate loan, a single fixed-rate calculation cannot predict future instalments.

Frequently Asked Questions

Can I calculate EMI in Google Sheets?
Yes. Use =-PMT(monthly_rate, number_of_months, principal). With an APR entered as a percentage, monthly_rate is APR divided by 12; with APR entered as a whole number, divide it by 1,200.

Why is the Google Sheets PMT result negative?
Sheets follows a cash-flow sign convention: borrowing is an inflow and repayments are outflows. Prefixing PMT with a minus sign displays the instalment as a positive amount.

Does EMI include interest?
Yes. Each regular EMI combines principal repayment and interest for that period. This estimate does not include separate fees, insurance or other charges.

Why does my Sheets result differ from a lender’s figure?
Possible reasons include a different rate convention, payment date, rounding method, fees, changing rates or a term entered in the wrong units. Compare the inputs and assumptions with the lender’s repayment schedule.

Can this calculator handle a variable interest rate?
No. It assumes one fixed APR throughout the term. A rate change can alter future payments or the time needed to repay, so a single-rate estimate will not represent that schedule.

This result is an estimate for educational purposes, and the lender’s disclosure gives the exact figures.

Related

• EMI Calculator Comparison: Formula and Key Differences
• Credit Card EMI Calculator: Monthly Payment and Interest
• Car EMI Calculator: Estimate Your Monthly 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 EMI | - | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | - | pmt * yrs * 12 - p | 2