The Excel PMT function is a financial function that helps you calculate the periodic payment for a loan or investment, assuming a constant interest rate and fixed payments.
Syntax
=PMT(rate, nper, pv, [fv], [type])
Arguments
| rate | The interest rate per period. |
| nper | The total number of payment periods in an investment or loan. |
| pv | The present value or the principal amount of the investment or loan. |
| [fv] | (Optional) The future value, or a cash balance you want to attain after the last payment. If omitted, it is assumed to be 0. |
| [type] | (Optional) Indicates whether payments are due at the beginning or end of the period. Use 0 if payments are due at the end and 1 if due at the beginning. If omitted, it is assumed to be 0 (end of the period). |
How to use
Let’s understand how to use the PMT function with an example:
Example: You want to calculate the monthly payment for a $10,000 loan with an annual interest rate of 5%, to be repaid over 3 years, with payments made at the end of each month.
|
1 |
=PMT(5%/12, 3*12, 10000) |
This formula uses a monthly interest rate (5% divided by 12 months) and the total number of payments (3 years multiplied by 12 months) to calculate the monthly payment for the loan. The present value (loan amount) is $10,000. The PMT function will return the monthly payment amount.
Additional Information
The PMT function is commonly used for various financial calculations, including loan repayments, annuities, and investments. It helps you determine how much you need to pay or how much you will receive regularly to reach a financial goal.