How to use the CUMIPMT function
What is the CUMIPMT function?
The CUMIPMT function calculates the accumulated interest based on a start and end period on a loan.
What is accumulated interest?
Accumulated interest refers to the total amount of interest earned on an investment or paid on a loan up to a specified date. This process of interest earning interest is called compounding.
What is a loan?
A loan is an amount of money you borrow from an individual or entity. The lender is usually a corporation, financial institution, or government, however it can also be an individual. A lender is an entity or individual that makes funds available for a borrower that can be a person or a business. The borrower agrees to repay the loan amount plus interest over an agreed-upon period of time.
What are the start and end period on a loan?
The start period or start date is the date the loan begins and interest accrual starts. The end period or maturity date is the final due date when the loan must be fully repaid.
The CUMIPMT function lets you calculate the accumulated interest based on a start and end date, however, these dates must not necessarily be the start and end date of the loan.
For example, a loan is to be repaid after 5 years. The CUMIPMT function lets you calculate the accumulated interest between year 3 and 4.
CUMIPMT function Syntax
CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMIPMT function Arguments
rate | Required. The interest rate. |
nper | Required. The total number of periods in an annuity.
Examples: |
pv | Required. Present value. |
start_period | Required. The first period. |
end_period | Required. The last period. |
type | Required. When payments are due.
0 - End of period. |
What is an annuity?
An annuity is a series of equal payments made over a defined period of time. The payments are usually made monthly, quarterly, or annually. These payment intervals are the "periods" that nper counts.
For example, if an annuity pays out quarterly for 3 years, there would be:
4 payments per year
3 years
So nper = 4 * 3 = 12 total payment periods
What is interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is a monthly payment?
A monthly payment is a scheduled payment that occurs every month as part of an ongoing loan, mortgage, or other finance agreement.
What is a quarterly payment?
A quarterly payment is a scheduled payment that occurs every quarter as part of an ongoing loan, mortgage, or other finance agreement.
CUMIPMT function example
The image above shows the CUMIPMT function in cell E3 calculating the accumulated interest for month 25 to 36 for a 10 year loan of 100 000.
Formula in cell E3:
The first argument is the interest rate per year, we calculate the interest on a monthly basis so the argument has to be divide with 12, there are 12 months in a year.
The second argument is the number of periods the interest is calculated for, 10 year * 12 = 120 months or periods.
CUMIPMT function not working
The CUMIPMT function returns
- #NUM error value if:
- rate <= 0
- nper <= 0
- pv <= 0
- start_period < 1
- end_period < 1
- start_period > end_period
- type is not 0 (zero) or 1
Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10-year period.
This image shows the interest for months 25 to 36 and the cumulative interest. The amount in cell G22 is the same as in the top image cell E3.
Functions in 'Financial' category
The CUMIPMT function function is one of many functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form