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:

Annual 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,

Compound Interest Formula in Excel:
 AB
1Original Investment:100
2Annual Interest Rate:4%
3Number of Years:5
4Future 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:
 AB
1Original Investment:100
2Annual Interest Rate:4%
3Number of Years:5
4Future 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:
 ABCDE
1Original Investment:100 Value at end of year:
2Annual Interest Rate:4% 1=B$1*(1+B$2)^D2
3   2=B$1*(1+B$2)^D3
4   3=B$1*(1+B$2)^D4
5   4=B$1*(1+B$2)^D5
6   5=B$1*(1+B$2)^D6
 Results:
 ABCDE
1Original Investment:100 Value at end of year:
2Annual Interest Rate:4% 1104
3   2108.16
4   3112.4864
5   4116.985856
6   5121.6652902

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,

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:
 AB
1Original Investment:100
2Annual Interest Rate:4%
3Number of Years:5
4Future 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,

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:
 AB
1Original Investment:100
2Annual Interest Rate:4%
3Number of Years:5
4Future 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.