PPMT Function in Excel Tutorial

Excel Tutorial

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:

  1. Principal Component: The amount used to reduce the original loan balance.
  2. 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.

ArgumentMeaning
rateInterest rate for each payment period
perThe specific payment period for which you want to calculate principal
nperTotal number of payment periods
pvPresent value or original loan amount
fvFuture value or remaining balance after the final payment (optional)
typePayment 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:

  • 1 means the first payment period.
  • 2 means the second payment period.
  • 3 means the third payment period.
  • 12 means 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.

TypeMeaning
0Payment at the end of each period
1Payment 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:

ParticularsValue
Loan Amount₹1,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration12 Months
Monthly EMIApproximately ₹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 PeriodPrincipal 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

ParticularsValue
Loan Amount₹1,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration12 Months
Payment Period1

Excel Formula

=PPMT(1%,1,12,100000)

Expected Result

-₹7,884.88 (approximately)

Explanation

  • 1% is the monthly interest rate.
  • 1 represents the first payment period.
  • 12 represents the total monthly payments.
  • 100000 is 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

ParticularsValue
Loan Amount₹1,00,000
Monthly Interest Rate1%
Total Periods12
Payment Period2

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

ParticularsValue
Car Loan₹3,00,000
Annual Interest Rate10.8%
Monthly Interest Rate0.9%
Loan Duration36 Months
Payment Period6

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

ParticularsValue
Business Loan₹5,00,000
Annual Interest Rate12%
Monthly Interest Rate1%
Loan Duration60 Months
Payment Period12

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

ParticularsValue
Business Loan₹2,00,000
Annual Interest Rate12%
Quarterly Interest Rate3%
Loan Duration3 Years
Total Quarterly Payments12
Payment Period1

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

ParticularsValue
Loan Amount₹1,50,000
Monthly Interest Rate1%
Loan Duration24 Months
Payment Period1
Payment TimingBeginning 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

ParticularsCellValue
Loan AmountB2₹2,00,000
Annual Interest RateB312%
Monthly Interest RateB41%
Total PeriodsB536
Selected Payment PeriodB610

Excel Formula

=PPMT(B4,B6,B5,B2)

Expected Result

-₹5,479.58 (approximately)

Explanation

The formula uses cell references instead of fixed numbers.

  • B4 contains the monthly interest rate.
  • B6 contains the selected payment period.
  • B5 contains the total number of payments.
  • B2 contains 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:

ParticularsValue
Loan Amount₹5,00,000
Annual Interest Rate12%
Monthly Interest RateCalculate manually as Annual Rate / 12
Loan Duration60 Months
Selected Payment Period1

Your Tasks

  1. Enter all loan details in Excel.
  2. Calculate the monthly interest rate.
  3. Use the PPMT function to calculate the principal component for the first month.
  4. Change the selected payment period to 12.
  5. Calculate the principal component for month 12.
  6. Change the selected payment period to 36.
  7. 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 rate argument represents the interest rate for each payment period.
  • The per argument identifies the payment period.
  • The nper argument represents the total number of payment periods.
  • The pv argument 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.