How to Use PPMT Function in Excel (Find Principal Payment)

Hello friends! Today I show you PPMT() function in Excel. It is the special brother of IPMT!

What is PPMT? It stand for "Principal Payment". When you pay $200 for your car loan every month, some money is interest (IPMT) and some money actually pays off the car price! PPMT helps you see exactly how much money pays off the real loan each month!

🛠️ How to write PPMT Function

To use PPMT, you need to tell Excel: the interest rate, WHICH month you want to check, total months, and the total loan amount. It is exactly the same as IPMT!

Formula Syntax:

=PPMT(rate, per, nper, pv)

• rate: Interest rate from bank (Must divide by 12 for monthly rate!).
• per: WHICH month you want to check? (Example: 1 for first month, 5 for fifth month).
• nper: Total number of months you will pay. (Example: 5 years = 60 months).
• pv: Present Value. Total loan money you borrow right now.

Check out my Advance Loan Calculator Tool to calculate loan payment easily!

📊 Short & Clear Example

Imagine you buy a car for $10,000 (This is PV). Bank charge 6% interest per year (This is Rate). You will pay for 5 years (This is NPER).

You want to know: How much real loan do I pay off in the very FIRST month?

Formula to find Principal for Month 1:

=PPMT(B1/12, B2, B3*12, B4)

• Result: -$143.33
• What it means? It means in your very first month, even if your total payment is $193.33, only $143.33 goes to pay off the car! The rest is interest!
• Why divide and multiply by 12? Because we check monthly! Rate 6% divide by 12, and 5 years multiply by 12 (60 total months).

💡 Easy Pro Tips

1. Principal goes UP over time: If you change per to month 60 (the very last month): =PPMT(B1/12, 60, B3*12, B4). The result is -$192.37! As you get closer to finishing the loan, more of your money goes to the principal!
2. Make it Positive: The result shows minus (-) because it is money you pay. If you want a positive number, just put a minus sign before the PV: =PPMT(B1/12, B2, B3*12, -B4).
3. The Magic Formula: Remember this always: PPMT (Principal) + IPMT (Interest) = PMT (Total Monthly Payment). Try adding them together!

📚 Useful Resources

• How to Use IPMT Function in Excel (Find Interest Payment)
• How to Use PMT Function in Excel (Calculate Loan Easy)
• How to Use PV Function in Excel (Find Present Value)