How to Use the PMT Function in Google Sheets

As a go-to spreadsheet tool, Google Sheets offers formulas for data import, transformation, and analysis. What’s more, it includes a set of easy-to-use finance formulas for each type of data operation.

To import data, you can use the GOOGLEFINANCE or YAHOOF to directly import stock information into Google Sheets. As for data analysis tasks, Google Sheets also offers the FORECAST function to predict future values as well as the PMT function.

In this article, you’ll gain a full understanding of what the PMT formula is and what it’s used for, the PMT formula syntax in Google Sheets. You’ll also learn how to use the PMT function to calculate weekly, monthly, quarterly, and semi-annual payments to settle a loan.

What is the PMT formula?

The PMT formula is a type of financial function used in Excel and Google Sheets. It’s used to calculate the total payment (principal and interest) required to settle a loan or investment based on periodic payments that have a fixed interest rate for a given period.

For example, let’s say you want to invest an amount or ask for a loan. By calculating the payments required to pay back the loan, or investment, you’ll know how much you’ll end up paying in the end. To calculate this, you’ll need information on the loan amount, the interest rate, and the number of payments.

The PMT Function in Google Sheets

Just like in Excel, you can use the PMT function to calculate how much you’ll need to pay on a monthly basis to settle a loan. Let’s see how the PMT formula is structured to better understand what it can do.

The PMT Formula Syntax

=PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])

Below, you’ll learn about each argument that makes up the formula, compulsory and optional:

• rate is a compulsory argument as it refers to the interest rate.
• number_of_periods is a compulsory argument that defines the number of payments that you’ll need to make. These periods correspond to months.
• present_value is the third compulsory argument that describes the current value of the annuity, i.e., the number of payments that you’ll make at equal intervals (monthly mortgage, insurance, pension, etc.).
• future_value is an optional argument that expresses the value that will remain after having paid the final payment.
• end_or_beginning is an optional argument that indicates if the payments are due at the end (0) or beginning (1) of each established period.

How to use the PMT function in Google Sheets?

To apply the PMT function, you need to know the loan amount, interest rate, and months needed to settle the loan. This is how to use the PMT function in Google Sheets.

How to use the PMT function to calculate the annual payment?

To calculate the annual payment for a loan, you won’t need the optional arguments (See the previous section). This is how you can use the PMT formula to calculate the annual payment required to settle a loan.

1. 1. Open the Google Sheets containing the data on the rate, number-of-periods, and present-value. Type in “=PMT” on an empty cell to trigger the PMT function in Google Sheets.

1. 2. To include the first argument, select the cell that contains the interest rate, aka rate. Google Sheets will automatically include the cell reference.

1. 3. Since the rate usually corresponds to an annual rate, you should divide this number by 12, to obtain the interest rate per month. Add a comma before selecting the next argument.

1. 4. Type in a comma and then include the number of payments by selecting the corresponding cell. Remember that this number represents the months, aka number_of_periods.

1. 5. Select the cell corresponding to the last argument, the loan amount, aka present_value.

1. 6. Close parentheses ad press “Enter” to apply the PMT formula. You should immediately see the result.

How to use the PMT function to calculate weekly, monthly, quarterly, and semi-annual payments?

To calculate the PMT function according to the frequency of payment, you’ll need to modify the rate and number_of_periods. Let’s first see how to use the PMT function to calculate weekly payments.

How to use the PMT function to calculate weekly payments?

To calculate how much you need to pay per week to settle a loan, this is how to use the PMT formula in Google Sheets.

1. 1. Follow steps 1-2 from the previous section.
2. 2. Divide the rate by “52” and include a comma before the next argument.

1. 3. Select the number_of_periods and then multiply by 52, “*52”, and follow with a comma.

1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.

1. 5. Google Sheets should output the result for your weekly payment, as shown below.

How to use the PMT function to calculate monthly payments?

To calculate how much you need to pay per month to settle a loan, this is how to use the PMT formula in Google Sheets.

1. 1. Follow steps 1-2 from the previous section.
2. 2. Divide the rate by “12” and include a comma before the next argument.

1. 3. Select the number_of_periods and then multiply by 12, “*12”, and follow with a comma.

1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.

1. 5. Google Sheets should output the result for your monthly payment, as shown below.

How to use the PMT function to calculate quarterly payments?

To calculate how much you need to pay per quarter to settle a loan, this is how to use the PMT formula in Google Sheets.

1. 1. Follow steps 1-2 from the previous section.
2. 2. Divide the rate by “4” and include a comma before the next argument.

1. 3. Select the number_of_periods and then multiply by 4, “*4”, and follow with a comma.

1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.

1. 5. Google Sheets should output the result for your quarterly payment, as shown below.

How to use the PMT function to calculate semi-annual payments?

To calculate how much you need to pay per 6 months to settle a loan, this is how to use the PMT formula in Google Sheets.

1. 1. Follow steps 1-2 from the previous section.
2. 2. Divide the rate by “2” and include a comma before the next argument.

1. 3. Select the number_of_periods and then multiply by 2, “*2”, and follow with a comma.

1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.

1. 5. Google Sheets should output the result for your semi-annual payment, as shown below.

As you can see, the formula output is a negative value. This is because a loan payment is a cash outflow; to obtain a positive value, you can multiply it by “-1” or adapt the PMT formula by adding a minus sign “-” at the beginning, =-PMT().

Moreover, if you want to calculate payments at the end of each month, you would need to set future_value to “0” and end_or_beginning to “1”.

Conclusion

As with any formula, the PMT function is the fastest way to arrive at an accurate result, and this is something you want, especially when it comes to long-term payments, whether it’s 12 months or 12 years.

Now that you understand what the PMT function is and what it can be used for. You should feel comfortable using the PMT formula in your Google Sheets to calculate weekly, monthly, quarterly, and semi-annual payments on a loan or investment.

If you want to learn more about how to use Google Sheets for other finance purposes, check out the articles below.

• How to Calculate Compound Interest in Google Sheets
• Google Sheets Amortization Schedule (Easy Guide)