Calculate Interest on Savings

Do it by Hand or Use Free Spreadsheet Templates

Adding Machine
Use spreadsheets to speed the process or do manual calculations. Katrina Charmatz / Getty Images

When you’re building up your assets, it’s helpful to calculate interest: you’ll be able to plan for important goals and understand your progress towards those goals. It’s relatively easy to calculate the interest you earn – especially if you use free spreadsheets or online calculators.

This page will cover how to calculate the following:

  • Simple interest
  • Single (one-time) investments
  • Compound interest
  • Ongoing (monthly, for example) investments

    How to Calculate Interest you Earn

    Interest is the “cost of money.” When you lend money or deposit money into an interest-bearing account, you’ll usually get your money back plus a little bit extra. That extra amount is interest, or your compensation for letting somebody else use your money.

    When you deposit funds into savings accounts or certificates of deposit (CDs) at a bank or credit union, you’re basically lending your money to the bank. The bank will take the funds and invest elsewhere, possibly lending that money to other customers.

    To calculate the interest from a savings account, you’ll need to know:

    • The amount of your deposit or the amount you lend, using the variable “P” for principal
    • When interest is calculated and paid (yearly, monthly, or daily, for example), using “n” for the number of times per year
    • The interest rate, using “i” and the rate in decimal format
    • How long you’ll earn interest, using “t” for the term (or time) in years

      Basic Example: assume you deposit $100 at the bank, you earn interest annually, and the account pays 5%. How much will you have after one year?

      For the most basic calculation, you can use the simple interest formula to solve for the interest amount (I). We’ll move on to compound interest below.

      The simple interest formula is:

      P x r x t = I

      1. $100 x 5% x 1 year = $5
      2. $100 x .05 x 1 year = $5

      This calculation would work if your interest rate is quoted as annual percentage yield (APY). Most banks advertise APY: the number looks better (it’s higher) than “the interest rate,” and it’s simpler because it takes compounding into effect. However, you might only know the interest “rate” – and not know the APY.

      In that case, if your bank calculates interest monthly and adds earnings to your account monthly (as many banks do), you’ll need to do a different calculation.

      Calculate Compound Interest

      Compounding happens when you earn interest, and then you earn even more interest on the interest earnings you previously received.

      To calculate compound interest on a savings account, your formula needs to take into account periodic interest payments into the account (if your bank pays interest monthly, for example) as well as an increasing account balance.

      With the same example above, we’ll use the formula for compound interest to calculate the ending amount (A):

      A = P (1 + r/n) ^ nt

      If it’s been a while since math class, the caret (^) is for exponentiation, which means a number is raised to the power of another. For example, “x^3” means x cubed (or x raised to the third power). If your browser shows the formatting correctly, this is another way to show it: A = P (1 + r/n)nt.

      1. A = $100 x (1 + 0.05/12) ^ (12 x 1)
      2. A = $100 x (1.004167) ^ (12)
      3. A = $100 x 1.051
      4. A = $105.117 (or, $105.12 if your bank rounds up)

      As you’ll notice, this answer is slightly higher than the $5 of interest earned using simple interest. 5% is the interest rate in our example, but the APY is actually 5.12%. Whenever interest is paid more frequently than annually, you’ll end up earning more than the stated rate (but the APY tells you exactly what you’ll earn without the need for more calculation).

      Of course, your earnings get more impressive as the dollar amounts increase and with longer holding times.

      Calculate with a Spreadsheet

      Spreadsheets can automate the process for you and allow you to make quick changes to your inputs. To calculate your interest earnings with a spreadsheet, us a future value calculation. Microsoft Excel and the free Google Sheets (among others) use the code “FV” for this calculation.

      An example in Google Sheets is already filled out for you. You can download that template and change the numbers for your own needs.

      To make your own spreadsheet, start by entering the following in any cell to figure your earnings using simple interest:


      That formula asks for the interest rate, number of periods, periodic payment (if any), future value (unless you’re starting with zero), and an option (not included here) for the beginning or end of period.

      If you want to get fancy, enter the rate, time, and principal in separate cells. Then you can refer to those cells from your formula and easily change them for “what-if” calculations.

      The formula above gives you simple interest (not compound) because there is only one compounding period.

      To use compound interest, you’ll need to do some conversions. Change the annual rate to a monthly rate: 5% divided by 12 months becomes 0.004167. You’ll also convert the number of periods to 12. To calculate for more than one year, you’d use 12 per year (for example, 4 years would be 48 periods – but it’s easiest to use the spreadsheet linked to above instead).

      Ongoing Savings

      The examples above assume you make a single deposit.

      Monthly investments: if you make regular deposits to your account at the end of each month, instead of a single lump-sum deposit, you’ll need to modify your calculation or your spreadsheet formula.

      If you were to deposit $100 per month for the next five years, starting from zero, you’d use:


      Note that you’ve kept the monthly interest rate, and you’ve adjusted the number of periods to 60 months.

      To calculate by hand, you’d use the future value of an annuity calculation:

      FV = Pmt x (((1 + r) ^ n) – 1)/r)

      1. FV = 100 x (((1 + 0.004167) ^ 60) – 1) / 0.004167)
      2. FV = 100 x (1.283 – 1) / 0.004167
      3. FV = 100 x 68.0067
      4. FV = 6800. 67

      Your final answer may vary slightly depending on rounding.

      Continue Reading...