PMT Function in Excel Tutorial

Excel Tutorial

The PMT function in Excel is used to calculate the regular payment required to repay a loan or investment over a specific period. It is commonly used for home loans, car loans, personal loans, and other financial calculations.

In this tutorial, you will learn how PMT works, understand its arguments, and calculate monthly loan payments using practical examples.

What Is the PMT Function in Excel?

PMT stands for Payment. It calculates the fixed periodic payment required to repay a loan based on:

  • Interest rate
  • Total number of payment periods
  • Present value (loan amount)

For example, suppose you take a loan of ₹5,00,000 at an annual interest rate of 10% for 5 years. You want to know how much EMI you need to pay every month.

The PMT function calculates that regular payment.

Important: PMT calculates the regular payment amount, not the total interest paid separately. It assumes the interest rate and payments remain constant throughout the loan period.

PMT Function Syntax

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

Understanding the Arguments

ArgumentMeaning
rateInterest rate for each payment period.
nperTotal number of payment periods.
pvPresent value, usually the loan amount.
[fv]Optional future value after the last payment. Default is 0.
[type]Optional payment timing: 0 = end of period, 1 = beginning of period. Default is 0.

1. rate – Interest Rate Per Period

The rate is the interest rate applicable to each payment period.

If your bank charges 12% annual interest and you make monthly payments, the monthly interest rate is:

=12%/12

Result: 1% per month.

If payments are quarterly, divide the annual interest rate by 4. Always match the rate period with the payment frequency.

2. nper – Total Number of Payments

This argument represents the total number of installments.

For a 5-year loan with monthly payments:

=5*12

Result: 60 monthly payments.

3. pv – Present Value

This is the current value of the loan or the amount borrowed.

For example, if you borrow ₹5,00,000, the present value is ₹5,00,000.

4. fv – Future Value

This argument is optional. It represents the amount you want remaining after the final payment.

For a fully repaid loan, the future value is normally 0.

5. type – Payment Timing

This argument decides when each payment is made.

TypeMeaning
0Payment at the end of each period
1Payment at the beginning of each period

If you omit this argument, Excel assumes 0.

Most standard loan EMI calculations use type 0.

Important Concept: Why Does PMT Return a Negative Value?

When you calculate a loan payment using PMT, Excel generally returns a negative amount.

This happens because Excel follows a cash-flow sign convention:

  • Money received as a loan is positive.
  • Money paid back as installments is negative.

For example, if the loan amount is entered as positive ₹5,00,000, PMT may return a negative monthly payment.

You can enter the loan amount as negative to display the payment as a positive number.

Both approaches are valid. The sign represents the direction of money movement.

Practical Examples of PMT Function in Excel

Question 1: Calculate Monthly EMI for a Personal Loan

Problem Statement

A person takes a personal loan of ₹5,00,000 at an annual interest rate of 12% for 5 years. Calculate the monthly EMI.

Given Data

ParticularsValue
Loan Amount₹5,00,000
Annual Interest Rate12%
Loan Tenure5 Years
PaymentsMonthly

Excel Formula

=PMT(12%/12,5*12,-500000)

Expected Result

Approximately ₹11,122.22 per month.

Explanation

  • 12%/12 converts the annual interest rate into a monthly rate.
  • 5*12 calculates 60 monthly installments.
  • -500000 represents the loan amount as a cash outflow in the formula’s sign convention, so the payment appears positive.

Excel PMT Formula calculates the fixed monthly payment required to repay the loan over 60 months.

Concept Covered

Always match the interest rate period and payment period. Monthly payments require a monthly interest rate and the total number of monthly installments.

Question 2: Calculate EMI for a Home Loan

Problem Statement

A person takes a home loan of ₹30,00,000 at an annual interest rate of 8.5% for 20 years. Calculate the monthly EMI.

Given Data

ParticularsValue
Loan Amount₹30,00,000
Annual Interest Rate8.5%
Loan Tenure20 Years
PaymentsMonthly

Excel Formula

=PMT(8.5%/12,20*12,-3000000)

Expected Result

Approximately ₹26,035.70 per month.

