Languages
Back
Languages
Use Excel to Find the Payment and Total Interest on a Loan
> ... Math > Calculating Interest, Excel Functions > Use Excel to Find the Payment and Total Interest on a Loan

This lesson combines what you learned about calculating the payment on a loan using the PMT function with calculating the total amount of the loan in order to calculate the interest on a loan. As you watch this video, follow along in Excel. You can use Excel for your practice problems and the quizzes, so it will be helpful for you to practice.

Use Excel to Calculate the Total Interest Charged on a Loan

Video Source (07:26 mins) | Transcript

Total interest = Total amount of loan − Original principal amount.

Practice Problems

  1. Use Excel to calculate the total interest on a \($45,000\) loan for a small business making monthly payments with an interest rate of \(7.5\%\) over \(10\) years. (
    Solution
    x
    Solution:
    \($19,098.96\)
    )
  2. Use Excel to calculate the total interest on a \($45,000\) loan for a small business making monthly payments with an interest rate of \(5.25\%\) over \(15\) years. (
    Solution
    x
    Solution:
    \($20,114.09\)
    Details:
    To find out the total interest, we need to start by finding out the total amount that we have paid. We know the following:

    Amount of loan = pv = \($45,000\)

    Annual Interest rate = r = \(5.25\%\)

    Length of the loan = \(15\) years

    Payments per year = n = \(12\)

    Using this information we can find the following:

    \(\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0525}{12}\text{ or }\frac{5.25\%}{12}\)

    Total number of payments = \(nper = years \times n = 15 \times 12 = 180\)

    We put the above information into Excel so that it can do the calculations for us:

    An Excel spreadsheet with the above information separated into columns. Each line in column A contain the names of the arguments (or variables) as well as Excel’s abbreviation of each. Column B has corresponding numbers for each argument. The values for the arguments that we haven't calclated yet are blank. Column C has hints on how to do the calculations. Row 1: Loan Calculations, blank, Hints; Row 2: Amount of Loan (pv), $45000, blank; Row 3: Annual interest rate, 5.25%, blank; Row 4: Periodic Interest rate (rate), blank, (annual rate/number of payments per year); Row 5: Length of loan in years, 15, blank; Row 6: Payments per year, 12, blank; Row 7: Total number of payments (nper), blank, (number of payments per year times number of years); Row 8: Monthly Payment (use PMT function), blank, blank; Row 9: Total loan amount with interest, blank, (monthly payment times nper); Row 10: Total interest paid, blank, (total loan amount with interest plus amount of loan).

    We will have Excel do the calculations for us. Any time we begin with an "=" it tells Excel that we want it to make a calculation.

    To find the periodic rate, we select the cell where we want the calculation to be performed, then type:

    =B3/B6

    (B3 contains the annual interest rate and B6 contains the number of payments per year.)

    Then press enter.

    This is the same spreadsheet as in the previous image. In Column B line 4, where we want Excel to calculate the periodic interest rate, =B3/B6 has been typed.

    Next, we will calculate the total number of payments (nper). The total number of payments is found by multiplying the length of the loan by the number of payments per year. We will use Excel to calculate the total number of payments by selecting the corresponding cell and typing the following:

    =B5*B6

    (B5 contains the length of the loan in years and B6 contains the payments per year.)

    Then press enter.

    This is the same spreadsheet as in the previous images. In column B line 7, where we want Excel to calculate the number of payments, =B5*B6 has been typed.

    To calculate the monthly payment, we will type the following into the corresponding cell:

    =pmt(B4,B7,B2)

    (B4 contains the periodic rate or RATE, B7 contains the total number of payments or NPER, B2 contains the amount of loan or PV.)

    Then press enter.

    This is the same spreadsheet as in the previous images. In column B row 8, where we want Excel to calculate the monthly payment, =PMT(B4,B7,B2) has been typed.

    To calculate the total loan amount with interest we multiply the monthly payment by the total number of payments (nper). We will have Excel calculate that for us by typing in the following:

    =B8*B7

    (B8 contains the monthly payment. B7 contains the total number of payments.)

    Then press enter.

    This is the same spreadsheet as in the previous images. In column B row 9, where we want Excel to calculate the total loan amount with interest, =B8*B7 has been typed.

    To calculate the interest paid we add the total amount with interest to the amount of the loan. We will have Excel make the calculation by typing the following:

    =B9+B2

    (B9 contains the total amount with interest. B2 contains the amount of the loan.)

    Then press enter.

    This is the same spreadsheet as in the previous images. In column B line 10, where we want Excel to calculate the total interest paid, =B9+B2 has been typed.

    The value that appears in B10 is (\($20114.09\)).

    So the total interest paid is \($20,114.09\)
    )
  3. Use Excel to calculate the total interest on a car loan paid monthly given the following values: (
    Solution
    x
    Solution:
    \($2,494.39\)
    )
    • Amount of loan = \(13,500\)
    • Annual interest rate = \(5.75\%\)
    • Length of the loan = 6 years
  4. Use Excel to calculate the total interest on a car loan paid monthly given the following values: (
    Solution
    x
    Solution:
    \($1,818.09\)
    Details:
    To find out the total interest, we need to start by finding out the total amount that we have paid. We know the following:

    Amount of loan = pv = \($13,500\)

    Annual Interest rate = r = \(4.25\%\)

    Length of the loan = \(6\) years

    Payments per year = n = \(12\)

    Using this information we can find the following:

    \(\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0425}{12}\text{ or }\frac{4.25\%}{12}\)

    Total number of payments = \(nper = years \times n = 6 \times 12 = 72\)

    We put the above information into Excel so that it can do the calculations for us:

    An Excel spreadsheet with three columns and 10 rows. Column A contains the names of the arguments or variables as well as the Excel abbreviations. Column B contains the numbers we have so far. There are blank spaces where we will use Excel to make calculations. Column C has hints on how to do the calculations. Row 1: Headers of Loan Calculation, blank, Hints; Row 2: Amount of Loan (PV); $13500, blank; Row 3: Annual Interest Rate, 4.25%, blank; Row 4: Periodic Interest Rate, blank, (annual rate divided by number of payments per year); Row 5: Length of loan in years, 6, blank; Row 6: Payments per year, 12, blank; Row 7: Total number of payments (nper), blank, (number of payments per year times number of years); Row 8: Monthly Payment (use PMT function), blank, blank; Row 9: Total loan amount with interest, blank, (monthly payment times nper); Row 10: Total interest paid, blank, (total loan amount with interest plus amount of loan).

    We will have Excel do the calculations for us. Any time we begin with an "=" it tells Excel that we want it to make a calculation.

    To find the periodic rate, we select the cell where we want the calculation to be performed, then type:

    =B3/B6

    (B3 contains the annual interest rate and B6 contains the number of payments per year.)

    Then press enter.

    The same spreadsheet as in the previous image. We want Excel to calculate the periodic interest rate in cell B4 so =B3/B6 has been typed in that cell.

    Next, we will calculate the total number of payments (nper). The total number of payments is found by multiplying the length of the loan by the number of payments per year. We will use Excel to calculate the total number of payments by selecting the corresponding cell and typing the following:

    =B5*B6

    (B5 contains the length of the loan in years and B6 contains the payments per year.)

    Then press enter.

    This is the same spreadsheet as in previous images. In cell B7, where we want Excel to calculate the number of payments, =B5*B6 has been entered.

    To calculate the monthly payment, we will type the following into the correct cell:

    =pmt(B4,B7,B2)

    (B4 contains the periodic rate or RATE, B7 contains the total number of payments or NPER, B2 contains the amount of loan or PV.)

    Then press enter.

    This is the same spreadsheet as in previous images. In cell B8, where we want Excel to calculate the monthly payment, =PMT(B4,B7,B2) has been entered.

    To calculate the total loan amount with interest we multiply the monthly payment by the total number of payments (nper). We will have Excel calculate that for us by typing the following:

    =B8*B7

    (B8 contains the monthly payment. B7 contains the total number of payments.)

    Then press enter.

    This is the same spreadsheet as in previous images. In cell B9, where we want Excel to calculate the total loan amount with interest, =B8*B7 has been typed.

    To calculate the interest paid we add the total amount with interest to the amount of the loan. We will have Excel make the calculation by typing the following:

    =B9+B2

    (B9 contains the total amount with interest. B2 contains the amount of the loan.)

    Then press enter.

    This is the same spreadsheet as in previous images. In cell B10, where we want Excel to calculate the total interest paid, =B9+B2 has been typed in.

    The amount that appears in cell B10 is (\($1818.09\)). This is the total amount of interest paid on the loan.
    )
    • Amount of loan = \(13,500\)
    • Annual interest rate = \(4.25\%\)
    • Length of the loan = 6 years
  5. Use Excel to calculate the total interest on a mortgage paid monthly given the following values: (
    Solution
    x
    Solution:
    \($170,095.53\)
    )
    • Amount of loan = \(245,500\)
    • Annual interest rate = \(3.875\%\)
    • Length of the loan = 30 years
  6. Use Excel to calculate the total interest on a cell phone for \($800\) at \(5.25\%\) interest over two years making monthly payments. (
    Solution
    x
    Solution:
    \($44.48\)
    )

Need More Help?

  1. Study other Math Lessons in the Resource Center.
  2. Visit the Online Tutoring Resources in the Resource Center.
  3. Contact your Instructor.
  4. If you still need help, Schedule a Tutor.
Find the Total Cost of a Loan Given the Payment Information