Annual compound interest schedule

Annual compound interest schedule Excel formula

To calculate annual compound interest, you can use a formula based on the SEQUENCE, FV function, starting balance, and annual interest rate.

How to calculate the annual compound interest schedule?

Steps to calculate the compound interest over multiple periods in Excel:

  1. Select cell A1.
  2. Type =C5 * (1 + interest)^(SEQUENCE(n+1,1,1,1)-1)
  3. Here, C5 is the starting balance, C6 is the interest rate, and n is the number of years.
  4. Press Enter
  5. The formula returns all balances for n years automatically in a single spill range.

Example

In this example, we have a dataset in range B2:C5 defining an initial balance, annual interest rate, and the number of years for compounding.

The goal is to calculate the compound interest balance for each year and display the results in column F. The formula in cell F3 uses the SEQUENCE function to generate a series of year indices, which are applied in a compound interest formula that multiplies the starting balance by the annual rate raised to the power of each year’s term.

Formula:

=C3* (1 + C4)^(SEQUENCE(C5+1,1,1,1)-1)

Annual compound interest schedule Excel formula

Why use this Formula?

  • The formula automatically calculates and spills the results into subsequent cells without copying or dragging them.
  • Adjust the starting_balance, interest, or year values to recalculate for different scenarios instantly.
  • A single formula handles all the calculations for any number of periods.

Explanation

Here’s a detailed breakdown of the formula:

  1. C3: refers to the starting balance, which is 20,000 and this value will act as the principal amount for the compound interest calculation.
  2. 1 + C4: C4 is the annual interest rate (7%, or 0.07 in decimal form). Adding 1 results in the growth factor: 1 + 0.07 = 1.07. This growth factor represents the multiplier applied to the balance each year to include the interest.
  3. SEQUENCE(C5+1,1,1,1): C5 specifies the number of years (5 years). SEQUENCE(C5+1, 1, 1, 1) generates a sequence from 0 to 5 (inclusive) because C5+1 ensures the inclusion of the “Start” balance. This results in {0; 1; 2; 3; 4; 5} where each number represents a specific period.
  4. SEQUENCE(C5+1,1,1,1)-1: Subtracting 1 from the sequence shifts the time periods to start from 0 instead of 1. The sequence now becomes {0; 1; 2; 3; 4; 5}.
  5. (1 + C4)^(SEQUENCE(C5+1,1,1,1)-1): This applies the growth factor raised to the power of each time period. For t = 0, the growth factor becomes 1.07^0 = 1 (no growth for the starting balance). For t = 1, it becomes 1.07^1, then 1.07^2 for t = 2, and so on.
  6. Finally, the starting balance (C3) is multiplied by the growth factor for each period, generating the balance at each stage.

Get the annual compound interest schedule with the FV function

The FV function calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax: =FV(rate, nper, pmt, pv, [type])

  • rate: Interest rate per period.
  • nper: Total number of payment periods.
  • pmt: Payment made each period (optional).
  • pv: Present value (principal investment).
  • [type]: Optional, indicates when payments are made (0 = end of period, 1 = beginning).
Annual compound interest schedule FV function

In the formula, C3 is the annual interest rate of 7%, representing the balance’s growth factor. The second argument, “1” specifies 1 period for each calculation, corresponding to a year. The formula is recalculated for each year individually. The third argument, “0” represents no additional payment (no periodic contributions to the balance). The fourth argument is the present value (PV), the starting balance 20,000. We use a negative value because this amount is an outgoing investment.

Manual Compound Interest Formula

Finally, you can use the formula below:

=C2 * (1 + C3)^Year

The main disadvantage of this formula is that it requires a separate formula for each year and does not dynamically spill values.

Download the practice file.