Yes. In Microsoft Excel for Microsoft 365 on Windows, enter the starting balance, annual interest rate, number of years, and compounding periods per year in cells B2:B5, then use =B2*(1+B3/B5)^(B4*B5) to calculate the future value. This formula assumes a fixed nominal annual rate and no additional deposits or withdrawals.
Set Up a Basic Compound Interest Calculator
Start with a small input table so the assumptions are visible and easy to change. For example, enter these labels in A2:A5 and values in B2:B5:
- B2: starting balance, such as 10000
- B3: annual interest rate, entered as 5% (or 0.05)
- B4: number of years, such as 10
- B5: compounding periods per year, such as 12 for monthly
With those values in B2:B5, enter =B2*(1+B3/B5)^(B4*B5) in B6. The result is approximately $16,470.09 for a $10,000 starting balance earning a fixed 5% nominal annual rate compounded monthly for 10 years. Format B2 and B6 as currency, B3 as a percentage, and B4:B5 as numbers. The formula returns a nominal future dollar amount; it does not account for taxes, fees, inflation, or changes in the rate.
For the period count in B5, common illustrative choices are 1 for annual, 2 for semiannual, 4 for quarterly, and 12 for monthly compounding. A value of 365 represents daily compounding only when that convention matches the account or contract. Always check how the actual provider defines its rate and compounding schedule.
What Compound Interest Means
Compound interest is interest calculated on an amount that can include both the original principal and interest already added. If interest is credited to the balance and left there, later interest can be calculated on that larger balance. By contrast, simple interest is calculated only on the original principal. The difference tends to become more noticeable over longer periods, at higher rates, and when interest is compounded more frequently.
The Excel formula divides the annual rate by the number of compounding periods in a year, then raises the per-period growth factor to the total number of periods. In the example, 5% divided by 12 gives a monthly rate of about 0.4167%, and 10 years multiplied by 12 gives 120 periods. The formula applies that growth factor 120 times.
A quoted annual rate needs careful interpretation. The basic formula treats B3 as a nominal annual rate, with the periodic rate calculated as B3 divided by B5. If a financial product instead gives an effective annual rate, applying that rate as though it were nominal can overstate the result. The rate definition and compounding convention should come from the account terms, not from an assumption about what a percentage means.
How the Formula Works
In =B2*(1+B3/B5)^(B4*B5), B2 is the initial balance, B3/B5 is the rate for one compounding period, and B4*B5 is the total number of periods. The caret symbol raises a number to a power. Excel recalculates the result whenever an input changes, which makes the sheet useful for comparing clearly defined scenarios.
Excel also has a built-in future-value function. With the same basic layout—B2 as the starting balance, B3 as the nominal annual rate, B4 as years, and B5 as periods per year—enter =FV(B3/B5,B4*B5,0,-B2) in a result cell. It returns approximately $16,470.09 for the example. FV uses a sign convention for cash flows: the starting amount is entered as negative here so the future value appears as a positive result. The direct formula is often easier to inspect; FV can be convenient when a model also includes regular payments.
Include Regular Deposits
A savings projection often needs to include periodic contributions as well as interest. If deposits are made at the end of each compounding period, the formula must add the future value of those deposits to the future value of the starting balance. This is different from simply adding all deposits to the principal, because earlier deposits have more periods to earn interest than later ones.
For an illustrative monthly-deposit model, use B2 for the starting balance, B3 for the nominal annual rate, B4 for years, B5 for periods per year, and B6 for the deposit made at the end of each period. With B2 = 10000, B3 = 5%, B4 = 10, B5 = 12, and B6 = 200, enter =B2*(1+B3/B5)^(B4*B5)+B6*(((1+B3/B5)^(B4*B5)-1)/(B3/B5)) in B7. It returns approximately $47,526.52. That total comprises about $16,470.09 from the starting balance and about $31,056.43 from the 120 monthly deposits and their modeled growth.
This contribution formula assumes the deposit amount and rate stay constant, deposits occur at the end of every period, and the deposit frequency matches the compounding frequency. If deposits are made at the beginning of each period, each deposit has an extra period to grow, so the result will be higher. If the actual deposit schedule differs—for example, monthly deposits with a rate credited quarterly—the simple formula may not match the account mechanics. Also, the formula divides by the periodic rate, so a zero-rate case needs a separate calculation: starting balance plus deposit amount multiplied by total periods.
Make a Year-by-Year Balance Table
A single future-value figure hides the path between the starting amount and the final amount. A year-by-year table makes the projection easier to review and chart. For this separate example, put year numbers 0 through 10 in A2:A12, the initial balance in B2, and the annual rate in D2. Enter the opening balance in B2 and 5% in D2. With the year labels in place, enter =B2*(1+$D$2) in B3 and fill it down through B12. The formula assumes interest is compounded annually and no deposits or withdrawals occur. At year 10, B12 is approximately $16,288.95 on a $10,000 opening balance.
The dollar signs keep the rate reference fixed as the formula is copied down, while the reference to the previous balance changes by row. In Microsoft 365 for Windows, drag the fill handle at the lower-right corner of the selected cell to copy the formula, or copy and paste it into the cells below. Excel for Mac uses the same worksheet formula and fill-handle approach. The annual table gives a different result from the monthly example because it compounds once per year rather than 12 times per year.
Check Inputs, Formatting, and Scenarios
Make inputs easy to distinguish from calculated results. A simple layout might use one fill color for editable assumptions, another for formula cells, and a note identifying whether deposits occur at the start or end of each period. These visual cues are useful when someone else opens the workbook or when you revisit it months later.
Before trusting a result, check that:
- The rate is entered as a percentage, such as 5%, rather than 5, which Excel interprets as 500%.
- The number of years and periods per year are positive numbers.
- The rate used is the relevant nominal or effective rate and matches the stated compounding convention.
- Currency formatting is applied to dollar amounts without rounding the underlying calculation.
- Regular deposits are modeled at the correct frequency and timing.
To compare scenarios, change one assumption at a time or create a small table with different rates, time horizons, or deposit amounts. Label each case so the displayed results cannot be mistaken for guaranteed outcomes. Excel’s What-If Analysis tools can also help examine input changes; on Windows, find them on the Data tab under What-If Analysis. The exact available tools can depend on the Excel version and workbook setup.
Common Errors and Important Limits
A frequent mistake is using the annual rate directly as the periodic rate. If B3 contains 5% and B5 contains 12, the monthly rate in the basic model is B3/B5, not B3. Another common error is multiplying years by 12 while leaving the rate annual, which mixes monthly period counts with an annual rate. The rate and period count must use compatible units.
Results can also differ from a bank, loan, or investment statement for legitimate reasons. The provider may use a different day-count convention, apply interest daily but credit it on another schedule, change the rate over time, round interest at each posting, charge fees, or impose deposit timing rules. A fixed-rate Excel projection is a simplified model, not a statement of what an account will actually pay.
Compound interest is not the same thing as investment performance. A savings account may have a stated rate, while investments can rise or fall and do not deliver a guaranteed fixed return. The spreadsheet also does not automatically account for inflation, income taxes, withdrawal penalties, or product-specific terms. Use the calculation to understand assumptions, not as financial, tax, investment, or legal advice; verify real terms with the lender or account provider, and consult a qualified professional for advice about your circumstances.
Alternatives to a Custom Formula
A direct formula is transparent and flexible for a basic projection. FV is useful when the calculation includes regular equal payments, and a year-by-year table is preferable when you want to see intermediate balances. A built-in online calculator can be quicker for a one-off estimate, but a spreadsheet makes it easier to inspect inputs, compare scenarios, and keep a record of assumptions.
Templates can save setup time, but inspect their formulas before relying on them. Confirm whether they assume payments at the beginning or end of a period, whether the rate is nominal or effective, and whether they include fees or inflation. In Excel for the web, the core formulas in this article work in the browser; ribbon placement and some analysis features can differ from the Windows desktop application. On Mac, the formulas are the same, though some ribbon and keyboard interactions differ.
Frequently Asked Questions
What is the Excel formula for compound interest?
For a starting balance in B2, annual nominal rate in B3, years in B4, and compounding periods per year in B5, use =B2*(1+B3/B5)^(B4*B5). It assumes no additional deposits or withdrawals and a fixed rate.
How do I calculate compound interest monthly in Excel?
Set the periods-per-year input to 12 and use the basic formula with the annual rate divided by 12. Make sure the rate you enter is stated on a basis that supports that calculation, and check the provider’s terms if you are modeling a real account.
Can Excel calculate compound interest with monthly deposits?
Yes. Use a future-value calculation that includes both the starting balance and recurring payments, such as the contribution formula above. Confirm whether deposits happen at the start or end of the month, since timing changes the projected total.
Why does my Excel result differ from a compound interest calculator?
Compare the rate definition, compounding frequency, contribution timing, and rounding assumptions. Also check for a percentage entered incorrectly or a mismatch between the rate period and the number of periods. Different assumptions can produce different answers even when both formulas work correctly.
Does Excel’s FV function work for compound interest?
Yes. FV calculates future value using a periodic rate, number of periods, and optional regular payments. Its cash-flow sign convention can make the starting balance appear negative in the formula so the projected value displays as positive.
Does the result include inflation or tax?
No. The basic formulas calculate a nominal future balance under the specified assumptions. They do not adjust for purchasing power, taxes, fees, or other account terms unless you add those factors to a separate model.
Related
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- UPPER Excel Formula: Capitalize Text in Excel CellsTo make every letter in text uppercase in Excel, use the UPPER function. If the original text is…
- Find a Position in Excel with MATCH and XMATCH FormulasTo find where a value sits within a list, use MATCH or XMATCH. If the value to find…
- Excel Formula for Range of Values: Find the SpreadTo calculate the numerical range of values in Excel, subtract the smallest value from the largest: =MAX(A2:A7)-MIN(A2:A7). If…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the…
- Excel Formula for Tomorrow’s Date: TODAY()+1 ExplainedIn Excel, use =TODAY()+1 to return tomorrow’s date. Enter it in a cell and format that cell as…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.