The Excel PMT function is used to calculate loan repayments based on constant payments and a constant interest rate.
The syntax for the PMT function is:
=PMT(rate, nper, pv, [fv], [type])
| Argument | Purpose |
|---|---|
| Rate | The interest rate for the loan |
| Nper | The total number of payments for the loan |
| PV | The present value, or total amount a number of future payments is worth now |
| FV | The future value, or total remaining after the last payment has been made.This argument is optional, and if omitted the total is assumed to be 0 |
| Type | When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 00 – Payments are made at the end of the period1 – Payments are made at the beginning of the period |
The examples below show the PMT function being used to calculate loan payments based on different parameters.
| Function | Result |
|---|---|
| =PMT(C4/12,B4*12,A4,0,1) | £1,156.20 in monthly paymentsC4/12 – annual interest rate divided by 12 to return the monthly rateB4*12 – period of loan in years multiplied by 12 to return the number of monthly paymentsA4 – loan amount that i owe0 – The final total after the last payment will be 0 as the loan is completely paid off1 – Payments will be made at the beginning of each period |
| =PMT(C4/52,B4*52,A4) | £268.10 in weekly payments |
