PPMT
The PPMT function returns the principal portion of a specified loan or annuity payment based on fixed periodic payments and a fixed interest rate.
PPMT(periodic-rate, period, 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.
period: A number value representing the payment period for which you want to calculate the amount of principal or interest. period must be greater than 0.
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). =PPMT(0.06/12, 25, 10*12, 200000, –100000, 0) returns –£687.80 (negative because it is a cash outflow), the principal portion of the first payment of the third year of the loan term (period is 25 because this is the 25th payment). =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 (payment 25). =PMT(0.06/12, 10*12, 200000, –100000, 0) returns –£1,610.21, the amount of each periodic payment during the term of the loan. 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. |