The basic formula for savings account interest

Excel can calculate what your savings account will earn in interest using a single formula. The most common approach uses the compound interest formula, which accounts for interest earned on your interest — the way most savings accounts actually work.

The formula is: =P*(1+r/n)^(n*t)

Here, P is your starting balance, r is the annual interest rate (as a decimal), n is how many times per year interest compounds, and t is the number of years. For a savings account that compounds daily, n would be 365. For monthly compounding, n would be 12.

If you start with $5,000, earn 4.5% annual interest compounded daily, and leave it untouched for 2 years, the formula in Excel would look like this: =5000*(1+0.045/365)^(365*2). That gives you $5,472.79 — the amount you'd have at the end.

Key Takeaways

  • The compound interest formula =P*(1+r/n)^(n*t) calculates your final balance, not just the interest earned, so subtract your starting balance to see only the interest.
  • Your interest rate must be entered as a decimal (4.5% becomes 0.045) and n depends on how often your bank compounds: daily is 365, monthly is 12, quarterly is 4.
  • You can build a spreadsheet that updates automatically when you change the rate, starting balance, or time period, so you can test different scenarios without retyping the formula.
  • Most savings accounts compound daily, but some compound monthly or quarterly — check your account documents to know which n value to use.

Setting up your spreadsheet with labeled cells

Rather than typing numbers directly into the formula, create a spreadsheet where each piece of the calculation sits in its own cell. This makes it easier to change one number and see the result update when ready.

In column A, write labels: "Starting Balance", "Annual Interest Rate", "Compounds Per Year", "Years", and "Final Balance". In column B, enter the actual numbers: 5000, 0.045, 365, 2. Leave the cell for "Final Balance" empty — that's where your formula will go.

In cell B5 (next to "Final Balance"), type: =B1*(1+B2/B3)^(B3*B4). Excel will read the values from the cells above and calculate the result. Now if you change the starting balance in B1 to $10,000, the final balance updates automatically.

Calculating interest earned, not just the final balance

The formula above shows what you'll have at the end. To see only the interest you earned, subtract your starting balance from the final balance.

Add another row to your spreadsheet labeled "Interest Earned". In the cell next to it, type: =B5-B1 (assuming B5 holds your final balance and B1 holds your starting balance). This shows you that on $5,000 at 4.5% for 2 years, you earn $472.79 in interest.

This separation is useful because it shows you exactly what the bank paid you, separate from your own money. Many people find this clearer than looking at a final balance alone.

Handling different compounding schedules

Not all savings accounts compound at the same rate. Your account documents will state whether interest compounds daily, monthly, quarterly, or annually. The n value in the formula changes based on this.

For daily compounding, n = 365. For monthly, n = 12. For quarterly, n = 4. For annual, n = 1. Some banks use 360 days instead of 365 for daily compounding — check your disclosure documents to be certain. If you're unsure, call the bank or log into your account online; most banks show the compounding frequency in the account details.

You can make this flexible in your spreadsheet by creating a cell that holds the compounding frequency, then reference that cell in your formula. This way you can test what happens if you move your money to an account with different compounding without rewriting the formula.

Using Excel's built-in financial functions

Excel also has a FV function (Future Value) that does the same calculation in a different format. The syntax is: =FV(rate, nper, pmt, pv).

Here, rate is the interest rate per compounding period (annual rate divided by the number of periods per year), nper is the total number of compounding periods, pmt is any regular deposits (use 0 if you're not adding money), and pv is your starting balance (entered as a negative number).

For the same example — $5,000 at 4.5% compounded daily for 2 years — the formula would be: =FV(0.045/365, 365*2, 0, -5000). This also gives you $5,472.79. Some people find the FV function easier to read once they understand what each piece means; others prefer the compound interest formula because it's more transparent. Both are correct.

Testing different scenarios in one spreadsheet

Once you have your formula set up, you can quickly see how changes affect your interest. Try different starting balances, different rates, or different time periods by changing the numbers in your cells.

For example, change your starting balance from $5,000 to $10,000 and watch the interest earned jump from $472.79 to $945.58. Or lower the rate to 3.5% and see how much less you earn. This is useful for deciding whether to move money to a higher-rate account or for planning how much to save to reach a goal.

You can also create a second set of cells to compare two accounts side by side. Set up the same formula twice — once for Account A and once for Account B — and see which one pays more over the same time period. This takes the guesswork out of comparing savings account offers.

Common mistakes when setting up the formula

The most frequent error is forgetting to convert the interest rate to a decimal. If your account pays 4.5%, you must enter 0.045 in the formula, not 4.5. Entering 4.5 would calculate as if you're earning 450% interest, which is obviously wrong.

Another common mistake is using the wrong n value. If your account compounds monthly but you use 365 in the formula, your calculation will be off. Check your account documents or call the bank to confirm. Some people also forget that n is the number of times per year, not the total number of times over the entire period — the formula multiplies n by t to get the total compounding periods.

A third mistake is entering the starting balance as a positive number in the FV function when it should be negative. The FV function treats money you put in as negative (money out of your pocket) and the result as positive (money you receive back). If you enter the starting balance as positive, you'll get the wrong answer.

Frequently Asked Questions

What if my bank compounds interest more than once a day?

Most banks compound daily, which means once per day. Some older accounts or certain institutions may compound less frequently — monthly or quarterly. A very small number of banks used to compound continuously, but this is rare now. Check your account documents or ask your bank directly. If they say "daily", use 365 for n.

Do I need to account for taxes on the interest I earn?

The formula calculates the gross interest before taxes. Interest on savings accounts is taxable income, so you'll owe taxes on what you earn. Your bank will send you a 1099-INT form at tax time showing the interest. Excel can't calculate your tax liability because that depends on your tax bracket, but you can subtract an estimated tax amount from the interest earned if you want to see your after-tax return.

What if I make regular deposits to the account?

The basic formula assumes you deposit money once and leave it alone. If you add money regularly — say $100 per month — the calculation becomes more complex. The FV function can handle this: use the pmt parameter to enter your regular deposit amount. For example, =FV(0.045/365, 365*2, -100, -5000) calculates interest on $5,000 with $100 added each month for 2 years.

Why does my calculated interest not match what the bank shows?

Small differences usually come from rounding or the exact number of days the bank uses (some use 360 instead of 365). Larger differences might mean the rate changed during the period, or the bank compounds on a different schedule than you assumed. Log into your account online and check the transaction history — most banks show each interest deposit, so you can add them up and compare to your formula.

Can I use this formula for other types of accounts?

Yes. The compound interest formula works for any account that earns interest — money market accounts, certificates of deposit (CDs), and even some checking accounts. The only difference is the rate and compounding frequency, which you'll find in the account documents. The formula itself stays the same.