Excel Formula For Compounding Interest

Definition And Building Compound Interest Formula

Finance Basics 2 – Compound Interest in Excel

Suppose you have some investable money of the amount of $10,000. You go to a bank and the bank said their savings rate is 6% per year. You deposited the money with the bank for the next 3 years as you felt safe with the bank and the interest rate is competitive.

So, your principal is: $10,000

The annual interest rate is: 6%

After 1 Year:

After 1 year, you will receive interest of amount: $10,000 x 6% = $10,000 x = $600

So, after 1 year, your principal + interest will be:

= $10,000 + $600

= $10,000 + $10,000 x 6%

= $10,000

If you withdraw this interest , then your principal at the beginning of the 2nd year will be $10,000. But if you dont withdraw the interest, your principal at the beginning of the 2nd year will be $10,000 + $600 = $10,600 And this is where compounding starts. When you dont withdraw the interest, the interest is added to your principal. The principal and earned interest work as your new principal for the next year. Your next years interest is calculated based on this new principle. Eventually, the yearly return from investments in the coming years gets bigger.

After 2 Years:

At the beginning of year 2, your new principal is: $10,600

At the end of year 2, you will receive interest of the amount: of $10,600 x 6% = $636. Lets make the compound interest rate formula from the above expression:

= $10,000 + $10,600 x 6% = $10,000 + $10,000 x 6%

= $10,000

= $10,000 x ^2

=p^n

So, your principal + interest at the end of year 2 will be:

=p^n

=$10,000 x ^2

Examples Of Daily Compound Interest Formula

Lets take an example to understand the calculation of Daily Compound Interest in a better manner.

Example #1

Lets say you have $1000 to invest, and you can leave that amount for 5 years. The financial institution where you are depositing the money is offering you a 10% interest rate, which will be compounded daily. Calculate the Daily Compound Interest.

Solution:

Ending Investment is calculated using the formula given below:

Ending Investment = Start Amount * ^

Ending Investment = $1,000 * ) ^
Ending Investment = $1,648.61

Daily Compound Interest is calculated using the formula given below

Daily Compound Interest = Ending Investment Start Amount

Daily Compound Interest =$1,648.61 $1,000
Daily Compound Interest = $648.61

Daily compound interest which you have earned $648.60.

If the given rate is compounded annually, then

For Annual Compounding

Ending Investment is calculated using the formula given below

Ending Investment = Start Amount * ^ n

Ending Investment = $1,000 * ^ 5
Ending Investment = $1,610.51

Daily Compound Interest is calculated using the formula given below.

Daily Compound Interest = Ending Investment Start Amount

Daily Compound Interest =$1,610.51 $1,000
Daily Compound Interest = $610.51

So you can see that in daily compounding, the interest earned is more than in annual compounding.

Example #2

Bank 1: Interest Rate: 12.5% Compounding Daily
Bank 2: Interest Rate: 12.5% Compounding Annually

Solution:

Interest Rate: 12.5% Compounding Daily

Future Values Of An Investment Using Compound Interest Formula

Initially, using the following compound interest formula, we can calculate future values on investment for any compounding frequency.

A = P ^

Where,

A = Total amount after nt periods
P = The amount invested at the beginning. It cannot be withdrawn or changed in the investment period.
r = Annual Percentage Rate
n = Number of times interest is compounded per year
t = Total time in years

Check out the image below. I have shown 4 variations of the above formula.

Finally, you see that for the same investment of $10,000, we get the following results:

For daily compounding: $18220.29
And for Quarterly compounding: $18140.18

So, if the number of compounding per year is higher, the return is also higher.

Don’t Miss: Compound Interest Calculator With Withdrawals

How To Calculate Compound Interest Using Excel

There are many ways to calculate compound interest rates and totals, including finance calculating websites, traditional calculators and a pen and paper. One of the easiest ways to calculate compound interest is with the spreadsheet application, Microsoft Excel. Here are the steps for calculating compound interest using Excel:

How To Create Quarterly Compound Interest Calculator In Excel

how to calculate future value with compounded interest in excel Quant ...

Compound Interest is a crucial part of financial computation that so many of us utilize on a regular basis.Excel has a financial function to calculate the compound interest rate. In this article, we will create a quarterly compound interest calculator in Excel.

Quarterly Compound Interest Calculator.xlsx

Don’t Miss: How To Find Interest Rate On Loan

General Formula To Calculate Compound Interest

In this example, we are going to use this = PV * POWER, n) formula of compound interest. Follow each step carefully and dont miss even a single step as you can get confused:

Step 1: Go to the cell where to store calculated compound interest and select it. Then in the formula bar, use the following compound interest formula to calculate the interest for the first year.

= PV * POWER, n)

