With the PMT function, you can return a payment amount, based on
loan information. In this example:

The loan amount is $10,000

The interest rate is 5% annually

The loan is for a 4 year term, with 48 monthly payments

In cell C6, the PMT function calculates the monthly payment, based
on the annual rate, which is divided by 12 to get the monthly rate,
the number of payments (periods) and the loan amount (present value):

=PMT(C2/12,C3,C4)

The payment, -230.29, is calculated as a negative amount, because
you are paying that amount out of your bank account.

If you would prefer to see the result as a positive number, you can
use a minus sign before the PMT function:

=-PMT(C2/12,C3,C4)

Example 2: Calculate the Payment on a Canadian
Mortgage

For Canadian mortgage loans, the interest is compounded semi-annually,
rather than monthly, even if the payments are monthly. To calculate
the payments, you need a different rate calculation, instead of the
simple Rate/12.

Note: Visit your bank's website, or check with your banker, to confirm
how your bank will calculate the payments.

In this example:

The mortgage loan amount is $100,000

The interest rate is 5% annually, compounded semi-annually

The loan is for a 20 year term, with 240 monthly payments

In cell C6, the PMT function calculates the monthly payment, based
on the annual rate, the number of payments (periods) and the loan
amount (present value):

=PMT((C2/2+1)^(1/6)-1,C3,C4)

Instead of simply dividing the rate by 12, the rate calculation is:
(Rate/2+1)^(1/6)-1

(Rate /2 +1) is the semi-annual interest as a proportion of the
annual rate. In this example, the rate is 5/2 = 2.5% each 6 months.
So at the end of 6 months you owe 1.025 of what you owed at the
beginning.

Payments are monthly, and there are 6 months in a half year, so
the proportional rate is raised to the power of 1/6. In this example,
the monthly rate is 1.025 ^(1/6)=1.00412391547

The 1, that was added for the rate calculation, is subtracted

The payment, -657.13, is calculated as a negative amount, because
you are paying that amount out of your bank account.

If you would prefer to see the result as a positive number, you can
use a minus sign before the PMT function:

=-PMT((C2/2+1)^(1/6)-1,C3,C4)

Example 3: Enhanced Loan Calculator

In the previous examples, you had to enter the total number of payments
due, after calculating that number -- number of years in the loan
term, times the number of payments per year.

To make things easier, this Excel loan payment calculator lets you
select the payment frequency from a drop
down list of options.

In the sample file, the Lists sheet has a lookup table of frequencies
and number of payments per year, for each frequency.

Based on the frequency that you select, a number of payments per
year is calculated in cell E5, using a VLOOKUP
formula.

=IFERROR(VLOOKUP(C5,FreqLU,2,0),"")

The payment amount is calculated with the PMT function:

=IFERROR(PMT(C7/E5,E6,-C4),"")

In this workbook, there is a minus sign before the present value
variable, so the monthly payment is shown as a positive number. You
can omit the minus sign, to show the payment as a negative number.