How to use the PMT formula in Google Sheets

Nowadays, long-term payments are becoming more and more popular in both personal and professional environments. Suppose you need to pay back a loan from an initial business investment, or you are investing parts of your income on a regular basis. It’s wise to have a good idea of the final amount you’ll pay at the end of the payment period. Rather than spending time carrying out complex calculations, we can use the PMT formula in Google Sheets to calculate everything for us.

Note: If you want to learn more about other google sheet functions, we have a post with 100+ Google Sheet Functions

Why use the PMT formula?

When it comes to finances, it’s extremely important to get everything right. By using the PMT formula, not only are you avoiding any risk of human error to receive an accurate result, but you can also have a clear understanding of your payments long-term. The PMT works in any long-term payment situation, whether it’s for 30 months or 30 years.

Here are just a few examples of when you’d use the PMT formula:

• Business investment
• Car loan
• Mortgage
• Student loans

What is the PMT formula?

The PMT formula is used to calculate constant payments that have a constant interest rate. You can calculate the payments for the loan via the given loan amount, interest rate, and the number of payments.

Syntax

Here’s the following PMT formula:

• rate – is the rate of interest.
• number_of_periods – is the number of periods that the buyer wishes to make payment for.
• present_value – is the current value of the annuity.
• future_value – OPTIONAL: is the amount of the future value that remains after we make the final payment.
• end_or_beginning – OPTIONAL (0 by default): a 0 indicates that we are making the payments at the end of each period. And a value of 1 specifies that we are making payments at the beginning of each payment period.

Please note:

• In order for the formula to work, the interest rate and time period between installments (months, years, quarters…) must be fixed.
• The formula does not take into account other aspects such as tax or other fees.
• You must keep the units consistent throughout the entire formula.

> If you’re paying a monthly payment for 4 years, then your number_of_periods value will be 12*4.
> If your installments are monthly, then you must divide the annual interest rate by 12 to get the ‘monthly’ rate.

Now that we understand what the PMT formula is and what it can be used for, let’s explore how to use the PMT formula in Google Sheets.

How to use the PMT formula in Google Sheets

Let’s say I am an entrepreneur who has just started my new business. I have taken a loan out of $20,000, with an annual interest rate of 9%.

I am paying back in monthly installments over two years – 24 periods.

Using the PMT formula, I can calculate exactly how much I will have to pay back each month.

In order to calculate the amount I will have to pay at the end of every month, the PMT formula should look like this:

Here is my result. I will have to pay $913.69 every month to pay back my loan (with interest) over 2 years.

Now let’s say I want to pay my loan at the beginning of every month. The PMT formula will look like this:

Here is my result. My monthly payment reduces to $906.89 per month.

Now let’s say I want a balance of $4,000 leftover from my last monthly payment.

First, let’s add it to the spreadsheet.

The PMT formula will then look like this:

Here is my result. The monthly payment is larger at $1066.43.

The PPMT and IPMT formulas

There are 2 other formulas that are closely linked to the PMT formula, in order to help us learn more about our loans or investments:

• PPMT: This function calculates the principal value of your payments over a particular period.
• IPMT: This function calculates the interest value of your payments over a particular period.

In short, these two formulas can help to calculate the two aspects of your monthly payments (principal and interest) that have been calculated using the PMT.

Why the PMT formula returns a negative number

PMT follows the sign rule of the finance functions in Google Sheets: money you receive is positive, and money you pay out is negative. The loan comes to you, so the payments that go back out show as negative numbers, like the -$913.69 above.

To show the payment as a positive number, do one of these:

• Put a minus sign before the function: =-PMT(A2/12, A3, A4)
• Wrap the function in ABS: =ABS(PMT(A2/12, A3, A4))
• Enter the loan amount as a negative number, such as -20000.

How to calculate weekly, quarterly, and yearly payments with PMT

The PMT formula works with any payment schedule, as long as the rate and the number of periods use the same unit. Divide the annual rate by the number of payments per year, and count the periods in that unit too.

Here’s the same $20,000 loan at 9% a year over 24 months (cells A2 to A4) on each schedule:

If your sheet holds the term in years instead of months, multiply it by the number of payments per year. For weekly payments over two years, that’s =PMT(A2/52, 2*52, A4).

How to calculate a mortgage payment in Google Sheets

A mortgage is a long loan with monthly payments, so PMT handles it the same way. Say you buy a $375,000 home with a $75,000 down payment and borrow the rest at a fixed 6.5% a year over 30 years. Set up the sheet like this:

• B1: home price, 375000
• B2: down payment, 75000
• B3: annual interest rate, 6.5%
• B4: loan term in years, 30

Then enter this formula:

=-PMT(B3/12, B4*12, B1-B2)

B1-B2 is the amount you borrow, $300,000. B3/12 turns the yearly rate into a monthly one, and B4*12 turns 30 years into 360 payments. The minus sign before PMT makes the result positive: you pay $1,896.20 a month.

To see the total interest, multiply the payment by the number of payments and subtract the loan: =-PMT(B3/12, B4*12, B1-B2)*B4*12-(B1-B2). Over 30 years, that’s $382,633.47. Change B4 to 15 and the payment rises to $2,613.32 a month, while the total interest drops to $170,397.98.

These figures cover principal and interest only. Property tax, home insurance, and mortgage insurance come on top. To see how each payment splits between principal and interest month by month, build a Google Sheets amortization schedule.

Other loan functions to use with PMT

PMT gives you the payment. These functions answer the other questions about the same loan or investment:

• FV returns what an investment is worth after a series of fixed payments at a fixed rate.
• RATE returns the interest rate per period when you know the payment, the number of periods, and the loan amount.
• NPER returns how many payments it takes to pay off a loan at a given rate and payment.

PMT formula Google Sheets

And there you have it! Now that you know how to calculate monthly payments for loans and investments, it may be a good idea to learn about PMT’s related formulas to get the most out of your long-term payments.

Why not take a look at our article on How to use the PPMT formula in Google Sheets?

Alternatively, discover some related articles below!