CUMPRINC function
The CUMPRINC function in Excel calculates the cumulative principal paid on a loan or investment over a specified range of periods. This function is useful for understanding how much principal has been paid down on a loan or investment between two periods.
Syntax
CUMPRINC(rate, nper, pv, start_period, end_period, type)
Parameters
rate: The interest rate for each period.- For example, if the annual interest rate is 6% and payments are made monthly, the monthly interest rate would be
0.06/12.
- For example, if the annual interest rate is 6% and payments are made monthly, the monthly interest rate would be
nper: The total number of payment periods in the loan or investment.- For example, if the loan is for 5 years with monthly payments,
nperwould be5 * 12 = 60.
- For example, if the loan is for 5 years with monthly payments,
pv: The present value (the principal amount of the loan or investment).- For example, if you are borrowing $10,000, the present value would be
10000.
- For example, if you are borrowing $10,000, the present value would be
start_period: The first period in which to calculate the principal.- For example, if you want to start calculating from the 2nd period, you would use
2for this parameter.
- For example, if you want to start calculating from the 2nd period, you would use
end_period: The last period in which to calculate the principal.- For example, if you want to calculate the principal paid between the 2nd period and the 5th period, you would use
5for this parameter.
- For example, if you want to calculate the principal paid between the 2nd period and the 5th period, you would use
type: The timing of the payments:- 0 (default) means payments are made at the end of each period.
- 1 means payments are made at the beginning of each period.
How It Works
The CUMPRINC function calculates the total principal paid between the start period and the end period of a loan or investment. The result will be a negative number, as it represents a cash outflow.
Example
Suppose you have a loan with the following details:
- Annual interest rate: 6% (monthly interest rate =
0.06 / 12 = 0.5%) - Loan amount (present value): $10,000
- Total periods: 5 years (60 months)
- Start period to calculate from: Period 1
- End period to calculate to: Period 12 (i.e., the principal paid in the first year)
The formula would be:
=CUMPRINC(0.06/12, 60, 10000, 1, 12, 0)
Explanation of the Example:
0.06/12: Monthly interest rate (6% annual rate divided by 12).60: Total number of periods (5 years * 12 months).10000: The present value (loan amount).1: Start period (Month 1).12: End period (Month 12).0: Payments are made at the end of each period.
The function will return the cumulative principal paid from Month 1 to Month 12 (the first year).
Important Notes
- The CUMPRINC function calculates principal payments, whereas the CUMIPMT function calculates interest payments.
- The result will typically be a negative number, as it represents money being paid out. To make it positive, you can multiply the result by
-1. - If you need to know the cumulative interest paid instead of principal, use the CUMIPMT function.
- The type argument specifies whether payments are made at the beginning or the end of the period. Typically, loans assume payments are made at the end of each period, which is why type is often set to
0.
Summary
The CUMPRINC function in Excel calculates the total cumulative principal paid on a loan or investment over a specified range of periods. It is useful for understanding how much of the loan principal has been repaid over time. The result will be negative, representing the principal paid out.