Excel > Functions

PMT Function

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 0 0 - Payments are made at the end of the period 1 - 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.

Using the PMT function to calculate payments

Function	Result
=PMT(C4/12,B4*12,A4,0,1)	£1,156.20 in monthly payments C4/12 - annual interest rate divided by 12 to return the monthly rate B4*12 - period of loan in years multiplied by 12 to return the number of monthly payments A4 - loan amount that i owe 0 - The final total after the last payment will be 0 as the loan is completely paid off 1 - Payments will be made at the beginning of each period
=PMT(C4/52,B4*52,A4)	£268.10 in weekly payments

Function

Result

=PMT(C4/12,B4*12,A4,0,1)

£1,156.20 in monthly payments

C4/12 - annual interest rate divided by 12 to return the monthly rate
B4*12 - period of loan in years multiplied by 12 to return the number of monthly payments
A4 - loan amount that i owe
0 - The final total after the last payment will be 0 as the loan is completely paid off
1 - Payments will be made at the beginning of each period

=PMT(C4/52,B4*52,A4)

£268.10 in weekly payments

Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.

Follow us on

Other Financial Functions