INTRODUCTION
In our lives, we always come through ups and downs mentally, physically and financially too.
When it comes to the finances, we might need to apply for a loan. The loan is an amount burrowed from any financial institution on a fixed rate of interest and for a fixed term . We pay the emi at regular intervals.
The function which we are going to discuss helps us find out the cumulative interest to be paid in a certain period of time. The function is CUMIPMT FUNCTION IN EXCEL.
CUMIPMT function comes under the FINANCIAL FUNCTIONS category in Excel.
CUMIPMT returns the cumulative interest paid on a loan between a starting period and ending period.
PURPOSE OF CUMIPMT IN EXCEL
CUMIPMT returns the cumulative (Total interest accumulated) between a starting and ending date.
When we have taken a loan , the interest keeps on accumulating. With the help of this function we find out the accumulated interest on the particular value.
PREREQUISITES TO LEARN CUMIPMT
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the financial terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned aboveWhat Excel does? How to use formula in Excel?
SYNTAX: CUMIPMT FUNCTION
The Syntax for the function is
=CUMIPMT(INTEREST RATE, PERIOD OF PAYMENT, CURRENT VALUE, STARTING PERIOD, ENDING PERIOD, TYPE)
INTEREST RATE is the rate of interest of the loan. [Rate needs to be divided as per the number of payments]
PERIOD OF PAYMENT is the total period of the payment for which the payment will be done.
CURRENT VALUE is the current principle value.
STARTING PERIOD is the number showing the period of calculation of interest. It starts with 1. .
ENDING PERIODis the number showing the last period of calculation.
TYPE is the type determining the time of payment.
0 Payment at the end of period
1 Payment at the starting of the period.
EXAMPLE:CUMIPMT IN EXCEL
DATA SAMPLE
Let us try an example for the calculating the interest for a particular period.
Suppose we have the loan of 100000 dollars.
We took the loan for one year and will be paying monthly.
Rate of interest is 10% per annum.
Let us calculate the cumulated interest for the complete period.
Let us finalize the arguments for our formula
INTEREST RATE =10/12% as monthly payment so interest rate also converted to monthly.
NUMBER OF PAYMENT PERIODS 12 as payment is monthly for one year.
CURRENT VALUE is 100000 dollars
STARTING PERIOD is 1
LAST PERIOD is 12
TYPE is 0
STEPS TO USE COUPPCD
The data is put in the cells from J12 TO J17.For the resultwe put the formula in J19 as
=CUMIPMT(J12,J13,J14,J15,J16,0)
As we paid the interest monthly, the rate of interest was divided by 12.
The result comes out to be 5499.06 which will be the cumulative interest for the 12 months.
CONFUSION CLARIFICATIONS
CUMULATIVE INTEREST
Many people get confused in this after finding out the interest for a year and comparing it with the interest found by these formulas. For example, the 10% interest for 100000 should be 10000 but it comes out to be only around 5500. So , we should keep in mind that we are paying the interest and principle back every month and interest is calculated only on the left out amount and not on the complete amount. That is why this logic won’t work here.