AccountancyCBSE Class 12 · Use of Spreadsheet in Business Applications

Loan Repayment Schedule Using the PMT Function: Class 12 Steps and Solved Examples

Updated 15 October 2026 · Fact-checked

The PMT function gives the equal instalment needed to repay a loan: =PMT(rate, nper, pv, [fv], [type]), where rate is interest per period, nper the number of payments and pv the loan amount. A repayment schedule then shows, for each period, the opening balance, interest (opening balance × rate), principal repaid (instalment − interest) and closing balance, which reaches zero.

Every instalment is the same size, but interest shrinks and principal grows each period until the loan balance reaches zero.

Illustration drawn for QuestionBuddy.

Understand Loan Repayment Schedule with PMT

PMT syntax. =PMT(rate, nper, pv, [fv], [type])

rate: interest rate per period (12% p.a. paid monthly → 12%/12 = 1%).
nper: total number of payments (2 years monthly → 24).
pv: present value, the loan amount.
fv: balance left after the last payment; 0 (or omitted) for a loan.
type: 0 or omitted = payment at the end of each period; 1 = at the beginning.

Excel shows money paid out as negative; write =−PMT(...) or enter pv as negative to show a positive instalment.

Matching units. Rate and nper must use the same period. Quarterly payments on a 12% p.a. loan for 4 years: rate 3%, nper 16.

Opening balance (period 1) = loan; later periods = previous closing balance.
Instalment = PMT result, referenced absolutely (for example ＄C＄3).
Interest = opening balance × rate (rate cell absolute).
Principal = instalment − interest.
Closing balance = opening balance − principal.

Copy the row down for nper periods; the last closing balance should be 0 (tiny rounding differences aside).

What the schedule shows. Each instalment is the same, but its make-up changes: interest falls and principal rises over time, because interest is charged on a shrinking balance. Total interest = instalment × nper − loan.

Related functions. PV, FV and RATE use the same arguments to find a loan amount, the future value of savings, or the interest rate.

Loan Repayment Schedule with PMT: key facts

PMT: =PMT(rate, nper, pv, [fv], [type]); Use =−PMT(...) to show a positive instalment.
Instalment (closed form): Loan × r ÷ (1 − (1 + r)^(−n)); r = rate per period, n = number of periods.
Interest for a period: Opening balance × rate per period
Principal for a period: Instalment − interest
Total interest: Instalment × number of periods − loan

How to answer Loan Repayment Schedule with PMT questions

Loan questions test the PMT syntax, unit matching and the logic of each schedule column.

1Convert the annual rate to the payment period and count the number of payments.
2Write =PMT(rate, nper, pv) with cell references; add a minus sign for a positive result.
3Set up the schedule columns: opening, instalment, interest, principal, closing.
4Use absolute references for the rate and instalment cells; copy the row down.
5Check the final closing balance is zero.
6Interpret: interest share falls and principal share rises over time.

Loan Repayment Schedule with PMT in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

EMI on a business loan

Suppose a young entrepreneur takes a loan of ₹4,00,000 at 12% p.a. to buy equipment for a printing shop, repayable in equal monthly instalments over 2 years.

Write the PMT formula, find the monthly instalment, and the total interest paid.

Try it first, then show the solutionSolution

Rate per month = 12%/12 = 1%; nper = 24.
=−PMT(1%, 24, 400000) = ₹18,829.39 per month.
Total paid = 18,829.39 × 24 = ₹4,51,905.33; interest = ₹51,905.33.

Answer: Monthly instalment ₹18,829.39; total interest about ₹51,905 over two years. The schedule would show the interest part falling from ₹4,000 in month 1 as the balance shrinks.

Memory hook: Same instalment every month, but the interest slice keeps shrinking.

Tricks to remember Loan Repayment Schedule with PMT

Short memory aids. Say each one aloud once, then use it on a question the same day.

R-N-P
PMT needs Rate per period, Number of periods, Present value: 'Repay Now, Promptly'.
Use it when: Argument order.
Match the clock
Monthly payments need a monthly rate and months; quarterly need quarterly.
Use it when: Unit matching.
Minus for plus
Excel shows payments as negative; put a minus before PMT for a positive figure.
Use it when: Presentation.

Loan Repayment Schedule with PMT: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Monthly rate · 1 mark · MCQBoard level

