Formulas and Examples to Calculate Interest on Savings

Free Spreadsheet Templates, and Instructions to DIY

 © The Balance, 2018

As you grow your savings, it’s helpful to learn how to calculate interest. Doing so allows you to plan for the future and understand your progress toward your goals. It’s easy to calculate the interest you earn, especially when you use free spreadsheets or online calculators.

This page shows how to calculate the following:

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

Just want an answer? Use this calculator example in Google Sheets 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 takes the funds and invests, possibly lending that money to other customers.

Get organized: To calculate the interest from a savings account, gather the following pieces of information:

  • The amount of your deposit or the amount you lend, using the variable “P” for principal.
  • How frequently to calculate and pay interest (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 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%. How much will you have after one year?

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

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

The calculation above works when your interest rate is quoted as annual percentage yield (APY), and when you’re calculating interest for a single year. Most banks advertise APY: That number looks better than “the interest rate” because it’s a higher number, and it’s easy to work with because it takes compounding into effect. However, you might only know the interest “rate”—and not know the APY.

If you only know your rate, or if you want more detailed numbers, you need to use a different calculation or a spreadsheet. When banks calculate interest monthly and add earnings to your account each month, as many banks do, that calculation may not be accurate.

Calculate Compound Interest

Compounding happens when you earn interest, and then the money you earned generates additional interest.

With compound interest, you earn 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 depend on

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

Compound interest formula:

  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 your last 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 see, compound interest results in slightly more 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 banks pay interest more frequently than annually, the APY is higher than the stated annual interest “rate.” But APY tells you exactly how much you’ll earn over a year, without the need for complicated calculations.

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, use 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 with the 5% example. You can download that template and change the numbers for your own needs.

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

Future value example:

=FV(0.05,1,0,100)

That formula asks for the following items, separated by commas:

  1. Interest rate
  2. Number of periods
  3. Periodic payment (if any)
  4. Future value (unless you’re starting with zero)
  5. Optional value for the beginning or end of period (not shown here)

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% divided by 12 months becomes 0.004167. Next, 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.

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 need to modify your calculation or your spreadsheet formula.

If you deposit $100 per month for the next five years, starting from zero, you could use:

Interest on a series of investments:

=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:

Example of a series of investments:

  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 result may vary slightly due to rounding.