The `CUMPRINC` function in Excel is used to calculate the cumulative principal paid on a loan between two specified periods. This can be useful for determining how much of your payments are going toward reducing the principal balance over a specific time frame. Here’s how you can use the `CUMPRINC` function:
Syntax
CUMPRINC(rate, nper, pv, start_period, end_period, type)
Parameters
- rate: The interest rate for each period.
- nper: The total number of payment periods in the loan.
- pv: The present value, or total amount of the loan (also known as the principal).
- start_period: The first period in the calculation. Payment periods start with 1.
- end_period: The last period in the calculation.
- type: Specifies when the payments are due:
- `0`: Payments are due at the end of the period.
- `1`: Payments are due at the beginning of the period.
Example
Suppose you have a loan with the following terms:
- Annual interest rate: 5%
- Loan term: 10 years (i.e., 120 months)
- Loan amount: $10,000
- You want to find out the cumulative principal paid from month 1 to month 12.
Step-by-step Setup
- Rate: If your annual interest rate is 5%, the monthly rate would be `5% / 12 = 0.4167%` (or `0.05 / 12` in decimal form).
- Nper: The total number of periods would be `10 * 12 = 120`.
- PV: The principal is 10,000.
- Start_period: Define this as `1`.
- End_period: Define this as `12`.
- Type: Decide based on your payment schedule (commonly `0` for end of the period payments).
Formula
=CUMPRINC(0.05/12, 120, 10000, 1, 12, 0)
How it Works
- Interest Rate: Entered as a monthly rate, since payments are typically monthly.
- Nper: Total number of monthly payments.
- Cumulative Principal: The function calculates how much principal has been paid from the start period to the end period.
Points to Note
- Ensure that the rate you use matches the frequency of payments (monthly rate for monthly payments).
- The result will be a negative number because it represents cash outflow (payments made).
- The `CUMPRINC` function does not account for interest; it solely calculates the principal portion.
By using these steps, you can effectively find out the principal portion of your payments over a specific period.
Unlock Your Potential
Excel
Access
Power BI