A loan at 12% p.a. is repaid in monthly instalments over 2 years. The 'rate' and 'nper' arguments in PMT are:
(a) 12%, 2
(b) 1%, 24
(c) 12%, 24
(d) 1%, 2

Try it first, then show the solutionSolution

Monthly rate = 12% ÷ 12 = 1%; number of payments = 2 × 12 = 24.

Answer: (b) 1%, 24

Example 2: Monthly instalment and first two rows · 3 marks · Short answerBoard level

A loan of ₹1,00,000 at 12% p.a. is repayable in 12 equal monthly instalments at the end of each month. Write the PMT formula, find the instalment, and show the interest and principal for months 1 and 2. (3 marks)

Try it first, then show the solutionSolution

=−PMT(1%, 12, 100000) = ₹8,884.88.
Month 1: interest = 1,00,000 × 1% = 1,000; principal = 7,884.88; closing = 92,115.12.
Month 2: interest = 921.15; principal = 7,963.73; closing = 84,151.39.

Answer: Instalment ₹8,884.88; interest falls from ₹1,000 to ₹921.15 while principal rises.

Example 3: Annual schedule · 4 marks · Short answerHarder board level

Prepare a repayment schedule for a loan of ₹3,00,000 at 8% p.a., repayable in 3 equal annual instalments at the end of each year. (4 marks)

Try it first, then show the solutionSolution

Instalment = −PMT(8%, 3, 300000) = ₹1,16,410.05.
Year 1: interest 24,000.00; principal 92,410.05; closing 2,07,589.95.
Year 2: interest 16,607.20; principal 99,802.86; closing 1,07,787.09.
Year 3: interest 8,622.97; principal 1,07,787.09; closing 0.

Answer: Instalment ₹1,16,410.05; total interest ₹49,230.16; closing balance nil after year 3.

Example 4: Quarterly payments · 3 marks · Short answerHarder board level

A company borrows ₹6,00,000 at 12% p.a., repayable in equal quarterly instalments over 4 years. Write the PMT formula and find the instalment and total interest. (3 marks)

Try it first, then show the solutionSolution

Rate per quarter = 3%; nper = 16.
=−PMT(3%, 16, 600000) = ₹47,766.51.
Total interest = 47,766.51 × 16 − 6,00,000 = ₹1,64,264.15.

Answer: Quarterly instalment ₹47,766.51; total interest ₹1,64,264.15.

Example 5: Longer loan, smaller EMI · 1 mark · Assertion-reasonHOTS / competency

Assertion (A): Increasing the repayment period of a loan from 10 to 20 years at the same rate reduces the monthly instalment but increases the total interest paid.
Reason (R): A longer period spreads the principal over more payments, while interest is charged on the outstanding balance for longer.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.

Try it first, then show the solutionSolution

₹10,00,000 at 12%: 120 months → ₹14,347.09 (interest ₹7,21,651); 240 months → ₹11,010.86 (interest ₹16,42,607).
R explains both effects.

Answer: (a) Both A and R are true, and R is the correct explanation of A.

Where students lose marks in Loan Repayment Schedule with PMT

The slips examiners see most often, and what to write instead.

Mistake: Using the annual rate with monthly periods
Not converting the rate.
Wrong working
=PMT(12%, 24, 400000)
Right working
=PMT(1%, 24, 400000)
Do this: Rate and nper in the same unit.
Usually costs: 1-2 marks.
Mistake: Interest on the original loan every period
Treating it as simple interest.
Wrong working
Interest each month = 1,00,000 × 1%.
Right working
Interest = opening balance of that month × 1%.
Do this: Interest falls as the balance falls.
Usually costs: 1-2 marks.
Mistake: Relative reference to the rate cell
Copying without ＄.
Wrong working
=B6*C2 copied down becomes =B7*C3.
Right working
=B6*＄C＄2.
Do this: Fix input cells.
Usually costs: 1 mark.
Mistake: Reporting a negative instalment without comment
Excel's sign convention.
Wrong working
Instalment = −8,884.88.
Right working
=−PMT(...) gives 8,884.88 (outflow).
Do this: Add the minus sign or explain the sign.
Usually costs: 1/2 mark.

Exam tips

