Set up your spreadsheet with the four numbers you need
To calculate interest earned in Excel, you need four pieces of information: your starting balance, the annual percentage yield (APY), how often the bank compounds interest, and how many days or months you want to measure. Once you have those, Excel does the math for you using a single formula.
Open a new spreadsheet and create labels in column A: "Starting Balance", "Annual APY", "Compounding Frequency", and "Time Period (days)". In column B, enter the actual numbers from your account. For example: $5,000 in B1, 4.5 in B2 (meaning 4.5%), "daily" in B3, and 365 in B4 if you want to see one year of interest.
The compounding frequency matters because banks don't add interest once a year—they add it multiple times. Daily compounding means interest gets calculated and added to your balance every single day. Monthly compounding does it once a month. The more often it compounds, the more interest you earn, because you earn interest on your interest.
Key Takeaways
- The formula for compound interest in Excel is =Starting_Balance * (1 + APY/Compounding_Periods)^(Compounding_Periods * Years) minus your starting balance to see only the interest earned.
- Most savings accounts compound daily, which means you need to divide the APY by 365 and multiply the time period by the number of times interest compounds.
- You can build a simple table that shows your balance growing day by day or month by month by copying the formula down and adjusting the time period in each row.
- The difference between simple interest (interest on your starting balance only) and compound interest (interest on your balance plus accumulated interest) grows larger the longer your money sits in the account.
Use the compound interest formula for daily compounding
Most savings accounts compound interest daily. The formula for compound interest is: Final Balance = Starting Balance × (1 + APY ÷ Compounding Periods) ^ (Compounding Periods × Years). To find just the interest earned, subtract your starting balance from the final balance.
In Excel, this looks like: =B1*(1+B2/100/365)^(365*B4/365)-B1. The B1 is your starting balance. B2 is your APY (divided by 100 to convert the percentage to a decimal, then divided by 365 for daily compounding). The exponent (365*B4/365) represents how many times interest compounds over your time period. The -B1 at the end subtracts your starting balance so you see only the interest earned, not the total.
If your bank compounds monthly instead of daily, change the 365s to 12 (for 12 months per year). If it compounds quarterly, use 4. Check your account agreement or your bank's website to confirm the compounding frequency—most online savings accounts compound daily, but some older accounts or money market accounts may differ.
Build a day-by-day or month-by-month growth table
To see how your balance grows over time, create a second table with two columns: "Date" or "Day" in column D, and "Balance" in column E. In D1, enter 0 (for day zero). In E1, enter your starting balance from B1. In D2, enter 1. In E2, enter the formula =E1*(1+$B$2/100/365).
The dollar signs ($) lock the APY cell so it doesn't change when you copy the formula down. Copy this formula down for as many rows as you need—if you want a year of daily data, copy it down 365 rows. Each row will show your balance after one more day of interest. The balance in each row becomes the starting point for the next row's calculation, which is how compound interest actually works.
For a month-by-month view instead, use the same structure but change the formula to =E1*(1+$B$2/100/12) and increment column D by 1 for each month instead of each day. This is easier to read if you want to see the big picture over several years.
Compare simple interest versus compound interest
Simple interest only pays you interest on your starting balance—it ignores the interest you've already earned. Compound interest pays you interest on your balance plus all the interest that has accumulated. Over time, compound interest wins by a growing margin.
To see the difference in Excel, create a third column called "Simple Interest Balance". In the first row, enter =B1+(B1*B2/100*B4/365). This calculates interest only on your original starting balance, not on accumulated interest. Copy it down alongside your compound interest column. After a few years, the compound interest column will be noticeably higher, especially at higher APY rates.
This comparison matters because it shows you the real value of leaving money untouched in a savings account. Every dollar of interest you earn starts earning interest itself. The longer your money stays in the account, the more this effect compounds.
Account for deposits or withdrawals during the period
The basic formula assumes you deposit money once and leave it alone. If you make regular deposits (like $100 every month), you need a more detailed table. Create columns for Date, Starting Balance, Deposit, Interest Earned, and Ending Balance.
In the Starting Balance column, reference the previous row's Ending Balance. In the Deposit column, enter your regular deposit amount (or leave it blank for months with no deposit). In the Interest Earned column, use =(Starting_Balance + Deposit) * (APY/100/365) to calculate interest on the new total. In the Ending Balance column, add Starting Balance + Deposit + Interest Earned.
Copy this row down for each month or quarter you want to track. This method shows you how regular deposits accelerate your growth because each new deposit also starts earning interest immediately. It's the most realistic picture of how a savings account actually grows when you're adding money over time.
Check your Excel calculation against your bank statement
Once you've built your formula, verify it against your actual account. Pull your bank statement from three or six months ago and compare the interest your bank actually paid you to what your Excel calculation predicts. They should be very close—within a few cents for smaller balances.
If they don't match, the most common reason is that the APY changed during the period. Banks adjust rates frequently, so if you used a single APY for the entire calculation but your rate changed midway, your Excel number will be off. Check your statement for the exact dates rates changed, then break your calculation into separate periods with different APY rates for each.
Another reason for a mismatch is the exact number of days the bank uses. Some banks use 365 days per year; others use 360. A few use the actual calendar days in the period. Your statement should show the interest calculation method—look for "daily balance method" or similar language in the fine print.
Frequently Asked Questions
What if my bank compounds interest quarterly or annually instead of daily?
Change the 365 in your formula to match your compounding frequency: 4 for quarterly, 2 for semi-annual, or 1 for annual. For example, quarterly compounding would be =B1*(1+B2/100/4)^(4*B4/365)-B1. The exponent still uses your time period in days or years, but the division and multiplication adjust for how often interest actually compounds.
Can I use this formula if my APY changes during the year?
Yes, but you need to split your calculation into separate periods. Calculate interest for the first period at the old rate, use that ending balance as the starting balance for the second period, and calculate at the new rate. Add the interest from both periods together. This is why checking your statement against your formula matters—it catches rate changes you might have missed.
Why does my Excel calculation show more interest than my bank paid?
The most likely reason is that your APY changed during the period, or you used the wrong compounding frequency. Less commonly, the bank may use 360 days instead of 365 in their calculation. Check your statement for the exact APY on each date and the compounding method, then adjust your formula to match.
Do I need to account for taxes on the interest I earn?
Your Excel calculation shows the gross interest earned before taxes. Interest on savings accounts is taxable income, and you'll report it on your tax return. You can add a separate calculation in Excel to estimate taxes owed (multiply interest earned by your tax bracket), but the interest itself is calculated the same way regardless of taxes.
What's the difference between APY and APR for a savings account?
APY (annual percentage yield) includes the effect of compound interest, while APR (annual percentage rate) does not. For savings accounts, always use APY in your formula because APY is what you actually earn. APR is mainly used for loans and credit cards, where you pay interest instead of earning it.