Compound Interest Spreadsheet: Build One in Excel Easily

A compound interest spreadsheet in Excel can calculate how an investment may grow as interest earns interest. Put the starting balance in A2, the annual interest rate as a decimal in B2, the number of years in C2, and the number of compounding periods per year in D2; then use =A2*(1+B2/D2)^(C2*D2). This version assumes no additional deposits or withdrawals and a fixed rate.

What a compound interest spreadsheet calculates

Compound interest is interest calculated on both the original amount and interest previously added to it. A spreadsheet makes the effect easier to see across different rates, time periods, and contribution amounts. It can also help distinguish the amount deposited from the growth produced by interest.

The basic calculation uses four inputs: the initial balance, the annual nominal rate, the number of years, and the number of times interest compounds each year. For example, monthly compounding means 12 periods per year; quarterly compounding means four. The formula divides the annual rate by the periods per year, then applies that periodic rate for every period in the full term.

This is a projection, not a promise of what an account or investment will earn. Savings account rates can change, while investment returns vary and may be negative. The simple formula also leaves out taxes, fees, inflation, and any rules specific to a financial product.

Set up the inputs in Excel

In a blank worksheet, use column A for labels and column B for values. A compact input area could look like this:

A2: Starting balance; B2: 1000
A3: Annual interest rate; B3: 5%
A4: Years; B4: 10
A5: Compounding periods per year; B5: 12

For the formula above, place the inputs in A2:D2 as stated in the opening paragraph, or adjust the cell references to match your own layout. To avoid confusion, a single-row layout can place the values in A2, B2, C2, and D2, with the labels in row 1. Enter 5% as a percentage or 0.05 as a decimal; do not enter 5 to mean five percent, because Excel treats that as 500% in this calculation.

Common period values are 1 for annual, 2 for semiannual, 4 for quarterly, 12 for monthly, and 365 for daily compounding when that is how the product defines its calculation. Real account agreements may use daily balances, specific day-count conventions, or other rules, so the spreadsheet’s result may not exactly match a statement.

Use the basic compound interest formula

With the initial balance in A2, annual nominal rate in B2, years in C2, and compounding periods per year in D2, enter this formula in E2 to calculate the future balance without later deposits:

=A2*(1+B2/D2)^(C2*D2)

The caret raises a value to a power. If the balance is $1,000, the annual rate is 5%, the term is 10 years, and interest compounds monthly, the result is about $1,647.01. That is approximately $647.01 in growth above the initial $1,000. The calculation uses a constant 5% nominal annual rate, divided into 12 equal monthly rates.

Format the result cell as currency using Home > Number. The result’s displayed decimals depend on the number format, not on whether the underlying calculation is exact to the nearest cent. Keep input cells separate from formula cells so you can change assumptions without accidentally replacing the calculation.

Add regular contributions with Excel’s FV function

If you plan to add the same amount at the end of every compounding period, Excel’s FV function can include those deposits. Suppose A2 contains the starting balance, B2 the annual nominal rate, C2 the number of years, D2 the compounding periods per year, and E2 the contribution made at the end of each period. Enter the following in F2:

=FV(B2/D2,C2*D2,-E2,-A2,0)

Excel’s FV function takes the periodic rate, total number of periods, payment per period, present value, and payment timing. The minus signs mark the starting balance and contributions as money paid into the account, so the future value is returned as a positive amount. The final 0 means payments occur at the end of each period. Use 1 instead if deposits happen at the beginning of each period and that timing is appropriate for the account.

For a $1,000 starting balance, 5% annual nominal rate, 10 years, monthly compounding, and $100 deposited at each month-end, the result is approximately $17,175.23. The $13,000 total put in consists of the initial $1,000 plus 120 monthly deposits of $100; the projected growth is about $4,175.23. This estimate assumes the rate remains unchanged and that each contribution is made on schedule.

If there are no recurring contributions, use zero for E2 in this layout. FV also handles a zero interest rate, avoiding a division by the periodic rate that would occur in some hand-written annuity formulas.

Build a period-by-period schedule

A single future-value result is useful for a quick estimate, but a schedule shows how the balance changes over time. For a monthly schedule, put the period number in A2, the opening balance in B2, the deposit in C2, interest in D2, and closing balance in E2. Use the monthly rate in a fixed input cell, such as H2, and the initial balance in H3. At the first row, set A2 to 1, B2 to the initial balance, and C2 to the planned deposit. With the annual rate in H1 and deposits added at month-end, enter the following in D2:

=B2*($H$1/12)

With opening balance in B2, month-end deposit in C2, and interest for that period in D2, enter this in E2:

=B2+C2+D2

