top of page

MS Excel: PPMT function to calculate loans / mortgages / installment

  • Writer: Fakhriddinbek
    Fakhriddinbek
  • Apr 29
  • 2 min read

If you're working with loans, mortgages, or installment plans, Excel’s PPMT function helps break down your payments and show how much of each one goes toward the principal—not just the total payment.


Two Excel function argument windows for PPMT formula displayed on a spreadsheet. Tabs, icons, and menus visible in the toolbar.

Let's explore how it works step by step, from the basics to more advanced, real-world uses.


The PPMT function calculates the principal portion of a loan payment for a given period, based on constant interest and fixed payments.


Syntax:


=PPMT(rate, per, nper, pv, [fv], [type])


Argument

Description

rate

Interest rate per period

per

Payment period (e.g., 1 for first month)

nper

Total number of periods

pv

Present value (loan amount)

fv

[Optional] Future value (default = 0)

type

[Optional] 0 = end of period (default), 1 = beginning


Example 1 – Basic Monthly Loan Calculation


Imagine you take a $10,000 loan with 10% annual interest, paid over 12 months.


=PPMT(10%/12, 1, 12, -10000)


Returns –$791.59


This is the principal part of your first monthly payment.


The loan amount is entered as negative (-10000) because it's an outgoing payment (money you owe).


To find the interest part, use the IPMT function:


=IPMT(10%/12, 1, 12, -10000)


To find the total payment, use PMT:


=PMT(10%/12, 12, -10000)


This shows you how much of each payment goes to:

  • Principal (PPMT)

  • Interest (IPMT)

  • Combined (PMT)


Example 2 – View Payment Breakdown Over Time


Set up a payment schedule:


Period

Principal (PPMT)

Interest (IPMT)

Total Payment (PMT)

1

=PPMT(rate,1,12,-10000)

=IPMT(rate,1,12,-10000)

=PMT(rate,12,-10000)

2

=PPMT(rate,2,12,-10000)

=IPMT(rate,2,12,-10000)

=PMT(rate,12,-10000)

...

...

...

...


As months go by:

  • PPMT increases (you pay more principal)

  • IPMT decreases (less interest is due)

This reflects how amortization works in real loans.


Advanced Usage: Start Payments at Beginning of Period


Add the [type] argument to make payments at the start of each period:


=PPMT(10%/12, 1, 12, -10000, 0, 1)


This slightly changes the numbers, since interest is calculated differently.


Use Case: Loan Tracker or Amortization Table


Set up a dynamic table using:

  • PPMT() for each period’s principal

  • IPMT() for each period’s interest

  • PMT() for total

  • A running balance formula:


=Previous Balance - Current Principal


Combine these in Excel with dropdowns or sliders (via Data Validation) to create an interactive loan planner.


Summary Table


Function

Purpose

PPMT

Principal part of payment

IPMT

Interest part of payment

PMT

Total periodic payment


Final Thoughts


The PPMT function is ideal for:


  • Financial modeling

  • Loan tracking

  • Understanding amortization

  • Creating dashboards


Use it with PMT, IPMT, and optional arguments (fv, type) for complete flexibility.

Wrap PPMT in ROUND() or ABS() if you want cleaner numbers or positive values.


Comments


bottom of page