Compound Interest Formula in Excel: Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The compound interest formula in Excel is either the built-in FV function or the manual expression $A = P(1 + r/n)^{nt}$. Both return the same future value when you feed them the same rate, term and compounding frequency. This article shows the syntax, a step-by-step build and a worked example you can copy into a sheet.

Quick Answer

Use FV(rate, nper, pmt, [pv], [type]) for a lump sum or a series of equal deposits [1].
rate is the interest rate per period, so divide the annual rate by the number of compounding periods per year [1].
nper is the total number of periods, so multiply years by periods per year [1].
For a single lump sum, set pmt to 0 and pass the starting amount as a negative pv [1].
The manual formula $A = P(1 + r/n)^{nt}$ gives the same answer and is easier to audit.

The Formula

The compound interest formula in Excel has two equivalent forms. The manual version is:

$$A = P\left(1 + \frac{r}{n}\right)^{nt}$$

Each symbol means:

Symbol	Meaning
$A$	Future value (the balance at the end)
$P$	Present value (the starting amount)
$r$	Nominal annual interest rate, as a decimal
$n$	Number of compounding periods per year
$t$	Time in years

The Excel function version is FV(rate, nper, pmt, [pv], [type]). Its arguments map onto the same ideas [1]:

Argument	Meaning	Required
`rate`	Interest rate per period	Yes
`nper`	Total number of payment periods	Yes
`pmt`	Payment made each period	Yes
`pv`	Present value, the lump sum today	Optional
`type`	0 for payments at period end, 1 for period start	Optional

If you omit pmt, you must include pv, and the other way around [1]. Cash you pay out is negative and cash you receive is positive [1].

How to Calculate It Step by Step

Write down the inputs. Annual rate, years, compounding frequency and starting balance.
Convert the rate to a per-period rate. Divide the annual rate by $n$. For monthly compounding at 6 percent, that is 6%/12.
Convert the term to periods. Multiply years by $n$. Five years monthly is 60 periods [1].
Choose your method. Use FV for a quick answer, or the manual formula when you want to show the math.
Enter the formula. For a lump sum with no extra deposits, =FV(rate, nper, 0, -pv).
Check the sign. A negative pv produces a positive future value, which matches the convention that money paid out is negative [1].
Sanity-check the output. The result must be larger than the starting amount whenever the rate is positive.

A useful cross-check is the effective annual rate. The EFFECT function returns the effective annual interest rate given a nominal rate and the number of compounding periods per year [2]. If your manual formula and EFFECT disagree, one of your inputs is wrong.

Worked Example

Suppose you deposit $1,000 at a nominal 10 percent annual rate, compounded annually, for 2 years. Here $P = 1000$, $r = 0.10$, $n = 1$ and $t = 2$.

Year 1 interest is 10 percent of $1,000, which is $100, so the balance becomes $1,100. Year 2 interest is 10 percent of $1,100, which is $110, so the balance becomes $1,210.

The manual formula gives the same result:

$$A = 1000(1 + 0.10)^{2} = 1000 \times 1.21 = 1210$$

In Excel, =FV(0.10, 2, 0, -1000) returns 1210. The extra $10 in year 2, compared with simple interest, is interest earned on the first year's interest. That difference is the whole point of compounding.

Now change the compounding to monthly. The per-period rate becomes 0.10/12 and the number of periods becomes 24. The formula =FV(0.10/12, 24, 0, -1000) returns a slightly larger figure than 1210, because interest is added twelve times a year instead of once. More frequent compounding at the same nominal rate always produces a higher ending balance.

How to Interpret the Result

The future value is the balance at the end of the term, assuming the rate never changes and no money is withdrawn. Read it as a projection, not a promise.

Three things change the number most:

The rate. Small rate changes compound into large balance changes over long horizons.
The term. Time is the strongest input, because the exponent grows.
The frequency. Monthly compounding beats annual compounding at the same nominal rate.

