The compound interest formula
With compound interest, the interest of each period is added to the balance and earns interest itself in the following periods. With simple interest only the original amount earns interest: $10,000 at 4% for 10 years earns 10 × $400 = $4,000, a final balance of $14,000. Compounded once a year, the same money ends at $14,802.44. The extra $802.44 is interest on interest, and the gap grows with the rate and the time.
The formula has four parts. r ÷ n is the rate per period (4% ÷ 12 = 0.333% per month), n × t is the total number of periods (12 × 10 = 120 months), and (1 + r ÷ n)^(n × t) is the growth factor, here 1.4908. Multiply it by the starting balance and you have the future value.
In Excel, keep each input in its own cell and refer to it. With the balance in B2, the rate in B3, the years in B4 and the periods per year in B8, the formula is =$B$2*(1+$B$3/B8)^(B8*$B$4). The dollar signs keep the inputs fixed when you fill the formula down for different compounding frequencies.
The FV function: syntax and sign convention
The syntax is =FV(rate, nper, pmt, [pv], [type]):
- rate: the interest rate per period, not per year. Monthly: annual rate ÷ 12.
- nper: the total number of periods. Monthly for 10 years: 120.
- pmt: a regular deposit each period (0 if there is none).
- pv: the starting balance.
- type: 0 or omitted if deposits come at the end of each period, 1 if at the start.
FV follows a cash-flow sign convention: money you pay in is negative, money you get back is positive. That is why the example uses -B2 and -B5. If you enter them as positive numbers, FV returns the same amount with a minus sign, which is not a calculation error, only the other point of view. The FV function gives exactly the same results as the written-out formula (columns C and D of the table).
Compounding frequency and the effective annual rate
The more often interest is compounded, the higher the final balance, but the effect flattens quickly. At 4% over 10 years, moving from annual to semi-annual compounding adds $57.03, from monthly to daily only $9.59, and from daily to continuous compounding, calculated with =$B$2*EXP($B$3*$B$4), just $0.33.
The effective annual rate (often shown as APY on savings accounts) expresses each frequency as one comparable yearly rate: =EFFECT(nominal_rate, periods_per_year), or by hand =(1+r/n)^n-1. A nominal 4% compounded monthly is an effective 4.074%; compounded daily it is 4.081%. Compare offers on the effective rate, not on the nominal rate, and if the rates already are effective rates, use them with annual compounding.
Worked example: $10,000 at 4% for 10 years
The four inputs are yellow. The frequency table compares the written-out formula with FV and shows the effective rate; the block below adds monthly deposits.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Input | Value | |||
| 2 | Starting balance ($) | 10,000 | |||
| 3 | Nominal annual rate | 4.00% | |||
| 4 | Years | 10 | |||
| 5 | Monthly deposit ($) | 200 | |||
| 6 | |||||
| 7 | Compounding | Periods per year | FV with the formula ($) | FV function ($) | Effective annual rate |
| 8 | Annually | 1 | 14,802.44 | 14,802.44 | 4.000% |
| 9 | Semi-annually | 2 | 14,859.47 | 14,859.47 | 4.040% |
| 10 | Quarterly | 4 | 14,888.64 | 14,888.64 | 4.060% |
| 11 | Monthly | 12 | 14,908.33 | 14,908.33 | 4.074% |
| 12 | Daily | 365 | 14,917.92 | 14,917.92 | 4.081% |
| 13 | Continuous | – | 14,918.25 | – | 4.081% |
| 14 | |||||
| 15 | Monthly compounding with deposits | Value | |||
| 16 | Future value, deposits at month end | 44,358.29 | |||
| 17 | Future value, deposits at month start (type 1) | 44,456.45 | |||
| 18 | Total paid in | 34,000 | |||
| 19 | Interest earned (month-end deposits) | 10,358.29 | |||
| 20 | Starting balance with simple interest only | 14,000.00 | |||
| 21 | Years to double, exact | 17.67 | |||
| 22 | Years to double, rule of 72 | 18.00 | |||
- C11
=$B$2*(1+$B$3/B11)^(B11*$B$4)Compound interest formula, monthly - D11
=FV($B$3/B11,B11*$B$4,0,-$B$2)Same result with FV - E11
=EFFECT($B$3,B11)Effective annual rate - C13
=$B$2*EXP($B$3*$B$4)Continuous compounding - E16
=FV(B3/12,B4*12,-B5,-B2)Balance plus monthly deposits - E21
=NPER(B3,0,-1,2)Years to double at 4% a year
Fictional balance and a fixed illustrative rate. Taxes, fees, inflation and rate changes are ignored, and nothing here is investment advice.
Compound interest with regular deposits
Regular deposits go into the pmt argument. With $200 a month: =FV(B3/12,B4*12,-B5,-B2) = $44,358.29. That is the starting balance grown to $14,908.33 plus the deposits, which grow to $29,449.96 by the annuity formula pmt × ((1 + i)^n − 1) ÷ i with i = 0.333% and n = 120.
The total paid in is $10,000 + 120 × $200 = $34,000, so the interest earned is $10,358.29. If each deposit is made at the start of the month (type = 1), every deposit earns one more month of interest and the result rises to $44,456.45.
If you want to see the balance year by year, build a schedule: each row takes the previous balance, adds a period's interest and the deposit, for example =previous*(1+rate/12)+deposit. The last row must equal the FV result, which is a good check of the table. The same structure, run in reverse with a payment taken out, is a loan amortization schedule.
Doubling time and solving for the other inputs
How long does it take to double money at 4% compounded yearly? Solve the formula for the number of periods: =NPER(B3,0,-1,2) returns 17.67 years, the same as =LN(2)/LN(1+B3). The rule of 72 gives a quick mental estimate, 72 ÷ 4 = 18 years, and is most accurate for rates around 6 to 10%.
Excel has a function for each unknown of the compound interest equation: PV for the starting amount needed to reach a target, NPER for the time, PMT for the deposit, RATE for the rate per period, and RRI for the annual growth rate between two values, which is the CAGR described in the CAGR formula guide.
Common mistakes
- Using the annual rate with monthly periods.
=FV(4%,120,…)compounds 4% a month for 120 months. Divide the rate by 12 when nper is in months. - Typing 4 instead of 4%. A rate of 4 means 400% per period. Enter 4% or 0.04.
- Being surprised by a negative FV. FV uses cash-flow signs. Enter what you pay in as negative to get a positive future value.
- Mixing up nominal and effective rates. A quoted APY or effective rate already includes compounding. Compounding it again monthly overstates the result.
- Ignoring deposit timing. Deposits at the start of each period earn one period more interest than at the end. Set the type argument to match.
- Counting years in nper with monthly deposits. Rate, nper and pmt must all use the same period: months with months, years with years.
Frequently asked questions
What is the formula for compound interest in Excel?
Type =P*(1+r/n)^(n*t) with your cells, for example =B2*(1+B3/12)^(12*B4) for monthly compounding, or use the FV function: =FV(B3/12,B4*12,0,-B2). Both give the same result.
How do I calculate monthly compound interest in Excel?
Use a monthly rate and the number of months: =FV(annual_rate/12, years*12, 0, -starting_balance). For $10,000 at 4% for 10 years that is $14,908.33.
Why does the FV function return a negative number?
FV uses a cash-flow sign convention: money paid in and money received have opposite signs. Enter the starting balance and deposits as negative numbers and FV returns a positive balance.
What is the difference between simple and compound interest?
Simple interest is paid only on the original amount, compound interest also on the interest already earned. $10,000 at 4% for 10 years grows to $14,000 with simple interest and $14,802.44 with annual compounding.
How long does it take to double money at 4%?
At 4% compounded yearly it takes 17.67 years, which Excel returns with =NPER(4%,0,-1,2). The rule of 72 estimates 72 / 4 = 18 years.