Set up your Excel sheet with the four numbers you need

To calculate how much interest your savings account will earn, you need four pieces of information: the amount of money you start with (called the principal), the annual interest rate your bank pays (the APY), how long the money sits in the account (in years), and how often the bank adds interest to your account (called the compounding frequency).

Open a blank Excel spreadsheet and create labels in column A for each of these. In column B, enter the actual numbers from your bank statement or account details. For example: Principal in A1, your dollar amount in B1; Annual Interest Rate (APY) in A2, your percentage in B2; Years in A3, your timeframe in B3; and Compounding Frequency in A4, how many times per year interest is added in B4.

Most savings accounts compound interest monthly (12 times per year) or daily (365 times per year). Check your bank's website or account agreement to find out which applies to you. This matters because more frequent compounding means you earn a tiny bit more interest.

Key Takeaways

  • You need four numbers to calculate savings interest: starting balance, annual interest rate (APY), length of time in years, and how often interest compounds (usually monthly or daily).
  • The Excel formula for compound interest is =B1*(1+(B2/B4))^(B3*B4), where B1 is principal, B2 is APY, B3 is years, and B4 is compounding frequency.
  • To find just the interest earned (not the total), subtract your starting balance from the final amount using a second formula.
  • You can change any number in your setup to see how different interest rates, time periods, or starting amounts would affect your earnings.

Enter the compound interest formula in Excel

The formula Excel uses to calculate compound interest is based on a mathematical equation that accounts for interest being added multiple times per year. In cell B5, type this formula exactly: =B1*(1+(B2/B4))^(B3*B4)

Here is what each part means: B1 is your starting balance. B2 is your annual interest rate, divided by B4 (the number of times per year interest compounds) to get the rate per compounding period. The part (1+(B2/B4)) represents your balance growing by that small amount each period. The ^(B3*B4) means you are raising that growth to the power of however many compounding periods happen in your timeframe. When you press Enter, Excel calculates the total amount you will have at the end.

If your interest rate is shown as a percentage (like 4.5%), you need to convert it to a decimal before the formula works. In B2, enter 0.045 instead of 4.5%. If you already entered it as 4.5, you can modify the formula to =B1*(1+((B2/100)/B4))^(B3*B4) to handle the conversion automatically.

Calculate just the interest earned, not the total balance

The formula above gives you the total amount in your account at the end — your original money plus all the interest. If you want to know only how much interest you earned, you subtract the starting balance from that total. In cell B6, type: =B5-B1

This takes the final amount (B5) and subtracts your principal (B1), leaving only the interest portion. This number is useful if you want to see at a glance how much your money grew without having to do mental math.

Test your spreadsheet with a real example

Let's say you have $5,000 in a savings account earning 4.5% APY, compounded monthly, and you want to know how much you will have after 2 years. Enter these numbers: B1 = 5000, B2 = 0.045, B3 = 2, B4 = 12. When you press Enter on the formula in B5, you should see approximately $5,472.08. The interest earned (B6) would be about $472.08.

You can verify this makes sense by thinking about it roughly: 4.5% of $5,000 is $225 per year, so over 2 years you might expect around $450. The actual number is slightly higher because of compounding — each month, you earn interest on the interest from previous months. This is why the exact formula matters.

Adjust your numbers to compare different scenarios

One advantage of building this in Excel is that you can change any single number and when ready see the result. If you are deciding between two banks, enter one bank's APY in B2, note the interest earned, then change B2 to the other bank's rate and compare. The same works for different time periods — change B3 from 2 years to 5 years to see how much longer you would earn.

You can also copy your setup into multiple columns to compare several scenarios side by side. For instance, put one bank's information in columns A and B, another bank's in columns D and E, and run the formula in both places. This makes it straightforward to see which account would earn you more money over the same period.

Handle daily compounding if your bank uses it

Some banks, especially online savings accounts, compound interest daily instead of monthly. If yours does, enter 365 in B4 instead of 12. The formula stays exactly the same — Excel will automatically account for the more frequent compounding. Daily compounding earns you slightly more than monthly, but the difference is usually small (often a few dollars per year on typical balances).

A few banks use 360 days instead of 365 for daily compounding. Check your account agreement or call your bank to confirm which one applies to you. The difference between 360 and 365 is tiny, but if you are comparing accounts precisely, it matters.

Frequently Asked Questions

What if my interest rate changes during the time period?

This formula assumes the interest rate stays the same for the entire period. If your bank changes the rate, you would need to break the calculation into sections — calculate the interest for the first period at the old rate, then use that ending balance as the starting balance for the next period at the new rate. You can do this by running the formula multiple times in separate rows.

Does the formula work for money I add to the account over time?

No, this formula only works if you deposit the money once and leave it alone. If you add money monthly or at other intervals, the calculation becomes more complex. You would need to either add each deposit separately to the formula or use a different approach, such as creating a month-by-month table that adds deposits and compounds interest each row.

Why is my calculated interest slightly different from what the bank shows?

Banks sometimes round interest differently than Excel does, or they may use 360 days instead of 365 for daily compounding. Small differences of a few cents are normal. If the difference is larger, double-check that you entered the correct APY and compounding frequency from your account agreement.

Can I use this formula for other types of accounts?

Yes. The same formula works for certificates of deposit (CDs), money market accounts, and any account where interest compounds at regular intervals. Just enter that account's APY and compounding frequency. It does not work for accounts with variable rates that change frequently or for investments like stocks.