How To Calculate Compound Interest in Excel
Compound Interest Formula
If you want to calculate the future value of an investment, earning a constant rate of interest, this is done using the following compound interest formula:
where,
- P is the initial amount invested;
- r is the annual interest rate (as a decimal);
- n is the number of periods over which the investment is made.
Compound Interest Formula in Excel
In Excel, you can calculate the future value of an investment, earning a constant rate of interest, using the formula:
=P*(1+r)^n
where,
- P is the initial amount invested;
- r is the annual interest rate (as a decimal or a percentage);
- n is the number of periods over which the investment is made.
Compound Interest Formula in Excel:
| A | B | |
|---|---|---|
| 1 | Original Investment: | 100 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Future Value: | =100*(1+4%)^5 |
For example, if you invest $100 for 5 years at an with interest paid annually at rate of 4%, the future value of this investment can be calculated by typing the following formula into any Excel cell:
=100*(1+4%)^5
which gives the result 121.6652902.
I.e. the future value of the investment (rounded to 2 decimal places) is $121.67.
Compound Interest Formula Using Excel References
Compound Interest Formula Using Excel References:
| A | B | |
|---|---|---|
| 1 | Original Investment: | 100 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Future Value: | =B1*(1+B2)^B3 |
As with all Excel formulas, instead of typing the numbers directly into your compound interest formula, you can use references to cells containing numbers.
The Excel compound interest formula in cell B4 of the above spreadsheet on the right uses references to the values stored in cells B1, B2 and B3 to perform the same compound interest calculation.
I.e. the formula uses cell references to calculate the future value of $100, invested for 5 years with interest paid annually at rate of 4%. Again, this returns the result 121.6652902.
Calculate Compound Interest Over Multiple Years
The same Excel compound interest formula can be used to show the value of an investment as it grows over a number of years.
The following spreadsheet shows the value of $100, invested at an annual interest rate of 4%, after 1, 2, 3, 4 and 5 years:
|
Formulas:
|
||||||||||||||||||||||||||||||||||||||||||
|
Results:
|
||||||||||||||||||||||||||||||||||||||||||
Note the $ signs in the above formulas are simply to prevent these references adjusting as the formula in cell E2 is copied down to cells E3-E6. To learn more about this, see the page on Absolute and Relative Cell References.
How To Calculate Compound Interest in Excel When Interest is Paid Monthly
If the interest on your investment is paid monthly (while being quoted as an annual interest rate), the Excel compound interest formula becomes:
=P*(1+r/12)^(n*12)
where,
- P is the initial amount invested;
- r is the annual interest rate (as a decimal or a percentage);
- n is the number of years over which the investment is made.
I.e. the annual interest rate is divided by 12 to give a monthly interest rate, and the number of years is multiplied by 12 to give the number of months over which the investment is made.
This formula is shown in the following spreadsheet:
Compound Interest Formula With Interest Paid Monthly:
| A | B | |
|---|---|---|
| 1 | Original Investment: | 100 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Future Value: | =B1*(1+B2/12)^(B3*12) |
The Excel compound interest formula in cell B4 of the above spreadsheet on the right once again calculates the future value of $100, invested for 5 years with an annual interest rate of 4%. However, in this example, the interest is paid monthly.
This formula returns the result 122.0996594.
I.e. the future value of the investment (rounded to 2 decimal places) is $122.10.
How To Calculate Compound Interest in Excel When Interest is Paid Quarterly
If the interest on your investment is paid quarterly (while being quoted as an annual interest rate), the Excel compound interest formula becomes:
=P*(1+r/4)^(n*4)
where,
- P is the initial amount invested;
- r is the annual interest rate (as a decimal or a percentage);
- n is the number of periods over which the investment is made.
I.e. the annual interest rate is divided by 4 to give a quarterly interest rate, and the number of years is multiplied by 4 to give the number of quarters over which the investment is made.
Compound Interest Formula With Interest Paid Quarterly:
| A | B | |
|---|---|---|
| 1 | Original Investment: | 100 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Future Value: | =B1*(1+B2/4)^(B3*4) |
Therefore, when interest is paid quarterly, the future value of $100, invested for 5 years with an annual interest rate of 4% is calculated by the Excel formula:
=100*(1+4%/4)^(5*4)
which returns the result 122.019004.
I.e. the future value of the investment (rounded to 2 decimal places) is $122.02.
Further Compound Interest Formulas in Excel
See also our Excel Financial Functions page for details of Excel's built-in compound interest and other financial functions.