Now you can calculate the total interest you will pay on the load easily as follows: 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: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)List 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 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) 12Excel formula: Calculate loan interest in given year excel loan calculate total amountexcel loan calculate total amountExcel formula: Calculate loan interest in given year 16/10/2018 · In the example illustrated below, the operation syntax inserted into the formula bar of cell B6 is thus-=PMT (B3/12,B2,-B1) This calculates the monthly payment with interest for the loan. Figure 2. of Excel PMT Function. Loans consist of 4 basic parts. The Loan amount, Rate of Interest, the loan duration (number of regular payments), and an Worked Excel Example to Calculate total Loan Amount Before using the =PMT equation set up an excel work book with the required information - interest rate, amount to be borrowed and number of installments. In the worked example below a loan of 1,000 is required which be paid back over 12 monthly installments at monthly interest.

Tags: