calculate-loan

01 Loan payments

Loan EMI Excel: Calculate Monthly Payments

Calculator No. 01-15059 · Updated · Formula and assumptions shown below

Monthly payment 405.53
Total interest
4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
20000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount, Interest rate (APR) (%), Loan term (years)
You get
Monthly payment, Total interest
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A loan EMI Excel calculator estimates the fixed monthly payment on a loan from its principal, annual interest rate, and term. Enter those three figures to see the monthly payment and total interest; the result uses a standard fixed-rate repayment formula and is an estimate, not a lender quote.

Formula

EMI means equated monthly instalment: a level payment made each month during a loan term. The calculator applies the standard amortizing-loan payment formula:

Payment = P × r ÷ [1 − (1 + r)−n]

  • P is the amount borrowed, in your currency.
  • r is the monthly interest rate as a decimal, calculated here as annual APR ÷ 1,200. For an 8% APR, r = 8 ÷ 1,200.
  • n is the number of monthly payments, calculated as loan term in years × 12.

Total interest is calculated as monthly payment × number of payments − principal. The payment is displayed to two decimal places, and the total-interest result uses that rounded payment. This makes the displayed arithmetic easy to follow, though a real lender may use more precise daily or periodic calculations and a different final payment.

Worked Example

Using the calculator’s default values—principal 20,000 in your currency, APR 8%, and a five-year term:

  1. Monthly rate: r = 8 ÷ 1,200 = 0.0066667.
  2. Number of payments: n = 5 × 12 = 60.
  3. Substitute into the formula: payment = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
  4. The monthly payment rounds to 405.53 in your currency.
  5. Total interest = 405.53 × 60 − 20,000 = 4,331.80 in your currency.

What Loan EMI in Excel Means

“Loan EMI Excel” commonly refers to working out a monthly loan instalment in Microsoft Excel or a similar spreadsheet. The word EMI is widely used for a scheduled payment that combines interest and principal. The calculator above performs the same core arithmetic without requiring a spreadsheet. Its figures can also be recreated in a workbook using the payment formula, provided the rate and term are entered in the right units.

At the start of a typical amortizing loan, more of each level payment goes toward interest because the outstanding balance is larger. As the balance falls, the interest portion usually shrinks and more of the payment reduces principal. The payment stays level under the formula’s assumptions, but its internal split changes over time.

How to Use and Read the Result

  • Enter the amount borrowed, not the total of all payments.
  • Enter the annual APR as a percentage: type 8 for 8%, not 0.08 or 8.0 if the rate is actually 8.0%.
  • Enter the term in years. The calculator converts it to monthly payments.
  • Read the first result as the estimated monthly payment, then use total interest to see the financing cost implied by the same payment schedule.

For a real quote, use the principal, rate, and exact term shown by the lender. If the repayment schedule is not monthly, or the interest rate changes during the term, this fixed-rate monthly estimate will not reproduce it. When checking a spreadsheet result, confirm that it uses a monthly rate and total months—not an annual rate paired with a monthly payment count.

What Changes the EMI

For the same interest rate and term, a larger principal leads to a larger monthly payment. For the same principal and rate, a longer term generally lowers each scheduled monthly payment but creates more payment periods, so total interest can be higher. A higher rate also increases the payment and the total interest, all else equal.

These relationships describe the formula, not whether a particular loan is suitable for someone. The calculator cannot determine a borrower’s budget, eligibility, or the full cost of a loan. Obtain actual rates, fees, and payment details directly from the lender’s disclosure rather than treating the example inputs as current market figures.

Common Excel and Calculation Mistakes

  • Mixing percentage formats: The expression uses APR in percentage points and divides by 1,200. If a spreadsheet instead stores 8% as 0.08, its formula must be written for that representation.
  • Using years as months: A five-year monthly loan has 60 payments, not five.
  • Rounding too early: Rounding the monthly rate or intermediate factors can shift the result. Keep full precision during calculation and round the displayed payment to cents.
  • Confusing payment with interest: EMI includes both interest and principal; it is not the interest charge alone.
  • Assuming every loan is level-payment: Interest-only, variable-rate, balloon, and irregular-payment loans need different calculations.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are made monthly at the end of each period.
  • Interest is compounded monthly using APR ÷ 12.
  • Fees, taxes, insurance, PMI, escrow, late charges, and other lender-specific costs are not included.
  • The formula assumes equal scheduled payments and does not model extra payments or a changing balance from other transactions.

Frequently Asked Questions

How do I calculate EMI in Excel?
Use the principal, monthly rate, and total number of monthly payments in the standard amortizing-payment formula shown above. Keep the annual-rate conversion consistent with how the spreadsheet stores percentages.

Is EMI the same as a monthly payment?
EMI describes a fixed, scheduled monthly instalment in an amortizing loan. A loan with variable payments or a changing rate may not have the same payment throughout its term.

Does EMI include interest and principal?
Yes. Under the formula here, each scheduled payment covers interest for that period and reduces the principal balance, except that the precise split changes as the balance is repaid.

Why does my Excel EMI differ from this estimate?
Check whether the APR was entered as 8 or 0.08, whether the term was converted to months, and whether the calculation rounds the payment or rate differently. Fees, payment dates, and lender-specific methods can also change actual figures.

Does a longer loan term reduce total interest?
It generally reduces the scheduled monthly payment when principal and rate are unchanged, but the borrower makes payments for more months. In this fixed-rate formula, total interest can therefore increase with a longer term.

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

Related

Formula used
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

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →