To calculate savings in Excel, combine a compound interest formula for the starting balance with an ordinary annuity formula for the recurring deposits, and add the two pieces together. The starting balance grows as initial × (1 + i)^N, where i is the periodic rate and N is the total number of compounding periods. The stream of deposits grows as contribution × ((1 + i)^N − 1) / i, which assumes each deposit lands at the end of its period — the standard, slightly conservative assumption. The future value is the sum of both pieces, and interest earned is that future value minus every dollar you actually put in. Once those two formulas sit in a workbook, a savings projection updates the moment you change any input — the same math a browser-based Savings Calculator runs the moment you type a value.
The Two Formulas Behind Every Savings Projection
Every savings spreadsheet — and every serious savings calculator — rests on two well-known formulas. The first, used for a starting balance or a single lump sum, is the compound interest formula. Each period, the existing balance earns a slice of the annual rate, so the balance grows multiplicatively rather than linearly. The second, used for a stream of identical deposits, is the future value of an ordinary annuity. Each deposit is treated as a small lump sum that starts earning interest from the moment it lands, and the math sums up every deposit's individual growth at the end of the horizon.
Why two formulas instead of one? A compound interest calculator grows a single lump sum and shows how one deposit snowballs over time. A savings calculator, by contrast, models a stream of contributions — every month (or quarter, or year) you add more, and each new deposit starts earning interest from the moment it lands. That recurring pattern matches how real savings accounts, emergency funds, and automatic transfers behave, so the future value reflects disciplined, ongoing saving rather than a one-off deposit. The two formulas are joined at the end: future value = (initial × (1 + i)^N) + (contribution × ((1 + i)^N − 1) / i). The periodic rate i is the annual rate divided by the number of compounding periods per year, and N is the total number of periods.
Build a Savings Workbook in Excel
You can set up the entire projection in a single sheet. The inputs go in one block, the formulas in another, and you can format the output cells as currency so the result is easy to read at a glance.
- Label your inputs. In cells B1 through B5, type: Starting Balance, Deposit Amount, Deposits Per Year, Annual Rate (%), and Years.
- Enter the values. In cells C1 through C5, type your numbers. For a monthly plan with a 5% rate, C3 = 12 and C4 = 5. C5 = 10 means a ten-year horizon.
- Calculate the total number of periods in cell C6 with the formula =C3*C5. This gives N, the total compounding periods.
- Calculate the periodic rate in cell C7 with =C4/100/C3. This gives i, the rate per period.
- Calculate the future value of the starting balance in cell C8 with =C1*(1+C7)^C6. This is the lump-sum growth piece.
- Calculate the future value of the deposits in cell C9 with =C2*((1+C7)^C6-1)/C7. This is the annuity piece. To avoid a divide-by-zero error when the rate is zero, wrap it as =IF(C7=0, C2*C6, C2*((1+C7)^C6-1)/C7).
- Add the two pieces together in cell C10 with =C8+C9. This is the projected future value.
- Calculate total contributions in cell C11 with =C1+C2*C6. This is your starting balance plus every deposit you make.
- Calculate interest earned in cell C12 with =C10-C11. This isolates the growth from your own money.
Once the formulas are wired up, every change to an input cell — a higher rate, a longer horizon, a bigger deposit — recalculates the whole projection instantly. The split between contributions and interest earned is the most useful output for planning, because it shows how much of the final balance is growth versus money you set aside.
Worked Example: $1,000 Start, $200 a Month, 5%, 10 Years
To see the formulas in action, take a starting balance of $1,000, a monthly deposit of $200, an annual rate of 5%, and a 10-year horizon. The workbook resolves the numbers as follows.
| Cell | Label | Value or Formula |
|---|---|---|
| C1 | Starting Balance | 1,000 |
| C2 | Deposit Amount | 200 |
| C3 | Deposits Per Year | 12 |
| C4 | Annual Rate (%) | 5 |
| C5 | Years | 10 |
| C6 | Total Periods (N) | C3 × C5 = 120 |
| C7 | Periodic Rate (i) | C4 / 100 / C3 ≈ 0.004167 |
| C8 | Future Value of Initial | 1,000 × (1.004167)^120 ≈ 1,647.01 |
| C9 | Future Value of Deposits | 200 × ((1.004167)^120 − 1) / 0.004167 ≈ 31,056.48 |
| C10 | Total Future Value | 1,647.01 + 31,056.48 ≈ 32,703.49 |
| C11 | Total Contributions | 1,000 + 200 × 120 = 25,000 |
| C12 | Interest Earned | 32,703.49 − 25,000 = 7,703.49 |
The arithmetic works out to a projected future value of about $32,703.49, with total contributions of $25,000 and interest earned of $7,703.49. The interest number is the one most people underestimate: roughly $7,700 of growth, layered on top of regular saving, is a meaningful boost over a decade. Change any input — a $250 monthly deposit, a 6% rate, a 20-year horizon — and the workbook shows the new split immediately. For a side-by-side comparison of two deposit levels, the guide How to Calculate Savings Between Two Deposit Scenarios walks through the matching setup.
Where Excel Savings Models Break Down
Most broken savings projections fall into one of three traps. The first is mixing up deposit timing: the formula above assumes an ordinary annuity, meaning each deposit lands at the end of its period. Depositing at the start of each period would earn a little more interest, and a simple ×(1+i) multiplier applied to the annuity piece captures that. The second trap is treating the periodic rate as the annual rate. Forgetting to divide by the compounding frequency per year inflates the result, sometimes dramatically. The third is the divide-by-zero risk when the rate is zero: the annuity formula collapses, so the workbook needs an IF guard around the deposit formula.
Other modeling decisions deserve a deliberate call. Compounding frequency does change the future value: with the same annual rate, monthly compounding and monthly deposits produce a slightly higher result than annual compounding and annual deposits, because interest is calculated and added more frequently. Real savings accounts layer in fees, taxes, rate changes, and deposit timing that no single formula captures — see Compound interest on Wikipedia for the standard treatment and Future value on Wikipedia for the formal annuity derivation.
A Faster Alternative for the Same Projection
Excel is versatile, but it carries overhead: every input is a separate cell, every formula is a maintenance liability, and a single broken reference can silently corrupt the entire projection. For a quick read on a savings goal, the Savings Calculator handles the same five inputs — starting balance, regular deposit, frequency, annual rate, and years — and recalculates the future value, total contributions, and interest earned the moment you change a value. No formulas to type, no divide-by-zero check to write, no cell to misformat.
The tool runs entirely in your browser. No numbers are uploaded, no account is required, and there is no waiting on a server, so you can use it on a shared computer without leaving a trail of personal data. Under the hood it uses the same two formulas as the Excel workbook above: initial × (1 + i)^N for the starting balance and contribution × ((1 + i)^N − 1) / i for the deposits, with the same ordinary annuity assumption. The split between contributions and interest earned is shown directly, so you can see clearly how much of the final balance is growth versus money you set aside.
Picking the Right Inputs for Your Goal
The model is only as good as its inputs. For a short-term goal like a vacation fund, a horizon of one to three years with monthly deposits and a conservative rate gives a realistic read. For a long horizon like a down payment, a fifteen- to twenty-year horizon with a higher rate and a steady monthly deposit tells a different story — doubling the time horizon without adjusting the rate more than doubles the interest earned, because compounding accelerates late in the schedule. Switching the deposit frequency between monthly, quarterly, and annually lets you see how much of the result comes from the cadence itself rather than the rate.
Estimates from any savings model are for general information only and are not financial advice. Actual returns depend on real account terms, fees, taxes, and rate changes, so verify any figure with a licensed professional before acting on it. A spreadsheet or a browser tool is a starting point for a conversation, not a substitute for one. For related Excel work, the guide How to Calculate Compound Interest in Excel (Step-by-Step) covers the lump-sum case, and the Compound Interest Calculator handles the single-deposit version of the same math.