How to use CUMIPMT function in Excel?

The `CUMIPMT` function in Excel is used to calculate the cumulative interest paid on a loan or investment over a specified period. The syntax for the `CUMIPMT` function is as follows:

CUMIPMT(rate, nper, pv, start_period, end_period, type)

Here’s a breakdown of the parameters:

rate: The interest rate for each period.
nper: The total number of payment periods in the loan or investment.
pv: The present value, or principal amount, of the loan or investment.
start_period: The first period in the calculation. The period is specified as a number where 1 represents the first period.
end_period: The last period in the calculation.
type: Specifies when payments are due. Use 0 for end of the period and 1 for beginning of the period.

Example Usage:

Suppose you have a loan with the following details:

Annual interest rate: 5%
Loan term: 10 years
Loan amount: $10,000
You want to calculate the total interest paid from the 1st to the 2nd year.

To calculate this using the `CUMIPMT` function, follow these steps:

   =CUMIPMT(0.05/12, 120, 10000, 13, 24, 0)

Convert the annual interest rate to a periodic rate: If payments are monthly, divide the annual rate by 12. For our example: `5% / 12 = 0.4167% per month`.
Convert years to periods: Multiply the number of years by the number of periods per year. For monthly payments over 10 years: `10 * 12 = 120 periods`.
Apply the CUMIPMT function:

`0.05/12`: Monthly interest rate.
`120`: Total number of payment periods.
`10000`: Present value (loan amount).
`13`: The start period for 2nd year (the 13th month).
`24`: The end period for 2nd year (the 24th month).
`0`: Payments are due at the end of the period.

Notes:

The function will return a negative value because it represents cash outflow, i.e., the paid interest.
Ensure that the period terms (`start_period`, `end_period`) are consistent with the payment frequency (monthly, quarterly, etc.).
If the type is set to `1`, it means payments are due at the beginning of the period, which impacts the calculation of interest.

By using the `CUMIPMT` function with appropriate parameters, you can efficiently calculate cumulative interest over specified periods of your financing or investment.

facebooklinkedin