PMT
The PMT function returns the fixed periodic payment for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and a fixed interest rate.
PMT(periodic-rate, num-periods, present-value, future-value, when-due)
periodic-rate: A number value representing the interest rate per period. periodic-rate is entered as a decimal (for example, 0.08) or with a per cent sign (for example, 8%). periodic-rate is specified using the same time frame (for example, monthly quarterly, or annually) as num-periods. For example, if num-periods represents months and the annual interest rate is 8%, periodic-rate is specified as 0.00667 or 0.667% (0.08 divided by 12). periodic-rate can be negative, but the result returned by the function may be difficult to interpret.
num-periods: A number value representing the number of periods. num-periods is specified using the same time frame (for example, monthly, quarterly or annually) as periodic-rate. num-periods must be greater than or equal to 0.
present-value: A number value representing the initial investment, or the amount of the loan or annuity. present-value is often formatted as currency. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
future-value: An optional number value that represents the value of the investment or remaining cash value of the annuity (positive amount), or the remaining loan balance (negative amount), after the final payment. future-value is often formatted as currency. At the end of the investment period, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be the balloon payment due on a loan (negative) or the remaining value of an annuity contract (positive). If omitted, future-value is assumed to be 0.
when-due: An optional modal value that specifies whether payments are at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1).
end (0 or omitted): Payment is treated as being received or made at the end of each period.
beginning (1): Payment is treated as being received or made at the beginning of each period.
Notes
The currency shown in this function result depends on your Language & Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone & Region settings in iCloud Settings.
Example |
|---|
A hypothetical amortising loan of £200,000 (present-value is positive because it is a cash inflow) has an annual interest rate of 6% payable monthly (periodic-rate is 0.06/12) in arrears (when-due is 0), a term of 10 years (num-periods is 10*12), and a balloon (final) payment of £100,000 (future-value is –100000 because this is a cash outflow). =PMT(0.06/12, 10*12, 200000, –100000, 0) returns –£1,610.21 (negative because it is a cash outflow), the amount of each periodic payment during the term of the loan. The interest and principal portions of a payment can be found using IPMT and PPMT. =IPMT(0.06/12, 25, 10*12, 200000, –100000, 0) returns –£922.41, the interest portion of the first payment of the third year of the loan term (period is 25 because this is the 25th payment). =PPMT(0.06/12, 25, 10*12, 200000, –100000, 0) returns –£687.80, the principal portion of the first payment of the third year of the loan term (payment 25). Note that the interest component returned by IPMT, together with the principal component returned by PPMT, are equal to the monthly payment returned by PMT. =PMT(0.06/12, 10*12, 200000, 0, 0) returns –£2,220.41, the amount of each periodic payment during the term of the loan, assuming it is fully amortising (no balloon payment) over the 10-year term. =PMT(0.06/12, 20*12, 200000, 0, 0) returns –£1,432.86, the amount of each periodic payment during the term of the loan, assuming it is fully amortising over a 20-year term. |