Quick Summary
Step-by-step compound interest calculator in Google Sheets. The math, monthly contributions, inflation adjustment, and projection at 10, 20, 30 years.
Quick answer. A compound interest calculator in Google Sheets compresses three inputs (starting amount, monthly contribution, annual rate) into a single
FVcall. For lump-sum growth:=P*(1+r/n)^(n*t). For lump sum plus monthly contributions:=FV(rate/12, years*12, -contribution, -principal). The harder question is what rate to feed it. Nominal returns inflate the future number; real (inflation-adjusted) returns show actual purchasing power. The walkthrough below builds both, then runs the projection at 10, 20, and 30 years so the gap is impossible to miss.
Compound interest is the rare piece of math that gets dramatic with patience. Five percent of $10,000 is $500, which is uninteresting. Five percent compounded for 30 years turns $10,000 into about $43,200, which is. The effect lives in the exponent, and Google Sheets does the exponent for you.
The tricky part is not the formula. It is choosing the inputs honestly. A calculator that uses 10 percent nominal and ignores inflation produces a comforting number that has nothing to do with what the money will buy in 30 years. The version below separates the math from the assumption.
What compounding actually does
Interest earns more interest. That is the entire idea, and the consequence is that growth accelerates as the balance grows.
A rough mental check is the rule of 72. Divide 72 by the annual return and you get the years to double. At 6 percent real, money doubles roughly every 12 years. At 4 percent, every 18. At 8 percent, every 9. The rule is an approximation, but it builds the intuition: doublings stack, and the last doubling does most of the work.
Over a 36-year horizon at 6 percent, a dollar doubles three times - so it ends up worth around 8 dollars. Not because of luck, but because each doubling is bigger than the one before in absolute terms.
The basic formula
The textbook form:
A = P * (1 + r/n)^(n*t)Where:
Ais the future valuePis the principal (starting amount)ris the annual interest rate as a decimal (0.07 for 7 percent)nis the number of compounding periods per year (12 for monthly, 365 for daily)tis the number of years
In a single Google Sheets cell:
=P*(1+r/n)^(n*t)If the principal is in cell B1, the rate in B2, the compounding periods in B3, and the years in B4:
=B1*(1+B2/B3)^(B3*B4)That is the calculator. Everything beyond this is variations on it.
Building it in Google Sheets
Five labeled cells and one formula are enough for a lump-sum calculator.
| Cell | Label | Example |
|---|---|---|
| A1 | Principal | 10,000 |
| A2 | Annual rate (decimal) | 0.06 |
| A3 | Compounding periods/year | 12 |
| A4 | Years | 30 |
| A5 | Future value | =A1*(1+A2/A3)^(A3*A4) |
Plug in $10,000 at 6 percent compounded monthly for 30 years and A5 returns about $60,226. The same inputs compounded annually (n=1) return $57,435 - a $2,800 gap purely from how often the interest is credited.
For most public market scenarios, the compounding-frequency question is a rounding error. The difference between monthly and daily is under 1 percent over 30 years. Where it matters is high-yield savings and credit card balances, where daily compounding meaningfully changes the number.
Adding monthly contributions with FV
A one-time deposit is not how most people save. They put money in every month. That changes the math from a single exponent to an annuity, and Google Sheets has a function for it: FV (future value).
=FV(rate/12, years*12, -contribution, -principal)Where:
rate/12is the monthly interest rateyears*12is the total number of monthly periods-contributionis the monthly addition (negative because money is flowing out of your pocket into the account)-principalis the starting balance (also negative for the same reason)
At $10,000 starting balance, $500 monthly contributions, 6 percent annual rate, 30 years:
=FV(0.06/12, 30*12, -500, -10000)The function returns about $562,500. Of that, $190,000 is contributed cash ($10,000 starting plus $500 times 360 months), and the remaining $372,500 is the compounded growth. The contributions are roughly a third of the final balance. The growth is roughly two thirds.
The sign convention trips people up. FV returns a positive number when contributions are entered as negative, because positive output represents money owed to you at the end. Flip the signs and FV returns a negative number that means the same thing. Either works as long as it stays consistent across the workbook.
Real vs nominal returns - the part everyone skips
This is the section where most compound interest articles go quietly wrong. They show a calculator with a 10 percent return assumption, multiply for 30 years, and produce a future balance in 2056 dollars that gets compared to spending power in 2026 dollars. The numbers do not mean what the reader thinks they mean.
There are two ways to handle it. Both work; neither can be ignored.
Nominal returns are the headline numbers. The S&P 500 has averaged around 10 percent nominal over the past century, including dividends. That number assumes no adjustment for inflation. The future balance produced by a nominal calculator is in future dollars, which buy less than today’s dollars.
Real returns subtract inflation. With nominal returns averaging 10 percent and US inflation averaging roughly 3 percent long-term, the real return has been around 6 to 7 percent. Long-term US equity real returns have averaged in that 6 to 7 percent range since 1928, per Damodaran’s historical dataset. This is not a forecast for the next 30 years - it is what has happened in the past.
One approach for long-horizon math: use real returns and keep contributions and spending in today’s dollars. The future balance is then directly comparable to today’s expenses. A real return of 6 percent on $500/month plus a $10,000 starting balance over 30 years projects to about $562,500 in today’s purchasing power. The same scenario at 10 percent nominal produces $1,328,600 in future dollars, which translates to roughly $547,000 in today’s dollars after 3 percent annual inflation. Close, but not the same.
Mix the conventions - nominal returns with today’s-dollars spending - and the projection overstates retirement readiness by a wide margin. The math is forgiving of which convention you pick, as long as it stays consistent.
Side-by-side projection at 10, 20, 30 years
To make the real-vs-nominal gap visible, here is the same scenario at three horizons. Starting balance $10,000, monthly contribution $500, monthly compounding. Numbers rounded.
| Years | Nominal at 10% | Real at 7% | Real at 5% | Gap (nominal vs 7% real) |
|---|---|---|---|---|
| 10 | $129,500 | $106,600 | $94,100 | 18% |
| 20 | $453,000 | $300,900 | $232,600 | 34% |
| 30 | $1,328,600 | $691,200 | $460,800 | 48% |
Three takeaways from this table.
First, over 30 years the nominal-vs-real gap is enormous. A retirement projected at “$1.3 million” in nominal terms is closer to $690,000 in real purchasing power - still a lot, but a different number. Articles that quote the nominal figure without flagging it are setting up readers for a surprise in 2056.
Second, the 2 percentage point gap between 5 percent real and 7 percent real produces a 50 percent difference in 30-year outcome. The return assumption is the single most leveraged input in any long-horizon projection.
Third, the early years look modest. Most of the dramatic spread appears in years 20 to 30. Compounding rewards patience, which is the boring half of the answer that the math never quite manages to soften.
The role of starting age
Time matters more than rate. A 25-year-old contributing $300/month at 6 percent real until age 65 ends up with about $597,000 in today’s dollars. A 35-year-old contributing $600/month - twice the monthly amount, but starting a decade later - ends up with about $603,000. Doubling contributions barely catches up to ten extra years of compounding.
This is the most underrated input in any compound interest calculation. The math is not subtle: the back end of a 40-year horizon is where the doublings happen. A decade lost at the start is a doubling lost at the end.
Tracking this kind of question is the entire purpose of building a 40-year retirement plan in Google Sheets - it lets you see the sensitivity to retirement age, savings rate, and return assumption side by side.
Common mistakes
Mixing annual and monthly rates. If the formula expects a per-period rate and you feed it an annual rate, the projection inflates by a factor of 12 or more. Worth confirming for every formula: is the rate in this cell annual, or already divided by the compounding frequency?
Forgetting compounding frequency. A 6 percent annual rate compounded once a year is not the same as 6 percent compounded monthly. The effective annual rate is slightly higher with more frequent compounding. Small effect at low rates, larger at high rates.
Using nominal returns with today’s-dollars assumptions. Covered above, but worth restating: a $1 million target in 30 years means something different depending on which dollar you mean.
Conflating contributions with returns. A projection that ends at $562,500 with $190,000 contributed cash and $372,500 of growth is not “a 562 percent return”. It is a 6 percent annual return that compounded into roughly 196 percent total growth on contributions over 30 years. Beginners often quote the wrong number; the way to check is to compute the implied annual rate from FV and see if it matches the assumption.
Treating the projection as a forecast. It is a calculation given inputs, not a prediction. The market does not deliver smooth 6 percent annual returns. It delivers chaotic returns that average to something. Tracking realized returns over time is its own problem - the TWR and XIRR walkthrough covers how to measure what happened versus what was projected.
Sensitivity analysis - what one percentage point changes
A useful pattern for any projection is to ask what one input does when others stay fixed. For a 30-year horizon with $500/month contributions and a $10,000 start:
| Real return | 30-year balance | Change vs. 6% baseline |
|---|---|---|
| 4% | $380,000 | -32% |
| 5% | $461,000 | -18% |
| 6% | $562,500 | baseline |
| 7% | $691,000 | +23% |
| 8% | $854,500 | +52% |
A 1 percentage point change in return assumption shifts the 30-year balance by 20 to 25 percent. That is comparable to changing the monthly contribution by 30 to 50 percent. Return assumptions are not interchangeable details - they drive the answer.
One way to handle uncertainty is to run three scenarios side by side: conservative (4 percent real), baseline (6 percent), optimistic (7 percent). The spread, not the midpoint, is the actual answer.
When the spreadsheet runs out
A single-cell compound interest calculator handles a lump sum or a steady monthly contribution. It starts to creak when:
- Contributions change over time (raises, sabbaticals, mortgage payoff freeing up cash flow)
- Multiple accounts compound at different rates (taxable, 401(k), Roth, HSA)
- Withdrawals enter the picture (early retirement, college, home purchase)
- Tax treatment differs across accounts at withdrawal time
At that point, what you need is a multi-year projection grid, not a single cell. The same compound growth formula gets applied year by year, with contributions and withdrawals layered in.
Templates that fit
- Compound Interest Calculator - For the “what does this lump sum or steady contribution grow to” question. Dashboard, year-by-year projection table, principal-versus-interest breakdown, and multiple compounding frequencies.
- Retirement Financial Planning Projections - For the harder case where contributions change, multiple accounts compound at different rates, and a withdrawal phase needs modeling. 40-year accumulation plus withdrawal with configurable real-return assumptions.