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

Introduction

In this lesson, you will learn how to calculate the interest on a loan using Microsoft Excel.

This video illustrates the lesson material below. Watching the video is optional.

Using Excel to Calculate the Payment and Total Interest on a Loan

Figure 1 below illustrates all of the different variables that are needed to calculate the payment. Here is a listing of the information titles in each cell in column A.

  • A2: Annual Interest Rate
  • A3: Period Rate (rate). This is the Annual Interest Rate divided by the Payments per year. It will appear as an argument in the payment function.
  • A4: Number of Years
  • A5: Payments per Year
  • A6: nper. This is the number of payments per year times the number of years. It will appear as an argument in the payment function.
  • A7: Loan Amount (Pv = Present Value). This will appear as an argument in the payment function.
  • A8: Monthly Payment (use PMT Function)
  • A9: Loan Amount with Interest. This is the monthly payment times the nper.
  • A10: Interest Expense. This is the Loan Amount with Interest + Loan Amount.
  • A11: Average Monthly Interest Expense. This is the Interest Expense divided by nper.

Column B will contain the numbers. Notice that in Figure 1, period rate, nper, and loan amount are highlighted in yellow. These are the three variables that go into the payment function.

The figure shows that period rate, nper, and loan amount are highlighted in yellow. These are the three variables that go into the payment function.

Figure 1

Example 1
Find the monthly payment and total interest on a loan with an annual interest rate of 6% (.06) over five years.

First, type the known numbers into column B in Excel:

  • Annual Interest Rate: .06
  • Number of Years: 5
  • Payments per Year: 12

Use this information to calculate the Period Rate (rate), and nper.

The known numbers .06 for Annual Interest Rate, 3 for Number of Years, and 12 for Payments per Year are typed under column B in the Excel spreadsheet.

Figure 2

The period rate is the portion of the annual interest rate that is paid on each payment, so it is the same as the annual rate divided by the number of times each payment is made each year.

  • In Excel, use cell references to calculate the period rate in cell B5. The formula looks like this: =B2/B5
  • Press Enter to calculate.
To calculate the Period Rate, the formula: =B2/B5 is used. Make sure to press enter to calculate the formula.

Figure 3

The Period Rate (rate) is 0.005. In other words, it is 0.5% each month. The nper is the total number of payments that are made over the course of the loan. This is calculated by taking the number of payments per year and multiplying it by the number of years.

  • In Excel, use cell references to calculate the nper in cell B6. The formula looks like this: =B5*B4
  • Press Enter to calculate.
To calculate the nper in cell B6, use the following formula: =B5*B4. Make sure to press Enter to calculate the formula.

Figure 4

The nper in this example is 60. The Loan Amount (Pv = Present Value) usually does not need to be calculated. In this example, it is given: $12,000.

The nper for the example in this image is 60. This also shows that the Loan Amount usually does not need to be calculated. In this example, it is given: $12,000.

Figure 5

Use all of this information to calculate the monthly payment.

  • In cell B8, type =PMT(
  • Enter the needed arguments using cell references.
    • The rate is B3
    • The nper is B6
    • The Pv is B7
  • Separate each with a comma and include a right parenthesis. The formula looks like this: =PMT(B3,B6,B7)
  • Press Enter to calculate.
This figure shows how to calculate the monthly payment using the formula: =PMT(B3,B6,B7).

Figure 6

Excel shows that this is a negative number because it is a payment to be made. In Excel, sometimes a negative number is shown as red with parentheses around it. The loan payment is $231.99 every month for 60 months. This monthly payment pays for part of the principal, and it pays for the monthly interest charge.

This image shows the Monthly Payment is a negative number and colored red.

Figure 7

You will pay $231.99 every month for 60 months in order to pay off a loan of $12,000. Remember, this payment includes both the principal and the interest rate of the loan.

To calculate the total amount of interest paid over the 60 payments, multiply the monthly payment by the total number of payments or the nper.

  • In cell B9, type =B8*B6
  • Press Enter to calculate
To calculate the Loan Amount with Interest is calculate using the formula: =B8*B6.

Figure 8

The Loan Amount with Interest over five years is $13,919.62. This total amount for the cost of the loan includes the original amount of the loan plus the interest on the loan. Notice this is a negative number. To find the interest alone, add it to the loan amount because the loan amount is a positive number.

  • In cell B10, type =B9+B7
  • Press Enter to calculate.
This figure shows how to calculate the Interest Expense using formula: =B9+B7.

Figure 9

The total interest expense on this loan is $1919.62. To calculate the average monthly interest expense, divide this total interest amount by the number of payments made on the loan, or nper.

  • In cell B11, type = B10/B6
  • Press Enter to calculate.
This figure shows how to calculate the Average Monthly Interest Expense using the formula: =B10/B6.

Figure 10

The average monthly interest expense is $31.99.

The Average Monthly Interest Expense is $31.99.

Figure 11

Early payments on a loan mostly pay for interest. As the loan is paid over time, more and more of the payment pays for the principal.

Things to Remember


  • To calculate the payment and total interest on a loan, first arrange the known information on an Excel spreadsheet
  • Find the period rate and nper using this information
  • Use =PMT in Excel and supply the required arguments (rate, nper, Pv)

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, start by finding out the total amount that you have paid. You 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 you 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\)

    Put the above information into Excel so that it can do the calculations for you:

    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 you haven't calculated 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).

    Any time you begin with an "=" it tells Excel that you want it to make a calculation.

    To find the periodic rate, select the cell where you 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 select Enter.

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

    Next, 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. 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 select Enter.

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

    To calculate the monthly payment, 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 you want Excel to calculate the monthly payment, =PMT(B4,B7,B2) has been typed.

    To calculate the total loan amount with interest, multiply the monthly payment by the total number of payments (nper). Have Excel calculate that for you by typing in the following: =B8*B7

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

    Then select Enter.

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

    To calculate the interest paid, add the total amount with interest to the amount of the loan. 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 select Enter.

    This is the same spreadsheet as in the previous images. In column B line 10, where you 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, start by finding out the total amount that you have paid. You 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 you 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\)

    Put the above information into Excel so that it can do the calculations for you:

    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).

    Any time we begin with an "=" it tells Excel that you want it to make a calculation.

    To find the periodic rate, select the cell where you 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 select Enter.

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

    Next, 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. 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 select Enter.

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

    To calculate the monthly payment, 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 select Enter.

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

    To calculate the total loan amount with interest, multiply the monthly payment by the total number of payments (nper). Have Excel calculate that for you by typing the following: =B8*B7

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

    Then select Enter.

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

    To calculate the interest paid, add the total amount with interest to the amount of the loan. 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 select Enter.

    This is the same spreadsheet as in previous images. In cell B10, where you 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