How to calculate total interest paid on a loan in Excel?
Let’s say you have bought a house with a bank loan, and you need to pay the bank every month in coming years. Do you know how much interest you will pay on the loan? Actually, you can apply the CUMIPMT function to figure it out easily in Excel.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
For example, you have borrowed $100000 from bank in total, the annual loan interest rate is 5.20%, and you will pay the bank every month in the coming 3 years as below screenshot shown. 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:
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 total amount of loan; B4 is the first period you pay the bank, while B5 is the last period you pay the bank.
Now you will get the total interest you will pay. See screenshot: