calculate-loan

01 Loan payments

Home Loan EMI Repayment Calculator in Excel

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

Monthly EMI 1,672.88
Total interest
201,491.20
= Monthly emi × loan term × 12 − loan amount
Loan amount
200000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount, Interest rate (APR) (%), Loan term (years)
You get
Monthly EMI, 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 home loan EMI repayment calculator in Excel estimates your fixed monthly principal-and-interest payment from the loan amount, annual interest rate, and term. Enter those three figures below to see the estimated EMI and total interest; the formula can also be copied into a spreadsheet for a repayment schedule.

Formula

The standard fixed-payment formula is:

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

Here, P is the starting principal in your currency, r is the monthly interest rate as a decimal, and n is the number of monthly payments. For an annual rate entered as a percentage, r = APR ÷ 1,200; for a term in years, n = years × 12. The calculator rounds the monthly payment to two decimal places and calculates total interest as rounded EMI × number of payments − principal.

This is equivalent to the formula used in the calculator: p × (apr / 1200) ÷ [1 − (1 + apr / 1200)−(yrs × 12)]. The rate is entered as an annual percentage, not a decimal: enter 8 for 8%, not 0.08. The currency unit is shown as “-” because the calculator does not assume a country or currency; interpret every money amount in your currency.

Worked Example

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

  1. Convert the annual percentage to a monthly decimal rate: r = 8 ÷ 1,200 = 0.0066667.
  2. Find the number of monthly payments: n = 20 × 12 = 240.
  3. Apply the formula: EMI = 200,000 × 0.0066667 ÷ [1 − (1.0066667)−240].
  4. The unrounded result is approximately 1,672.88, so the displayed monthly EMI is 1,672.88.
  5. Total scheduled payments using that rounded EMI are 1,672.88 × 240 = 401,491.20. Estimated total interest is 401,491.20 − 200,000 = 201,491.20.

The last payment in a lender’s actual schedule may differ slightly because of payment dates, daily interest, rounding practices, or adjustments. This example is a mathematical illustration, not a quoted loan offer.

Assumptions

  • The interest rate remains fixed for the full term.
  • Payments are made monthly, with the first payment one month after the loan begins.
  • Interest is modelled monthly at APR ÷ 12, with a constant monthly payment.
  • The calculation excludes fees, taxes, insurance, mortgage insurance, and escrow. It also excludes extra payments and rate changes.

Using the EMI Calculator in Excel

To make a simple spreadsheet, put the principal in cell B1, the annual percentage rate in B2, and the term in years in B3. In B4, use Excel’s PMT function with a monthly rate and a monthly term. For example, with those inputs in the cells above, enter:

=-PMT(B2/1200,B3*12,B1)

Excel’s PMT function generally returns a negative value when the principal is entered as a positive cash inflow, because it treats payments as cash flowing out. The leading minus sign displays the payment as a positive amount. Format B4 to two decimal places. An equivalent direct formula is =B1*(B2/1200)/(1-(1+B2/1200)^(-B3*12)). Make sure the term is in years in this example; if you enter months instead, do not multiply it by 12.

For an amortization schedule, create one row per month. A basic schedule can use the previous balance multiplied by the monthly rate for that month’s interest, EMI minus interest for principal repaid, and previous balance minus principal repaid for the new balance. The final row may need rounding adjustment to bring the balance to zero. Spreadsheet signs, date conventions, and rounding can affect the display, so compare the schedule’s first payment with the calculator’s estimate.

Reading the Results and Building a Schedule

The EMI is the modelled recurring payment toward principal and interest, not necessarily the entire amount collected each month. A housing payment can also involve charges or separately billed costs that this calculator does not include. Check the lender’s payment breakdown and use the actual figures from its disclosure when comparing your estimate with a statement or offer.

Total interest here is the sum of the rounded monthly payments minus the original principal. It is an estimate over the full stated term, assuming every scheduled payment is made as modelled. It does not mean that the same amount of principal is repaid in every instalment: early payments generally contain a larger interest component under this fixed-rate amortization formula, while later payments contain a larger principal component.

  • For each month, calculate interest as opening balance × monthly rate.
  • Calculate principal repaid as EMI − that month’s interest.
  • Calculate closing balance as opening balance − principal repaid.
  • Use the closing balance as the next month’s opening balance, and keep the payment date and rounding method consistent.

Common Excel Errors and Limits

A frequent mistake is entering 8% as 0.08 in a formula that divides a percentage input by 1,200. This calculator expects 8. Another is using 8 as the monthly rate instead of converting an annual rate to a monthly rate. Term units matter too: this calculator expects years, while a spreadsheet schedule counts payments in months.

A changing-rate loan does not have one fixed payment for its entire life, so a single calculation cannot represent future changes. The formula also assumes regular monthly periods and a level payment; it does not model irregular dates, a payment holiday, fees added to principal, or extra repayments. For a variable rate, calculate scenarios using separate assumed rates and periods, and treat them as illustrations rather than forecasts.

Rounding every monthly interest amount to cents in a detailed Excel schedule can create a small difference from a formula that retains extra precision internally. Likewise, using an unrounded EMI to calculate totals will not match a total based on the displayed payment. This calculator deliberately uses the rounded payment for its total-interest estimate. Use the lender’s schedule for exact contractual figures.

Frequently Asked Questions

How do I calculate home loan EMI in Excel?
Use =-PMT(annual_rate/1200,years*12,principal) when the rate is entered as a percentage such as 8 and the term is in years. The minus sign displays the payment as positive.

Does EMI include taxes and insurance?
No. The formula estimates principal and interest only. Taxes, insurance, fees, and other charges are not included unless you enter them separately in a different calculation.

Why does my Excel EMI differ from a lender’s figure?
Possible causes include a different rate or term, fees included in the financed amount, payment dates, changing rates, rounding, or a different method of calculating interest. Compare the inputs and request the lender’s payment breakdown.

Does making an extra payment change the EMI?
An extra payment can affect the outstanding balance, but its treatment depends on the loan’s terms and servicing. This fixed-payment calculator does not model extra payments or any resulting change to the payment or term.

Can I use this for a floating-rate home loan?
Only as a snapshot using an assumed rate for a specified period. It does not predict rate changes or automatically recalculate later payments.

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

Related

Formula used
in p | Loan amount | - | 200000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 20 | 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

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 →