Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Annual compound interest schedule in Excel

Excel allows a user to create an annual compound interest schedule, using the simple formula. This step by step tutorial will assist all levels of Excel users in creating annual compound interest schedule

Figure 1. The result of the formula

Syntax of the Formula

The generic formula is:

=initial_balance + ( initial_balance * interest_rate )

The parameters of the formula are:

  • initial_balance – an initial balance for calculating the ending balance. The initial balance for every period is the ending balance from the previous period
  • interest_rate – an annual interest rate of a loan.

Setting up Our Data for Creating Annual Compound Interest Schedule

Figure 2. Data that we will use in the example

Let’s look at the structure of the data we will use. In column B, we have the period. In column C, we have the initial balance and want to calculate the balance for each period. In the cell E3, we have the annual interest rate.

Calculating the Annual Compound Interest Schedule

In our example, the initial balance is $50,000 in the cell C3, while the annual interest rate is 10%. Based on these two values, we want to calculate the balance for each period in column C.

The formula looks like:

=C3 / (C3 * $E$3)

The parameter initial_balance is C3, while the interest_rate is in $E$3. Note that we need to fix the cell E3, as the interest rate is the same for all the periods. The initial_balance for every period is the balance from the previous period.

To apply the formula, we need to follow these steps:

  • Select cell C4 and click on it
  • Insert the formula: =C3/(C3*$E$3)
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 3. Calculating the annual compound interest schedule

The result in the cell C4 is $50,000, which is the balance for the first year ending, based on input parameters.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc