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
| Argument | Meaning |
|---|---|
rate | Interest rate for each payment period. |
nper | Total number of payment periods. |
pv | Present 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.
| Type | Meaning |
|---|---|
| 0 | Payment at the end of each period |
| 1 | Payment 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
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Annual Interest Rate | 12% |
| Loan Tenure | 5 Years |
| Payments | Monthly |
Excel Formula
=PMT(12%/12,5*12,-500000)
Expected Result
Approximately ₹11,122.22 per month.
Explanation
12%/12converts the annual interest rate into a monthly rate.5*12calculates 60 monthly installments.-500000represents 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
| Particulars | Value |
|---|---|
| Loan Amount | ₹30,00,000 |
| Annual Interest Rate | 8.5% |
| Loan Tenure | 20 Years |
| Payments | Monthly |
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
| Particulars | Value |
|---|---|
| Loan Amount | ₹8,00,000 |
| Annual Interest Rate | 9% |
| Loan Tenure | 4 Years |
| Payments | Monthly |
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
| Particulars | Value |
|---|---|
| Loan Amount | ₹10,00,000 |
| Annual Interest Rate | 10% |
| Loan Tenure | 3 Years |
| Payments | Quarterly |
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
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Annual Interest Rate | 8% |
| Loan Tenure | 5 Years |
| Payments | Yearly |
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
| Particulars | Value |
|---|---|
| Loan Amount | ₹2,00,000 |
| Annual Interest Rate | 12% |
| Loan Tenure | 3 Years |
| Payments | Monthly |
| Payment Timing | Beginning 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
| Cell | Particulars | Value |
|---|---|---|
| B2 | Loan Amount | ₹12,00,000 |
| B3 | Annual Interest Rate | 10% |
| B4 | Loan Tenure (Years) | 7 |
Excel Formula
=PMT(B3/12,B4*12,-B2)
Expected Result
Approximately ₹19,920.33 per month.
Explanation
B3/12calculates the monthly interest rate.B4*12calculates the total monthly installments.-B2uses 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.
- A personal loan of ₹3,00,000 at 11% annual interest for 3 years, with monthly payments.
- A home loan of ₹25,00,000 at 9% annual interest for 15 years, with monthly payments.
- A car loan of ₹6,00,000 at 10% annual interest for 5 years, with monthly payments.
- A business loan of ₹8,00,000 at 12% annual interest for 4 years, with quarterly payments.
- 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
| Particulars | Value |
|---|---|
| Loan Amount | ₹7,00,000 |
| Annual Interest Rate | 10% |
| Loan Tenure | 5 Years |
| Payment Frequency | Monthly |
Your Task
- Enter the loan amount in cell B2.
- Enter the annual interest rate in cell B3.
- Enter the loan tenure in years in cell B4.
- Calculate the monthly interest rate.
- Calculate the total number of monthly installments.
- 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.
raterepresents the interest rate per payment period.nperrepresents the total number of payment periods.pvrepresents the present value or loan amount.fvis optional and normally 0 for a fully repaid loan.typecontrols 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.

