The PPMT function in Excel is used to calculate the principal portion of a payment for a given period of an investment or a loan. Here’s how you can use the PPMT function:
Syntax
PPMT(rate, per, nper, pv, [fv], [type])
Parameters
- rate: The interest rate for the loan or investment per period.
- per: The period for which you want to find the principal payment (must be between 1 and nper).
- nper: Total number of payment periods.
- pv: Present value, or the total amount that a series of future payments is worth now. For a loan, this is the principal.
- [fv] (optional): Future value, or a cash balance you want to attain after the last payment is made. If omitted, it’s assumed to be 0.
- [type] (optional): Specify when payments are due. Use 0 if payments are due at the end of the period (default) and 1 if they are due at the beginning.
Steps to Use PPMT
=PPMT(5%/12, 1, 5*12, 10000)
- Open Excel and navigate to the cell where you want to calculate the principal payment.
- Enter the PPMT formula in that cell with all required parameters. For example, to calculate the principal payment of the 1st period on a loan of $10,000 with an annual interest rate of 5% to be paid monthly over 5 years:
- Here, `5%/12` is the monthly interest rate, `1` is the first period, `5*12` is the total number of monthly payments, and `10000` is the principal of the loan.
- Press Enter to calculate the result.
Example
If you have a loan with the following details:
- Annual interest rate: 6%
- Number of years to repay: 10
- Loan amount: $100,000
- Payments made at the end of each month
To calculate the principal payment of the second month, you can enter the formula:
=PPMT(6%/12, 2, 10*12, 100000)
Additional Tips
=PPMT(B1/12, B2, B3*12, B4)
- Make sure to divide the annual interest rate by the number of periods per year if dealing with monthly or quarterly payments.
- Ensure the `per` parameter is within the total number of payment periods `nper`.
- Use cell references for arguments if you want to make your formula more dynamic and easier to update. For instance:
where `B1` contains the annual interest rate, `B2` the period for which you want the payment, `B3` the number of years, and `B4` the loan amount.
Unlock Your Potential
Excel
Access
Power BI