Step 2: Like the above example, either you can provide the reference of the cell for the respective value needed or directly enter the value for calculation.

We are using cell reference. So, our formula will be like, =B4*POWER, B6)

Step 3: Press Enter and get the total amount after calculating three years compound interest in one go.

You can see that returned value is 1404.928 after applying compound interest 3 years.

You can also compare both results of compound interest calculated through the different formulas and note that the results are identical.

What Is The Monthly Compound Interest Formula

When a certain amount of money is borrowed for a specific duration, an extra amount needs to pay apart along with the borrowed amount. Then the extra amount which we pay at the fixed rate is called as interest. Compound interest is the total interest that includes the original interest and the interest of the new principal which is evolved out by adding the original principal to the due interest. For monthly compounded to calculate, the interest which is compounded all month in the whole year.

The Monthly compounded Interest Formula can be calculated as:

Monthly Compound Interest Formula = P * )12*t P

where,

Also Check: Interest Rate On Discover Card

Calculate Compound Interest With Regular Deposits Using Manual Formula

We can use an Excel formula for calculating compound interest with regular deposits. For this, you have to follow the steps below.

Steps:

Initially, we have taken only 9 months or periods . Add more periods under this column if necessary and apply the formulas from the above row.
After that, in cell C5 , we have used this formula, C5=$H$7. And then applied this formula to other cells in the column.

Then, in cell D5 , We used this formula, D5=H5+C5. This formula is used just once. This is just to add the initial investment to the formula.

Later, in the cell E5 , We have used this formula, E5=D5+D5*

This formula will add the Starting Principle to the interest earned ) for the period. We are dividing the yearly interest rate $I$6 by 12 as the regular deposit is made monthly. Copy the formula and apply it to the cells below.

Then, in cell D6 , We have used this formula, D6=E5+C6. This formula will add the new deposit to the amount at the end of the previous period. And then we copied down this formula for other cells in the column.

Finally, drag down the Fill Handle tool for other cells and your result will look like this.

Calculate Compound Interest Using The Formula In Excel

Calculate Compound Interest in Excel

Now that we’ve understood how compound interest works let’s learn how to calculate compound interest in Excel using the compound interest formula.

The compound interest formula is:

P is the principal or the initial investment.
P’ is the gross amount .
R is the interest rate.
N is the number of times compounding occurs per year.
T is the total time in which compound interest is applied.

Now that we’ve got the compound interest formula, let’s use it in Excel to calculate our compound interest for five years :

Create a table in Excel containing the principal value, interest rate, and other metrics required in the formula.

Write the compound interest formula in cell B5

Here the principal is given in cell B1, the interest rate in cell B2, the compounding period in cell B3, and the total number of years in cell B4. So, your compound interest formula should look something like this:

Press enter, and you’ll get the gross amount, more commonly referred to as the future value, you’ll get after five years.

You’ve essentially created a compound interest calculator. Now, it’s simply a matter of altering the values to calculate your compound interest.

You May Like: Student Loan Refinance Interest Rates

Syntax Of The Fv Formula

The generic formula for the FV function is:

The parameters of the FV function are:

rate an interest rate for the period
nper the total number of periods of an investment
pmt payment per period
the present value of a loan and must be negative. This is an optional parameter. If its omitted, the default value is 0
specifies when the payments are made: at the end of a period or at the beginning of a period . By default, its 0.

How To Calculate Compound Interest In Excel Formula

Example #1 Using Mathematical Compound Interest Excel Formula

Suppose we have the following information to calculate compound interest in Excel.

As we have described the formula above, we will implement the same in MS Excel using cell references in excel Cell References In Excel Cell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more and various operators.

As the C2 cell contains the principal amount . We need to multiply this value with the interest rate.

The interest is to be compounded quarterly , so we need to divide the annual interest rate with cell C5.

As interest is being compounded four times in a year, we need to reference a cell where the number of years is mentioned to multiply 4 with the number of years. That is why the formula would be like this:

After pressing the Enter button, we will get 15764.18 as the future value with compound interest.

It is like a compound interest calculator in Excel now. We can change the value for the annual interest rate, the number of years, and compounding periods per year as below.

Example #2 Using the Compound Interest Calculation Table in excel

Suppose we have the following information to calculate compound interest in a table excel format .

The result is shown below:

The future value after four quarters will be 15764.18.

Don’t Miss: Examples Of Statement Of Interest

Insert Formula To Generate Quarterly Compound Interest Calculator In Excel

We already know about the formula for finding the quarterly compound interest in the above section of the article. So now we are going to put the formula of quarterly compound interest in our resulting cell. Lets follow the steps to get a clear idea of how the formula works.

STEPS:

Firstly, select the resulting cell. So we are selecting cell C9.
Secondly, write the formula there.

