This guide covers everything you need to know about the Google Sheets PMT function, including its definition, syntax, use cases, and how to use it.
What is the PMT Function? How Does It Work?
The PMT function in Google Sheets helps you figure out how much you have to pay for loans or investments that have the same payment amount each time. It’s like a calculator for figuring out your monthly car loan payment or how much you need to invest every year to hit your savings goal.
Here’s how it works: you tell the PMT function about the interest rate, how many times you’ll make a payment (like how many months for a car loan), how much the loan or investment is for, and a couple of other details. It then uses a special formula to work out how much each payment will be.
It’s important to make sure everything matches up. For example, if you’re thinking about monthly payments, you need to adjust the annual interest rate to a monthly one and tell the function how many months there are.
This isn’t just for car loans or house mortgages. Any time you need to figure out payments that happen over and over again for a certain period, the PMT function can help. So, it’s handy for things like student loans, retirement investments, and more.
PMT Syntax
The syntax and arguments for the function are as follows:
PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
- rate: This is the interest rate you’ll be charged or earn. Think of it as the cost for borrowing money or the earnings from an investment. It’s a must-have number.
- number_of_periods: This tells us how many times you’ll be making a payment or receiving an amount. For example, for a 2-year monthly loan, this would be 24 (because 2 years x 12 months = 24).
present_value: Imagine this as the total amount of the loan or investment right now.
future_value (you can skip this): If you want some money left after all payments are done, you’d put that amount here. If you don’t add this, it just thinks you want nothing left at the end.
end_or_beginning (you can skip this too): Simply put, this tells if you’re paying at the start (use 1) or end (use 0) of each period. If you don’t tell it, it assumes you’re paying at the end.
Usage notes related to syntax and arguments:
- Make sure your interest rate and the number of payments match in terms of time. For example, for monthly payments, use a monthly interest rate (yearly rate divided by 12).
- If you don’t specify a future value, the PMT function thinks it’s zero, which means no money left after the last payment.
- If the end_or_beginning argument is omitted, the PMT function assumes that the payments are due at the end of each period.
- The PMT function will return a negative value if the present_value (or future_value if specified) is considered an outgoing payment (money paid out), and positive if it’s an incoming payment (money received).
Examples of How to Use the PMT Function
Here are some practical examples of how to use the PMT function in Google Sheets:
Example #1: Calculating Monthly Mortgage Payments
Suppose you have a mortgage loan of $200,000 with an annual interest rate of 5% and a term of 30 years. You want to find out your monthly mortgage payment. You would use the PMT function as follows:
=PMT(5%/12, 30*12, 200000)
The first argument is the interest rate per period (5% annual interest rate divided by 12 months), the second argument is the total number of periods (30 years times 12 months), and the third argument is the present value or total loan amount.
This formula will return the monthly payment you must make to pay off the loan in 30 years, which should be around -$1,073.64. The negative sign indicates a payment.
Example #2: Determining Car Loan Payments
Let’s say you plan to buy a car with a loan of $25,000. The loan has an annual interest rate of 3.5% and must be paid off in 5 years. You want to know how much your monthly payments will be. You can use the PMT function to calculate this:
=PMT(3.5%/12, 5*12, 25000)
This formula will return the monthly payment you would have to make to pay off the car loan in 5 years, which should be around -$454.89.
Example #3: Computing Student Loan Repayments
Assume you have a student loan of $35,000 with an annual interest rate of 4.45% and a repayment period of 10 years. You want to determine your monthly payments. You can use the PMT function as follows:
=PMT(4.45%/12, 10*12, 35000)
This formula will return the monthly payment you would have to make to pay off the student loan in 10 years, which should be around -$363.54.
Example #4: Figuring Out Credit Card Payments
Suppose you have a credit card balance of $5,000 with an annual interest rate of 18.9% and want to pay it off in 2 years. You can calculate your monthly payments using the PMT function:
=PMT(18.9%/12, 2*12, 5000)
This formula will return the monthly payment you would have to make to pay off the credit card balance in 2 years, which should be around -$262.48.
Why Is PMT Not Working? Troubleshooting Common Errors
If you’re experiencing difficulties with the PMT function in Google Sheets, it might be due to a few common errors. This section will provide you with an understanding of these errors, their potential causes, and how to fix them.
#VALUE! Error
Cause: The #VALUE! error is often due to incorrect data types being used in the PMT function. This error is usually seen when you’re using non-numeric values in a formula that expects numbers.
Solution: To fix this error, ensure all your PMT function inputs are numeric values. Check your cells for any non-numeric characters or incorrect cell references. Ensure that the interest rate (rate), number of periods (number_of_periods), and principal amount (present_value) are all numbers.
#NUM! Error
Cause: The #NUM! error typically appears when the input values in your PMT function lead to an impossible calculation. For example, you may have input a negative value for the number of periods or the interest rate.
Solution: To solve this, check your input values to ensure they make sense. The rate and number_of_periods must be non-negative, and the principal amount (present_value) should not be zero. Adjust any incorrect values to resolve the #NUM! error.
#DIV/0! Error
Cause: The #DIV/0! error is usually displayed when trying to divide a number by zero within the PMT function. This could occur if the interest rate or the number of periods is set to zero.
Solution: If you encounter the #DIV/0! error, check the arguments in your PMT function. Make sure neither the interest rate or the number of periods is zero. If you intend for your loan to have no interest, consider using a very small number close to zero instead.
#N/A Error
Cause: The #N/A error often happens when the PMT function is expecting input values, but they are not available or not provided.
Solution: To fix this error, ensure all necessary arguments are provided to the PMT function. The rate, number_of_periods, and present_value are required arguments, so check your function to make sure none of these is missing. If your function references other cells, ensure those cells contain the necessary data.
Using PMT With Other Google Sheets Functions
Combining PMT with other Google Sheets functions can create powerful formulas that provide deeper insights into your financial data. Here are a few examples of how you can use PMT in conjunction with other Google Sheets functions.
With SUM
Usage: The SUM function can be used with PMT to calculate the total amount paid over the course of a loan. This can be done by multiplying the PMT result (monthly payment) by the total number of payments (loan term).
Example: If you have a loan with a principal of $10,000, an annual interest rate of 5%, and a term of 5 years, you can calculate the total amount paid over the course of the loan using the following formula:
=SUM(PMT(5%/12, 5*12, 10000)*5*12)
With IFERROR
Usage: The IFERROR function can be used with PMT to handle errors when calculating the monthly payment. This function returns a specified value if the formula results in an error and the normal result if not.
Example: If you’re calculating the monthly payment for a loan, but the input cells for interest rate, term, or principal are empty, the PMT function will return an error. You can use the IFERROR function to return a message like “Incomplete data” in such cases.
=IFERROR(PMT(B2/12, B3*12, B4), “Incomplete data”)
With ROUND
Usage: The ROUND function can be used with PMT to round the result to a specified number of decimal places. This can be useful when dealing with currency, as it allows you to round the monthly payment to the nearest cent.
Example: If you’re calculating the monthly payment for a loan with a principal of $10,000, an annual interest rate of 5%, and a term of 5 years, you can round the result to the nearest cent using the following formula:
=ROUND(PMT(5%/12, 5*12, 10000), 2)
For more details on the PMT function, check out the official documentation at the Google Docs Editors Help Center.
