What Is PPMT Function In Google Sheets?
The Periodic Payment or the PPMT function in Google Sheets calculates the principal part/portion of the total loan amount. It helps users calculate the initial, final, or any periodic principal amount. When using the Google Sheets PPMT function we must also ensure to divide the interest based on annual, semi-annual, quarterly, monthly, or weekly period payments.
For example, we will use the PPMT function in Google Sheets to calculate the principal amount of the loan amount of ‘$20,000’ and the period is “12 years” at ‘10%’ of the interest rate.
Select cell B5, enter the formula =PPMT(B4,1,B3,-B2) and press the “Enter” key.
The result is ‘$935.27’, as shown above.
Table of Contents
Key Takeaways
- The PPMT Function in Google Sheets helps us calculate principal part of the total loan amount in annual, semi-annual, quarterly, monthly or weekly periods.
- In aperiodic loan payment, PMT is the total amount (interest amount + principal amount), IPMT is the interest portion of the total amount, and PPMT is the principal portion of the total amount.
- The present_value of the loan amount is entered as negative because it is the amount that gets deducted periodically.
- The input rate has to be consistent. If the payments are made quarterly, it will convert the annual interest rate into the quarterly rate (rate%/4), and the period number has to be converted from years to quarters (=per*4).
PPMT() Google Sheets Formula
The syntax of the PPMT Google Sheets formula is,
The arguments of the PPMT Google Sheets formula are,
- rate: It is the rate of interest on the loan.
- period: It is the total maturity date.
- number_of_periods: It is the total payment period.
- present_value: It is the present value of the investment.
The above-mentioned four arguments are mandatory.
- [future_value]: It is an optional argument. It is the future value of the investment.
- [end_or_beginning]: It is an optional argument. It specifies whether the payment is made at the beginning or the end of the loan period.
How To Use PPMT Google Sheets Function?
We can use the PPMT function in Google Sheets in 2 ways, namely,
- Access from the Google Sheets ribbon
- Enter in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon 🠖
Choose an empty cell for the output 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “Financial” option right arrow 🠖 à select the “PPMT” function, as shown below.
The “PPMT” formula appears, as shown below. Enter the arguments as the cell reference.
Method #2 – Enter in the worksheet manually 🠖
- Select an empty cell for the output.
- Type =PPMT( in the selected cell. [Alternatively, type =P or =PP and double-click the PPMT function in Google Sheets from the list of suggestions shown by Google Sheets.]
- Enter the arguments as cell values or cell references and close the brackets.
- Press the “Enter” key.
Examples
We will understand some advanced scenarios using the PPMT function in Google Sheets examples.
Example #1
Mr. Raj Mohan has taken a loan of $500,000 for five years, and the interest rate is 4%. We will calculate his monthly PPMT, using PPMT in Google Sheets.
In the table, the data is,
- Column A contains a Description.
- Column B contains Amount.
The steps to calculate the PPMT using PPMT function in Google Sheets are,
Step 1: Select cell B7 and enter the formula =PPMT(B4/12, i.e., the rate of interest value.
Step 2: Enter the value of ‘period’ as “1”. The formula now is =PPMT(B4/12,1
Step 3: Enter the value of ‘number_of_periods’ as “B3*12”, i.e., the monthly period value. The formula now is =PPMT(B4/12,1,B3*12
Step 4: Enter the value of ‘present_value’ as “-B2”. The formula nowis =PPMT(B4/12,1,B3*12,-B2
Step 5: Enter the value of ‘future_value’ as “B5”. The formula now is =PPMT(B4/12,1,B3*12,-B2,B5
Step 6: Enter the value of ‘[end_or_beginning]’ as “0”, i.e., the payment type, as shown below.
Step 7: Now, close the brackets andpress the “Enter” key. The complete formula is =PPMT(B4/12,1,B3*12,-B2,B5,0)
The result is “$7,541.59”, as shown above.
Example #2
Mrs. Lorence has borrowed $80,000 for two years, and the interest rate is 15%. We will calculate his quarterly PPMT using the PPMT function in Google Sheets.
In the table, the data is,
- Column A contains a Description.
- Column B contains Amount.
The procedure to calculate the PPMT using the PPMT function in Google Sheets is,
Select cell B7, enter the formula =PPMT(B4/4,1,B3*4,-B2,B5,0) andpress the “Enter” key.
[Note: The ‘rate’ is “B4/4” and the ‘number_of_periods’ is “B3*4”, since it is the quarterly period value.]
The result is “$8,759.87”, as shown above.
Example #3
XYZ company has taken a loan of $2,500,000 for five years; the interest rate is 14%. We will calculate the company’s weekly PPMT using the PPMT function in Google Sheets.
In the table, the data is,
- Column A contains a Description.
- Column B contains Amount.
The procedure to calculate the PPMT using the PPMT function in Google Sheets is,
Select cell B7, enter the formula =PPMT(B4/52,1,B3*52,-B2,B5,0) andpress the “Enter” key.
[Note: The ‘rate’ is “B4/52” and the ‘number_of_periods’ is “B3*52”, as it is the weekly period value.]
The result is “$6,651.88”, as shown above.
Example #4
Ms. Kathrine has taken a loan of $200,000 for five years, and the interest rate is 8%. We will calculate her semi-annual PPMT using the PPMT function in Google Sheets.
In the table, the data is,
- Column A contains a Description.
- Column B contains Amount.
The procedure to calculate the PPMT using the PPMT function in Google Sheets is,
Select cell B7, enter the formula =PPMT(B4/2,1,B3*2,-B2,B5,0) andpress the “Enter” key.
[Note: The ‘rate’ is “B4/2” and the ‘number_of_periods’ is “B3*2”, since it is the semi-annual period value.]
The result is “$16,658.19”, as shown above.
Example #5
For example, we apply the PPMT function in Google Sheets to calculate the principal amount of the loan amount of ‘$100,000’, and the period is “5 years” at ‘3%’ of the interest rate.
Select cell B5, enter the formula =PPMT(B4/12,2,B3*12,-B2), and press the “Enter” key.
The result is ‘$1,550.74’, as shown above.
Important Things To Note
- The “#VALUE!” error occurs when:
- The values in the PPMT formula are non-numeric.
- The cell reference is incorrect.
- We don’t divide the interest based on annual, semi-annual, quarterly, monthly, or weekly period payments.
- The “#NUM! error” occurs if the “period” value is less than 0 or greater than the “number_of_periods” value.
- The type argumentvalue will be either 0 or 1 for the payment made at the end or beginning of the period, respectively. If no value is provided, by default, it will be taken as 0.
Frequently Asked Questions
We often forget in which category a function falls, here, the “PPMT” function. Then, we can insert the function as follows:
Choose an empty cell 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “All” option right arrow 🠖 select the “PPMT” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the PPMT function by following the path shown below.
• Choose an empty cell 🠖 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🠖 click the “All” option right arrow 🠖 select the “PPMT” function, as shown below.
The PPMT may not work due to the following reasons.
1. If the values in the PPMT formula are non-numeric.
2. If the cell reference is incorrect.
3. When the interest is not divided based on annual, semi-annual, quarterly, monthly, or weekly period payments.
4. If the “period” value is less than 0 or greater than the “number_of_periods” value.
5. If the loan amount selected is not in proper format.
For example, consider the example 5 data. The amount is $100,000 [International format]. However, if we change the value to $1,00,000 [Indian Format], then, the value is considered as text and we will get “#VALUE!” error, as shown below.
Download Template
This article must help understand the PPMT function in Google Sheets formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to PPMT function in Google Sheets. We learn its formula and use it to find the principal portion of a loan amount with examples. You can learn more from the following articles. –
Leave a Reply