How to Calculate Compound Interest in Excel

Compound interest is the interest earned on both the initial principal and on the accumulated interest of previous periods. Excel provides several ways to compute it quickly, whether you need a singleperiod result or a full amortization schedule.

Key Concepts

• Principal (P) the initial amount of money.
• Rate (r) interest rate per period (expressed as a decimal).
• Number of periods (n) how many compounding intervals.
• Future Value (FV) the amount after interest is applied.

Basic Formula

The mathematical expression for compound interest is:

FV = P (1 + r)ⁿ

In Excel you can write this as:

=A1*(1+B1)^C1

where:

Cell	Content
A1	Principal
B1	Rate per period (e.g., 0.05 for 5%)
C1	Number of periods

Using the FV Function

Excels builtin FV function handles the same calculation and adds flexibility for regular payments.

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

• rate interest rate per period.
• nper total number of periods.
• pmt payment each period (0 if none).
• pv present value (entered as a negative number).
• type 0 = end of period (default), 1 = beginning.

Example $5,000 invested at 6% annual interest, compounded yearly for 10 years:

=FV(0.06, 10, 0, -5000)

The result will be $8,954.24.

Monthly Compounding Example

Suppose you invest $3,000 at an annual rate of 4.5%, compounded monthly for 5 years. Convert the annual rate to a monthly rate and the years to months:

A1 = 3000      // principalB1 = 0.045/12  // monthly rateC1 = 5*12      // total monthsD1 = =FV(B1, C1, 0, -A1)

Cell D1 will display $3,795.21.

Creating an Amortization Table

When you need to track balance, interest earned, and cumulative interest each period, set up a table:

Period	Beginning Balance	Interest	Ending Balance
0	=A1
1	=E2	=B2*$B$1	=B2+C2

Explanation:

• Column B (Beginning Balance) for row 2 references the ending balance of the previous row.
• Column C calculates interest: =B2*$B$1, where $B$1 holds the period rate.
• Column D adds interest to the beginning balance.

Copy the second row down for as many periods as required. The final balance matches the FV calculation.

Handling Different Compounding Frequencies

Excel can adapt to daily, quarterly, or any custom frequency. Use the following general steps:

1. Determine compound frequency (f): e.g., 12 for monthly, 4 for quarterly.
2. Convert the annual rate to the periodic rate: =annual_rate/f.
3. Convert total years to periods: =years*f.
4. Apply the FV function with the periodic rate and period count.

Example 7% annual rate, compounded quarterly for 3 years:

rate = 0.07/4      // 0.0175 per quarternper = 3*4        // 12 quartersFV = =FV(rate, nper, 0, -2000)   // $2,000 principal

The future value is $2,516.31.

WhatIf Analysis with Data Tables

Excels Data Table tool can show how the future value changes with different rates or periods.

1. Set up a grid with rates (or periods) across the top and the other variable down the side.
2. In the topleft cell of the grid, reference the FV formula.
3. Select the entire grid and choose Data WhatIf Analysis Data Table.
4. Specify the cell that holds the rate (Column Input) and the cell that holds the period count (Row Input).

The table instantly fills with future values for every combination, useful for sensitivity analysis.

Tips and Common Pitfalls

• Use absolute references (e.g., $B$1) for rates and principal when copying formulas.
• Enter cash outflows (principal, payments) as negative numbers; Excel returns a positive future value.
• Remember to convert percentages to decimals (5% 0.05) before using them in formulas.
• For daily compounding, use 365 as the frequency, but be aware of leapyear differences.
• If you need continuous compounding, use the formula =P*EXP(r*t), where EXP is the exponential function.

Conclusion

Excel makes compoundinterest calculations easy, from a single line formula to a full amortization schedule. By mastering the FV function, basic arithmetic formulas, and Excels datatable feature, you can model savings, investments, and loan repayment scenarios with confidence.