=FV(rate/n, nper*n, -pmt, -pv, type) To calculate future investment value with monthly contributions, use =FV(annual_rate/12, years*12, -monthly_deposit, -initial_deposit). Omit monthly_deposit if making a one-time lump sum investment.
How to Calculate Compound Interest in Excel & Google Sheets
Compound interest is the engine of wealth building—earning interest on your initial principal and on previously accumulated interest. In spreadsheet modeling, the most powerful and flexible tool for compounding calculations is the FV (Future Value) function.
Whether you’re modeling a 401(k) retirement plan, high-yield savings account (HYSA), or corporate treasury reserves, this guide covers both lump-sum and recurring monthly contribution models.
The Core Future Value Formula
The FV function handles interest rate conversion, compounding frequency, and recurring deposits automatically:
=FV(rate, nper, [pmt], [pv], [type])Argument Reference:
rate: Interest rate per compounding period (e.g.,8% / 12for monthly compounding).nper: Total number of compounding periods (e.g.,30 years * 12 months = 360).pmt: Recurring deposit amount per period (e.g.,-$500).pv: Starting lump-sum investment (e.g.,-$10,000).type(Optional):0= payments at end of period (default);1= payments at start of period.
Example 1: 401(k) Growth with Monthly Contributions
Suppose you start with $10,000 in a retirement account, contribute $500 at the end of each month, and earn an average annual return of 8.0% over 25 years.
| Cell | Parameter | Value | Formula / Notes |
|---|---|---|---|
| B1 | Initial Principal (PV) | $10,000.00 | Lump sum starting balance |
| B2 | Monthly Contribution (PMT) | $500.00 | Added each month |
| B3 | Annual Expected Return | 8.00% | Annualized rate (S&P 500 average) |
| B4 | Investment Horizon | 25 years | Years to retirement |
| B5 | Compounding Frequency | 12 | Monthly |
| B6 | Future Portfolio Value | $545,862.08 | =FV(B3/B5, B4*B5, -B2, -B1, 0) |
Breakdown of the Result:
- Total Out-of-Pocket Cash Invested:
$10,000 + ($500 * 300) = $160,000.00 - Total Compound Interest Earned:
$545,862.08 - $160,000.00 = $385,862.08 - More than 70% of the ending balance came purely from compounded returns!
Example 2: Lump-Sum Growth (No Monthly Deposits)
If you make a one-time $25,000 deposit into a Certificate of Deposit (CD) paying 5.25% compounded quarterly for 5 years:
=FV(5.25%/4, 5*4, 0, -25000)- Returns:
$32,442.27
Alternatively, you can use the direct mathematical formula in any cell:
=25000 * (1 + 0.0525 / 4)^(4 * 5)Year-by-Year Growth Schedule Table
To display an annual breakdown in your spreadsheet:
| Year | Starting Balance | Annual Deposits | Interest Earned | Ending Balance |
|---|---|---|---|---|
| 1 | $10,000.00 | $6,000.00 | $1,051.81 | $17,051.81 |
| 5 | $45,869.64 | $6,000.00 | $4,008.20 | $55,877.84 |
| 10 | $112,058.46 | $6,000.00 | $9,473.49 | $127,531.95 |
| 20 | $331,438.41 | $6,000.00 | $27,576.45 | $365,014.86 |
| 25 | $501,489.28 | $6,000.00 | $41,607.72 | $545,862.08 |
Compounding Frequency Comparison
See how compounding frequency impacts a $50,000 investment at 7.0% over 10 years:
| Frequency | Periods/Yr ($n$) | Formula | Ending Value | Total Gain |
|---|---|---|---|---|
| Annually | 1 | =FV(7%, 10, 0, -50000) | $98,357.57 | +$48,357.57 |
| Quarterly | 4 | =FV(7%/4, 40, 0, -50000) | $100,079.95 | +$50,079.95 |
| Monthly | 12 | =FV(7%/12, 120, 0, -50000) | $100,482.97 | +$50,482.97 |
| Daily (365) | 365 | =FV(7%/365, 3650, 0, -50000) | $100,687.58 | +$50,687.58 |
Pro Tips for Modeling Investments
- Adjust for Inflation (Real vs. Nominal Returns): To calculate purchasing power in today’s dollars, subtract expected inflation from your return rate (e.g., $8% \text{ nominal} - 2.5% \text{ inflation} = 5.5% \text{ real rate}$).
- Combine with Goal Seek: Need to find how much you must save monthly to reach
$1,000,000? Use Excel’s Goal Seek (Data > What-If Analysis > Goal Seek) or thePMTfunction:
Result:=PMT(8%/12, 30*12, 0, -1000000)$670.98/monthrequired to reach $1M in 30 years at 8%.
Frequently Asked Questions
Why does the FV function output a negative value?
In financial modeling, investments into a portfolio are treated as outgoing cash flows. Prefix both the monthly deposit (pmt) and initial principal (pv) with minus signs (e.g. -pmt, -pv) to output a positive future balance.
When should I set the 'type' argument to 1 vs 0 in the FV formula?
Set type to 0 (or omit it) if deposits occur at the end of each month (ordinary annuity). Set type to 1 if deposits occur at the beginning of each month (annuity due), earning an extra month of interest.
How can I calculate compound interest without the FV function?
For a lump-sum investment without recurring deposits, the standard mathematical formula is =Principal * (1 + Rate / n)^(n * Years).