Don’t forget to share this post

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.
Google Sheets Amortization Schedule Easy Guide
Google Sheets Amortization Schedule (Easy Guide)

An amortization schedule helps to determine how your monthly payments affect your loan cost. Here’s how to create an amortization schedule in Google Sheets.

READ MORE

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.
How to Use the PMT Function in Google Sheets Compulsory arguments in PMT formula
How to Use the PMT Function in Google Sheets - Compulsory arguments in PMT formula
  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.
How to Use the PMT Function in Google Sheets Include rate
How to Use the PMT Function in Google Sheets - Include rate
  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.
How to Use the PMT Function in Google Sheets Divide rate by 12
How to Use the PMT Function in Google Sheets - Divide rate by 12
  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.
How to Use the PMT Function in Google Sheets Include number of periods
How to Use the PMT Function in Google Sheets - Include number of periods
  1. 5. Select the cell corresponding to the last argument, the loan amount, aka present_value.
How to Use the PMT Function in Google Sheets Include present value
How to Use the PMT Function in Google Sheets - Include 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 in Google Sheets Result of PMT formula
How to Use the PMT Function in Google Sheets - Result of PMT formula

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 Calculate Compound Interest in Google Sheets

Here’s how to use Google Sheets to quickly find the compound interest annually, monthly, and daily. Easily explained, with examples.

READ MORE
How To Calculate Compound Interest In Google Sheets

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.
How to Use the PMT Function in Google Sheets Divide rate by 52
How to Use the PMT Function in Google Sheets - Divide rate by 52
  1. 3. Select the number_of_periods and then multiply by 52, “*52”, and follow with a comma.
How to Use the PMT Function in Google Sheets Multiply number of periods by 52
How to Use the PMT Function in Google Sheets - Multiply number of periods by 52
  1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.
How to Use the PMT Function in Google Sheets Include loan amount and press enter
How to Use the PMT Function in Google Sheets - Include loan amount and press enter
  1. 5. Google Sheets should output the result for your weekly payment, as shown below.
How to Use the PMT Function in Google Sheets Week payment using PMT
How to Use the PMT Function in Google Sheets - Week payment using PMT

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.
How to Use the PMT Function in Google Sheets Divide rate by 12 2
How to Use the PMT Function in Google Sheets - Divide rate by 12
  1. 3. Select the number_of_periods and then multiply by 12, “*12”, and follow with a comma.
How to Use the PMT Function in Google Sheets Multiply number of periods by 12
How to Use the PMT Function in Google Sheets - Multiply number of periods by 12
  1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.
How to Use the PMT Function in Google Sheets Include loan amount and press enter 2
How to Use the PMT Function in Google Sheets - Include loan amount and press enter
  1. 5. Google Sheets should output the result for your monthly payment, as shown below.
How to Use the PMT Function in Google Sheets Monthly payment using PMT
How to Use the PMT Function in Google Sheets - Monthly payment using PMT

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.
How to Use the PMT Function in Google Sheets Divide rate by 4
How to Use the PMT Function in Google Sheets - Divide rate by 4
  1. 3. Select the number_of_periods and then multiply by 4, “*4”, and follow with a comma.
How to Use the PMT Function in Google Sheets Multiply number of periods by 4
How to Use the PMT Function in Google Sheets - Multiply number of periods by 4
  1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.
How to Use the PMT Function in Google Sheets Include loan amount and press enter 3
How to Use the PMT Function in Google Sheets - Include loan amount and press enter
  1. 5. Google Sheets should output the result for your quarterly payment, as shown below.
How to Use the PMT Function in Google Sheets Quarterly payment using PMT
How to Use the PMT Function in Google Sheets - Quarterly payment using PMT

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.
How to Use the PMT Function in Google Sheets Divide rate by 2
How to Use the PMT Function in Google Sheets - Divide rate by 2
  1. 3. Select the number_of_periods and then multiply by 2, “*2”, and follow with a comma.
How to Use the PMT Function in Google Sheets Multiply number of periods by 2
How to Use the PMT Function in Google Sheets - Multiply number of periods by 2
  1. 4. Include the present_value and close parentheses. Press “Enter” to apply the formula.
How to Use the PMT Function in Google Sheets Include loan amount and press enter 4
How to Use the PMT Function in Google Sheets - Include loan amount and press enter
  1. 5. Google Sheets should output the result for your semi-annual payment, as shown below.
How to Use the PMT Function in Google Sheets Semi annual payment using PMT
How to Use the PMT Function in Google Sheets - Semi-annual payment using PMT

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”.

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Aug 18 2022, Updated Sep 23 2022