CUMPRINC
The CUMPRINC function returns the total principal included in loan or annuity payments over a chosen time interval based on fixed periodic payments and a fixed interest rate.
CUMPRINC(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 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).
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).
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 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 and Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone and Region settings in iCloud Settings.
Examples |
|---|
For a standard, amortising loan (such as a mortgage loan), the amount of principal reduction is higher in the later years compared to the early years. This example demonstrates just how much higher the later 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). =CUMPRINC(0.06/12, 30*12, 550000, 349, 360, 0) returns approximately -38,313.752523057, the amount of principal repaid in the last year (payments 349 to 360). =CUMPRINC(0.06/12, 30*12, 550000, 1, 12, 0) returns approximately -6,754.06441752226, the principal repaid in the first year (payments 1 to 12). The amount of principal repaid in the first year is only about 18% of the amount of principal repaid in the last year. |