Set up your spreadsheet with the four numbers you need
To calculate savings account interest in Excel, you need four pieces of information: your starting balance, the annual interest rate, how often interest compounds (daily, monthly, or yearly), and how many months or years you're calculating for. Once you have these, Excel can show you exactly how much interest you'll earn—no guessing, no online calculator needed.
Open a blank spreadsheet and create labels in column A: Principal (your starting balance), Annual Rate (as a decimal, so 2.5% becomes 0.025), Compounding Frequency (how many times per year), and Time in Years. Put the actual numbers in column B next to each label. This layout keeps everything visible and makes it straightforward to change numbers later to test different scenarios.
Key Takeaways
- The compound interest formula in Excel is =Principal*(1+(Annual Rate/Compounding Frequency))^(Compounding Frequency*Time in Years), entered as a single formula in one cell.
- Interest rates must be entered as decimals (2.5% becomes 0.025) or Excel will calculate them incorrectly.
- Compounding frequency is the number of times per year interest is added: daily is 365, monthly is 12, yearly is 1.
- To see only the interest earned (not your total balance), subtract your principal from the final amount using a second formula.
- Changing any single number—rate, time, or starting balance—when ready updates your result, so you can compare different scenarios side by side.
Enter the compound interest formula correctly
The formula Excel uses for compound interest is: =Principal*(1+(Annual Rate/Compounding Frequency))^(Compounding Frequency*Time in Years). If your numbers are in cells B1 through B4, the formula becomes =B1*(1+(B2/B3))^(B3*B4). The caret symbol (^) means "to the power of"—it's on your keyboard as Shift+6.
Click on an empty cell (usually B5 or B6) and type the formula exactly as written, using the cell references that match where you put your numbers. Press Enter. Excel calculates the result when ready. This number is your total balance after interest—it includes both what you started with and what you earned.
If the result looks wrong, check three things: that your interest rate is a decimal (0.025, not 2.5), that your compounding frequency matches your bank's actual schedule, and that you used the caret symbol, not an asterisk, for the exponent.
Understand what each part of the formula does
The formula breaks into pieces that each do one job. The principal is your starting amount. The fraction (Annual Rate/Compounding Frequency) divides your yearly rate by how many times it compounds, so you get the rate for each period. Adding 1 to that fraction is how compound interest works—it's the growth multiplier for each period.
The exponent (Compounding Frequency*Time in Years) tells Excel how many periods to explore that multiplier. If you compound monthly for 2 years, that's 12 times 2, or 24 periods. The asterisk (*) multiplies everything together. The order matters—Excel follows the standard math rule of exponents first, then multiplication and division from left to right.
Calculate interest earned separately from total balance
The formula above gives you your total balance. To see only the interest you earned, subtract your principal in a second cell. If your total balance is in B5 and your principal is in B1, type =B5-B1 in cell B6. This shows you the pure interest amount without the money you started with.
Many people want to see both numbers side by side: the total you'll have and the interest you earned. Label one cell "Total Balance" and another "Interest Earned" so you don't confuse them later. This is especially useful when you're comparing different interest rates or time periods.
Test different scenarios by changing one number at a time
Once your formula is set up, change any single number in column B and watch the result update when ready. Try a higher interest rate to see how much difference 0.5% makes. Try 5 years instead of 3. Try $10,000 instead of $5,000. Each change shows you the real impact without recalculating by hand.
Create a second set of formulas a few rows down to compare two scenarios side by side. For example, put one bank's rate and terms in rows 1–4, and another bank's in rows 8–11. Calculate both totals and interest amounts. Now you can see which bank actually pays more, not just which advertises a higher rate.
Handle daily compounding and longer time periods
Daily compounding uses 365 as your frequency (some banks use 360; check your account terms). Monthly is 12, quarterly is 4, and yearly is 1. The more often interest compounds, the slightly more you earn, but the difference is usually small unless your balance is very large or your rate is unusually high.
For time periods longer than a few years, the formula still works exactly the same way. A 10-year calculation uses the same formula as a 1-year one—just change the time number. Excel handles large exponents without any problem. If you're calculating for a child's college fund or a long-term savings goal, the formula scales up automatically.
Frequently Asked Questions
What if my bank compounds interest quarterly instead of monthly?
Change your compounding frequency from 12 to 4. Quarterly means four times per year. The formula recalculates when ready. You can test both 12 and 4 in separate rows to see how much difference the compounding schedule makes for your specific balance and rate.
Do I need to convert my interest rate to a decimal?
Yes. If your bank shows 2.5%, you must enter 0.025 in Excel. If you enter 2.5, Excel treats it as 250% and your result will be wildly wrong. Divide the percentage by 100 to convert it: 2.5 ÷ 100 = 0.025.
Can I use this formula for money market accounts or CDs?
Yes. The formula works for any account where interest compounds at regular intervals. Money market accounts and CDs use the same compound interest math as savings accounts. Just enter your actual rate, compounding frequency, and term length.
What if I want to add deposits to my savings each month?
The basic formula above assumes you deposit the principal once and leave it alone. To add regular monthly deposits, you need a more complex formula or a month-by-month table. Create a row for each month, calculate the interest on the previous month's balance, add your new deposit, and repeat. This is more work but shows you the real growth when you're saving regularly.
Why does my result have so many decimal places?
Excel shows the exact calculation, which often includes cents and fractions of cents. Right-click the cell with your result, select Format Cells, choose Currency, and set it to 2 decimal places. This rounds the display to dollars and cents without changing the actual number Excel uses for further calculations.