PPMT

• 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:

=PPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])

1. rate (required):
   The interest rate per period of the loan or investment.
2. period (required):
   The period for which to calculate the payment. Must be between 1 and the total number of periods.
3. number_of_periods (required):
   The total number of payment periods for the loan or investment.
4. present_value (required):
   The present value, or the total amount of the loan or investment.
5. future_value (optional):
   The future value, or the cash balance desired after the final payment. If omitted, the default is 0.
6. 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

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

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.