How to calculate total interest paid on a loan in Excel

You can watch a video tutorial here.

Excel is frequently used for calculations and supports all basic mathematical operations. Using formulas, you can use Excel to build different types of calculators in which you only need to change the parameters to get the result. This saves you the problem of doing each step of the calculation repeatedly. For example, you can build a calculator to calculate the total interest paid on a loan based on the loan amount, interest rate, and period of payment. In Excel, there are 2 ways of doing this. 

1. CUMIPMT() function: this returns the cumulative interest between a start and end period
   1. Syntax: CUMIPMT(rate, nper, pv, start_period, end_period,[type])
      1. rate: the interest rate
      2. nper: the total number of payments
      3. pv: the principal
      4. start_period: the number of the first payment (number of payments)
      5. end_period: the number of the last payment 
      6. [type] optional: specify whether the payment is due at the beginning or end of the loan
2. Use the formula:
   1. Total Interest paid = Total amount paid – Principal amount
      1. Total amount paid = Monthly payment * Total number of payments
         1. Monthly payment is computed using the PMT() function
         2. PMT() function: this calculates the monthly payment for a loan
            1. Syntax: PMT(rate, nper, pv, [fv], [type])
               1. rate: the interest rate
               2. nper: the total number of payments
               3. pv: the principal
               4. [fv] optional: remaining cash balance after the last payment is made, by default this is zero
               5. [type] optional: specify whether the payment is due at the beginning or end of the loan
         3. The total number of payments is computed using the Period of the loan (years) and the Payment frequency e.g. a 5-year loan with monthly payments will have 60 payments (5*12)

Option 1 – Use the CUMIPMT() function

Step 1 – Find the monthly interest rate

• The payment frequency is ‘Monthly’ so the monthly interest rate is to be computed
• Select the cell for the monthly interest to be displayed
• Type the formula using cell references:

= Interest rate (annual)/12

• Press Enter

Step 2 – Find the total number of payments

• Select the cell for the total number of payments to be displayed
• Type the formula using cell references:

= Period of the loan (years) * 12

Because the payment frequency is monthly, the number is multiplied by 12

• Press Enter

Step 3 – Create the formula using CUMIPMT()

• Select the cell for the total interest to be displayed
• Type the formula using cell references:

= -CUMIPMT(Monthly interest, Total number of payments, Loan amount,1,120)

Note: There are 120 total payments so the start_period is 1 and the end_period is 120. The function returns a negative number so a minus sign is added at the beginning to make the result positive.

• Press Enter

Option 2 – Use the formula

Step 1 – Find the monthly interest rate

• The payment frequency is ‘Monthly’ so the monthly interest rate is to be computed
• Select the cell for the monthly interest to be displayed
• Type the formula using cell references:

= Interest rate (annual)/12

• Press Enter

Step 2 – Find the total number of payments

• Select the cell for the total number of payments to be displayed
• Type the formula using cell references:

= Period of the loan (years) * 12

Because the payment frequency is monthly, the number is multiplied by 12

• Press Enter

Step 3 – Compute the monthly payment amount 

• Select the cell for the monthly payment to be displayed
• Type the formula using cell references:

= PMT(Monthly interest, Total number of payments, – Loan amount)

Note: The PMT() function returns a negative number, so a minus sign is put in front of the ‘Loan amount’ to make the result a positive number.

• Press Enter

Step 4 – Calculate the total interest

• Select the cell where the result is to be displayed
• Type the formula using cell references:

= (Monthly payment * Total number of payments) – Loan amount

• Press Enter