Explanation

The annual interest rate is divided by 12 because payments are monthly.

The total number of payments is 20 × 12 = 240.

PMT calculates the fixed monthly payment required to repay the home loan over 240 months.

Concept Covered

The loan tenure has a major impact on EMI. A longer tenure generally reduces the monthly payment but can increase the total interest paid.

Question 3: Calculate EMI for a Car Loan

Problem Statement

A customer purchases a car using a loan of ₹8,00,000 at an annual interest rate of 9% for 4 years. Calculate the monthly EMI.

Given Data

ParticularsValue
Loan Amount₹8,00,000
Annual Interest Rate9%
Loan Tenure4 Years
PaymentsMonthly

Excel Formula

=PMT(9%/12,4*12,-800000)

Expected Result

Approximately ₹19,908.45 per month.

Explanation

  • Monthly interest rate = 9% / 12.
  • Total installments = 4 × 12 = 48.
  • Loan amount = ₹8,00,000.

The formula returns the fixed monthly payment for the car loan.

Concept Covered

PMT can calculate installments for different loan types. Only the loan amount, interest rate, and tenure need to change.

Question 4: Calculate Quarterly Loan Payment

Problem Statement

A business borrows ₹10,00,000 at an annual interest rate of 10%. The loan must be repaid over 3 years with quarterly payments. Calculate each quarterly payment.

Given Data

ParticularsValue
Loan Amount₹10,00,000
Annual Interest Rate10%
Loan Tenure3 Years
PaymentsQuarterly

Excel Formula

=PMT(10%/4,3*4,-1000000)

Expected Result

Approximately ₹97,342.86 per quarter.

Explanation

The payment frequency is quarterly, so:

  • Quarterly interest rate = 10% / 4.
  • Total payments = 3 × 4 = 12.

The formula calculates the amount payable at the end of every quarter.

Concept Covered

The interest rate and number of payments must follow the same frequency. For quarterly payments, use a quarterly rate and quarterly payment count.

Question 5: Calculate Annual Loan Payment

Problem Statement

A company borrows ₹5,00,000 at an annual interest rate of 8% for 5 years. It makes one payment every year. Calculate the annual payment.

Given Data

ParticularsValue
Loan Amount₹5,00,000
Annual Interest Rate8%
Loan Tenure5 Years
PaymentsYearly

Excel Formula

=PMT(8%,5,-500000)

Expected Result

Approximately ₹1,25,228.23 per year.

Explanation

Here, the payment frequency is yearly, so the annual interest rate is used directly.

The total number of payments is 5 because the company makes one payment per year.

Concept Covered

You do not always need to divide the interest rate by 12. Divide it only when the payment frequency is monthly.

Question 6: Calculate Payment at the Beginning of Each Period

Problem Statement

A company takes a loan of ₹2,00,000 at an annual interest rate of 12% for 3 years. Payments are made monthly at the beginning of each month. Calculate the monthly payment.

Given Data

ParticularsValue
Loan Amount₹2,00,000
Annual Interest Rate12%
Loan Tenure3 Years
PaymentsMonthly
Payment TimingBeginning of Month

Excel Formula

=PMT(12%/12,3*12,-200000,0,1)

Expected Result

Approximately ₹6,571.02 per month.

Explanation

The formula uses:

  • Monthly interest rate = 12% / 12.
  • Total payments = 36.
  • Loan amount = ₹2,00,000.
  • Future value = 0.
  • Type = 1 because payments are made at the beginning of each month.

When payments are made at the beginning of each period, the payment amount is generally lower than the equivalent end-of-period payment because payments start earlier.

Concept Covered

The type argument changes payment timing. Use 1 for beginning-of-period payments and 0 for end-of-period payments.

Question 7: Calculate Monthly Payment Using Cell References

Problem Statement

A finance department maintains loan details in Excel. Calculate the monthly EMI using cell references instead of typing values directly into the formula.

Given Data

CellParticularsValue
B2Loan Amount₹12,00,000
B3Annual Interest Rate10%
B4Loan Tenure (Years)7

Excel Formula

=PMT(B3/12,B4*12,-B2)

Expected Result

Approximately ₹19,920.33 per month.

Explanation

  • B3/12 calculates the monthly interest rate.
  • B4*12 calculates the total monthly installments.
  • -B2 uses the loan amount as a negative cash flow so the payment is displayed as positive.

If you change the loan amount, interest rate, or tenure in the input cells, Excel PMT formula recalculates the monthly payment.

Concept Covered

Cell references make financial calculations flexible. You can build a loan EMI calculator by changing only the input values.

Student Practice Tasks

Try calculating the following payments yourself using the PMT function.

  1. A personal loan of ₹3,00,000 at 11% annual interest for 3 years, with monthly payments.
  2. A home loan of ₹25,00,000 at 9% annual interest for 15 years, with monthly payments.
  3. A car loan of ₹6,00,000 at 10% annual interest for 5 years, with monthly payments.
  4. A business loan of ₹8,00,000 at 12% annual interest for 4 years, with quarterly payments.
  5. A loan of ₹4,00,000 at 8% annual interest for 5 years, with annual payments.

Final Student Challenge: Build a Loan EMI Calculator

Create a simple loan EMI calculator in Excel PMT Formula using the following input details.

Loan Details

ParticularsValue
Loan Amount₹7,00,000
Annual Interest Rate10%
Loan Tenure5 Years
Payment FrequencyMonthly

Your Task

  1. Enter the loan amount in cell B2.
  2. Enter the annual interest rate in cell B3.
  3. Enter the loan tenure in years in cell B4.
  4. Calculate the monthly interest rate.
  5. Calculate the total number of monthly installments.
  6. Use PMT to calculate the monthly EMI.

Final Formula

=PMT(B3/12,B4*12,-B2)

Expected Result

Approximately ₹14,873.27 per month.

Your calculator should recalculate the EMI whenever you change the loan amount, interest rate, or loan tenure.

Key Takeaways

  • PMT calculates fixed periodic loan or investment payments.
  • rate represents the interest rate per payment period.
  • nper represents the total number of payment periods.
  • pv represents the present value or loan amount.
  • fv is optional and normally 0 for a fully repaid loan.
  • type controls whether payments happen at the beginning or end of each period.
  • Monthly payments require a monthly interest rate and monthly payment count.
  • A negative loan amount in the formula displays the payment as a positive value.

Frequently Asked Questions

1. What is the PMT function in Excel?

The PMT function calculates the fixed payment required for a loan or investment over a specific period, based on a constant interest rate and payment schedule.

2. What is the full form of PMT?

PMT stands for Payment. It is a financial PMT function used to calculate regular payments.

3. Can PMT calculate monthly EMI?

Yes. Divide the annual interest rate by 12 and multiply the loan tenure in years by 12 to calculate monthly EMI.

4. Why does PMT return a negative value?

Excel uses a cash-flow sign convention. If the loan amount is entered as positive, the payment is generally returned as negative because it represents money going out.

5. What is the difference between rate and nper?

rate is the interest rate per payment period, while nper is the total number of payment periods.

6. What is the purpose of the fv argument?

The fv argument specifies the desired balance after the final payment. For a fully repaid loan, it is normally 0.

7. What is the difference between type 0 and type 1?

Type 0 means payments happen at the end of each period. Type 1 means payments happen at the beginning of each period.

8. Can PMT calculate quarterly payments?

Yes. Divide the annual interest rate by 4 and multiply the loan tenure in years by 4.

9. Does PMT calculate the total interest paid?

No. PMT calculates the regular payment amount. It does not directly return the total interest paid.

10. Does PMT work for a zero-interest loan?

Yes. If the interest rate is zero, PMT divides the loan amount by the total number of payments, assuming the future value is zero.

11. Can PMT calculate a car loan EMI?

Yes. Enter the car loan amount, interest rate, and repayment tenure to calculate the fixed installment.

12. Is PMT available in all modern Excel versions?

Yes. PMT is a long-established financial PMT function and is available in commonly used Excel desktop versions, including Excel 2016, 2019, 2021, 2024, and Microsoft 365.