The PMT function in Google Sheets is a powerful tool designed to compute the periodic payment for an annuity investment. It does this by taking into account fixed periodic payments and a constant interest rate, making it invaluable for financial analysis and planning.

Syntax

PMT(rate, nper, pv, [fv], [type])

• rate: The interest rate per period.
• nper: The total number of payment periods.
• pv: The present value, or total amount of the loan or investment.
• fv (optional): The future value, or cash balance you want after the last payment.
• type (optional): The timing of payments; 0 indicates the end of the period, while 1 indicates the beginning.

Example #1

=PMT(0.05/12, 60, 30000)

This function calculates the monthly payment for a $30,000 loan with a 5% annual interest rate over 5 years. The result would be approximately $566.14.

Example #2

=PMT(0.03/12, 72, 10000, 0, 1)

Here, we are finding the monthly payment for a $10,000 loan at a 3% annual interest rate over 6 years, with payments made at the beginning of each period. The result would be around $157.36.

Example #3

=PMT(0.07, 10, -5000)

This example computes the annual payment needed for a $5,000 investment at a 7% interest rate over 10 years, yielding a payment of approximately $882.50.

Error handling

• NUM!: This error occurs if the number of periods (nper) is less than 0, or if the interest rate is less than or equal to 0 and the present value is not 0.
• VALUE!: Triggered when any of the arguments are non-numeric or the cell references are invalid.
• DIV/0!: Happens if the rate argument is set to 0 and the total number of payments (nper) is not specified correctly.

Conclusion

The PMT function in Google Sheets is an essential tool for calculating recurring payments, allowing users to easily evaluate their financial commitments. By adjusting the parameters, individuals can tailor the calculations to their specific lending or investment scenarios, making financial planning more effective and informed.