Set up your spreadsheet with the account details

Open a blank Excel sheet and create column headers for the information you need: Principal (the starting balance), Annual Interest Rate, Time Period (in years or months), and Calculated Interest. Put your actual numbers in the row below each header.

For example, if you have $5,000 in a savings account earning 4.5% annual interest, you would enter 5000 in the Principal column and 0.045 in the Annual Interest Rate column (convert percentages to decimals by dividing by 100). If you want to know the interest after one year, enter 1 in the Time Period column.

Most savings accounts use simple interest or compound interest. Simple interest is straightforward: you earn interest only on your original deposit. Compound interest is more common at banks and means you earn interest on your interest as well, which grows your balance faster. Check your account statement or bank website to see which method your account uses.

Key Takeaways

  • Simple interest uses the formula =Principal × Rate × Time, and Excel can calculate this in one cell using cell references instead of typing numbers.
  • Compound interest requires the formula =Principal × (1 + Rate/Compounds per year)^(Compounds per year × Time), which grows your money faster than simple interest.
  • Most savings accounts compound interest daily or monthly, so check your bank's disclosure to know which number to use in the formula.
  • You can build a table showing your balance month by month or year by year by copying the compound interest formula down and adjusting the time value in each row.

Calculate simple interest with a single formula

In the cell next to your data, type the formula for simple interest: =A2*B2*C2 (replace A2, B2, and C2 with the cell references where you put your principal, rate, and time). Press Enter. Excel will multiply those three numbers and show you the total interest earned.

For the $5,000 example at 4.5% for one year, the formula would calculate $225 in interest. If you want to see the total balance after interest, create another cell with the formula =A2+D2 (principal plus interest).

Simple interest is rare in savings accounts today, but it is useful to understand because it is the easiest calculation. Most banks use compound interest instead, which means your money grows faster.

Calculate compound interest with the correct formula

Compound interest uses a different formula because interest gets added to your balance, and then you earn interest on that new balance. In Excel, type: =A2*(1+B2/C2)^(C2*D2), where A2 is your principal, B2 is your annual rate, C2 is how many times per year interest compounds, and D2 is the number of years.

The tricky part is knowing how often your bank compounds interest. Most savings accounts compound daily (365 times per year) or monthly (12 times per year). A few compound quarterly (4 times per year). Your bank's rate disclosure document or website will tell you. If it says "APY" (Annual Percentage Yield), that number already includes compounding, so you can use it directly in a simpler formula: =A2*(1+B2)^D2.

Using the same $5,000 at 4.5% APY for one year, the formula =5000*(1+0.045)^1 gives you $5,225 total, or $225 in interest. With daily compounding at a nominal 4.5% rate, the result is slightly higher because interest compounds 365 times instead of once.

Build a month-by-month or year-by-year growth table

To see how your balance grows over time, create a table with two columns: one for the time period (Month 1, Month 2, etc., or Year 1, Year 2, etc.) and one for the balance. In the first balance cell, enter your starting principal. In the second cell, use the compound interest formula but change the time value to represent just that one period.

For monthly tracking, the formula in the second row would be =A2*(1+B2/12)^1 if you are compounding monthly. Copy this formula down and change only the exponent (the ^1 part) to ^2, ^3, ^4, and so on for each subsequent month. Alternatively, reference the previous month's balance and multiply by (1 + monthly rate): =E2*(1+B2/12), where E2 is the previous balance. This second method is faster because you only type the formula once and copy it down.

This table shows you exactly when your money will reach certain milestones and helps you compare different interest rates or deposit amounts side by side.

Compare different scenarios by changing one number at a time

Once you have a working formula, you can test what happens if you deposit more money, leave it longer, or move it to an account with a higher rate. Change only the principal, rate, or time value and watch the interest recalculate automatically. This is much faster than doing the math by hand for each scenario.

Create separate columns for different rates side by side so you can see the difference between a 3% account and a 5% account at a glance. Or create separate tables for different deposit amounts. This helps you decide whether it is worth moving your money to a higher-rate account or whether the difference is small enough that staying put makes sense.

Understand what your bank's rate disclosure actually tells you

Banks publish two different numbers: the APR (Annual Percentage Rate) and the APY (Annual Percentage Yield). APR is the simple interest rate without compounding. APY includes the effect of compounding and is always higher. When you see a savings account advertised at "4.5%", that is usually the APY, which is the number you should use in your Excel formula if you want to know your real earnings.

If your bank gives you only the APR and tells you how often interest compounds, use the compound interest formula with the APR. If your bank gives you the APY, use the simpler formula =Principal*(1+APY)^Years. The APY already accounts for compounding, so you do not need to divide by the number of compounds per year.

Frequently Asked Questions

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

APR is the stated rate without compounding. APY is the actual rate you earn after compounding is factored in. Use APY in your formula if your bank provides it, because it shows your real return. If you only have APR, you must use the full compound interest formula and divide by the number of times interest compounds per year.

Can I use Excel to track interest from multiple savings accounts at once?

Yes. Create a separate row for each account with its own principal, rate, and compounding frequency. Use the same formula structure for each row, just change the cell references. You can then add a total row at the bottom to see your combined interest across all accounts.

Why does my Excel calculation show a different number than my bank statement?

Banks sometimes round interest or apply it on specific dates rather than continuously. Your Excel formula assumes perfect compounding on the schedule stated. Small differences are normal. Large differences usually mean your bank compounds more or less often than you entered, or the rate changed during the period.

Do I need to update my formula if my interest rate changes?

Yes. When your bank changes your rate, update the Annual Interest Rate cell with the new number. If the rate changed partway through the year, you may need two separate calculations: one for the old rate over the number of days it applied, and one for the new rate over the remaining days.

Can Excel show me how much interest I earn each month without recalculating the whole balance?

Yes. Create a column for monthly interest earned by subtracting the previous month's balance from the current month's balance. Or use the formula =Previous Balance × (Monthly Rate) to show just that month's interest without the principal. This helps you see whether your interest is growing as your balance grows.