What is Compound Interest?
In the world of finance, we often come across the term Compound interest. When you invest or acquire a loan, the compound interest is the interest accrued on both the principal as well as the interest accumulated over past periods. It differs from the simple interest in that the simple interest is accrued only on the principal value. Let us look at a simple example of how compound interest works in Google sheets. Suppose we have invested $10,000 into a company that compounds at 7% annually. Let us calculate the compound interest in Google Sheets using a formula to find the final value of this investment after five years. Enter the details in a spreadsheet.
Enter the following formula in cell B5.
=B1(1 + B2/B3)^(B4*B3)
It is based on the formula: Compound Interest = P(1+R/t)^(n*t). Here, P is the principal, R is the interest rate, t is the compounding period per year, and n is the number of years. Press Enter. You get the value the investment will be worth in 5 years, which is $14,026.
Table of contents
Key Takeaways
- Compound interest is the interest accrued on both the principal and the interest accumulated over past time periods for a loan or investment.
- The compound interest formula is:
Where:- P is the principal amount
- r is the annual interest rate
- n is the number of times interest is compounded per year.
- t is the time the money is invested for (years)
- Compound interest can be useful for building wealth by saving money in savings accounts with compounded interest, compounding in stocks, bonds, etc. can cause substantial growth in your money and so on. It is important to understand compound interest when taking a mortgage as well.
Syntax
To understand how Compound interest in Google Sheets works, let us look at the syntax.
Compound Interest = P(1+R/t)^(n*t)
Here,
- P is the principal amount.
- R is the interest rate.
- N is the number of years.
- t is the compounding period in a year. If the interest is compounded annually, t = 1; if it is semi-annual, t =2; and if monthly, t = 12.
To understand how this formula works, consider you are investing $1,000 in a business venture. You have an interest rate of 5% on this investment compounded annually.
In the first year, you get an interest of 5% on $1000 (=$50). So, at the end of the first year, you have $1050.
In the second year, you earn an interest of $1,050, the compounded amount. So, 5% of $1050 gives $52.5. Now, you have $1050 + $52.50 = $1102.50 at the end of the second year, and so on.
Thus, you derive the basic formula to compute the compound interest in Google Sheets.
How to calculate compound interest in Google Sheets?
As seen above, we can use the compound interest formula to find the ending value of a loan or an investment after a certain period of time. Let us look at how the formula can be entered annually in Google Sheets to calculate the final value and, subsequently, the compound interest. In a simple example, let us calculate the compound interest on a loan of $2,000 over five years at a 7.5% interest rate compounded annually.
Step 1: Enter the required data to calculate the Google Sheets compound interest as done below.
Step 2: Apply the compound interest formula mentioned above in the syntax. So, the formula to be entered in cell B5 is based on: = P(1+R/t)^N*t. Using the cell references we enter,
=B1*(1 + B2/B3)^B4*B3
Step 3: Press Enter. You will get the final amount as shown below.
Step 4: To calculate the compound interest, you can subtract the final amount from the initial principal.
Examples
Compound Interest is an important concept in finance. Hence, in the following three examples, we will show you how to compute it using Google Sheets and how to find the compound interest annually, monthly, and daily.
Example #1 – Annual Compounding
Let us now apply the above formula to compute the Compound interest in Google Sheets when compounding annually. It can be done in two ways.
- Using the compound interest formula
- Using the FV Function
We are investing an initial amount of $5,000 in a venture at an annual interest rate of 6%. It is compounding annually, and we can now calculate the ending amount at the end of every year for up to 5 years.
Let us enter the details in a Google Sheet.
Step 1: We have to calculate the ending value at the end of the first year. Let us use the compound interest formula in cell D2.
=$B$2*(1 + $B$3/$B$4)^($B$4*C2).
Here, the cell reference is given for these cell values as they remain fixed for all five years.
Step 2: Press Enter; you can see the final amount at the end of one year.
Step 3: Now, drag the compound interest Google Sheets formula fill handle from D2 and D6.
Thus, you can get the ending value at the end of each year up to 5 years.
Step 4: To verify this formula, let us use the FV formula. Future value is used to compute the future value of an investment at a fixed interest rate. The formula for FV is:
=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
- rate is the annual interest rate.
- number_of_periods defines the number of payments to be made.
- payment_amount refers to the amount per period to be paid; it is zero if there is no amount
- present_value represents the current value of the annuity.
- end_or_beginning indicates whether payments are due at the end (0) or at the beginning (1) of the period
Apply the following formula in cell B6 and hence, find the final value at the end of five years.
=FV(B3,C6,0, -B2, 0)
Step 5: Press Enter. You can see we have computed the value accurately.
Example #2 – Monthly Compounding
So far, all the examples we came across had yearly compounding. Let us look at how to calculate the ending value if the compounding in monthly. Suppose we have a loan of $5,000 that compounds at 7.5% annually and is compounded on a monthly basis, which is 12 times a year.
How to Calculate Compound Interest Compounded Monthly
Enter these parameters to calculate the compound interest in Google sheets.
Step 1: Here, the only change is the number of periods in a year. Since interest is compounded monthly, it means the number of periods in a year, t is 12. Now, let us apply the compound interest formula in cell B6.
=B2(1 + B3/B4)^(B4*B5)
Step 2: Press Enter. We get the total amount when we compound a 7.5% interest on a monthly basis at the end of ten years.
Example #3 – Daily Compounding
To calculate the compound interest with daily compounding in Google Sheets, let us follow these steps.
Let us consider the same values as earlier. Consider that we acquire a loan of $5,000 that has an interest rate of 7.5% annually and is compounded daily. Here, t =365. The number of years is 10.
The following image shows how the data has been entered in a Google Sheet.
Step 1: Now, enter the formula for the compound interest calculation in cell B5.
=B1(1 + B2/B3)^(B3*B4)
Step 2: Press Enter. You get the final value after 10 years at an interest rate of 7.5% on a principal of $5000.
Important Things To Note
- If you observe the examples above, the daily compounding interest has a higher future value of the same investment than monthly compounding because the interest rate adds interest to the principal amount every day rather than each month.
- Format cells with percentages and currency as required for better visualization.
- Ensure consistency in the units of time you use. The time (t) should also be in years for an annual interest rate.
Frequently Asked Questions (FAQs)
We use the following formula to calculate the compound interest in Google Sheets.
Compound Interest = P(1+R/t)^(n*t)
Here, P is the principal, R is the interest rate, t is the compounding period per year, and n is the number of years.
For different compounding periods, we must adjust the value of t.
Daily: t = 365
Monthly: t = 12
Quarterly: t = 4
Weekly: t = 52
Yearly: t = 1
The simple interest is calculated on the principal amount of a loan. It can be calculated by multiplying the principal by the interest rate and term of a loan. Compared to compound interest, it grows at a steady rate, and the returns are lower.
Simple Interest = (Principal x Rate x Time)
We calculate the compound interest on the principal amount and the accumulated interest of the previous periods.
Compound Interest: Compound Interest = P(1+R/t)^(n*t)
It grows at an exponential rate due to compounding.
To calculate compound interest in Google Sheets, you can use the FV function. The function takes five arguments: the interest rate, the number of payments, the amount per period, the current value of the annuity, and whether payments are due at the beginning (1) or end (0) of each period. Using these, you get the final values at the end of the loan or investment term, thereby helping you find the compound interest.
Download Template
This article must help understand the Compound Interest In Google Sheets formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Compound Interest In Google Sheets. We learn syntax, how to calculate compound interest, with examples, and points to note. You can learn more from the following articles.
Leave a Reply