Check Sheet Logo
Check Sheet

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.

Usage

Use the PMT formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:

=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
Parameters:
  1. rate (required):
    The interest rate per period of the loan or investment.
  2. number_of_periods (required):
    The total number of payment periods over the life of the loan or investment.
  3. present_value (required):
    The present value, or amount of the loan or investment.
  4. future_value (optional):
    The future value, or the balance remaining after the final payment is made. Defaults to 0 if not specified.
  5. end_or_beginning (optional):
    A flag indicating whether payments are due at the end or beginning of each period. Use 0 or omitted for payments due at the end of each period, and 1 for payments due at the beginning of each period. Defaults to 0 if not specified.

Examples

Here are a few example use cases that explain how to use the PMT formula in Google Sheets.

Loan payments

Suppose you borrow $20,000 at an annual interest rate of 5%, to be repaid over 5 years with monthly payments. You can use the PMT formula to calculate the monthly payment required to fully pay off the loan.

Mortgage payments

Suppose you take out a 30-year fixed-rate mortgage for $200,000 at an annual interest rate of 4%. You can use the PMT formula to determine your monthly mortgage payment, which will remain constant over the life of the loan.

Annuity payments

Suppose you invest $10,000 in an annuity that pays 6% interest annually for 10 years. You can use the PMT formula to calculate the amount of each annual payment you will receive over the life of the annuity.

Common Mistakes

PMT not working? Here are some common mistakes people make when using the PMT Google Sheets Formula:

Missing arguments

One or more required arguments are missing. Check that all required arguments are included in the formula.

Incorrect order of arguments

The arguments are in the wrong order. Check that the arguments are in the correct order as specified in the formula syntax.

Invalid argument type

One or more arguments are of the wrong type. Check that all arguments are of the correct type as specified in the formula syntax.

Incorrect rate

The rate argument is not in the correct format. Check that the rate is a decimal number between 0 and 1.

Incorrect number of periods

The number_of_periods argument is not a positive integer. Check that the number_of_periods is a positive integer.

Related Formulas

The following functions are similar to PMT or are often used with it in a formula:

  • PV

    The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.

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

  • RATE

    The RATE formula returns the interest rate per period of an annuity. This formula is often used in financial analyses to calculate the rate of return on an investment. It assumes that payments are made at regular intervals and that the interest rate remains constant throughout the duration of the annuity.

  • NPER

    The NPER function calculates the total number of payment periods required to pay off an investment based on a constant payment amount, a fixed interest rate, and the present value of the investment. It is commonly used in financial planning and investment analysis.

  • NPV

    The NPV function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input.

Learn More

You can learn more about the PMT Google Sheets function on Google Support.