How to Calculate Compound Interest Using the Circular Reference in Excel

Calculating the compound interest using circular referenced cells saves time in the manual figuring of varied interest rates. For example, you can quickly compute the earnings on a variable interest bearing checking account or tally the compounded interest earned on an investment. By creating circular referenced cells, you can alter the contents of cells without changing the formulas. The spreadsheet can then be copied and pasted in another spreadsheet, ready to calculate different investments when needed. Follow the steps below to learn one of the easiest ways to accomplish these tasks and be ready to tally accurately without multiplying numerical stress.

Instructions

1. 

   In a blank spreadsheet in Excel, enter the "Principle" amount in a cell. This amount reflects a dollar value that earns interest.

2. 

   Enter the "Months" and corresponding "Interest Rates" in cells to represent those months of earning interest on the Principle amount.

3. 

   Format the "Interest Rates" as numbers with three decimal spaces. (To do this, highlight the range of cells containing the interest rates. Select "Format" from the toolbar pull-down menu. Select "Cells." Choose "Number" from the category list. Click on the up-arrow beside the "Decimal" list box and select "3." Click "OK" to exit the Format Cells dialog box.

4. 

   Create a cell to enter the formula to calculate the compounded interest.

5. In the formula toolbar, enter the syntax, " =FVSCHEDULE " and reference the cell containing the principle and the range of cells containing the variable interest rates.

6. If needed, alter information in the circular referenced cells that contain the "Principle" and the "Interest Rates." The "Compounded Interest" cell will automatically calculate.

Tips & Warnings

• To create a separate cell indicating the compounded interest amount separate from the principle amount, use the following formula:

• =FVSCHEDULE(PRINCIPLE, RANGE OF INTEREST RATES)-PRINCIPLE

• This example references step 5 but subtracts the Principle figure at the end of the formula:

• =FVSCHEDULE(B6,E8:E19)-B6

• It is essential for the "Principle" cell to be shown as currency and the "Interest Rate" cells as number format as indicated in step 3 above.

• The Analysis ToolPak Add-in must be activated for the formula to work and not return the "#NAME?" error. To add this ToolPak go to "Tools" and select "Add-Ins," then select the "Analysis ToolPak" and click "OK" to exit the dialog box.