Formulas and Examples to Calculate Interest on Savings

Free Spreadsheet Templates and Instructions to DIY

 © The Balance, 2018

As you build up your savings, it’s helpful to learn how to calculate interest. Doing so allows you 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

Just want an answer? Use this spreadsheet to calculate interest (you need to make a copy for your own use).

How to Calculate Interest You Earn

Interest is the cost of money. When you lend money or deposit funds into an interest-bearing account, you typically 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 make deposits into savings accounts or certificates of deposit (CDs) at a bank or credit union, you’re 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 the following pieces of information:

  • 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 for, using “t” for the term (or time) in years.

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

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

Using the simple interest formula:

  1. P x r x t = I
  2. $100 x 5 percent x 1 year = $5
  3. $100 x .05 x 1 year = $5

This calculation works when your interest rate is quoted as annual percentage yield (APY). Most banks advertise APY: The number looks better than “the interest rate” because it’s a higher number, 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, you’ll need to do a different calculation. If your bank calculates interest monthly and adds earnings to your account monthly, as many banks do, a simple interest calculation is not accurate.

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 two things into account:

  1. More frequent periodic interest payments into the account, instead of one annual payment. For example, your bank might pay interest monthly.
  2. An increasing account balance that subsequent interest calculations are based on

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

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

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.

As you’ll notice, the compound interest calculation is slightly higher than the $5 of interest earned using simple interest. 5 percent is the interest rate in our example, but the APY is actually 5.12 percent. Whenever interest is paid more frequently than annually, the APY is higher than the stated annual interest “rate.” But APY tells you exactly how much you’ll earn, without the need for additional calculation.

An extra 12 cents might not seem like much. The earnings get more impressive with higher dollar amounts and 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 Google Sheets (among others) use the code “FV” for this formula.

The spreadsheet link at the top of this article 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:

=FV(0.05,1,0,100)

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 shown here) for the beginning or end of period.

The expression above uses the simple interest example from earlier. It shows simple interest (not compound interest) because there is only one compounding period.

For a more advanced spreadsheet, 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.

To use compound interest, you need to adjust several numbers. Change the annual rate to a monthly rate: 5 percent divided by 12 months becomes 0.004167. Also, convert the number of periods to 12. To calculate for more than one year, you’d use 12 per year. For example, four 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:

=FV(0.004167,60,100)

Note that you use a monthly interest rate, and you adjust the number of periods to 60 months.

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

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

Your final answer may vary slightly due to rounding.