Microsoft KB Archive/71952

= XL: How to Calculate Interest Paid for Multiple Periods =

Article ID: 71952

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



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 &quot;More Information&quot; section.



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.  Start Excel and create a new workbook. Determine the interest rate per period:

Nine percent per annum divided by 12 months per year returns .0075. 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.

 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:  Start Excel and create a new workbook. Type the following formula in the worksheet:

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

</li> Press CTRL+SHIFT+ENTER to enter the formula as an array.</li> The formula returns 11215.34 ($11,215.34).</li></ol>

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.

<div class="references_section">