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.

Download Excel template (.xlsx) 34 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 27 Oct 2026

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.

Preview: the Example calculator tab. Fictional case: $10,000 to start, $200 added at the end of every month, 6% a year compounded monthly, 30 years.
$261,128.76Balance after 30 years
$82,000Total you put in
$179,128.76Interest earned (68.6% of balance)
$124,491.23In today's money at 2.5% inflation
Example year by year, fictional data: after 30 years, $82,000 contributed and $179,129 interest, a balance of $261,129.$0$75k$150k$225k$300k151015202530YearTotal contributedTotal interest

Chart: the Example year by year tab. Purple = money put in (including the start balance); green = interest earned so far. Interest passes total contributions in year 18.

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, colour key, method, limits, disclaimer
DashboardPick Example or My numbersKey figures and a native stacked chart: contributions vs interest by year
Calculator8 inputs: starting balance, contribution, contributions per year, start or end of period, annual rate, compounding per year, years, inflationEffective 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 yearNothing (formulas)Up to 50 rows: start balance, contributions, interest, end balance, running totals, end balance in today's money
Example calculator / Example year by yearAlready filledThe fictional case shown on this page

How to use it

  1. Open Calculator and fill the 8 yellow cells. "Inputs complete?" turns to Yes.
  2. Use the quoted annual rate and how often interest is added (12 = monthly, 365 = daily). For an investment, the rate is only an assumption.
  3. 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.
  4. Check that "Difference (should be 0.00)" shows $0.00. That confirms the year-by-year table and Excel's FV() agree.
  5. Read Year by year for each year's interest, and the last column for buying power after inflation.
  6. 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.

FieldFormula
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

  1. Rate per month: (1 + 6%/12)^(12/12) − 1 = 0.5%. Effective annual rate: (1.005)^12 − 1 = 6.168%.
  2. The $10,000 start balance grows to $10,616.78 over 12 months.
  3. Twelve $200 deposits at month end grow to $2,467.11 by year end.
  4. 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)

YearStartContributionsInterestEnd balanceTotal inTotal interestToday'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:

CompoundingEffective annual rateBalance after 30 yearsInterest
Annually (1)6.000%$252,337.51$170,337.51
Quarterly (4)6.136%$259,456.72$177,456.72
Monthly (12) — the example6.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 rateRule of 72 (years)Exact (years)Difference
1%72.0069.66+2.34
2%36.0035.00+1.00
3%24.0023.45+0.55
4%18.0017.67+0.33
5%14.4014.21+0.19
6%12.0011.90+0.10
8%9.009.01-0.01
10%7.207.27-0.07
12%6.006.12-0.12
15%4.804.96-0.16
20%3.603.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

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

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.