Compound Interest in Excel Using Data Tables PSS ID Number: Q39217 Article last modified on 02-26-1993 PSS database name: W_eXceL
2.x 3.00
WINDOWS
Summary:
The following information describes a method to calculate a table of ten compounded interest rates for x periods. These calculations are based on the principal amount of one dollar. To obtain the future value of a particular interest rate for different principal amounts, multiply the principal amount by the compounded rate. The compounded rate is found by cross-referencing the interest rate and period desired.
More Information:
To create the data table, do the following:
- Starting in Cell B3 and continuing downward, enter the periods in sequential order (B3:B13).
- Starting in C4 and moving across Row 2, enter the interest rates in sequential order (C4:C15).
- In Cell B2, enter the following formula: =1*(1+A1)^A2.
- Leave Cells A1 and A2 as empty “null” cells. They are used by the Data Table as place holders.
- Select the entire table, including the formula in B2. Choose Data Table and then enter A2 in the Column Entry box and A1 in the Row Entry box.
Excel will now create your data table. For ease of use, if your table extends over a large number of periods, use Formula Create Names. Create Names for the interest rates (C4:C15) and the periods (B3:B13). Entering the correct formula (i.e., =(interest_rate period) will allow easy access to compounding rates that may be buried in large tables.
Additional reference words: 2.0 2.00 2.1 2.10 3.0 3.00
Copyright Microsoft Corporation 1993.
