Set up your spreadsheet with the account details

Start by opening a blank Excel sheet and labeling your columns. In the first row, create headers: Principal (the amount you start with), Annual Interest Rate, Time in Years, and Interest Earned. Put your numbers in the row below.

For example: if you have $5,000 in the account, the rate is 4.5% per year, and you want to see what you earn over 2 years, enter those values in their columns. Keep the interest rate as a decimal — so 4.5% becomes 0.045 in the cell.

If your savings account compounds interest monthly or daily instead of yearly, add another column labeled Compounding Frequency. Write "12" for monthly or "365" for daily. This matters because interest compounds more often means you earn slightly more.

Key Takeaways

  • Simple interest uses the formula =Principal * Rate * Time, and works for accounts that don't compound.
  • Compound interest uses the formula =Principal * (1 + Rate/Frequency)^(Frequency * Time), and accounts for interest earned on your interest.
  • Most savings accounts use daily or monthly compounding, so the compound formula is more accurate than simple interest.
  • You can build a table in Excel that shows your balance month by month or year by year to watch your money grow.

Calculate simple interest with a one-line formula

Simple interest is the easiest calculation and works if your bank pays interest once per year without compounding. In an empty cell, type the formula: =A2*B2*C2 (where A2 is your principal, B2 is your rate as a decimal, and C2 is the time in years).

If you have $5,000 at 4.5% for 2 years, the formula gives you $450 in interest. That's $225 per year, which is straightforward to verify: $5,000 times 0.045 equals $225, times 2 years equals $450.

Most savings accounts don't use simple interest anymore, but this formula is useful if you're comparing a promotional rate that's quoted as simple interest, or if you're working with a very old account statement.

Use the compound interest formula for real savings accounts

Compound interest is what most banks actually pay. The formula in Excel is: =A2*(1+B2/D2)^(D2*C2) (where A2 is principal, B2 is annual rate as a decimal, D2 is compounding frequency, and C2 is time in years).

Using the same example — $5,000 at 4.5% for 2 years, compounded daily (365 times per year) — the formula becomes =5000*(1+0.045/365)^(365*2). This gives you about $460.92 in interest, which is $10.92 more than simple interest. That extra money is interest earned on your interest.

If your bank compounds monthly instead, change the 365 to 12. The result will be slightly less than daily compounding but still more than simple interest. Check your account statement or the bank's website to confirm whether they compound daily, monthly, or quarterly.

Build a month-by-month growth table

To see how your balance changes over time, create a table with three columns: Month, Starting Balance, and Ending Balance. In the first row, enter 0 for the month and your principal amount as the starting balance.

In the Ending Balance cell for month 0, type the formula: =B2*(1+$B$2/365)^(1/12). This calculates one month of compound interest using your annual rate divided by 365 days and raised to the power of 1/12 (one month out of twelve). The dollar signs lock the rate cell so it doesn't change when you copy the formula down.

In the next row, set the Starting Balance equal to the previous month's Ending Balance. Copy the Ending Balance formula down for as many months as you want to track. You'll see your balance grow month by month, and the growth accelerates slightly because each month's interest is calculated on a larger amount.

Account for deposits or withdrawals during the period

If you're adding money to the account regularly, the month-by-month table is the best approach. Add a fourth column called Deposit, and in the Starting Balance formula for each row, add the deposit amount: =B2+C2+D2 (previous ending balance plus deposit).

For example, if you deposit $200 every month, enter 200 in the Deposit column for each row. The Starting Balance for month 2 becomes the Ending Balance from month 1 plus the new $200 deposit. Then the Ending Balance formula applies interest to that larger amount.

This method is more accurate than trying to use a single formula because deposits happen at specific times, and interest accrues on them from that point forward. A table lets you control exactly when each deposit enters the calculation.

Check your math against the bank's numbers

Once you've built your spreadsheet, compare your calculated interest to what your bank reports on your statement. Small differences (a few cents) are normal because banks may round differently or use slightly different day counts.

If your calculation is significantly different — more than a dollar or two on a $5,000 balance — check three things: the interest rate (make sure you're using the current rate, not an old one), the compounding frequency (daily, monthly, or quarterly), and the time period (some banks calculate interest from the first of the month, others from the day of deposit).

Your bank's disclosure statement, usually called the Truth in Savings document or the account terms, will specify exactly how they calculate interest. If you can't find it online, call the bank and ask for the compounding method and the exact annual percentage yield (APY) they're using.

Frequently Asked Questions

What's the difference between APR and APY in my Excel calculation?

APR is the annual percentage rate without compounding. APY is the annual percentage yield, which includes the effect of compounding. For Excel, use the APR (the base rate) in your compound interest formula, not the APY. The formula itself accounts for compounding and will produce the APY result.

Can I use Excel to compare two different savings accounts?

Yes. Build two identical tables side by side with different interest rates and compounding frequencies. After one year or five years, compare the ending balances. This shows you exactly how much more one account earns than the other, which is more useful than comparing rates alone.

Why does my Excel calculation show more interest than the bank statement?

The most common reason is using the wrong compounding frequency. If you used daily compounding but your bank compounds monthly, your number will be higher. Also check that you're using the current rate — promotional rates often expire and drop to a lower standard rate.

Should I use simple or compound interest for my spreadsheet?

Use compound interest. Nearly all savings accounts compound at least monthly, and most compound daily. Simple interest only applies to very specific products, and using it will underestimate what you actually earn.