Home Loan EMI in Excel: PMT and a Checked Schedule

Place the assumptions in separate cells

For a simple fixed-rate home-loan model, put principal 3000000 in B1, annual nominal interest rate 8% in B2 and term 20 years in B3. Label each cell so that another reader can distinguish an amount from a percentage and years from months. The illustration assumes equal payments at the end of each month, no rate resets, no prepayments and no separate charges. It is a spreadsheet model rather than a lender's quote. Use the actual amount to be financed instead of assuming the property price and principal are identical.

Use PMT with monthly units

In B4 enter =-PMT(B2/12,B3*12,B1). The monthly rate is the annual decimal rate divided by 12, and the payment count is years multiplied by 12. The initial minus sign displays the outgoing payment as a positive amount when the principal is positive. Microsoft's PMT documentation describes constant payments and a constant interest rate, with end-of-period timing when the timing argument is omitted. For this example, B4 evaluates to approximately 25093.20. Merely formatting B4 as currency does not change the underlying unrounded number used by other formulas.

Check the percentage cell before trusting the result

Entering 8% in B2 stores the decimal0.08. Entering the number8 and then applying percentage formatting instead represents800%, which is a very different rate. If an input sheet deliberately stores the plain number8, its formula needs an additional division by 100; do not apply both conventions simultaneously. Similarly, putting240 into a cell labeled years and then multiplying by 12 creates2880 payments rather than 240. A visible input block makes these unit mistakes easier to identify than embedding every number directly inside one long formula.

Reconcile payment, total and interest

With the stated assumptions, the unrounded payment is about 25093.202069804. Over240 months, total installments are approximately 6022368.50 and modeled interest is3022368.50 after subtracting the 3000000 principal. In Excel, B4*B3*12 calculates the total and B4*B3*12-B1 calculates interest. Multiplying the displayed25093.20 instead would give6022368.00, a fifty-paise difference caused by rounding the payment first. Keep sufficient internal precision and decide separately how a real agreement rounds installments and adjusts the final payment.

Build rows that explain the payment

Use columns A throughF for period, opening balance, interest, payment, principal repaid and closing balance, with the first data row at7. Set A7=1, B7=$B$1, C7=B7*$B$2/12, D7=$B$4, E7=D7-C7 and F7=B7-E7. In the next row, set A8=A7+1 and B8=F7, then copy the interest, payment, principal and closing-balance formulas down. The first month's interest is20000 and principal repaid approximately 5093.20. The next row must begin with the previous closing balance, not the original principal again.

Include controls for the finished sheet

After240 payment rows, the closing balance should be effectively zero within the chosen rounding convention. The principal column should sum to3000000, and payment minus interest should equal principal in every row. Use a zero-rate scenario as another check:3000000 divided by 240 is12500 per month. If the PMT result and the recurrence disagree substantially, inspect relative versus absolute cell references, the payment timing and the conversion from annual to monthly rate. Do not hide a large unexplained balance by simply overwriting the last row with zero. With the first payment on row7, payment240 belongs on row246. A formula copied one row too far adds an unintended extra installment.

State what the workbook and local tool omit

PMT's basic payment does not independently include taxes, reserves or other loan charges. Add separate budget lines for costs outside the modeled principal. Floating-rate changes, partial disbursements, construction-period interest and prepayments require additional dated logic; one original PMT calculation cannot describe every later event. The linked EMI Calculator can independently check the initial 3000000,8%,20-year scenario, but it does not open or validate an Excel workbook. Use the lender's repayment documents for actual obligations and preserve the sheet's assumptions alongside any comparison so changes in rate or principal are visible.

Related guides

Loan Amortization: Payment, Interest and Balance

Build an amortization schedule row by row, check the first two months and twelve-month balance, and distinguish full-term totals from the local first-year display.

EMI Amortization: How Each Payment Reduces Debt

Understand why the interest and principal shares change within a fixed EMI, follow a three-year example, and distinguish amortization from interest-only repayment.

Monthly Mortgage Payment Formula: Principal, Interest and Escrow

Calculate fixed mortgage principal and interest, include the down payment correctly, and separate the loan installment from taxes, insurance and other housing costs.