Syntax. PMT(Rate,Nper,Pv,Fv,Type)
Definition. This function returns the amount of the annuity (regular payment) of a process that is part of an annuity calculation. For a repayment calculation, this is the annuity of a loan that is paid off though annuity repayment.
Important
The finance mathematical benefit principle used in Excel,
Payment of the creditor + Payment of the debtor = 0
means that you need to differentiate between disbursements and deposits in regard to the leading sign (borrowing and repayment, investment and disinvestment).
Here Excel differs from the benefit principle used in finance mathematical literature.
Payment of the debtor = Payment of the creditor
Arguments
Rate (required) The period interest rate. This is frequently an annual interest rate.
Nper (required) The number of interest dates for the entire duration. It is assumed that these dates coincide with the annuity payment dates.
Pv (required/optional) The start value of one payment direction. For disbursement plans, this is the account balance at the beginning of these considerations; for repayment plans, it is the loan amount.
Fv (required/optional) The end value of one payment direction. In savings processes, it is the desired savings goal; for disbursement plans, it is a desired account balance at the end of the considerations; and for repayment plans, it is the desired residual debt in the given time.
Type (optional) Specifies whether the regular payments take place at the start of the periods (Type = 1) or at the end of the periods (Type = 0). If this argument is not specified, Excel calculates with Type = 0.
One of the two arguments Pv or Fv must be specified.
Background. The five functions PV() = cash value, FV() = future value, PMT() = regular payment, NPER() = interest or payment periods, and RATE() = interest rate, have the following relationship when the benefit principle used by Excel is implemented:
where M is the Type (timing of payments).
The cash value interest is compounded, and so are the regular payments. Finally, the sum is compared to the future value.
Using one of these functions is equal to the respective basic finance mathematical task: calculation of an unknown variable from the preceding equation, when the other variables are known. The functions thus solve the equation for each of their members. For RATE() an approximation calculation is performed.
Note
Interest rates are usually specified as annual interest rates. The functions used here work correctly only if the periods match the interest rate. With intra-annual interest yield, the annual interest rate is usually distributed equally over the periods: 12 months at one twelfth of the interest rate, three months at a quarter, and half a year at half.
Examples. The headings of the following examples reflect the general usage of finance mathematical terms.
Annuity Calculation. A 60-year-old has saved $100,000.00 and would like to receive an additional monthly annuity. He agrees to 4.5 percent p.a. on the respective current balance for a duration of 15 years. How high is the annuity, if he is planning to use up all the capital? How high is it if he want to still have $10,000.00 in the account after 15 years?
For the first question, you determine the annuity with
=PMT(4.5%/12,15*12,-100000)
and get a result of $764.99. The second issue can be solved with
=PMT(4.5%/12,15*12,-100000,10000)
to get the result of $725.99. The minus sign at Pv should be interpreted as waiving immediate disbursement. Fv and PMT() then “show” the other payment direction.
The calculation is based on compound interest within one year. Because compound interest cannot be used for a regular savings account, the result is a little bit theoretical.
Repayment Calculation (Annuity Repayment). Assume that a debtor has taken out an annuity loan of $100,000. An interest rate of 5.5 percent p.a. with an interest commitment duration of five years was agreed on. The bank informs the debtor that the residual debt after five years is still $80,000.
How high is the (constant) monthly payment (repayment plus interest) for the debtor?
=PMT(5.5%/12,5*12,100000,-80000)
delivers the result: $748.69 (with a minus sign).
Unlike with a savings account, a mortgage loan uses a monthly interest of a twelfth of the agreed-upon yearly interest rate (nominal interest).
Important
Using formulas might be different from keeping detailed notes of an account history with deposits and withdrawals, because of rounding errors. This happens because the numbers for a savings account are rounded to two decimal places. If you are recreating such an account with Excel, you should use the function ROUND() for intermediary steps that involve money. Simply limiting the display of the cell value to two decimal places often leads to incorrect results.