PPMT
ThePPMT
function calculates the payment on the principal of an investment or loan given the interest rate, number of periods, and present value. It is commonly used in financial analysis to calculate the principal portion of a loan or investment payment. The function returns a negative number because it represents a payment toward the principal balance, which reduces the overall balance.
 How to use
PPMT
formula?  Examples of using
PPMT
formula PPMT
formula not working? Similar formulas to
PPMT
Usage
Use the PPMT
formula with the syntax shown below, it has 4 required parameters and 2 optional parameters:
 rate (required):
The interest rate per period of the loan or investment.  period (required):
The period for which to calculate the payment. Must be between 1 and the total number of periods.  number_of_periods (required):
The total number of payment periods for the loan or investment.  present_value (required):
The present value, or the total amount of the loan or investment.  future_value (optional):
The future value, or the cash balance desired after the final payment. If omitted, the default is 0.  end_or_beginning (optional):
The timing of the payments. If omitted, the default is 0, which represents the end of the period. Use 1 to indicate the beginning of the period.
Examples
Here are a few example use cases that explain how to use thePPMT
formula in Google Sheets.
Calculating loan payments
Use PPMT
to determine the principal portion of a loan payment. For example, if you have a $100,000 loan with a 5% interest rate and 360 monthly payments, you can calculate the principal portion of the 120th payment with =PPMT(5%/12, 120, 360, 100000)
.
Analyzing investment returns
Use PPMT
to calculate the principal portion of an investment payment. For example, if you invest $10,000 with a 6% annual interest rate and monthly payments over 5 years, you can calculate the principal portion of the 36th payment with =PPMT(6%/12, 36, 60, 10000)
.
Amortization schedules
Use PPMT
to create an amortization schedule for a loan or investment. By combining PPMT
with other financial functions like PMT
, IPMT
, and CUMIPMT
, you can build a comprehensive schedule that shows the payment breakdown for each period.
Common Mistakes
PPMT
not working? Here are some common mistakes people make when using the PPMT
Google Sheets Formula:
Incorrect order of arguments
One common mistake is entering the arguments in the wrong order. It's important to make sure that the rate, period, number_of_periods, present_value, future_value (if applicable) and end_or_beginning (if applicable) are entered in the correct order.
Missing arguments
Another common mistake is leaving out one or more required arguments. Make sure to include all required arguments and to enclose optional arguments in square brackets.
Using the wrong sign
A common mistake is using the wrong sign for one or more arguments. Make sure to use a negative sign for present_value and future_value if they represent cash paid out, and a positive sign if they represent cash received.
Not adjusting for compounding periods
If the rate argument is expressed as an annual percentage rate (APR) but the compounding period is not annual, the result will be incorrect. Make sure to adjust the rate for the compounding period using the appropriate formula.
Incorrect time period
If the period argument is not expressed in the same time period as the rate argument (e.g. monthly rate with an annual period), the result will be incorrect. Make sure to adjust the time period using the appropriate formula.
Related Formulas
The following functions are similar to PPMT
or are often used with it in a formula:

PMT
The
PMT
formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments. 
IPMT
The
IPMT
function calculates the interest payment for a given period of an investment or loan. It is commonly used to determine the interest portion of a loan payment. This function can be helpful when creating loan amortization schedules or when analyzing the cost of borrowing money. 
CUMIPMT
The
CUMIPMT
function calculates the cumulative interest paid on a loan between two periods. This function can be useful for tracking interest payments and determining the total amount of interest paid over the life of a loan. 
CUMPRINC
The
CUMPRINC
function calculates the cumulative principal paid on a loan between two periods. It is commonly used in financial analysis to determine the total principal paid on a loan during a specific time period. The function uses the declining balance method to calculate the interest and principal payments on a loan. 
FV
The
FV
function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.
Learn More
You can learn more about the PPMT
Google Sheets function on Google Support.