Introduction
The PPMT function in Excel calculates the principal portion of a loan or investment payment for a specific period. It helps you understand how much of each EMI reduces your outstanding loan balance. In this tutorial, you will learn the PPMT syntax, its arguments, and how to calculate principal payments using practical financial examples.
What Is the PPMT Function in Excel?
PPMT stands for Principal Payment.
When you repay a loan through monthly EMIs, each payment generally contains two components:
- Principal Component: The amount used to reduce the original loan balance.
- Interest Component: The amount charged by the bank for lending you money.
The PPMT function calculates only the principal component for a particular payment period.
For example, suppose you take a loan of ₹1,00,000 at 12% annual interest and repay it over 12 months.
Your monthly interest rate is 1%.
In the first month, your interest is ₹1,000. If your monthly EMI is approximately ₹8,884.88, the principal component is:
₹8,884.88 − ₹1,000 = ₹7,884.88
The PPMT function calculates this principal amount.
Important: PPMT function calculates only the principal portion of a payment. It does not calculate the complete EMI or the interest portion.
PPMT Function Syntax
=PPMT(rate, per, nper, pv, [fv], [type])
The function contains six arguments.
| Argument | Meaning |
|---|---|
| rate | Interest rate for each payment period |
| per | The specific payment period for which you want to calculate principal |
| nper | Total number of payment periods |
| pv | Present value or original loan amount |
| fv | Future value or remaining balance after the final payment (optional) |
| type | Payment timing: 0 for end of period, 1 for beginning of period (optional) |
Understanding Each Argument
1. rate – Interest Rate
This represents the interest rate for one payment period.
For example:
- Annual interest rate = 12%.
- Monthly interest rate = 12% / 12 = 1%.
- Quarterly interest rate = 12% / 4 = 3%.
If your loan payments are monthly, use the monthly interest rate.
2. per – Payment Period
This argument identifies the particular payment period for which you want to calculate the principal amount.
For example:
1means the first payment period.2means the second payment period.3means the third payment period.12means the twelfth payment period.
The value of per must be between 1 and the total number of payment periods.
3. nper – Total Number of Periods
This represents the total number of payments during the loan or investment period.
For example:
- 12 monthly payments = 12 periods.
- 24 monthly payments = 24 periods.
- 5 annual payments = 5 periods.
4. pv – Present Value
Present Value represents the current loan amount or investment amount.
For example, if a bank provides a loan of ₹1,00,000, the PV is ₹1,00,000.
5. fv – Future Value
This is the balance you want to have after the final payment.
For a fully repaid loan, FV is generally 0.
This argument is optional. If omitted, Excel assumes FV is 0.
6. type – Payment Timing
This argument tells Excel when payments are 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.
How Does PPMT Function Calculate the Principal Component?
Let’s understand the calculation using a simple loan.
Suppose:
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 12 Months |
| Monthly EMI | Approximately ₹8,884.88 |
During the first month:
- Interest component = ₹1,000
- Principal component = ₹7,884.88
- Total EMI = ₹8,884.88
The principal component reduces the outstanding loan balance.
After the first payment, the remaining principal becomes approximately:
₹1,00,000 − ₹7,884.88 = ₹92,115.12
In the second month, the principal component increases because the interest charged on the reduced balance is lower.
| Payment Period | Principal Component (Approx.) |
|---|---|
| Month 1 | ₹7,884.88 |
| Month 2 | ₹7,963.73 |
| Month 3 | ₹8,043.37 |
| Month 4 | ₹8,123.80 |
| Month 5 | ₹8,205.04 |
Notice that the principal portion increases with each payment in this typical fixed-payment, reducing-balance loan.
Important: Excel generally displays PPMT results as negative amounts when the loan amount (PV) is positive. This represents money paid by the borrower.
Practical Examples of PPMT Function in Excel
Let’s understand PPMT Function through practical examples from personal loans, car loans, business finance, and payment schedules.
Question 1: Calculate the Principal Component of the First EMI
Problem Statement
A customer takes a personal loan of ₹1,00,000 from a bank. The annual interest rate is 12%, and the loan must be repaid over 12 months.
Calculate the principal portion of the first monthly EMI.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 12 Months |
| Payment Period | 1 |
Excel Formula
=PPMT(1%,1,12,100000)
Expected Result
-₹7,884.88 (approximately)
Explanation
1%is the monthly interest rate.1represents the first payment period.12represents the total monthly payments.100000is the original loan amount.
Excel calculates approximately ₹7,884.88 as the principal portion of the first EMI.
The negative sign indicates an outgoing payment.
Concept Covered: Calculating how much of the first EMI reduces the loan balance.
Question 2: Calculate the Principal Component of the Second EMI
Problem Statement
A company employee has taken a loan of ₹1,00,000 at 12% annual interest. The loan duration is 12 months.
Calculate the principal component of the second monthly payment.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,00,000 |
| Monthly Interest Rate | 1% |
| Total Periods | 12 |
| Payment Period | 2 |
Excel Formula
=PPMT(1%,2,12,100000)
Expected Result
-₹7,963.73 (approximately)
Explanation
The second month’s principal component is higher than the first month’s principal component.
This happens because the outstanding loan balance has reduced after the first payment. As a result, less of the second EMI goes toward interest and more goes toward principal.
Concept Covered: Understanding how the principal portion changes during loan repayment.
Question 3: Calculate the Principal Component of the Sixth EMI
Problem Statement
A customer takes a car loan of ₹3,00,000 at an annual interest rate of 10.8%. The loan is repayable through 36 monthly payments.
Calculate the principal portion of the sixth EMI.
Data Table
| Particulars | Value |
|---|---|
| Car Loan | ₹3,00,000 |
| Annual Interest Rate | 10.8% |
| Monthly Interest Rate | 0.9% |
| Loan Duration | 36 Months |
| Payment Period | 6 |
Excel Formula
=PPMT(0.9%,6,36,300000)
Expected Result
-₹6,151.42 (approximately)
Explanation
The monthly interest rate is 0.9%, and the total loan period is 36 months.
The value 6 tells Excel to calculate the principal component for the sixth payment period.
Excel returns approximately ₹6,151.42 as the principal portion of the sixth EMI.
Concept Covered: Calculating the principal repayment for a selected EMI in a car loan.
Question 4: Calculate the Principal Component of the Twelfth EMI
Problem Statement
A business takes a loan of ₹5,00,000 at an annual interest rate of 12%. The loan is repayable over 60 months.
Calculate the principal component of the twelfth monthly payment.
Data Table
| Particulars | Value |
|---|---|
| Business Loan | ₹5,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | 1% |
| Loan Duration | 60 Months |
| Payment Period | 12 |
Excel Formula
=PPMT(1%,12,60,500000)
Expected Result
-₹6,155.40 (approximately)
Explanation
The first payment period has a higher interest component because interest is calculated on the original loan balance.
As payments continue, the outstanding principal reduces. Therefore, the principal portion of each fixed EMI generally increases.
For the twelfth payment period, PPMT Function calculates approximately ₹6,155.40.
Concept Covered: Finding the principal portion of a payment after several months of loan repayment.
Question 5: Calculate the Principal Component of the First Quarterly Payment
Problem Statement
A business takes a loan of ₹2,00,000 at an annual interest rate of 12%. The business makes quarterly payments over 3 years.
Calculate the principal portion of the first quarterly payment.
Data Table
| Particulars | Value |
|---|---|
| Business Loan | ₹2,00,000 |
| Annual Interest Rate | 12% |
| Quarterly Interest Rate | 3% |
| Loan Duration | 3 Years |
| Total Quarterly Payments | 12 |
| Payment Period | 1 |
Excel Formula
=PPMT(3%,1,12,200000)
Expected Result
-₹14,093.50 (approximately)
Explanation
The business makes quarterly payments, so the annual interest rate is divided by four.
12% / 4 = 3%
The first quarter’s interest is ₹6,000. The remaining portion of the quarterly payment goes toward reducing the principal.
PPMT Function calculates approximately ₹14,093.50 as the principal component.
Concept Covered: Calculating principal repayment when payments are made quarterly.
Question 6: Calculate the Principal Component When Payments Are Made at the Beginning
Problem Statement
A company takes a loan of ₹1,50,000 at a monthly interest rate of 1%. The loan is repayable over 24 months, and payments are made at the beginning of each month.
Calculate the principal component of the first payment period.
Data Table
| Particulars | Value |
|---|---|
| Loan Amount | ₹1,50,000 |
| Monthly Interest Rate | 1% |
| Loan Duration | 24 Months |
| Payment Period | 1 |
| Payment Timing | Beginning of Month |
Excel Formula
=PPMT(1%,1,24,150000,0,1)
Expected Result
-₹6,250.00 (approximately)
Explanation
The final argument is 1, which tells Excel that payments are made at the beginning of each month.
The first payment is made immediately, before interest accumulates during the first month.
Therefore, the first payment goes entirely toward reducing the principal.
Concept Covered: Understanding how beginning-of-period payments affect the principal component.
Question 7: Calculate the Principal Component Using Cell References
Problem Statement
A finance department maintains loan details in Excel. The team wants to calculate the principal component for a selected payment period without changing the formula manually.
Create the following table and calculate the principal amount using cell references.
Data Table
| Particulars | Cell | Value |
|---|---|---|
| Loan Amount | B2 | ₹2,00,000 |
| Annual Interest Rate | B3 | 12% |
| Monthly Interest Rate | B4 | 1% |
| Total Periods | B5 | 36 |
| Selected Payment Period | B6 | 10 |
Excel Formula
=PPMT(B4,B6,B5,B2)
Expected Result
-₹5,479.58 (approximately)
Explanation
The formula uses cell references instead of fixed numbers.
B4contains the monthly interest rate.B6contains the selected payment period.B5contains the total number of payments.B2contains the original loan amount.
You can change the selected payment period in B6 to calculate the principal component for another month.
Concept Covered: Creating a flexible principal repayment calculator using cell references.
Student Practice Tasks
Try solving these questions using the PPMT function.
Task 1: A person takes a loan of ₹2,00,000 at an annual interest rate of 12% for 24 months. Calculate the principal component of the third EMI.
Task 2: A customer takes a car loan of ₹4,00,000 at an annual interest rate of 9.6% for 48 months. Calculate the principal component of the tenth EMI.
Task 3: A business takes a loan of ₹3,00,000 at an annual interest rate of 8% and makes quarterly payments over 4 years. Calculate the principal component of the second quarterly payment.
Task 4: A person takes a loan of ₹1,20,000 at a monthly interest rate of 1% for 12 months. Calculate the principal component of the last EMI.
Final Student Challenge: Build a Loan Principal Calculator
Create the following Excel table:
| Particulars | Value |
|---|---|
| Loan Amount | ₹5,00,000 |
| Annual Interest Rate | 12% |
| Monthly Interest Rate | Calculate manually as Annual Rate / 12 |
| Loan Duration | 60 Months |
| Selected Payment Period | 1 |
Your Tasks
- Enter all loan details in Excel.
- Calculate the monthly interest rate.
- Use the PPMT function to calculate the principal component for the first month.
- Change the selected payment period to 12.
- Calculate the principal component for month 12.
- Change the selected payment period to 36.
- Compare the principal amounts for months 1, 12, and 36.
Challenge Goal: Create a simple calculator that shows how the principal portion of a loan payment changes over time.
Key Takeaways
- PPMT stands for Principal Payment.
- It calculates the principal portion of a payment for a specific period.
- The syntax is
=PPMT(rate,per,nper,pv,[fv],[type]). - The
rateargument represents the interest rate for each payment period. - The
perargument identifies the payment period. - The
nperargument represents the total number of payment periods. - The
pvargument represents the original loan or investment amount. - Type 0 represents payments at the end of each period.
- Type 1 represents payments at the beginning of each period.
- PPMT Function calculates only the principal component, not the complete EMI.
- For a typical fixed-payment, reducing-balance loan, the principal component generally increases as the loan is repaid.
Frequently Asked Questions (FAQs)
1. What is the PPMT function in Excel?
The PPMT function calculates the principal portion of a loan or investment payment for a specific period.
2. What is the full form of PPMT Function in Excel?
PPMT stands for Principal Payment.
3. What is the syntax of the PPMT function?
The syntax is =PPMT(rate,per,nper,pv,[fv],[type]).
4. What does the per argument mean?
The per argument specifies the payment period for which you want to calculate the principal amount, such as the first, second, or twelfth month.
5. Can PPMT calculate the principal portion of a monthly EMI?
Yes. Use the monthly interest rate, total number of monthly payments, and the required payment period.
6. Why does PPMT Function return a negative value?
Excel generally returns a negative value because the principal repayment is treated as money paid by the borrower. This follows Excel’s cash-flow sign convention.
7. What is the difference between PMT and PPMT Function?
PMT calculates the complete periodic payment, including interest and principal. PPMT calculates only the principal portion.
8. What is the difference between IPMT and PPMT Function?
PPMT calculates the principal portion of a payment, whereas IPMT calculates the interest portion.
9. What happens if the type argument is 0?
Type 0 means payments are made at the end of each payment period. This is also the default setting.
10. What happens if the type argument is 1?
Type 1 means payments are made at the beginning of each payment period.
11. Can PPMT Function calculate quarterly principal payments?
Yes. Use the quarterly interest rate and the total number of quarterly payments.
12. Can PPMT Function calculate the principal component of the last EMI?
Yes. Enter the total number of payment periods in the per argument to calculate the principal component of the final period.

