Google Sheets has a few useful financial functions for calculating things like the payments on a loan.
One such function is called the PMT function. This can be used to calculate things like payments on a loan, investment, mortgage, and more.
In this tutorial, I will show you how to use the PMT Function in Google Sheets
What is the PMT Function?
The PMT function calculates the payment that will be made for an annuity investment. The payment calculated will be based on a constant or fixed interest rate and have a fixed payment value.
You can use this function to calculate what the payment will be on a loan such as a mortgage loan.
The syntax of the PMT function is:
=PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
- rate – this is the interest rate
- number_of_periods – this is the number of payments that will be made
- present_value – the current value of the annuity
- future_value – this argument is optional. It is the future value remaining after the final payment
- end_or_beginning – this argument is optional and will be set to 0 by default. Here you specify whether payments are due at the end (0) or beginning (1) of each period
One important thing to pay attention to when using this function is that you need to make sure that the rate and number_of_periods arguments are consistent. For example, if you are using this function to calculate a mortgage payment paid monthly, the mortgage rate should be divided by 12 and the number_of_periods is the years of the loan multiplied by 12.
On the other hand, if you were calculating something with a quarterly payment the rate would be divided by 4, and the number of years of the loan would be multiplied by 4 for the number_of_periods argument.
This is what I mean by keeping these two arguments consistent in your formula. To get a better understanding of this, take a look at the next section where I go over an example of using this function.
Using the PMT Function
Now let’s take a look at how to use the PMT function in our spreadsheet. In this example, I will show you how to use the PMT function to calculate the mortgage payment in years and in months.
Here is an example of how this would be used in Google Sheets:
Now let’s break it down step by step. Here’s what you need to do:
1. The first step is to select the cell where you want the formula to calculate and then type the equals sign (=), then type the minus sign (-) and then PMT, and press yab on your keyboard. (PMT calculates as a negative so if you want your calculation to be a positive number we add a minus sign before the function)
2. Next we need to enter the first argument, which is the rate. Enter your interest rate or the cell that contains the interest rate. In my example, I am calculating both the yearly and monthly payments on a mortgage, so I have divided this rate by 12 to represent the months in a year. Add a comma when you are done with this step.
3. The next argument is the number_of_periods. Put in the appropriate number_of_periods or select the cell that contains this value. In my example, the length if 30 years (cell B4) and there are 12 months in each year so in my formula, this argument is (B4*12). Add a comma when you are done with this step.
4. The next argument we need to enter into the function is the present_value which is the value of the annuity. If you are calculating a loan payment, then you can enter the loan amount or the cell that contains the loan amount. When you are done with this step, place a closing parenthesis around your formula “)”
5. In this example, I am first calculating a yearly payment, so I multiply the entire function by 12 as the next step.
6. Press Enter on your keyboard and your formula will calculate. You will now have the payment that is made each period.
7. If you want to calculate monthly payment, copy the same formula and paste it into another cell and remove the end where you multiply it by 12.
8. Press Enter on your keyboard and you will now have calculated both the monthly and yearly payments using the PMT function.
The PMT function is an extremely useful financial function that can be used to calculate the regular payments on an annuity, mortgage, or other kinds of loans.
If you are regularly making these kinds of calculations in your own spreadsheets, this is definitely a function you should take the time to master.
It is fairly easy to use once you understand the parameters for the function.