Compound Interest Formula in Excel and Google Sheets
Purpose
Compound interest is one of those financial concepts that looks intimidating until you build it in a spreadsheet and see…
Compound interest is one of those financial concepts that looks intimidating until you build it in a spreadsheet and see the formula is just five variables. I use it constantly for investment projections, savings goal modelling, and explaining to clients why starting early matters so much more than contributing more later. In Excel and Google Sheets, you have two choices: the direct compound interest formula =P*(1+r/n)^(n*t) which is transparent and educational, or the built-in FV function which is shorter and more idiomatic for finance professionals. The monthly loan payment formula is the companion to this — it calculates the periodic payment required to repay a principal at compound interest. Both give identical results. The variables are principal (starting amount), annual rate (as a decimal, so 5% is 0.05), compounding frequency (monthly is 12, quarterly is 4, annual is 1), and time in years. Understanding what each variable does lets you build comparison tables that show how different compounding frequencies and rates interact — which is where spreadsheets become genuinely more powerful than a financial calculator.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=P*(1+r/n)^(n*t)
P = principal, r = annual rate (decimal), n = periods per year, t = years. Alternative: =FV(r/n, n*t, 0, -P)
Arguments
| Argument | Required | Description |
|---|---|---|
| P (principal) | Required | The starting amount — the initial investment or loan balance. |
| r (rate) | Required | The annual interest rate as a decimal. 5% = 0.05. |
| n (periods per year) | Required | How many times interest compounds each year: 12 for monthly, 4 for quarterly, 1 for annual, 365 for daily. |
| t (time in years) | Required | The number of years the money is invested or borrowed. |
How it works
The compound interest formula multiplies the principal by (1 + rate per period) raised to the total number of periods. The rate per period is the annual rate divided by the number of compounding periods per year (r/n). The total number of periods is the compounding frequency multiplied by the number of years (n*t). Each period, interest is earned on the principal plus all previously earned interest — this accumulation is what separates compound from simple interest. Monthly compounding at 5% annual means r/n = 0.05/12 = 0.4167% per month, and over 3 years the exponent is 12*3 = 36. The result is the total balance — principal plus accumulated interest. To isolate the interest earned, subtract the original principal from the result. The FV function achieves the same result in a different syntax: FV(r/n, n*t, 0, -P) where the principal is entered as negative because it represents a cash outflow.
Examples
1
Calculate the future value of a £10,000 investment at 5% annual interest compounded monthly for 3 years.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Label | Principal | Rate | Periods/Year | Years | Future Value |
| 2 | Monthly | 10000 | 0.05 | 12 | 3 | =B2*(1+C2/D2)^(D2*E2) |
| 3 | 10000 | 0.05 | 12 | 3 | 11614.72 |
Row 2: 11614.72
— 10,000 at 5% compounded monthly for 3 years grows to 11,614.72. The interest earned is 1,614.72.
Rate 0.05 is 5% as a decimal. Periods/Year 12 means monthly compounding. The exponent (D2*E2) = 12*3 = 36 total compounding periods. Entering values in separate cells makes it easy to build a comparison table by changing one variable at a time.
2
Compare monthly vs quarterly compounding at the same rate and term to show how compounding frequency affects the outcome.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Compounding | Principal | Rate | Periods/Year | Years | Future Value |
| 2 | Monthly | 10000 | 0.05 | 12 | 3 | 11614.72 |
| 3 | Quarterly | 10000 | 0.05 | 4 | 3 | =B3*(1+C3/D3)^(D3*E3) |
| 4 | Annual | 10000 | 0.05 | 1 | 3 | 11576.25 |
Row 2: 11607.55
— Quarterly compounding (n=4) gives 11,607.55 compared to monthly (11,614.72) and annual (11,576.25). More frequent compounding yields more — but the difference between monthly and quarterly is only £7.17 over 3 years.
The differences between compounding frequencies are small at low rates over short periods. Over decades or at higher rates, the gap widens significantly. A 30-year comparison at 7% shows a much larger difference — compound interest tables like this make the effect visible.
3
Use the FV function as an alternative to the direct formula for the same monthly compounding calculation.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Label | Principal | Rate | Periods/Year | Years | FV Result |
| 2 | Monthly | 10000 | 0.05 | 12 | 3 | =FV(C2/D2,D2*E2,0,-B2) |
| 3 | 11614.72 |
Row 2: 11614.72
— FV(0.05/12, 36, 0, -10000) returns 11,614.72 — identical to the direct formula. The principal is entered as -10000 because FV treats it as a cash outflow.
FV is the standard Excel finance function for this calculation. The direct formula P*(1+r/n)^(n*t) is preferred in educational contexts because every variable is explicit. FV is preferred by finance professionals because it follows Excel's consistent payment-function conventions.
Common use cases
1. Project the future value of a savings account or investment over multiple years with monthly compounding
=B2*(1+C2/12)^(12*E2)
2. Compare different interest rates or compounding frequencies side by side in a scenario table
=B2*(1+C2/D2)^(D2*E2)
3. Calculate the interest earned on an investment by subtracting the principal from the future value
=(B2*(1+C2/D2)^(D2*E2))-B2
4. Use FV to calculate compound growth with optional regular contributions as an additional payment argument
=FV(C2/12,E2*12,-payment,-B2)
5. Model a loan balance growing at compound interest to understand the true cost of deferred payment
=B2*(1+C2/12)^(12*E2)
Common errors
Result is much higher or lower than expected
The rate is entered as a percentage (5) rather than a decimal (0.05). Using 5 instead of 0.05 as the rate produces a wildly inflated result.
Fix: Always enter rates as decimals: 5% = 0.05, 3.5% = 0.035. Divide a percentage cell by 100 if needed: =B2*(1+C2/100/D2)^(D2*E2).
FV returns a negative number
FV follows the cash flow sign convention — money paid out is negative and money received is positive. Entering the principal as a positive number makes FV return a negative result.
Fix: Enter the principal as a negative in FV: =FV(rate,nper,0,-principal). The negative sign represents money leaving your account today to grow into the future value.
Result does not match an online calculator
Online calculators use different compounding assumptions — some use daily compounding, some annual, and some use slightly different rounding. Check that n (periods per year) in your formula matches the compounding frequency stated by the source you are comparing against.
Tips and variations
Put each variable in a separate cell for a flexible scenario model
Entering P, r, n, and t in individual cells and referencing them in the formula makes it trivially easy to change one variable and see the effect. This is how I build investment comparison tables — one formula, four input cells, instant scenarios.
=B2*(1+C2/D2)^(D2*E2)
Calculate interest earned by subtracting the principal from the result
The compound interest formula returns the total balance, not just the interest. Subtract the original principal to isolate the growth: =(B2*(1+C2/D2)^(D2*E2))-B2. This is the compound interest earned figure that most people actually want when they ask about compound interest.
=(B2*(1+C2/D2)^(D2*E2))-B2
Use FV with a payment argument to include regular contributions
=FV(rate,nper,-pmt,-pv) models a savings plan where you add a regular contribution each period on top of the initial deposit. The pmt argument is the periodic contribution entered as negative. This is the full compound interest model for retirement savings calculators.
=FV(C2/12,E2*12,-D2,-B2)
Excel vs Google Sheets
Excel vs Google Sheets
The compound interest formula and the FV function work identically in Excel 2003 and newer and every version of Google Sheets. The mathematical formula P*(1+r/n)^(n*t) uses only arithmetic operators available in all versions. FV is a built-in financial function in both applications with identical syntax.
Related reading
Frequently asked questions
The compound interest formula is =P*(1+r/n)^(n*t) where P is the principal, r is the annual interest rate as a decimal, n is the number of compounding periods per year, and t is the number of years. For example, =10000*(1+0.05/12)^(12*3) calculates the future value of 10,000 at 5% annual interest compounded monthly for 3 years.
Simple interest calculates interest only on the original principal: Interest = P*r*t. Compound interest calculates interest on the principal plus any accumulated interest from previous periods, so the interest itself earns interest. Over longer periods, compounding produces significantly higher returns — this is the effect often described as interest compounding on itself.
The FV function calculates future value: =FV(rate,nper,pmt,pv). For monthly compounding at 5% annual for 3 years with no regular payments: =FV(0.05/12,3*12,0,-10000). The principal is entered as negative because it represents money paid out. FV returns the future value as a positive number.
n is the number of times interest is compounded per year. Annual compounding is n=1, semi-annual is n=2, quarterly is n=4, monthly is n=12, and daily is n=365. More frequent compounding produces a slightly higher return because interest is added to the balance more often, and each new interest payment is slightly larger.
Subtract the principal from the compound interest formula result: =(P*(1+r/n)^(n*t))-P. For example, =(10000*(1+0.05/12)^(36))-10000 gives the interest earned, not the total balance. This is the compound interest earned, not the final account balance.