XL: How to Calculate Interest Paid for Multiple Periods (71952)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 2002
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q71952

SUMMARY

You can use the CUMIPMT worksheet function to return the cumulative interest paid on a loan between a start period and an end period.

You can also use the IPMT worksheet function to return the interest payment for a single given period for an investment based on periodic and constant payments, and a constant interest rate. To calculate the total interest paid over a range of time (multiple periods), the IPMT function can be used in an array formula as described in the "More Information" section.

MORE INFORMATION

Example 1: CUMIPMT Worksheet Function

To use the CUMIPMT worksheet function to determine the total interest paid for a specific period of time, follow the steps in the following example.

This example assumes that you want the calculate the cumulative interest for the first 12 months of a loan with an annual interest rate of 9 percent, the length of the loan is equal to 360 months, and the present value of the loan is equal to negative $125,000.
  1. Start Excel and create a new workbook.
  2. Determine the interest rate per period:

    Nine percent per annum divided by 12 months per year returns .0075.
  3. Type the following formula in the worksheet:

    =CUMIPMT(0.0075,360,125000,1,12,0)

    NOTE: If the CUMIPMT worksheet function is not available, you must install the Analysis Toolpak add-in.

  4. The formula returns -11215.34 (-$11,215.34).

Example 2: IPMT Worksheet Function

To use the IPMT worksheet function to calculate the same data, follow these steps:
  1. Start Excel and create a new workbook.
  2. Type the following formula in the worksheet:

    =SUM(IPMT(.0075,ROW(A1:A12),360,-125000))

  3. Press CTRL+SHIFT+ENTER to enter the formula as an array.
  4. The formula returns 11215.34 ($11,215.34).
The ROW function is used in this formula to return an array of period numbers, and any range can be used here. In this example, ROW(A1:A12) returns {1;2;3;4;5;6;7;8;9;10;11;12}.

This method works for any other range of periods. For example, to calculate the interest paid for the second year, type the range A13:A24 in place of A1:A12 in the ROW function.

REFERENCES

For more information about the IPMT worksheet function, click Microsoft Excel Help on the Help menu, type ipmt in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the CUMIPMT worksheet function, click Microsoft Excel Help on the Help menu, type cumipmt in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the ROW worksheet function, click Microsoft Excel Help on the Help menu, type row worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about install and use the analysis toolpak, click Microsoft Excel Help on the Help menu, type atp in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:8/15/2005
Keywords:kbhowto kbinfo KB71952
©2004 Microsoft Corporation. All rights reserved.