What Is PMT in Google Sheets?
PMT in Google Sheets stands for “payment.” It is defined as the payment for an annuity investment or loan with constant periodic payments and interest rates. It helps you determine the regular payment you should make to repay a loan. It is useful when you invest a part of your income on a periodic basis, and you wish to know the amount you will pay at the end of each payment period. It is also useful when paying back loans.
For example, you have a loan amount of $2000. It has an annual interest rate of 8%. Suppose you want to repay it over 3 years and will make monthly payments, you can find the amount you need to pay monthly using the PMT function. We use the formula s follows:
=PMT(8%/12, 3*12, -2000). Here, we enter the monthly interest, the total number of monthly payments, and the present value as the loan amount. Since the payment is outgoing, we use a negative symbol. We see that we must pay $62 approximately every month over a period of three years.
Key Takeaways
- The PMT function in Google Sheets is used to calculate the periodic payment for an investment/loan provided the payments and interest rate are constant. It helps determine the regular payment amount (like monthly) to repay a loan over a specified period.
- The syntax for the PMT function is as follows: =PMT(rate, nper, pv, [fv], [type]). Here,
- rate: The interest rate for the specified period
- nper: The total payment periods.
- pv: The total amount of the loan.
- [fv] (optional): The future value
- [type] (optional): Here, default 0 means payments are due at the end of the period, and 1 means payments are due at the beginning.
- PMT in Google Sheets is useful when you want to calculate your monthly mortgage or car loan payment. It is also useful if you wish to save each month to reach a specific future goal.
- Thus, the PMT function can be tailored to various financial scenarios by changing the inputs, making it a useful tool for financial planning in Google Sheets.
Syntax
The syntax of the PMT function in Google Sheets is as follows:
PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning])
Arguments:
- “rate” is the interest rate per period for the loan or investment.
- “number_of_periods” is the total number of payments to be made.
- “present_value” is the current value of the loan or investment.
- “future_value” (optional) – it is the future value of the loan or investment after the last payment is made. Default value is 0.
- “end_or_beginning” (optional) – it specifies whether payments are due at the end of each period (0) or the beginning (1).
How to Use PMT in Google Sheets?
From the Google Sheets Menu
You can easily insert the PMT function from the Google Sheets menu.
First, go to the “Insert” menu option. From here, go to “Functions” and choose “Financial” Choose the PMT function and enter the required arguments.
Manually Entering the FV function
You can also enter the PMT function directly in Google Sheets. We will show you how to do so with a simple example. Let us say you invest an amount of $20,000 for a period of 5 years and with an annual rate of interest of 5%. Find the PMT value of this investment which you will make semi-annually.
Step 1: First, we enter the given details in a Google sheet.
The amount of $20,000 is entered as the payment amount and it is an outflow.
Step 2: To find the PMT in Google Sheets, enter the following function in cell B4. Here, we write B1/2, as it is a semi-annual payment.
=PMT(B1/2, B2, B3)
This is based on the formula PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning]).
Step 3: Now, press Enter to get the future value in cell B4. The payment is calculated as -$2,285.18 on a semi-annual basis for a period of five years. As it is an outflow, it is a negative value.
Examples
Let us look at some examples on how to use PMT in Google Sheets.
Example #1 – Calculate weekly payments for home loan
In this example, let us say you have a home loan valued at $2,00,000 with an annual interest rate of 6%. It is for a tenure of 10 years. Calculate the weekly payment required.
Step 1: Enter all the details in a Google sheet. Here, since the payment is weekly, you must divide the annual interest rate by 52. The number of years is 10. So, the total number of payments will be 52* number of years.
Step 2: Apply the following formula in cell B4.
=PMT(B1,B2,-B3). Press Enter.
As seen above, a weekly payment of $511.69 should be paid for a tenure of 10 years to complete the loan payment.
Example #2 – Calculate monthly payments for home loan
Now, that we have seen weekly payments, for the same example as above, let us suppose the payments are to be made on a monthly basis. Here, the parameters for interest and loan period should be changed accordingly. Let us look at how the table changes.
Step 1: Here, since it is a monthly payment, we have divided the annual interest rate by 12. Also, as it is a monthly payment, the payment periods would be 10*12.
Apply the following formula in cell B4.
=PMT(B2,B3,-B4). Press Enter.
A quarterly payment of $4,961.63 has to be made towards the mortgage.
Example #3 – Calculate quarterly payments for mortgage
Now, let us say you have a mortgage with an amount of $250,000 and an annual interest rate of 5%. For the uninitiated, a mortgage is secured by the property itself. If the borrower is unable to repay the loan, the lender can take ownership of the property legally. The term of this mortgage is 20 years. Let us calculate the quarterly payments for this Google Sheets mortgage formula.
Step 1: Let us enter the details in a Google sheet. For monthly payments, divide the annual interest rate by 4. Since the number of years in 20, we multiply 20 with 4 to get the total number of payment periods.
Step 2: Apply the following formula in cell B5.
=PMT(B1,B2,-B3). Press Enter.
A quarterly payment of $4,961.63 must be made towards the mortgage.
Example #4 – Calculate annual payments for mortgage
Let us look at the same example as above, but with yearly payment. Here, the rate of interest and payment period will be unmodified as it is an annual payment.
Step 1: Enter the details in a Google sheet.
Step 2: Now, enter the following formula for the calculation of annual payments in B5.
=PMT(B2,B3,-B4)
The above image shows the annual payment to be made on the mortgage.
Rate of interest and Periods of payment for different payment frequencies
- Divide the annual interest rate by the number of payments per year.
- Multiply the number of years by the number of payments per year.
| Frequency | Interest Rate | Payment periods |
|---|---|---|
| Weekly | annual interest rate/52 | years* 52 |
| Monthly | annual interest rate/12 | years* 12 |
| Quarterly | annual interest rate/4 | years* 4 |
| Semi-annual | annual interest rate/2 | years* 2 |
| Annual | annual interest rate | years |
Important Things To Note
- Ensure that the units used for rate and number_of_periods are consistent. For example, for monthly loan payments, the annual interest rate should be divided by 12 and the number of payments is 12 for a year. However, if it is, say semi-annual, the annual percentage rate should be divided by 2 and the number of payments would be 2.
- Always remember that the PMT in Google Sheets can be calculated only when the interest rate and time period between installments is fixed.
- If your installments are monthly, you must divide the annual interest rate by 12 for the monthly rate.
- You use a negative sign which signifies an outgoing payment such as a loan.
Frequently Asked Questions (FAQs)
The payment amount returned by the PMT in Google Sheets is a negative number as it represents the outflow of money. It means that the payments are an outflow to be paid by the borrower. In case you wish the number to be positive, you can enter the amount parameter as a negative number or use the ABS function.
The syntax of the PMT function is : PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning]).
Here, future_value is the balance you want to attain after the last payment has been made. The default value is zero as the value at the end of a loan payment is zero.
fv (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it is assumed to be 0, which is usually the case for loans.
end_or_beginning (optional): It indicates when the payments are due. 0, the default value means one makes the payment at the end of the period, and 1 means, one makes it at the beginning of the period.
The PMT formula in Google Sheets is useful for making any long-term payments like mortgages and loans. We can calculate it for many loans which involve business investments, car loans, home loans and mortgages, student loans, etc.
No, we use the PMT in Google Sheets for regular and equal payments. You should use manual calculations for loans with irregular payments.
Download Template
This article must be helpful to understand the PMT in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is PMT in Google Sheets. We learn how to use function PMT in Google Sheets with its formula, examples and a working template. You can learn more from the following articles.
Leave a Reply