The PMT function returns the periodic payment amount for a loan or investment based on a constant interest rate per period and unchanging payment amounts.
This is a useful tool for financial planning, allowing you to understand how much you will need to pay each month.
Syntax
PMT(rate, number_of_payments, present_value, [future_value], [end_or_beginning])
Argument |
Description |
Permitted values |
|---|---|---|
discount |
Annual interest rate |
Numerical value (for example, 5%/12) or a reference to a cell containing a number |
number_of_payments |
Number of annuity payments |
An integer (for example, 180 for 15 years) or a reference to a cell containing a number |
present_value |
The amount equivalent to future payments calculated at the current time |
Numerical value (for example, −100,000) or a reference to a cell containing a number |
[future_value] |
(optional) Required savings amount after the last payment |
Numerical value (for example, 0 or 5000) or reference to a cell containing a number |
[end_or_beginning] |
(optional) The number that determines when a payment should be made: at the end (0) or at the beginning (1) of each period. By default, 0 |
0 or 1 |
Examples of use
Calculation of monthly loan payments
=PMT(15%/12, 30*12, -100000)
In this example, we calculate the monthly payment on a mortgage loan of 100,000 c.u. with an annual interest rate of 15% for a term of 30 years.
Please note that the current value is indicated with a minus sign to reflect the outflow of money.
Result: approximately 1264.4.
Calculation based on future value
=PMT(15%/12, 5*12, -20000, 0, 0)
Here we calculate the monthly payment on a loan of 20,000 c.u. with an annual interest rate of 15% for a term of 5 years, without any future obligations.
Result: approximately 475.8.
Notes
Make sure that the interest rate and the number of periods match each other (for example, if the rate is annual and the payments are monthly, the rate should be divided by 12).