Compound Interest for Excel Users

Compound interest grows your balance by earning interest on both the original principal and on previously earned interest. This guide shows the key formulas, Excel/Sheets functions, worked examples, and common mistakes—plus quick links to calculators.

Formula

Future Value (annual compounding)

FV = P × (1 + r)n

  • P = principal (starting amount)
  • r = annual rate (decimal)
  • n = years

Example: P=10,000, r=5%, n=10 → FV = 10000×(1+0.05)^10 = 16,288.95.

Excel/Sheets: =10000*(1+0.05)^10

Prefer a quick check? Use the Compound Interest Calculator.

Function

Periodic Contributions with FV

=FV(rate, nper, pmt, [pv], [type])

  • rate — periodic rate (e.g., annual/12 for monthly)
  • nper — total number of periods
  • pmt — contribution per period (negative = outflow)
  • pv — present value (starting balance; usually negative)
  • type — 0 = end of period; 1 = beginning

Example (monthly deposits): 5% annual, 10 years, $200/month, starting $5,000, paid at end of month:

=FV(0.05/12, 10*12, -200, -5000, 0)

Tip: Use type=1 if you deposit at the beginning of each period—this increases the ending balance.

Planning a goal? Try the Savings Goal Calculator as well.

Functions

Present Value and Payment Size (PV, PMT)

Find today’s value of a future amount

Excel: =PV(rate, nper, pmt, fv, [type])

Example: What lump sum today equals $20,000 in 8 years at 4%? =PV(0.04, 8, 0, 20000, 0) → -14,691.47 (negative = cash outlay).

Find the periodic payment required

Excel: =PMT(rate, nper, pv, [fv], [type])

Example: Save $50,000 in 15 years at 5% with $3,000 starting: =PMT(0.05/12, 15*12, -3000, 50000, 0) → monthly deposit (result negative = outflow).

Rates

Rates & Compounding Frequency

Match the period of your rate to the period of your contributions/compounding:

  • Monthly compounding: use rate = annual_rate/12, nper = years*12.
  • Quarterly compounding: rate = annual_rate/4, nper = years*4.
  • Daily (approx.): rate = annual_rate/365, nper = years*365.

Continuous compounding: FV = P × e^(r×t) (Excel: =P*EXP(r*t)).

Conversion

Effective Annual Rate (EAR)

When compounding occurs more than once per year, the effective annual return exceeds the nominal rate.

EAR: (1 + r/m)^(m) − 1 where m is compounding periods per year.

Example: 12% nominal, monthly compounding → (1 + 0.12/12)^12 − 1 = 12.68%.

Pitfalls

Common Gotchas

  • Mismatching compounding frequency and rate (annual vs monthly).
  • Forgetting that Excel financial functions use negatives for cash outflows.
  • Treating nominal and effective rates as identical.
  • Using text values (e.g., “5%” as text) rather than numeric percentages.

Verify results quickly with our Compound Interest Calculator.

FAQ

Frequently Asked Questions

What’s the difference between nominal APR and effective rate?

Nominal is the quoted annual rate; effective includes the impact of compounding within the year. Use EAR for apples-to-apples comparisons.

Why are FV and PMT returning negative values?

Excel follows a cash-flow sign convention: outflows are negative, inflows are positive. Wrap with - if you prefer a positive display.

Should I contribute at the beginning or end of the period?

Beginning-of-period contributions (type=1) compound for longer and produce a higher ending balance than end-of-period (type=0).

For spreadsheet fundamentals, see Percentage Formula in Excel/Sheets. Planning goals? Try the Savings Goal Calculator.