Author: Oscar Cronquist Article last updated on August 18, 2018

The PMT function returns the payment amount needed for borrowing a fixed sum of money based on constant payments and interest rate.

You can also use the PMT function to calculate the amount to save each period to reach a given sum, based on an interest rate and the number of periods.

The above image shows the annual payment needed for a 10-year 12 000 loan based on 5 percent interest rate.

Formula in cell C7:

=PMT(C3,C4,C5)

Excel Function Syntax

PMT(rate, nper, pv, [fv], [type])

Arguments

Rate Required. The interest rate.
nper Required. The number of payments.
pv Required. The present value.
[fv] Optional. The future value. Default value is 0 (zero).
[type] Optional. When payments are due, default value is 0 (zero).

0 (zero) :  at the end of the period.

1 : at the beginning of the period.

Comments

Think carefully about what period you use (years, quarterly, months, weekly, days or whatever unit) before you specify the number of payments (nper) and the interest rate.

Monthly payments: For a ten-year loan at an annual interest rate of 5 percent use 5%/12 (rate) and 10*12 (nper).

Annual payments: For a ten-year loan at an annual interest rate of 5 percent use 5% (rate) and 10 (nper).

Example

The image above demonstrates how to calculate the monthly savings amount needed to reach 100 000 with a 3% interest rate and 10 years of savings.

Formula in cell C9:

=PMT(3%/12,10*12,,100000)