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

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.

1. 2. Click on the cell for the value of the principal sum, then format it as currency. Below, cell B1.

1. 3. Click on the cell for the value of the annual interest rate, then format it as a percentage. In my example, B2.

1. 4. Click on the cell where you want to have the “Final Amount” value and type in the formula, as shown below.

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 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.

1. 2. Fill in the value for the “Annual Interest Rate”. Below, “8%” in cell “B2”, which is formatted as a percentage.

1. 3. Since I want to use annual or yearly compounding, there will only be “1” compounding period per year. Below, cell “B3”.

1. 4. Fill in the value for “Nº Years”. In my example, “10” in cell “B4”.

1. 5. That’s it. The final amount for this example is “$21,589.25”, in cell “B5”.

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”.

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 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”.

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”.

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:

• Google Sheets FORECAST Function (+ Examples)
• Excel GROWTH Function: How To Use and Examples