Compound interest formula with monthly contributions excel

Home » Excel-Formulas » Compound-Interest-Formula

Table of Contents Show

How To Calculate Compound Interest in Excel
Compound Interest Formula in Excel
Compound Interest Formula Using Excel References
Calculate Compound Interest Over Multiple Years
How To Calculate Compound Interest in Excel When Interest is Paid Monthly
How To Calculate Compound Interest in Excel When Interest is Paid Quarterly
Further Compound Interest Formulas in Excel
How do you calculate compound interest with monthly contributions?
How do I calculate monthly compound interest in Excel?
How do I calculate compound interest for recurring deposit in Excel?
How do you calculate monthly compounded monthly?

How To Calculate Compound Interest in Excel

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:

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:

	A	B	D	E
1	Original Investment:	100	Value at end of year:
2	Annual 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:

	A	B	D	E
1	Original Investment:	100	Value at end of year:
2	Annual Interest Rate:	4%	1	104
3			2	108.16
4			3	112.4864
5			4	116.985856
6			5	121.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,

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)^(B312)*

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)^(B34)*

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:

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.

How do you calculate compound interest with monthly contributions?

The monthly compound interest formula is used to find the compound interest per month. The formula of monthly compound interest is: CI = P(1 + (r/12) )^12t - P where, P is the principal amount, r is the interest rate in decimal form, and t is the time.

How do I calculate monthly compound interest in Excel?

A more efficient way of calculating compound interest in Excel is applying the general interest formula: FV = PV(1+r)n, where FV is future value, PV is present value, r is the interest rate per period, and n is the number of compounding periods.

How do I calculate compound interest for recurring deposit in Excel?

= FV(Rate,Nper,Pmt,Pv,Type) Modified Rate of Interest: The interest rate in Recurring Deposits (in this case case of 8.75%) is compounded on quarterly basis. Whereas FV is calculated on monthly basis because we are making monthly deposits.So we cannot directly put the standard bank rate into the above formula.

How do you calculate monthly compounded monthly?

Monthly compounding is calculated by the principal amount multiplied by one plus the rate of interest divided by several periods whole rises to the power of the number of periods. That whole is subtracted from the principal amount, which gives the interest amount.