To calculate compound interest in Excel, first decide whether the scenario has only starting capital or also includes monthly deposits. The direct formula handles the first case; FV handles the second and records whether each deposit is made at the beginning or end of the month.
Formulas you can copy directly
Without deposits, use =B2*(1+B3)^B4. With fixed deposits, use =FV(B3,B4,-B5,-B2,B6). Microsoft documents the syntax as FV(rate,nper,pmt,[pv],[type]): rate applies to each period, nper is the number of periods, pmt is the fixed deposit, pv is the starting capital, and type sets the deposit timing.
| Scenario | Formula | How to read it |
|---|---|---|
| No deposits | =B2*(1+B3)^B4 | Starting capital compounded at the monthly rate |
| With deposits | =FV(B3,B4,-B5,-B2,B6) | B6 = 0 at the end; B6 = 1 at the beginning |
| Equivalent monthly rate | =(1+B7)^(1/12)-1 | B7 contains an effective annual rate |
FV assumes a fixed rate and equal deposits. For the underlying idea before you build the sheet, read the compound interest guide.
Set up the cells and calculate without deposits
Place the labels in column A and the inputs in column B. Enter the rate as 1% or 0.01, not as 1. Cell B6 accepts 0 or 1 and affects only the case with deposits.
| Cell | Contents | Example value |
|---|---|---|
| B2 | Starting capital | 1000 |
| B3 | Monthly rate | 1% |
| B4 | Number of months | 3 |
| B5 | Monthly deposit | 100 |
| B6 | Deposit type | 0 for end; 1 for beginning |
To isolate the return on the starting capital, use =B2*(1+B3)^B4. With the example inputs, the calculation is 1000*(1+0.01)^3 and the stored result is 1030.301. If the cell displays two decimal places, you will see 1,030.30; retain the full value for later calculations.
Use the signs and type 0 or 1 correctly
B2 and B5 can remain positive inputs in the worksheet. The formula uses -B2 and -B5 because the starting capital and deposits are cash outflows. The future balance received therefore appears as a positive number. If both minus signs are removed, FV returns the same amount with a negative sign.
Type 0 is the default and represents deposits at the end of each month. Type 1 represents deposits at the beginning. With a positive rate, type 1 gives a higher balance because every deposit earns interest for one more period.
Audit the balance month by month
A balance schedule makes an incorrect rate, sign, or deposit time easier to spot. For end-of-period deposits, use S_n=S_(n-1)*(1+i)+A. For beginning-of-period deposits, use S_n=(S_(n-1)+A)*(1+i).
Build the audit in D2:F6. In D3, E3, and F3, enter 0, =$B$2, and =$B$2. On the next row, use =D3+1, =E3*(1+$B$3)+$B$5, and =(F3+$B$5)*(1+$B$3); then fill down through month 3.
| Month | End deposit | Beginning deposit |
|---|---|---|
| 0 | 1,000.00 | 1,000.00 |
| 1 | 1,110.00 | 1,111.00 |
| 2 | 1,221.10 | 1,223.11 |
| 3 | 1,333.31 | 1,336.34 |
The full month-three values are 1333.311 and 1336.3411. Compare those numbers with FV before rounding. When only the display uses two decimal places, later rows still calculate with the stored precision.
Match annual rates, monthly rates, and periods
The rate and number of periods must use the same unit. If B3 contains a monthly rate, B4 must contain months. An effective rate of 1% per month is equivalent to (1+0.01)^12-1 = 12.682503013...% per year. It should never be labeled as 12% per year.
A nominal annual rate compounded monthly may be divided by 12 to obtain the periodic rate defined by that convention. An effective annual rate needs a different conversion: (1+annual rate)^(1/12)-1. Confirm which kind of rate was quoted before selecting the formula.
Handle zero rates, references, separators, and errors
At a zero rate, starting capital of 1,000 plus three deposits of 100 totals 1,300 for both type 0 and type 1. This simple case is useful for checking signs and the number of deposits before introducing a positive rate.
Use absolute references such as $B$3 and $B$5 in the monthly audit so the rate and deposit do not move when formulas are filled down. English Excel examples normally use a period for decimals and commas between FV arguments. If your installation shows another convention, follow the separators in formulas that it already accepts. The Excel percentage guide explains the difference between entering 1, 1%, and 0.01.
- Unexpectedly high balance: B3 contains 1 instead of 1% or 0.01. This represents 100% per period, a rate 100 times greater than 1%, but the final compounded balance is not simply 100 times larger.
- Balance is negative: the capital and deposit signs do not follow the same cash-flow convention.
- FV and the audit differ: check B6 and where the deposit appears in the recurrence.
- Formula appears as text: remove a leading apostrophe and change the cell format to General.
- Small difference in cents: do not round each month; round the display only.
- Formula error: check the FV name and the argument separator used by your regional settings.
Check with the calculator and understand the limits
Use the investment calculator as a second check on the starting capital, rate, term, and deposits. Before comparing totals, confirm that the calculator and worksheet place deposits at the same point in each month. The audit table remains the independent check: it should end at 1333.311 for type 0 and 1336.3411 for type 1.
| Check | What to compare |
|---|---|
| Inputs | 1,000; 1% per month; 3 months; 100 deposit |
| Deposit timing | End of month or beginning of month |
| Precision | Stored value before display rounding |
| Schedule | Each row of the monthly recurrence |
| Total | FV, audit, and calculator under the same assumptions |
The example assumes a fixed rate, equal monthly deposits, and regular intervals. Taxes, fees, variable deposits, and rates that change over time are outside this illustration. When those elements apply, place each cash flow in its actual month and do not treat this formula's result as a guaranteed forecast.