PPMT 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
- Examples of using
PPMTformula not working?
- Similar formulas to
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.
ExamplesHere are a few example use cases that explain how to use the
PPMT formula in Google Sheets.
Calculating loan payments
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
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).
PPMT to create an amortization schedule for a loan or investment. By combining
PPMT with other financial functions like
CUMIPMT, you can build a comprehensive schedule that shows the payment breakdown for each period.
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.
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.
The following functions are similar to
PPMT or are often used with it in a formula:
PMTformula 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.
IPMTfunction 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.
CUMIPMTfunction 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.
CUMPRINCfunction 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.
FVfunction 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.
You can learn more about the
PPMT Google Sheets function on Google Support.