Compound Interest in Excel
Excel has no compound-interest function — you type the arithmetic yourself as =P*(1+r/n)^(n*t), or let FV do the same job. P is the starting principal, r the annual rate, n the number of times a year interest is added, and t the term in years. The entire difference between annual, monthly and daily compounding is the value of n: 1, 12 or 365. FV(rate, nper, pmt, pv) reaches the identical answer with the rate and periods already divided, and it is the one to reach for the moment regular contributions enter the picture, because the power formula has nowhere to put money paid in along the way.
The formula
=B1*(1+B2/B3)^(B3*B4) A = P(1 + r/n)^(nt) - principal, annual rate, compounds per year, years
=B1*(1+B2/12)^(12*B4) monthly compounding written out
=B1*(1+B2/365)^(365*B4) daily compounding
=B1*(1+B2/B3)^(B3*B4)-B1 the interest alone, with the principal taken back off
=FV(B2/12, 12*B4, 0, -B1) the same balance via FV; pv is negative so the result comes out positive
=FV(B2/12, 12*B4, -C1, -B1) with C1 paid in every month as well
=(B5/B1)^(1/B4)-1 the reverse: back out the annual rate from a start and an end valueA worked example
B1 holds a principal of 10,000, B2 an annual rate of 5%, B3 the number of compounds per year, and B4 a term of 10 years.
=B1*(1+B2/B3)^(B3*B4) with B3 set to 12
16,470.09 after ten years, of which 6,470.09 is interest. Set B3 to 1 and annual compounding gives 16,288.95; set it to 365 and daily compounding gives 16,486.65. Monthly to daily is a difference of 16.55 over the whole decade — worth knowing before spending an afternoon on the compounding frequency when the rate and the term are what actually move the number.
Which one do I need?
Frequently asked questions
Is there a compound interest function in Excel?
Nothing is named for it. The two standard routes are the arithmetic formula =P*(1+r/n)^(n*t) typed straight into a cell, and FV(rate, nper, pmt, pv), a general future-value function that produces the identical number when pmt is left at 0. Prefer FV once there are regular contributions, since the power formula has no argument for them.
Why is my compound interest result too high?
Usually the rate and the exponent are out of step. Monthly compounding needs the annual rate divided by 12 and the exponent multiplied by 12 — dividing the rate but leaving the exponent in years, or scaling the exponent while leaving the rate annual, both return believable but wrong balances. Check that the same n appears in r/n and in n*t.
How much does compounding frequency actually change the result?
Far less than most people assume at ordinary rates. On 10,000 at 5% over ten years, annual compounding returns 16,288.95, monthly returns 16,470.09 and daily returns 16,486.65 — under 200 of spread across the entire decade, and only 16.55 of that between monthly and daily. The rate and the term dominate the outcome.
How do I model a rate that changes partway through the term?
The single power formula assumes one constant rate, so it cannot represent this at all. Build a row per period instead, each row taking the previous balance times (1 + that period's rate), and read the closing figure off the last row. The same table absorbs deposits, withdrawals and rate changes that no closed-form formula covers.
New guides and tools, once a month
DE + EN · double opt-in · no spam