=C5*^)

Here, cell C5 indicates the principal amount, cell C6 represents the annual interest rate, and cell C7 denotes the total years of investment.

Finally, press Enter.

How Does Compound Interest Work

Suppose you invested $1000 with a 5% interest rate that will compound every year. In this case, you will earn $50 after one year, making your gross amount $1050. In the following year, the interest will apply to the gross amount, i.e., 5% of 1050. This will make your gross amount $1102.5.

Similarly, the interest will apply to the preceding year’s gross amount in the next year, i.e., 5% of 1102.5. As this continues, your initial investment increases exponentially yearly.

Compound interest differs from simple interest because in the former, the interest applies to the gross amount, while in the latter, it only applies to the principal. Compound interest has exponential increments solely because of this difference.

Also Check: What Accounts Have Compound Interest

How To Calculate Monthly Compound Interest In Excel

We can use the following formula to find the ending value of some investment after a certain amount of time:

A = Pnt

n: Number of compounding periods per year
t: Number of years

If the investment is compounded monthly, then we can use 12 for n:

A = P12t

The following example shows how to use this formula in Excel to calculate the ending value of some investment that has been compounded monthly.

Daily Compound Interest Formula

For the daily compound interest formula, use 365 as the parameter for Number of compounding periods per year:

= initial investment * ^

With the same factors, lets compound the interest daily:

Initial investment: $1,000
Number of compounding periods: 365

Heres the result:

In 10 years, your $1,000 investment will be worth $1,349.84 at 3% annual interest compounded daily.

You May Like: What Is Apr And Interest Rate

How To Calculate Compound Interest In Excel When Interest Is Paid Quarterly

If the interest on your investment is paid quarterly , the Excel compound interest formula becomes:

=P*^

P is the initial amount invested
r is the annual interest rate
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.

Calculate The Compound Interest Of The Investment

Compound Interest Formula in Excel – Calculate Investments Using Future Value (FV) Function in Excel

In our example, we want to calculate the compound interest of the investment in the cell E3. The interest rate is 6.50%, the total number of periods is 60 and the present value of the investment is $5,000.

The formula looks like:

The parameter rate is C2/12, as we must pass the monthly interest rate to the function. The nper parameter is C3 and the pmt is 0. The pv is in the C4 , with the minus.

To apply the FV function, we need to follow these steps:

Select cell E3 and click on it
Insert the formula: =FV

Figure 3. Using the FV function to calculate the compound interest of the investment

Finally, the result in the cell E3 is $6,914, which is the compound interest of the investment of $5,000 in 60 months with the annual interest rate of 6,50%.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Recommended Reading: S& p 500 Compound Interest Calculator

Using The Fv Function To Calculate Compound Interest In Excel

The second method to compute the compound interest is using the FV function. The term FV is short for Future Value.

The Excel FV function is a financial function that returns the future value of an investment. The function assumes a periodic and constant payment made with a constant interest rate.

Syntax of this function is as follows:

=FV

Here,

rate is the interest rate per payment period
nper is the number of payment periods for which you want to compute the future value
pmt is the additional payment made during each period. This value is usually specified as a negative number. If theres no additional payment being made then this value can be 0.
pv is the present value . This value is optional and, if ommitted, is assumed to be 0. It is also specified as a negative number .
type is an integer specifying when the payments are due. A value of 0 means the payment is due at the end of the period, while a value of 1 indicates that the payment is due at the beginning of the period. This value is also optional with a default value of 0.

Let us use the above function to compute the compound interest for the following example:

We use the FV formula to calculate the compound interest as follows:

=FV

Note that the above formula calculates the future value assuming that the interest is compounded just once every year within the given time period.

You need to make sure that both rate and nper values provided to the function are consistent.

=FV

Use Formula To Calculate Periodic Interest Rate In Excel

Well start by calculating the interest rate for a specific time period, such as months or years. We will compute the interest rate for months and then for years in the two subsections below. Follow the steps outlined below to do so.

We will apply theRATE function to have done it.

=RATE

Here,

Nper total payment periods number
Pmt the pre-set payment amounts each period that cannot be varied over the annuitys lifetime. It generally involves principal and interest but excludes taxes.
Pv the present worth of the loan.
Fv the future worth, or the cash balance you want after the last instalment It defaults to 0 if not specified.
Type specifies the date on which the payments are made:
Payment is due at the end of the period if 0 or absent .
1 the first payment is required at the start of the period.
Guess your best guess as to what the rate may be If you leave it blank, it defaults to 10%.

1.1 Monthly Interest Rate

Step 1:

Then, press Enter to get the Interest Rate.

1.2 Annual Interest Rate

We compute the monthly interest rate first, then multiply by 12 to get the annual interest rate. To understand, follow the steps below.