Explanation

For this example, we want to calculate the interest paid during each year in a 5-year loan of $30,000 with an interest rate of 5%. To do this, we set up CUMIPMT like this:

  • rate - The interest rate per period. We divide 5% by 12 because 5% represents annual interest.
  • nper - the total number of payment periods for the loan, 60.
  • pv - The present value, or total value of all payments now, 30000.
  • start_period - the starting period for a given year.
  • end_period - the ending period for a given year.

In the range F5:F9, here are the formulas used:

=CUMIPMT(5%/12,60,30000,1,12,0) // year 1
=CUMIPMT(5%/12,60,30000,13,24,0) // year 2
=CUMIPMT(5%/12,60,30000,25,36,0) // year 3
=CUMIPMT(5%/12,60,30000,37,48,0) // year 4
=CUMIPMT(5%/12,60,30000,49,60,0) // year 5

Note many values could be picked up directly with cell references, but are hardcoded in this example for readability.

Other periods

In this example, we are calculating interest by year, so periods are set up accordingly. However, you can adjust periods to calculate interest in any timeframe desired.

Explanation

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term), and the payment amount per period. One use of the PV function is to calculate the original loan amount, when given the other 3 components.

For this example, we want to find the original amount of a loan with a 4.5% interest rate, and a payment of $93.22, and a term of 60 months. The PV function is configured as follows:

rate - The interest rate per period. We divide the value in C5 by 12 since 4.5% represents annual interest:

C5/12

nper - the number of periods comes from cell C7, 60 monthly periods in a 5-year loan.

pmt - The payment made each period. This is the known amount of $93.22, which comes from cell C6. By convention, payments in PV are input as negative values.

With these inputs, the PV function returns 5,000.226, which is displayed as $5000 using number formatting. The actual loan amount is $5000 even, but the monthly payment is rounded to the nearest penny causing FV to return a slightly different result.