Don’t forget to share this post

Compound interest is an important financial concept to understand. Unlike simple interest, which is applied to the principal sum only, compound interest is applied periodically to the principal sum plus the interest already accrued. In other words, both the principal sum and the interest accrued grow with each period.

Whether you’ve taken a loan or made an investment, if compound interest is applied, you need to know how to calculate it. Fortunately, you can do this easily and quickly in Google Sheets using the compound interest formula; additionally, the CAGR formula in Google Sheets can be used to calculate the compound annual growth rate.

In this article, you will learn how to set up a simple template to calculate the future value of an investment where interest is compounded. By changing the value of one cell, you can change the compounding frequency - yearly, monthly, daily, or whichever periodicity you want to use.

What is Compound Interest?

With compound interest, the accumulated interest is added to the principal sum. This means that the interest will be calculated on a larger principal sum in the next period. Unlike simple interest, which is always calculated based on the initial amount, compound interest is periodically compounded, so the compounding frequency can have a significant impact on your finances.

Imagine you’ve invested $1000 with simple interest of 10% per year. By the end of the first year, you’ll have $1100. By the end of the second year, you’ll have $1200. What would happen if you made the same investment, but with annually compounded interest? At the end of the first year, you’d still have $1100, but at the end of the second year, you’d have $1210.

Essentially, with compound interest, you’re reinvesting your profits and earning further interest on that larger sum. The frequency with which interest is compounded can vary greatly, from yearly to daily. The more frequently interest is compounded, the greater the final amount will be. In the next section, you will learn how to set up your own compound interest calculator in Google Sheets.

How to Calculate Compound Interest in Google Sheets?

The following formula can be used to calculate the final amount earned on investment with compounding interest:

F = P*(1+r/n)^(n*y)
  • F = final amount
  • P = principal sum (the amount originally invested)
  • r = annual interest rate
  • n = number of compounding periods per year
  • y = number of years

Google Sheets FORECAST Function (+ Examples)

The Google Sheets FORECAST function predicts future values based on your data. Here’s how to use the FORECAST function step-by-step, with examples.

READ MORE
Google Sheets FORECAST Function Examples

You can easily set up a simple template in Google Sheets with the relevant variables. In the following sections, I will use the same values for all variables except the number of compounding periods per year. This way, you’ll see how the frequency of compounding - yearly, monthly, daily - affects the outcome, even when all other variables have the same values.

  1. 1. Set up a table like the one shown below, with the variable names clearly indicated to avoid confusion when you change the input.
How to Calculate Compound Interest in Google Sheets Table with Variables
How to Calculate Compound Interest in Google Sheets - Table with Variables
  1. 2. Click on the cell for the value of the principal sum, then format it as currency. Below, cell B1.
How to Calculate Compound Interest in Google Sheets Principal Sum Currency Format
How to Calculate Compound Interest in Google Sheets - Principal Sum Currency Format
  1. 3. Click on the cell for the value of the annual interest rate, then format it as a percentage. In my example, B2.
How to Calculate Compound Interest in Google Sheets Annual Interest Rate Percentage Format
How to Calculate Compound Interest in Google Sheets - Annual Interest Rate Percentage Format
  1. 4. Click on the cell where you want to have the “Final Amount” value and type in the formula, as shown below.
How to Calculate Compound Interest in Google Sheets Add Formula
How to Calculate Compound Interest in Google Sheets - Add Formula
  1. 5. When you click enter, you will see a “#DIV/0!” error, as the relevant cells are currently empty. It will disappear as soon as you start filling in values for the variables.
How to Calculate Compound Interest in Google Sheets Ignore DIV0 error
How to Calculate Compound Interest in Google Sheets - Ignore #DIV/0! error

How to Calculate Interest Compounded Annually?

Follow the steps below to calculate the final amount for an investment where interest is compounded annually.

  1. 1. In the template, fill in the value for the “Principal Sum”. In my example, “$10 000” in cell “B1”, is formatted as a currency.
How to Calculate Compound Interest in Google Sheets Principal Sum Value
How to Calculate Compound Interest in Google Sheets - Principal Sum Value
  1. 2. Fill in the value for the “Annual Interest Rate”. Below, “8%” in cell “B2”, which is formatted as a percentage.
How to Calculate Compound Interest in Google Sheets Annual Interest Rate Value
How to Calculate Compound Interest in Google Sheets - Annual Interest Rate Value
  1. 3. Since I want to use annual or yearly compounding, there will only be “1” compounding period per year. Below, cell “B3”.
How to Calculate Compound Interest in Google Sheets Number of Compounding Periods Value
How to Calculate Compound Interest in Google Sheets - Number of Compounding Periods Value
  1. 4. Fill in the value for “Nº Years”. In my example, “10” in cell “B4”.
How to Calculate Compound Interest in Google Sheets Number of Years Value
How to Calculate Compound Interest in Google Sheets - Number of Years Value
  1. 5. That’s it. The final amount for this example is “$21,589.25”, in cell “B5”.
How to Calculate Compound Interest in Google Sheets Final Amount Value
How to Calculate Compound Interest in Google Sheets - Final Amount Value
How to Use GOOGLEFINANCE Function in Google Sheets
How to Use GOOGLEFINANCE Function in Google Sheets?

Import current or historical financial market data from Google Finance & monitor real-time. Here's how to use the GOOGLEFINANCE function in Google Sheets

READ MORE

How to Calculate Interest Compounded Monthly?

Let’s see how the “Final Amount” for the same investment varies when I use monthly compounding, instead of yearly.

  1. 1. Click on the cell with the “Nº Compounding Periods per Year” value. Below, “B3”. For monthly compounding, the value should be “12”.
How to Calculate Compound Interest in Google Sheets Monthly Compounding
How to Calculate Compound Interest in Google Sheets - Monthly Compounding
  1. 2. That’s it. You have the final amount for your investment with monthly compounding. In my example, “$” in cell “B5”.
How to Calculate Compound Interest in Google Sheets Monthly Compounding Final Amount
How to Calculate Compound Interest in Google Sheets - Monthly Compounding Final Amount

How to Calculate Interest Compounded Daily?

Finally, let’s calculate the final amount for the same investment when interest is compounded daily.

  1. 1. Click on the cell with the “Nº Compounding Periods per Year” value. Below, “B3”. For daily compounding, the value should be “365”.
How to Calculate Compound Interest in Google Sheets Daily Compounding
How to Calculate Compound Interest in Google Sheets - Daily Compounding
  1. 2. That’s it. You have the final amount for your investment with monthly compounding. In my example, “$25,181.70” in cell “B5”.
How to Calculate Compound Interest in Google Sheets Daily Compounding Final Amount
How to Calculate Compound Interest in Google Sheets - Daily Compounding Final Amount

How to automate your data on top of Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

Conclusion

As you have seen, there’s a big difference between simple and compound interest. Given the same initial investment of $10 000 and an annual interest rate of 8% compounded over 10 years, the final amount can vary significantly depending on the compounding frequency.

You now know how to set up and use a compound interest calculator in Google Sheets. By changing the value for a single cell, you can calculate the final amount using different compounding frequencies - yearly, monthly, or daily.

If you want to learn more about financial formulas, check out these related articles:

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jul 21 2022, Updated Nov 11 2022