Select the cell you will place the calculated result in, type the formula =CUMIPMT (B2/12,B3 12,B1,B4,B5,1), and press the Enter key. See screenshot: Note: In the formula, B2 is the annual loan interest rate, B2/12 will get the monthly rate; B3 is the years of the loan, B3 12 will get the total number of periods (months) during the loan; B1 is the To calculate the total interest for a loan in a given year, you can use the CUMIPMT function. In the example shown, the total interest paid in year 1 is calculated by using 1 for start period and 12 for end period. The The formula in F5 is: = CUMIPMT(5 % 12,60,30000,1,12,0)17/10/2017 · In this article we’ll see how to use as little as a single worksheet cell to calculate the total interest for a loan. To do so we’ll use the CUMIPMT worksheet function—this translates to Cumulative Interest Payment. This can often be more efficient than building out a formal amortization table, and provides exactly the same your loan data in Excel as below screenshot shown: 2. In Cell F3, type in the formula, and drag the formula cell’s AutoFill handle down the range as you need. =IPMT($C$3/$C$4,E3,$C$4 $C$5, $C$2) 3. In the Cell F9, type in the formula =SUM(F3:F8), and press the Enter key. Now you will get the total amount of interest payments for the formula: Calculate loan interest in given year Excel formula: Calculate loan interest in given year How to calculate interest payments per period or total with Excel formu…How to Use Excel to Calculate How Much a Loan Will Cost To calculate the periodic interest rate for a loan, given the loan amount, the number of payment periods, and the payment amount, you can use the RATE function. In the example shown, the formula in C10 is: = RATE(C7, C6, - C5) 12The interest rate is divided by 12 to find the monthly interest rate and the term is multiplied by 12 to determine how many monthly payments you will make. Step 4 Enter "=B4_B3_12" into cell B5 to calculate the total cost of the loan by multiplying the number of monthly payments times the …
