Compound Interest Formula in Google Sheets: A Working Example

A spreadsheet is useful when you want to see exactly which assumptions produce a future balance. For a single deposit, one formula is enough. For equal recurring deposits, Google Sheets has an FV function that handles payment timing. The two methods answer different questions, so choose the one that matches your cash flows rather than reaching for a formula because it looks familiar.

The examples below use a hypothetical $1,000 starting amount, a constant 6% nominal annual rate, ten years and monthly compounding. The monthly-deposit example adds $100 at the end of each month. These are not forecasts. Rates can change, investments can lose value, and fees, taxes and inflation are excluded.

Set up the input cells

Cell	Label	Value
B1	Starting amount	1000
B2	Annual nominal rate	6%
B3	Years	10
B4	Deposit each month	100
B5	Periods per year	12

Enter the percentage in B2 as 6%, or enter 0.06 and format the cell as a percentage. Do not enter the plain number 6 and then divide it by twelve, because Sheets would treat that as 600%. Keep B5 at 12 for this example so the compounding periods and deposit periods are both monthly.

Formula for one deposit with no additions

Put =B1*(1+B2/B5)^(B5*B3) in another cell. This implements A = P(1 + r/n)^(nt). B1 is P, B2 is the annual nominal rate r, B5 is the number of compounding periods n, and B3 is time t in years. With the values above, the result is about $1,819.40 after ten years. The interest is about $819.40 after subtracting the original $1,000.

This formula ignores B4. It is correct only for a lump sum that remains in the model for the whole term without additional deposits or withdrawals. Do not add ten years of deposits to B1 and compound the entire amount for ten years. A deposit made in year nine has not been earning interest since year one.

Formula with $100 deposited each month

For equal deposits at the end of each month, enter =FV(B2/B5,B3*B5,-B4,-B1,0). The five arguments are the periodic rate, number of periods, periodic payment, starting present value and a timing flag. Google describes the FV function as calculating the future value of a constant-payment investment at a constant rate. Its documentation says the final argument is 0 for end-of-period payments and 1 for beginning-of-period payments.

In this example, the periodic rate is 6%/12, the number of periods is 10 × 12, and each payment is $100. The FV result is about $18,207.33. You contributed $13,000 in total: $1,000 initially and $100 in each of 120 months. The difference, about $5,207.33, is modelled growth. Keep contributions and growth separate if you share the result.

The minus signs before B4 and B1 are deliberate. FV follows a cash-flow sign convention: the money you put in is represented as an outflow so the future balance comes back positive. If you remove just one minus sign, the function can produce a confusing result because the two inputs represent cash moving in opposite directions. Use a consistent sign convention rather than hiding a negative output with formatting.

Beginning versus end of the month

Change the final 0 to 1 if each $100 deposit arrives at the beginning of the month. Under the same constant-rate assumptions, the result rises to about $18,289.27. The reason is simple: each contribution gets one extra monthly compounding period. That is a $81.94 difference in this ten-year example. Choose the setting that best reflects when the money actually enters the earning balance.

A regular paycheck deduction is not automatically a beginning-of-month deposit. It may arrive at a different date, and the account may calculate interest using its own daily-balance method. The FV timing flag is a simplified period convention, not a record of actual transaction dates.

Checks that catch most spreadsheet errors

With the rate set to 0%, the future balance should equal total deposits, or $13,000 here.
With B4 set to 0, FV should match the lump-sum formula at the same monthly rate and ten-year term.
Changing the years should change both the number of periods and the count of deposits.
Changing B5 to 4 without changing the deposit schedule would make B4 a quarterly deposit, not a monthly one.
The future balance includes the starting principal and deposits. Subtract them before calling the rest interest.

The second check is particularly useful: with B4 at zero, both formulas should give about $1,819.40. If they do not, inspect the rate format, B5 and cash-flow signs. You can round the displayed result to two decimals, but avoid rounding every intermediate period in a manually built schedule unless you are trying to match an institution's specific method.

For a quick audit trail, put the final balance, total deposits and calculated growth in three adjacent labelled cells. If the FV result is in B7, you can calculate total deposits as =B1+B4*B3*B5 and modelled growth as the final balance minus that total. These companion cells make it obvious when a large final value mostly reflects money you added yourself rather than the interest assumption.

When a single FV formula is not enough

FV assumes a constant rate, equal deposits and aligned payment periods. It will not automatically handle irregular deposits, changing rates, withdrawals or monthly contributions with quarterly compounding exactly as the simple cell setup implies. You can build a row-by-row schedule for those cases, applying each cash flow at its actual period, but it needs careful auditing.

ToolNimbly's compound interest calculator supports different contribution and compounding frequencies, and it shows a yearly table you can export as CSV. Use it as a cross-check for supported scenarios, remembering that it also assumes a constant rate and does not model withdrawals, taxes, fees or inflation. The Investor.gov calculator is another independent reference for basic compounding assumptions.

A useful Sheets model is one you can explain. Label the inputs, keep rate and period units consistent, test a zero-rate case, and make clear whether deposits occur at the start or end of a period. Then the number at the bottom becomes an auditable illustration rather than a mysterious promise.