If you add regular deposits, the pmt argument handles them, and the result becomes the combined future value of the lump sum and the payment stream [1]. Keep the units consistent: a monthly rate needs a monthly period count, and an annual rate needs an annual period count [1].

Doing It in Software

Excel. The fastest route is FV. For a lump sum:

=FV(0.06/12, 5*12, 0, -1000)

For a lump sum plus a monthly deposit, add the payment:

=FV(0.06/12, 5*12, -100, -1000)

The manual formula works too, and it makes the compounding visible:

=1000*(1+0.06/12)^(5*12)

If you want to compare nominal and effective rates, EFFECT takes the nominal rate and the periods per year [2]. For growth rates across irregular cash flows, Excel's CAGR approach uses XIRR instead [3]. If you need to round a displayed balance, see the Excel ROUND function for the syntax.

R. There is no single built-in compound interest function in base R, so write the formula directly:

principal <- 1000
rate <- 0.06
periods <- 60
principal * (1 + rate/12)^periods

Python. The same arithmetic works in plain Python. NumPy's old financial functions were removed in NumPy 1.20, but the separate numpy-financial package offers an fv function:

principal = 1000
rate = 0.06
periods = 60
principal * (1 + rate/12) ** periods

If you would rather not build the sheet yourself, the compound interest calculator returns the same future value from the same inputs.

Common Mistakes

Using the annual rate with monthly periods. Divide the annual rate by the periods per year before passing it to FV [1]. The fix is rate/12 for monthly compounding.
Forgetting to multiply the years. nper is total periods, not years [1]. Five years monthly is 60, not 5.
Mixing units. A monthly rate with an annual period count produces nonsense [1]. Pick one unit and stay in it.
Getting the sign wrong. Deposits are negative and withdrawals are positive [1]. If your future value comes out negative, flip the sign on pv.
Assuming the rate is fixed. FV uses a constant rate for the whole term [1]. Real accounts change rates, so treat the output as a scenario.
Confusing nominal and effective rates. A 6 percent nominal rate compounded monthly is not a 6 percent effective rate. Use EFFECT to convert [2].

Limitations

FV assumes a constant interest rate and constant payments for the entire term [1]. Real savings accounts, bonds and loans rarely behave that way, so the output is a projection under fixed assumptions. It also ignores fees, taxes and inflation, all of which reduce real purchasing power.

The function cannot model irregular deposits, rate changes or withdrawals that vary in size. For those cases you need a period-by-period table or a function built for irregular cash flows, such as XIRR for growth rates [3]. Compounding frequency also matters more than people expect, so always state whether a quoted rate is nominal or effective before comparing two options.

Frequently Asked Questions

What is the compound interest formula in Excel?

The manual form is $A = P(1 + r/n)^{nt}$, where $P$ is the principal, $r$ the annual rate, $n$ the periods per year and $t$ the years. The function form is FV(rate, nper, pmt, [pv], [type]) [1]. Both produce the same future value when the inputs match.

How do I calculate monthly compound interest in Excel?

Divide the annual rate by 12 and multiply the years by 12. For $1,000 at 6 percent for 5 years, use =FV(0.06/12, 5*12, 0, -1000). The rule is that rate and nper must use the same time unit [1].

Why does my FV result come out negative?

Excel treats money you pay out as negative and money you receive as positive [1]. If you enter the present value as a positive number, the future value flips sign. Enter the deposit as a negative pv to get a positive result.

What is the difference between nominal and effective interest rates?

A nominal rate is the quoted annual rate before compounding is applied. The effective rate accounts for compounding within the year and is always higher when compounding happens more than once annually. Excel's EFFECT function converts a nominal rate and a periods-per-year count into the effective annual rate [2].

Can FV handle regular deposits as well as a lump sum?

Yes. Pass the deposit amount as pmt and the starting balance as pv [1]. The function then returns the combined future value of the lump sum and the payment stream. The type argument controls whether payments fall at the start or the end of each period [1].

For related spreadsheet skills, the Excel formulas cheat sheet covers the core functions, and the Excel SUM function guide shows how to total a column of period-by-period balances.