CUMIPMT
The CUMIPMT function returns the total interest included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate.
CUMIPMT(periodic-rate, num-periods, present-value, starting-per, ending-per, when-due)
periodic-rate: A number value representing the interest rate per period. periodic-rate must be greater than 0, and is entered as a decimal (for example, 0.08) or with a percent 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).
num-periods: A number value representing the number of periods. num-periods must be 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).
starting-per: A number value representing the first period to include in the calculation. starting-per must be greater than 0 and less than ending-per.
ending-per: A number value representing the last period to include in the calculation. ending-per must be greater than 0 and also greater than starting-per.
when-due: A 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). Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1).
end (0): 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 12 and earlier, System Settings in macOS 13 and later, and Settings in iOS and iPadOS).
Examples |
|---|
For a standard, amortizing loan (such as a US mortgage loan), the amount of interest paid is higher in the early years, as compared to the later years. This example demonstrates just how much higher the early years can be. Suppose a mortgage loan with an initial loan amount of $550,000 (present-value), an interest rate of 6% per year payable monthly (periodic-rate is 0.06/12), and a 30-year term (num-periods is 30*12). =CUMIPMT(0.06/12, 30*12,550000, 1, 12, 0) returns approximately -32,816.2702425597, the interest for the first year (payments 1 through 12). =CUMIPMT(0.06/12, 30*12,550000, 349, 360, 0) returns approximately -1,256.58213702501, the interest for the last year (payments 349 through 360). The amount of interest paid in the first year is more than 26 times the amount of interest paid in the last year. |