Compound Interest Formula in Excel and Google Sheets
This tutorial will show several ways to calculate compound interest in Excel and Google Sheets.
What is Compound Interest?
When you invest money, you can earn interest on your investment. Let’s say, for example, that he invests $3,000 with an annual interest rate of 10%, composed annually. One year after the initial investment (called Current Amount), you earn $300 ($3,000 x 0.10) in interest, so your investment is worth $3,300 ($3,000 + $300). In the following period, you earn interest based on the gross figure of the previous period: $ 330 ($ 3,300 x 0.10) because your investment was worth $3,300. Now, it’s worth $3,630.
The general compound interest formula is VF = Va(1+r) n, where VF is the future value, Va is the current value, r is the interest rate per period and n is the number of capitalization periods.
How to Calculate Compound Interest in Excel
One of the simplest ways is to apply the formula (gross figure) x (1 + interest rate per period). If you are investing $1,000 with an interest rate of 15%, composed annually, below is how you would calculate the value of your investment after a year.
=B2*(1+$A2)
In this case, B2 is the capital and A2 is the interest rate per period. The “$” is used in the formula to set the reference to column A, since the interest rate is constant in this example.
Copy and paste the formula into cell D2 to compute your investment value after two years.
C2 is the current gross figure. Notice how B2 automatically changes to C2, since the cell reference has changed. You could copy the formula into additional columns to calculate the compound value in future years. We recommend structuring a table like this (vertically instead of horizontally):
Table of Total Mobile Compound Interest
Calculating compound interest is easy, but determining the worth of your investment after 10 years is inefficient. Instead, you can use a generalized formula of compound interest.
General Formula of Compound Interest (for Daily, Weekly, Monthly and Annual Capitalization)
A more efficient way to calculate compound interest in Excel is to apply the general interest formula: VF = Va(1+r)n, where VF is the future value, Va is the current value, r is the interest rate per period and n is the number of capitalization periods.
For instance, you invest $5,000 at 10% per year, compounded every six months, and want to compute its value after five years. The following spreadsheet shows how this calculation can be done in Excel.
=B1*(1+B2/2)^(B3*B4)
In this case, Va is the Current Amount, r is (Annual Interest Rate) / 2 because the interest is composed every six months (twice a year), n is (Capitalization Periods per Year), and VF is the Investment Value. This process is more efficient than the first because you do not have to calculate the gross figure after each period, saving a lot of calculation steps.
VF function and compound interest
Finally, the compound interest can be calculated with the Future Value Function incorporated in Excel. The VF function determines the future value of an investment using variables, like the prior method.
The variables (as shown above) are:
The rate is the interest rate for each period.
– núm_per is the number of capitalization periods.
Payment is the additional payment per period, and is represented as a negative number. If there is no value for “payment”, set a value of zero.
– va (optional) is the initial investment, which is also represented as a negative number. If there is no value for “go”, it must include a value for “payment“
– type (optional) indicates when the additional payments occur. “0” represents payments at the start of the period, while “1” shows payments at the end.
The previous example of semi-annual compound interest can be completed with the VF function
=VF(B2/B3;B3*B4;0;-B1)
The “rate” is (Annual Interest Rate) / (Meritization Period per year), “num_per” is (Meritization Periods per year) x (Years), “payment” is 0, and “go” is – (Current Amount).
Understanding how to calculate compound interest is important to predict the return on investments, whether for retirement planning or for your personal portfolio, and Excel is an excellent tool to do so.
For more information on compound interest, visit Investopedia
Calculate Compound Interest in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.
