The PMT function returns a payment amount, so you can use
it to:
► Calculate
the monthly payment due on a personal loan
► Calculate
the payment due for a Canadian mortgage loan, with interest compounded
bi-annually
Syntax
The syntax for the PMT
function in Microsoft Excel is:
=PMT( interest_rate, number_payments, PV, [FV],
[Type] )
Parameters or Arguments
interest_rate
The interest rate for the
loan.
number_payments
The number of payments for the
loan.
PV
The present value or principal
of the loan.
FV
Optional. It is the future
value or the loan amount outstanding after all payments have been made. If this
parameter is omitted, it assumes a FV value of 0.
Type
Optional. It indicates when
the payments are due. If the Type parameter is omitted, it assumes a Type value
of 0. Type can be one of the following values:
0 - the payment is made at the end of the
period;
1 - the payment is made at the beginning of the period.
1 - the payment is made at the beginning of the period.
Excel Pmt Function Examples
Example 1
In the following spreadsheet,
the Excel Pmt function is used to calculate the monthly payments on a loan of
$50,000 which is to be paid off in full after 5 years. Interest is charged at a
rate of 5% per year and the payment to the loan is to be made at the end of
each month.
Formula:
|
|
A
|
|
1
|
Monthly payments on a loan of
$50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth): |
|
2
|
=PMT(
5%/12, 60, 50000 )
|
Result:
|
|
A
|
|
1
|
Monthly payments on a loan of
$50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth): |
|
2
|
-943.56
|
Note that in this example:
► The
payments are made monthly, so the annual interest rate of 5% has been converted
into the monthly rate (=5%/12), and the period of 5 years is expressed in
months (=5*12).
► As
the future value is zero, and the payment is to be made at the end of the
month, the [fv] and [type] arguments can be omitted from the above function.
► The
value returned from the function is negative, as this represents an outgoing
payment (for the individual taking out the loan).
Example 2
In the spreadsheet below, the
Excel Pmt function is used to calculate the quarterly payments required to
increase an investment from $0 to $5,000 over a period of 2 years. Interest is
paid at a rate of 3.5% per year and the payment into the investment is to be
made at the beginning of each quarter.
Formula:
|
|
A
|
|
1
|
Quarterly payments into an investment
with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at start of each qtr): |
|
2
|
=PMT(
3.5%/4, 8, 0, 5000, 1 )
|
Result:
|
|
A
|
|
1
|
Quarterly payments into an investment
with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at start of each qtr): |
|
2
|
-600.85
|
Note that, in this example:
► The
payments into the investment are made quarterly, so the annual interest rate of
3.5% is converted into a quarterly rate (3.5%/4), and the number of years is
converted into quarters (=2*4).
► The
[type] argument has been set to 1, to indicate that the payment into the
investment is to be made at the beginning of each quarter.
► The
value returned from the function is negative, as this represents an outgoing
payment.
Notes
► The
PMT function can be used to figure out the future payments for a loan, assuming
constant payments and a constant interest rate.
For example, if you are borrowing $10,000 on a 24 month loan with an
annual interest rate of 8 percent, PMT can tell you what your monthly payments
be and how much principal and interest you are paying each month.
► The
payment returned by PMT includes principal and interest but will not include
any taxes, reserve payments, or fees.
► Be
sure you are consistent with the units you supply for rate and nper. If you
make monthly payments on a three-year loan at an annual interest rate of 12
percent, use 12%/12 for rate and 3*12 for nper. For annual payments on the same
loan, use 12 percent for rate and 3 for nper.
Common Problem
The result from the Excel Pmt
function is much higher or much lower than expected.
Possible Reason
When calculating monthly or
quarterly payments, users sometimes forget to convert annual interest rates or
the number of periods to months or quarters.
Solve this problem by ensuring
that the rate and the nper arguments are expressed in the correct units. I.e.:
|
months = 12 * years
|
monthly rate = annual rate / 12
|
|
quarters = 4 * years
|
quarterly rate = annual rate / 4
|
References
No comments:
Post a Comment