How to Use Compound Interest Formula in Excel (4 Easy Ways) (2024)

Get FREE Advanced Excel Exercises with Solutions!

Compound interest is the interest that’s calculated both on the initial principal of a deposit or loan and on all previously accumulated interest. Professor Albert Einstein once said, “Compounding interest is the most powerful force in the universe.” It is doubtful whether Einstein really said that or not, but it doesn’t take away from the importance of the message. Compounding is a very powerful force that either works against you (borrowing) or for you (investing). In this article, I will show you how to use the compound interest formula in Excel. Let’s get started with the basics of compound interest.

Table of Contents Expand

Knowing Simple Interest Formula

You can use the simple interest formula to determine the amount of interest that will be charged on a sum of money at a certain rate and for a specified amount of time. The formula for determining simple interest is,

I = Pnr

Where,

I = calculated simple interest
P = Initial Principal
n = number of periods
r = annual interest rate

For example, if in 5 years you invest $100 at a rate of 5%. Then the calculated simple interest will be, I = $100*5/100*5, that is $25.

Knowing Compound Interest Formula

For calculating the future value of any investment earning at a constant rate of interest the following formula can be used. Which is,

Future Value = P*(1+r)^n

Where,

P – the initial amount invested
r – annual interest rate (as a decimal or a percentage)
n – number of periods over which the investment is made

For example, if in 5 years you invest $100 at a rate of 5%. Then the calculated future value is, 100*(1+5%)^5 = 127.6282.

How to Use Compound Interest Formula in Excel: 4 Easy Ways

In this article, I will show you four easy ways to use the compound interest formula in Excel. In the first method, I will apply the basic formula of compound interest. Then, in the second procedure, I will calculate the compound interest for multiple years. Thirdly, I will consider different time contributions for calculating compound interest. Lastly, I will apply the FV function of Excel to calculate compound interest for different conditions.

For proceeding with our methods, I will use the following sample data set. Here, I have a table that states a $100 investment for 5 years at an annual interest rate of 5%.

1. Applying Compound Interest Formula in Excel

In the first procedure, I will apply the basic formula of compound interest in Excel. Here we are going to calculate the future value of some ventures using the formula of compound interest in Excel. We can use the formula directly to calculate the future value in Excel. For that, follow the below-given steps.

Step 1:

Firstly, write the following formula in cell C7.

=C4*(1+C5)^C6

Step 2:

Secondly, press Enter to see the future value in cell C7, which is $127.63.

Read More: Methods to Apply Continuous Compound Interest Formula in Excel

2. Calculating Compound Interest Over Multiple Years

The future value of some amount of investment for a number of years can be shown using the same formula. Here the investment goes as the years add up. To learn more about this method, go through the following steps.

3. Determining Compound Interest with Time Contributions in Excel

In the third procedure, I will determine compound interest with different time contributions in Excel. From this method, you can calculate daily compound interest in Excel, as well as weekly, monthly, or semiannually. So, follow the below-given steps to learn the procedure.

Step 1:

First of all, I will create a dropdown to collect all the time contributions into one cell.
For that, select cell C7 and then go to the Data tab of the ribbon.
From there, in the Data Tools group, select Data Validation.

Step 2:

Secondly, the Data Validation dialog box will appear.
From there, under the Allow label, choose List.
Then, in the Source type box, select the cell range C9:C13 from the Data Set worksheet that has the time interval of all the periods.
Lastly, press OK.

Step 3:

Thirdly, after performing the previous two steps, you will see the dropdown where all the time intervals are present.

Step 4:

Fourthly, insert the following formula in cell C8 to determine the compound interest for monthly contributions.

=C4*(1+C5/C7)^(C6*C7)

For monthly contributions, the value of cell C7 will be 12.

Step 5:

Finally, get the desired result by pressing Enter.

Step 6:

Furthermore, by changing the cell value of C7 to 365, you can determine the compound interest with daily contributions.
Hence, you can change C7 according to your preference.

4. Applying Excel FV Function for Calculating Compound Interest

It’s good practice to use Excel’s FV function, which calculates the future value based on different factors. The syntax for this FV function is,

