Finxora Logo
Sign In
Calculate Loan Payments with an Excel Amortization Schedule
Back to Blog

Calculate Loan Payments with an Excel Amortization Schedule

FINXORA
5 min read
loan amortization
excel
finance
debt management

Build an Excel loan amortization schedule with PMT, principal and interest formulas, rounding checks and practical guidance for Indian bank loan projections.

What an amortization schedule tells you

An amortization schedule breaks each loan instalment into interest and principal and shows the balance remaining after payment. For an Indian business preparing a bank loan proposal, it connects the proposed borrowing with monthly cash outflows and annual debt repayment. It also explains why an equal monthly instalment, or EMI, can remain constant while its principal and interest components change.

Excel can produce a transparent schedule when the assumptions are clearly defined. The example below uses a fully disbursed loan, a constant annual rate, equal monthly periods and payments at each month end. It is a planning model. Compare the result with the lender's repayment schedule before using it for accounting, payment instructions or a final borrowing decision.

Set up a separate input area

Keep assumptions outside the repayment table so that changing a loan term does not require editing many rows. Enter percentages as percentages: 12% in Excel is 0.12, whereas entering 12 without a percentage sign represents a very different rate.

CellInput or calculationExample
B2Disbursed principal1000000
B3Annual nominal interest rate12%
B4Tenure in months60
B5Monthly rate=B3/12
B6Positive monthly EMI=-PMT(B5,B4,B2,0,0)

The last two arguments in PMT specify a zero balance after repayment and instalments at the end of the period. For this example, the EMI is approximately Rs 22,244.45. The first month's interest is Rs 10,000, leaving approximately Rs 12,244.45 towards principal. These figures illustrate the calculation and are not a bank quotation.

The rate divided by twelve approach assumes a nominal annual rate with monthly periodic calculation. An effective annual yield needs a different conversion. Actual lenders may calculate interest on daily balances, apply different day counts or collect a broken-period interest amount before regular instalments begin.

Build the repayment table

Place headings in row 10: A for Period, B for Opening Balance, C for Payment, D for Interest, E for Principal and F for Closing Balance. Begin the first payment in row 11. Use the following formulas for a schedule that rounds monetary amounts to paise each month.

CellFormulaPurpose
A11=1First repayment period
B11=$B$2Opening loan amount
D11=ROUND(B11*$B$5,2)Interest for the period
C11=IF(A11=$B$4,ROUND(B11+D11,2),MIN(ROUND($B$6,2),ROUND(B11+D11,2)))Payment capped at the amount due, with final settlement
E11=ROUND(C11-D11,2)Principal repaid
F11=ROUND(B11-E11,2)Remaining principal

In row 12, enter =A11+1 in A12 and =F11 in B12. Copy the payment, interest, principal and closing balance formulas from row 11 into row 12. Then extend row 12 down until the period number reaches 60. Absolute references such as $B$5 keep the input cell fixed as formulas move down the sheet.

The final payment formula clears the remaining balance plus that month's interest. It can differ slightly from the regular EMI because the schedule rounds each month. Avoid hiding a residual balance with a display format or simply deleting it. A visible final settlement makes the adjustment explainable.

Check the schedule before relying on it

A spreadsheet can look complete while containing an incorrect reference. Build reconciliation checks beside the input area and investigate any difference rather than inserting a balancing amount.

  • The sum of principal repayments should equal the original disbursed principal.
  • The final closing balance should be zero after the final payment adjustment.
  • Total payments should equal total principal plus total interest.
  • Each opening balance after period one should equal the preceding closing balance.
  • For a normal positive-rate loan, interest should generally fall as principal reduces.
  • The number of payment rows should equal the stated tenure.

For the 60-month example ending in row 70, compare SUM(E11:E70) with B2. Compare SUM(C11:C70) with SUM(D11:D70)+SUM(E11:E70). Check F70 separately. Keep calculation precision and displayed precision consistent with the method selected; do not mix an unrounded PMT schedule with independently rounded principal figures without a reconciliation.

Account for loan terms that change the calculation

Floating interest rates

A constant-rate PMT model cannot forecast future rate resets. Add an effective rate column and apply the relevant periodic rate to each opening balance. When a reset occurs, establish whether the lender changes the EMI, the remaining tenure or both. Recalculate the remaining schedule from that date using the revised terms, rather than changing the historical rate for every row.

Moratorium and staged disbursement

A principal moratorium does not necessarily mean interest is waived. Interest may be payable during the moratorium or capitalised according to the loan terms. For staged disbursements, add each drawdown on its actual date or appropriate model period. Charging interest on the full sanctioned amount from day one can overstate the projected cost when funds are released gradually.

Prepayments and fees

Record an extra principal payment in a separate column and reduce the balance by that amount after accounting for regular repayment. Confirm whether the bank reduces tenure or EMI. Track processing charges, insurance and any applicable prepayment charges separately; a basic PMT calculation does not incorporate them merely because they appear in the sanction letter.

Use repayment figures in a project report

Monthly instalments need to be grouped into the correct financial years, especially when a loan begins partway through the year. Sum interest and principal separately. Interest normally enters the projected profit and loss calculation, while principal repayment affects cash flow and the outstanding loan balance. Treating the entire EMI as an operating expense distorts profit.

Debt service coverage ratio, or DSCR, compares the defined repayment resources with debt service. Under a common project-report convention, net profit plus depreciation plus term-loan interest is divided by term-loan principal plus interest for the year. Confirm the lender's treatment and reconcile the figures with the underlying statements; an EMI schedule alone does not establish repayment capacity.

Finxora's project report software includes repayment schedules, projected statements and DSCR from project inputs. Review the assumptions and compare the output with the sanction terms. The report samples show how schedules sit within a complete proposal, while financial tools can support preliminary calculations.

Keep an audit trail for each version

Record the source of the principal, interest rate, disbursement date and repayment start date beside the workbook. Save a dated version when the bank revises terms. Separate actual repayments from projected rows and reconcile actual closing principal with the lender's statement. This keeps the schedule understandable for the borrower, accountant and credit officer reviewing the loan file.

Frequently Asked Questions

Published on February 14, 2026

Updated on September 22, 2026

Back to Blog

Need bank-ready financials? Create a CMA report or a project report with Finxora in minutes.