Write the formula with arguments in order: =PMT(rate, nper, pv).
State the converted rate and periods before the formula.
In schedule questions, show at least the first two rows and the closing check.
Financial functions, especially PMT, are the most asked item in this chapter (1- and 4-mark questions).

Practice questions on Loan Repayment Schedule with PMT

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
In =PMT(rate, nper, pv, fv, type), the argument 'type' set to 1 means payments are made:
(a) at the end of each period
(b) at the beginning of each period
(c) once a year
(d) only at maturity
Hint
0 = end.
Answer
(b) at the beginning of each period
Q2 · 1 mark · MCQBoard level
The argument 'pv' in the PMT function stands for:
(a) payment value
(b) present value (loan amount)
(c) period value
(d) percentage value
Hint
What you borrow today.
Answer
(b) present value (loan amount)
Q3 · 3 marks · Short answerHarder board level
A loan of ₹2,00,000 at 9% p.a. is repayable in 24 equal monthly instalments. Write the formula and find the instalment and the first month's interest. (3 marks)
Hint
Rate 0.75%.
Answer
=−PMT(9%/12, 24, 200000) = ₹9,136.95. First month's interest = 2,00,000 × 0.75% = ₹1,500; principal = ₹7,636.95.
Q4 · 3 marks · Short answerHarder board level
A loan of ₹5,00,000 at 10% p.a. is repaid in 5 equal annual instalments. Find the instalment and show year 1 of the schedule. (3 marks)
Hint
Interest year 1 = 50,000.
Answer
Instalment = ₹1,31,898.74. Year 1: interest 50,000; principal 81,898.74; closing balance ₹4,18,101.26.
Q5 · 3 marks · Short answerHarder board level
Name the columns of a loan repayment schedule and give the formula logic for each. (3 marks)
Hint
Five money columns.
Answer
Opening balance (loan, then previous closing); instalment (PMT, fixed); interest (opening × rate); principal (instalment − interest); closing balance (opening − principal).
Q6 · 1 mark · Assertion-reasonHarder board level
Assertion (A): In a loan repayment schedule, the principal portion of each equal instalment increases over time.
Reason (R): Interest is calculated on the outstanding balance, which keeps falling.
(a) Both A and R are true, and R is the correct explanation of A.
(b) Both A and R are true, but R is not the correct explanation of A.
(c) A is true, but R is false.
(d) A is false, but R is true.
Hint
Instalment fixed, interest falling.
Answer
(a) Both A and R are true, and R is the correct explanation of A.
Q7 · 4 marks · Short answerHOTS / competency
For a ₹5,00,000 loan at 10% p.a. over 5 years, the end-of-year instalment is ₹1,31,898.74. Without recalculating PMT, find the instalment if payments are made at the beginning of each year (type = 1), and explain why it is lower. (4 marks)
Hint
Divide by (1 + rate).
Answer
Beginning-of-period instalment = 1,31,898.74 ÷ 1.10 = ₹1,19,907.95. It is lower because each payment is made a year earlier, so less interest accumulates on the outstanding balance.
Q8 · 3 marks · Short answerHOTS / competency
A student's schedule ends with a closing balance of ₹2,40,000 after the last instalment. Suggest two likely errors. (3 marks)
Hint
Rate units; references.
Answer
(1) The instalment was computed with mismatched units (for example, monthly periods with the annual rate in PMT, or the reverse), so it does not fit the schedule's interest calculation. (2) The rate or instalment cell was referenced relatively and shifted when copied, giving wrong interest or principal in later rows. Fix the units and use absolute references.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.PMT argument order?Show answer
rate, nper, pv, [fv], [type].
2.Monthly rate for 9% p.a.?Show answer
0.75%.
3.Interest for a period in the schedule?Show answer
Opening balance × rate per period.
4.What should the final closing balance be?Show answer
Zero.
5.Why is PMT's result negative?Show answer
Excel shows money paid out as negative.

Loan Repayment Schedule with PMT: frequently asked questions

What does the PMT function do?

It calculates the equal periodic payment needed to repay a loan, given the rate per period, number of periods and loan amount.

How do I convert an annual rate for monthly EMI?

Divide the annual rate by 12 and multiply the years by 12 for nper.

Why does the interest portion fall in each instalment?

Interest is charged on the outstanding balance, which reduces after every payment.

What is 'type' in PMT?

0 or omitted for payments at the end of each period; 1 for payments at the beginning.