Home › Templates › Compound interest spreadsheet
Compound Interest Spreadsheet
Compound interest means you earn interest on your deposits and on the interest they have already earned. This free spreadsheet shows it year by year: start balance, contributions, interest and end balance, with any compounding frequency, deposits at the start or end of each period, an FV() cross-check, the value after inflation and the Rule of 72.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no email, no add-on. The formulas are plain maths plus FV, EFFECT, INDEX and LN.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, colour key, method, limits, disclaimer |
| Dashboard | Pick Example or My numbers | Key figures and a native stacked chart: contributions vs interest by year |
| Calculator | 8 inputs: starting balance, contribution, contributions per year, start or end of period, annual rate, compounding per year, years, inflation | Effective annual rate (and EFFECT() check), rate per period, final balance, FV() cross-check and difference, total contributed, total interest, interest share, value in today's money, Rule of 72 vs exact doubling time, plus a Rule of 72 table for 1%–20% |
| Year by year | Nothing (formulas) | Up to 50 rows: start balance, contributions, interest, end balance, running totals, end balance in today's money |
| Example calculator / Example year by year | Already filled | The fictional case shown on this page |
How to use it
- Open Calculator and fill the 8 yellow cells. "Inputs complete?" turns to Yes.
- Use the quoted annual rate and how often interest is added (12 = monthly, 365 = daily). For an investment, the rate is only an assumption.
- Set contributions per year (12 for monthly) and pick Start of period or End of period. Use 0 as the contribution for a one-off deposit.
- Check that "Difference (should be 0.00)" shows $0.00. That confirms the year-by-year table and Excel's FV() agree.
- Read Year by year for each year's interest, and the last column for buying power after inflation.
- On Dashboard, switch B3 to My numbers to chart your own figures.
The definitions behind the sheet
The SEC's Saving and Investing for Students guide defines compound interest as "Interest paid on principal and on accumulated interest." In plain words: "With compound interest, you earn interest on the money you save and on the interest that money earns."
The Investor.gov compound interest calculator (SEC) uses the same building blocks as this sheet: an initial investment, a regular contribution ("Amount that you plan to add to the principal every month"), a length of time in years, an estimated interest rate and a compound frequency ("Times per year that interest will be compounded"). The spreadsheet adds a row for every year, start-of-period deposits, other deposit frequencies and an inflation column.
For the FV() check, Microsoft's FV documentation sets the rules: type 0 means payments at "the end of the period" and 1 at "the beginning of the period"; "Make sure that you are consistent about the units you use for specifying rate and nper"; and "cash you pay out, such as deposits to savings, is represented by negative numbers". Google Sheets' FV uses the same arguments.
The formulas, explained
Exact formulas from the file. Calculator inputs: B5 = starting balance, B6 = contribution, B7 = contributions per year (p), B8 = timing, B9 = annual rate (r), B10 = compounding per year (m), B11 = years, B12 = inflation.
| Field | Formula |
|---|---|
| Effective annual rate (B16) | =(1+B9/B10)^B10-1, checked by =EFFECT(B9,B10) |
| Rate per contribution period (B18) | =(1+B9/B10)^(B10/B7)-1 |
| Payment type (B19) | =IF(B8="Start of period",1,0) |
| End balance each year (Year by year E) | =B6*(1+i)^p+C*IF(i=0,p,((1+i)^p-1)/i)*(1+i*type) with i = B18, p = B7, C = B6 on Calculator |
| Interest that year (D) | =E6-B6-C6 (end − start − contributions) |
| FV() cross-check (B21) | =FV(B18,B7*B11,-B6,-B5,B19) |
| Total interest (B24) | =B20-B23 where B23 = B5+B6*B7*B11 |
| Today's money (H, B26) | =E6/(1+Calculator!$B$12)^A6 |
| Rule of 72 vs exact (B27, B28) | =72/(B16*100) and =LN(2)/LN(1+B16) |
When you deposit more often than interest compounds (say monthly deposits, annual compounding), the sheet uses the equivalent rate per deposit period, (1 + r/m)^(m/p) − 1. Some banks pay nothing on a deposit until the next compounding date, so a bank statement can be slightly lower than the sheet.
Worked example from the sample data
Year 1, step by step
- Rate per month: (1 + 6%/12)^(12/12) − 1 = 0.5%. Effective annual rate: (1.005)^12 − 1 = 6.168%.
- The $10,000 start balance grows to $10,616.78 over 12 months.
- Twelve $200 deposits at month end grow to $2,467.11 by year end.
- End of year 1: $13,083.89. Interest = $13,083.89 − $10,000 − $2,400 = $683.89.
Year-by-year excerpt (Example year by year tab)
| Year | Start | Contributions | Interest | End balance | Total in | Total interest | Today's money |
|---|---|---|---|---|---|---|---|
| 1 | $10,000.00 | $2,400.00 | $683.89 | $13,083.89 | $12,400 | $683.89 | $12,764.77 |
| 2 | $13,083.89 | $2,400.00 | $874.10 | $16,357.99 | $14,800 | $1,557.99 | $15,569.77 |
| 3 | $16,357.99 | $2,400.00 | $1,076.04 | $19,834.03 | $17,200 | $2,634.03 | $18,417.87 |
| 5 | $23,524.46 | $2,400.00 | $1,518.05 | $27,442.51 | $22,000 | $5,442.51 | $24,255.18 |
| 10 | $45,684.97 | $2,400.00 | $2,884.86 | $50,969.84 | $34,000 | $16,969.84 | $39,817.55 |
| 15 | $75,576.19 | $2,400.00 | $4,728.49 | $82,704.68 | $46,000 | $36,704.68 | $57,104.73 |
| 20 | $115,894.96 | $2,400.00 | $7,215.26 | $125,510.22 | $58,000 | $67,510.22 | $76,595.24 |
| 25 | $170,278.95 | $2,400.00 | $10,569.55 | $183,248.49 | $70,000 | $113,248.49 | $98,842.51 |
| 30 | $243,634.79 | $2,400.00 | $15,093.97 | $261,128.76 | $82,000 | $179,128.76 | $124,491.23 |
After 30 years the balance is $261,128.76. You put in $82,000; the other $179,128.76 (68.6%) is interest. The FV() cross-check returns $261,128.76, a difference of $0.00. At 2.5% inflation the balance buys what $124,491.23 buys today.
Deposit at the start instead of the end
Switching the same example to Start of period gives $262,133.28, $1,004.52 more, because each deposit earns one extra month of interest.
How much does the compounding frequency matter?
Same deposits, same 6% quoted rate, only the compounding changes:
| Compounding | Effective annual rate | Balance after 30 years | Interest |
|---|---|---|---|
| Annually (1) | 6.000% | $252,337.51 | $170,337.51 |
| Quarterly (4) | 6.136% | $259,456.72 | $177,456.72 |
| Monthly (12) — the example | 6.168% | $261,128.76 | $179,128.76 |
| Daily (365) | 6.183% | $261,949.77 | $179,949.77 |
Check against the SEC example
The SEC student guide says $730 saved for one year at 5% "would grow to $931.69 after 5 years, and grow to $3,155.02 after 30 years." Entering $730, no contributions, 5% compounded once a year for 30 years gives $931.69 in year 5 and $3,155.02 in year 30.
Rule of 72 vs exact doubling time
The Rule of 72 estimates how long money takes to double: 72 ÷ the annual rate in percent. The exact answer is LN(2) ÷ LN(1 + rate). The Calculator tab shows this table and the same check for your own rate (for the example's 6.17% effective rate: 11.67 vs 11.58 years).
| Annual rate | Rule of 72 (years) | Exact (years) | Difference |
|---|---|---|---|
| 1% | 72.00 | 69.66 | +2.34 |
| 2% | 36.00 | 35.00 | +1.00 |
| 3% | 24.00 | 23.45 | +0.55 |
| 4% | 18.00 | 17.67 | +0.33 |
| 5% | 14.40 | 14.21 | +0.19 |
| 6% | 12.00 | 11.90 | +0.10 |
| 8% | 9.00 | 9.01 | -0.01 |
| 10% | 7.20 | 7.27 | -0.07 |
| 12% | 6.00 | 6.12 | -0.12 |
| 15% | 4.80 | 4.96 | -0.16 |
| 20% | 3.60 | 3.80 | -0.20 |
The rule works best between about 6% and 10% and is a rough guide outside that range. It ignores new deposits.
Common mistakes
- Mixing periods in FV(). A 6% annual rate with monthly deposits needs the monthly rate and the number of months: FV(0.5%, 360, …), not FV(6%, 30, …).
- Forgetting the minus signs. FV treats deposits as money paid out, so they go in as negative numbers. Otherwise the answer comes out negative.
- Ignoring timing. Start-of-period deposits earn one more period of interest. Pick the option that matches when money actually arrives.
- Confusing APR with the effective rate. 6% compounded monthly is an effective 6.17% a year. Compare products on the effective rate.
- Reading big future numbers as today's money. Check the inflation-adjusted column before comparing with today's prices.
- Treating the rate as certain. A savings rate can change and investments can lose value. The sheet uses one fixed rate for the whole period.
FAQ
How do I make a compound interest spreadsheet in Excel?
Put the starting balance, rate, compounding periods per year and years in input cells. Each year's end balance is the start balance × (1 + rate per period)^periods plus that year's contributions with their interest. Copy the row down one line per year, then check the last row against =FV(rate per period, number of periods, -contribution, -start, type). The free template on this page does this for up to 50 years.
What is the formula for compound interest?
With no deposits: A = P × (1 + r/n)^(n×t), where P is the starting amount, r the annual rate, n the compounding periods per year and t the years. With regular deposits, add C × ((1 + i)^N − 1) / i, where i is the rate per deposit period and N the number of deposits; multiply that part by (1 + i) if you deposit at the start of each period.
How does the FV function work for compound interest?
FV(rate, nper, pmt, [pv], [type]) returns the future value with a constant rate and constant payments. Use the rate per period and the number of periods, enter deposits as negative numbers, and set type to 1 for deposits at the start of each period or 0 (the default) for the end. In the example, =FV(0.5%, 360, -200, -10000, 0) returns $261,128.76.
Does compounding monthly vs daily make a big difference?
Less than most people expect. In the example (6% for 30 years), monthly compounding gives $261,128.76 and daily gives $261,949.77, a difference of $821.01. Going from annual ($252,337.51) to monthly matters more. The rate and the number of years matter far more than the frequency.
How accurate is the Rule of 72?
It is a quick estimate of how many years money takes to double: 72 ÷ the annual rate in percent. At 8% it gives 9.00 years against an exact 9.01. At 2% it says 36 years against an exact 35.00, and at 20% 3.60 against 3.80. The spreadsheet shows both side by side.
How do I adjust compound interest for inflation?
Divide the future balance by (1 + inflation)^years. In the example, $261,128.76 after 30 years at 2.5% inflation is worth $124,491.23 in today's money. This keeps the result in the same buying power as the money you put in.
Related tools and templates
- FIRE calculator spreadsheetFIRE number and years to get there
- Compound interest calculatorQuick online answer
- CAGR calculatorAnnual growth rate between two values
- Future value calculatorFV of a sum or a series
- Inflation calculatorBuying power over time
- Investment fee calculatorWhat fees cost over time
- DCA calculatorProject regular investing
- DCA trackerLog recurring buys
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Example data is fictional: the balances, deposits and the 6% rate are made up and are not a forecast. Investment returns are not fixed and can be negative. Quotes from the SEC, Investor.gov, Microsoft Support and Google Docs Editors Help were retrieved on 27 October 2026.