FV(rate, nper, pmt, [pv], [type])

Where,

rate -the interest rate
nper -number of periods for the investment
pmt -the periodic payment
pv -the present value/initial investment
type is optional.

4.1. Applying FV Function

In this section, I will show you the application of the FV function to calculate compound interest in Excel. Here, the application will be a simple one with no extra conditions. To apply this function, see the following steps.

Step 1:

First of all, write the following formula for the FV function in cell C7.

=FV(C5,C6,0,-C4)

Here, 0 is used as there is no periodic payment.
Also, I used the negative sign as Excel treats this as “money out” for your investment.

Step 2:

Finally, after pressing Enter, you will see the desired compound interest of $201.22.

4.2. FV Function with Compounded Period

For calculating the future value to be compounded with a monthly, quarterly, weekly, or daily contribution, you need to divide the annual interest rate (for the FV function it is rate) with the contribution and multiply the contribution by the number of years (for the FV function it is nper).

Let’s say we want to calculate the future value of the same factors. The only difference will be the compounding period. Here, the compounded periods will be the total number of weeks per year, which is 52. The steps below show how it is done.

Step 1:

In the beginning, insert the following formula in cell C8 with compounding periods.

=FV(C5/C6,C7*C6,0,-C4)

Step 2:

Secondly, you will get the desired result by pressing Enter.

4.3. FV Function with Periodic Payment

Let’s calculate the compound interest with more options where additional contributions (pmet & type) are added. We will calculate the same example where an additional contribution of $500 is added. For doing this, see the following steps.

Step 1:

Firstly, enter the following formula containing periodic payment in cell C10.

=FV(C5/C6,C7*C6,-C8,-C4,C9)

Step 2:

Finally, after pressing Enter, you will get the desired result for this condition, which is $467,346.31.

Read More: Excel Formula to Calculate Average Annual Compound Growth Rate

Calculating Compound Interest Between Two Dates in Excel

Sometimes, users have to calculate compound interest that is between two dates. Doing this calculation manually can be time-consuming and tiresome. By using the IPMT function of Excel, we can solve it easily. Suppose, We have to calculate the interest on a $5000 loan amount with monthly compounding and an interest rate of 6%. We are to find the interest between 15-Jun-2022 and 14-Jul-2022. The loan is to be repaid in 12 years. To do this type of calculation, see the following result.

Step 1:

Firstly, type the following formula of the IPMT function in cell C11 to get the result.

=IPMT(C5/12,1,C7*C6,-C4)

Here, 1 stands for the time interval between the two aforementioned dates, which is 1 month.

Step 2:

Finally, by pressing Enter, you get the result for this procedure.

Determining Compound Interest with Regular Deposits

In this section, I will show you how to calculate compound interest using the Excel formula with regular deposits. To do this, we will use the Excel FV function. To learn more about this procedure, see the following steps.

Step 1:

At the beginning, insert the following formula of the FV function in cell C12 with all the necessary arguments.

=FV(C7,C9,-C10,-C4,C11)

Here, in cell C12, I select the payment type as 1.
Also, we will also pay $2,500 ten times a year, which is a regular deposit.

Step 2:

Finally, to get the desired result, press Enter after inserting the formula.

Read More: How to Calculate Compound Interest for Recurring Deposit in Excel

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Compound Interest Formula.xlsx

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to use the compound interest formula in Excel. Please share any further queries or recommendations with us in the comments section below.

How to Use Compound Interest Formula in Excel: Knowledge Hub

How to Calculate Compound Interest in Excel in Indian Rupees
CAGR Formula in Excel: With Calculator and 7 Examples
How to Calculate End Value from CAGR in Excel
How to Calculate CAGR with Negative Number in Excel
How to Calculate 3-Year CAGR with Formula in Excel
How to Calculate 5 Year CAGR Using Excel Formula
How to Create CAGR Graph in Excel

<< Go Back to Excel for Finance | Learn Excel

FAQs

How to use compound interest formula in Excel? ›

