PMT
A loan's payment each period at a constant rate.
Group: Financial. Compatible with DAX.
Syntax
PMT ( <rate>, <nper>, <pv> [, <fv>] [, <type>] )| Parameter | Type | Description |
|---|---|---|
rate | Number | The interest rate per period. For monthly payments on a yearly rate, divide that rate by 12. |
nper | Number | How many payment periods there are, all told. |
pv | Number | The amount at the start: what was borrowed, or what was put in. |
fv | Number optional | The balance wanted once the last payment is made; 0 when left out. |
type | Number optional | When the payments fall: 0 (when left out) at the end of each period, 1 at its start. |
Returns
Number. The payment for each period, interest and repayment together.
Remarks
- Cash going out is negative, cash coming in positive. A loan you take is a positive pv, so the payments on it come out negative; a deposit you make is negative.
- Give rate and nper in the same period: 6 % a year paid monthly for 4 years is a rate of 0.06 / 12 and an nper of 48.
- The payment covers interest and principal only, no fees. Multiply it by nper for the total paid.
- A blank pv gives BLANK. An nper of 0, or a rate of −1 or less, is an error.
Examples
The monthly payment on a loan
2,000,000 for a bottling line at 4.5 % a year, repaid monthly over 10 years.
EVALUATE
ROW (
"Payment", PMT ( 0.045 / 12, 120, 2000000 ),
"Total paid", 120 * PMT ( 0.045 / 12, 120, 2000000 )
)| Payment | Total paid |
|---|---|
| -20727.6817514034 | -2487321.81016841 |
Saving toward an amount
What to put aside each month to have 500,000 in three years at 3 %: the pv is 0 and the target is the fv.
EVALUATE
ROW (
"Each month", PMT ( 0.03 / 12, 36, 0, 500000 )
)| Each month |
|---|
| -13290.6048153265 |