Calculate cumulative loan principal payments

Calculate cumulative loan principal payments

To calculate the cumulative principal paid between any two loan payments in Excel, you can use a formula based on the CUMPRINC function.

Cumulative loan principal payments refer to the total amount of the original loan balance (principal) that has been repaid over a specified time, from the start of the loan to a particular point, or between two specific periods. When you make a loan payment, it consists of two parts.

Principal id the amount that reduces the original loan balance. Interest is the cost of borrowing money, which is calculated based on the outstanding loan balance. The cumulative principal payment is the total sum of the principal portions of all payments made up to a given time.

How to Calculate cumulative loan principal payments

  1. Select cell C10.
  2. Type =CUMPRINC(rate, nper, pv, start_period, end_period, type)
  3. Press Enter
  4. The formula returns the cumulative principal paid on a loan between specific periods.

Example

In this example, we have a dataset summarizing a loan repayment schedule with key inputs such as the loan amount of $10,000, an interest rate of 6.82%, and a term of 12 months. The goal is to calculate the total principal paid over the loan term and to track the cumulative principal at each monthly interval.

Formula:

=CUMPRINC(C4/C5,C5,C3,C6,C7,0)

Calculate cumulative loan principal payments

The formula in cell C10 uses the CUMPRINC function to determine the total principal between two specified periods, while the table in columns F and G displays the cumulative principal for each month. This setup allows for a detailed view of how the loan balance decreases over time.

Explanation

Here is a detailed breakdown of the formula:

CUMPRINC: This function calculates the cumulative principal paid on a loan between two specified periods. The formula requires details about the loan, including interest rate, total number of periods, and the specific range of periods for which the principal should be calculated.

  • C4 / C5 (Rate per period): C4 contains the annual interest rate (6.82% in the example). C5 contains the number of periods in a year (12 months, as this is monthly). Dividing the annual rate (C4) by the number of periods (C5) gives the monthly interest rate.
  • C5 (Number of total payments): This value represents the total number of periods for the loan. In this example, it’s 12 (indicating a 1-year loan with monthly payments).
  • C3 (Loan amount): The principal amount of the loan. Here, it is $10,000.
  • C6 (Start Period) and C7 (End Period): C6 specifies the first payment period (1 in this case). C7 specifies the last payment period for this calculation (12 here, meaning the entire loan duration).
  • 0 (Type of payment): The type argument specifies when payments are due: 0 means payments are due at the end of the period. 1 would mean payments are due at the beginning of the period.

The function determines the amount of the loan’s principal paid across the specified periods. In this case, it calculates the total principal paid from period 1 to period 12. The formula returns -10,000, indicating the total principal repaid throughout the loan (all 12 periods). The negative value reflects a cash outflow from the borrower’s perspective.

Cumulative loan principal payments using the PPMT function

In this example, we have a dataset summarizing a loan calculation with variables such as the loan amount ($10,000), interest rate (6.82%), and the number of payment periods (12 months).

The goal is to calculate the total principal paid between two specific periods, defined in the range from the Start Period (1) to the End Period (10). The formula in cell C10 uses the PPMT function combined with the ROW and INDIRECT functions, to sum up the principal portions of the payments within the specified range.

Formula:

=SUM(PPMT(C4/C5, ROW(INDIRECT(C6&”:”&C7)), C5, C3))

Use the PMT function to determine the monthly payment.

The result is $8286, the total principal between month 1 and month 10.

Download the sample file.