Learning the Basic Excel Formulas for Calculating Compound Interest. There are two basic formulas for calculating compound interest in Excel. The first formula is =P*(1+r/n)^(n*t) , where P is the principal amount, r is the interest rate, n is the compounding period, and t is the term.

Read On ›

What is the easiest way to calculate compound interest? ›

The formula for calculating compound interest is: Compound interest = total amount of principal and interest in future (or future value) minus principal amount at present (or present value)

Discover More Details ›

What is the easiest way to explain compound interest? ›

Compound interest is when you earn interest on the money you've saved and on the interest you earn along the way. Here's an example to help explain compound interest. Increasing the compounding frequency, finding a higher interest rate, and adding to your principal amount are ways to help your savings grow even faster.

How do I use the compound interest formula? ›

The formula we use to find compound interest is A = P(1 + r/n)^nt. In this formula, A stands for the total amount that accumulates. P is the original principal; that's the money we start with. The r is the interest rate.

See Details ›

What is the secret formula for compound interest? ›

Compound interest is calculated by multiplying the initial loan amount, or principal, by one plus the annual interest rate raised to the number of compound periods minus one. This will leave you with the total sum of the loan, including compound interest.

Find Out More ›

How much is $1000 worth at the end of 2 years if the interest rate of 6% is compounded daily? ›

Basic compound interest

For other compounding frequencies (such as monthly, weekly, or daily), prospective depositors should refer to the formula below. Hence, if a two-year savings account containing $1,000 pays a 6% interest rate compounded daily, it will grow to $1,127.49 at the end of two years.

Tell Me More ›

What is the formula for simple interest and compound interest? ›

simple interest formula is PRT. compound interest formula is P(1 + R)^T - P.

Show Me More ›

What is the simplest formula for compound interest? ›

The compound interest is found using the formula: CI = P( 1 + r/n)^nt - P. In this formula, P( 1 + r/n)^nt represents the compounded amount.

Explore More ›

What is the easiest way to calculate interest? ›

To calculate simple interest, multiply the principal amount by the interest rate and the time. The formula written out is "Simple Interest = Principal x Interest Rate x Time." This equation is the simplest way of calculating interest.

How can I compound interest faster? ›

4 Ways to Accelerate the Power of Compound Interest

Calculate Compound Interest with the Rule of 72. ...
Focus on Asset Classes with High Expected Returns. ...
Scrutinize Every Fee. ...
Manage Cash Appropriately. ...
Invest for the Long-Term — Set it and Forget it.

Mar 7, 2019

Show Me More ›

How to calculate compound interest in Excel? ›

Compound Interest Formula in Excel

on principal amount P.
at the rate of interest R.
for the number of years N.
and compounded T times per year.
we can use the formula = P*(1+R/T)^(N*T)

Apr 2, 2024

Read The Full Story ›

How does compound interest work for dummies? ›

If you are saving or investing, compound interest will increase the amount by which your money grows every year. This is because every year there is a larger sum to earn that interest. This can only work if you invest for growth, which is when you leave your money invested for a number of years.

See Details ›

What is the magic of compound interest? ›

When you invest, your account earns compound interest. This means, not only will you earn money on the principal amount in your account, but you will also earn interest on the accrued interest you've already earned.

Get More Info Here ›

What is the formula for compound interest in Excel and Google Sheets? ›

=A2*(1+B2/C2)^(C2*D2)

This formula represents the compound interest formula A(1 + r/n)^(nt), where A is the principal amount, r is the annual interest rate, n is the number of times interest is compounded per year, and t is the number of years.

What is the formula for compound interest in Excel with regular contributions? ›

An easy and straightforward way to calculate the amount earned with an annual compound interest is using the formula to increase a number by percentage: =Amount * (1 + %) . Where A2 is your initial deposit and B2 is the annual interest rate.

How to calculate present value with compound interest in Excel? ›

PV can be calculated in Excel with the formula =PV(rate, nper, pmt, [fv], [type]). If FV is omitted, PMT must be included, or vice versa, but both can also be included. Net present value (NPV) is different from PV, as it takes into account the initial investment amount.

View Details ›