For the next period, put 2 in A3, link the opening balance to the previous closing balance, and copy the deposit and interest calculations down. With the prior closing balance in E2 and the same annual rate in H1, the next opening balance is:

=E2

Then copy the interest formula down so each row calculates interest on that month’s opening balance. This schedule assumes the deposit is made after interest is calculated at month-end. If deposits occur at the start of a period, the order of interest and deposit changes. Label the timing clearly rather than treating the two conventions as interchangeable.

Choose a rate and compounding frequency carefully

Use the rate that matches the question you are asking. A stated annual nominal rate of 6% with monthly compounding is not the same as an effective annual rate of 6%. The nominal rate divided by 12 produces a monthly periodic rate; compounding those monthly rates results in an effective annual yield slightly above 6%. If a bank quotes an annual percentage yield (APY), that figure generally already reflects the effect of compounding under the account’s assumptions. Do not feed an APY into a formula as though it were a nominal rate and then compound it again without checking the product’s definition.

Also align the contribution frequency with the spreadsheet periods. A monthly deposit model should normally use monthly periods and a monthly rate. If you use annual periods but deposit monthly, the calculation needs to account for each deposit’s different time in the account; simply adding twelve deposits to the year-end balance will not represent monthly compounding accurately.

For investments, a fixed return is a simplifying assumption rather than a forecast. You can model an average annual rate for illustration, but actual returns arrive unevenly and can include losses. A more useful planning exercise is often to compare several plausible rates, contribution amounts, or time horizons, while avoiding the impression that any one line is guaranteed.

Common spreadsheet mistakes and limitations

Entering 5 instead of 5%: Excel interprets 5 as 500%, not five percent. Enter 5% or 0.05.
Mixing annual and monthly units: Use a monthly rate and 120 periods for a ten-year monthly calculation, not 5% and 10 as though the units matched.
Ignoring deposit timing: Deposits at the beginning of a period earn interest for that period; deposits at the end do not. Set the FV type argument accordingly.
Treating a projection as guaranteed: A constant-rate formula cannot represent changing savings rates, market volatility, or missed deposits.
Forgetting costs and taxes: Fees and taxes can reduce the amount that remains invested. Model them separately when you know their terms, and do not assume a pre-tax projection is spendable cash.

Inflation is another separate issue. A future balance of $17,000 will not necessarily buy as much as $17,000 today. To explore purchasing power, compare the projected future amount with an inflation-adjusted estimate, using an assumption appropriate to your purpose. Do not subtract an inflation percentage from the nominal rate and call the result exact; that shortcut is only an approximation.

Compare scenarios without obscuring assumptions

A practical spreadsheet can include a small comparison table with one row per scenario. Keep the initial balance, term, contribution timing, and frequency consistent, then vary one assumption at a time, such as the annual rate or monthly deposit. This makes it easier to see what causes the result to change. Add a clearly labeled total-contributions column so readers can distinguish money contributed from projected earnings.

For a side-by-side comparison, make each row’s inputs explicit rather than hiding different assumptions inside formulas. A low, middle, and high return scenario may help illustrate uncertainty, but the labels should say they are assumptions, not outcomes or recommendations. For savings products, use the stated rate and account conditions, and update the spreadsheet if the rate changes.

Use a spreadsheet for calculations and organization, not as a substitute for checking product terms or getting financial advice suited to your circumstances. Protect the worksheet from accidental edits by clearly marking input cells, and keep a note of where each rate or fee came from and when it was recorded.

Frequently Asked Questions

How do I calculate compound interest in Excel?
For a fixed rate with no additional deposits, use the initial balance multiplied by one plus the periodic rate raised to the total number of periods. In Excel, that is =A2*(1+B2/D2)^(C2*D2) when the cells contain the starting balance, annual rate, years, and periods per year in that order.

Can Excel calculate compound interest with monthly deposits?
Yes. Use FV with a monthly rate, the total number of months, the regular deposit, and the starting balance. Make the deposit and starting-balance arguments negative when you want the future value to display as positive, and choose payment timing with the final argument.

Is monthly compounding better than annual compounding?
For the same positive nominal annual rate and no other changes, more frequent compounding produces a slightly higher balance because interest is added sooner. Actual products may quote rates differently, and fees, conditions, or an APY can make a simple frequency comparison misleading.

Why does my Excel result differ from a bank calculator?
The bank may use a different rate definition, day-count method, rounding rule, deposit date, or compounding schedule. Check whether the quoted rate is an APY or nominal rate, and whether the calculator assumes deposits at the beginning or end of each period.

Can this spreadsheet predict investment returns?
It can illustrate what happens under a chosen constant-rate assumption, but it cannot predict changing market returns. Treat the result as a scenario, and remember that real investments can fluctuate, lose value, and incur fees or taxes.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.