How To Calculate Compound Interest in Excel

For example, lets say you have a deposit of $100 that earns a 10% compounded interest rate. The $100 grows into $110 after the first year, then $121 after the second year. Each year the base increases by 10%. The reason the second years gain is $11 instead of $10 is as a result of the same rate (10% in this example) being applied to a larger base ($110 compared to $100, our starting point).

Or lets say, $100 is the principal of a loan, and the compound interest rate is 10%. After one year you have $100 in principal and $10 in interest, for a total base of $110. In year two, the interest rate (10%) is applied to the principal ($100, resulting in $10 of interest) and the accumulated interest ($10, resulting in $1 of interest), for a total of $11 in interest gained that year, and $21 for both years.

For the formula for compound interest, just algebraically rearrange the formula for CAGR. You need the beginning value, interest rate, and number of periods in years. The interest rate and number of periods need to be expressed in annual terms, since the length is presumed to be in years. From there you can solve for the future value. The equation reads:

This formula looks more complex than it really is, because of the requirement to express it in annual terms. Keep in mind, if its an annual rate, then the number of compounding periods per year is one, which means youre dividing the interest rate by one and multiplying the years by one. If compounding occurs quarterly, you would divide the rate by four, and multiply the years by four.

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.

Finance Basics 2 – Compound Interest in Excel

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:

Step 1: Set up your worksheet

The goal of a compound interest calculation is to be able to project the total amount of an investment with compound interest over a number of years. To correctly set up your worksheet for an interest formula, you will need to start by creating a row of data. Column A represents the initial investment for your row of data. Column B is the interest rate. Meanwhile, columns C, D and E reflect year 1, year 2 and year 3 balances.

In this example, the initial investment is $100 and the interest rate is 8% percent. Therefore, the first cell in your row, under column A will say $100. Well say for simplicity this is in A2. The second cell in the row, Column B, will say 8% in B2. Columns C, D and E will remain empty until we finish calculations.

Step 2: Create a formula in C2 that calculates simple interest

In year one, interest is simply calculated by multiplying the investment by the interest, to find the amount of interest accrued in the first year, and then adding it to the investment. That can be taken care of by programming your year 1 cell with the equation =A2 x 1.08. This calculates the simple interest that accrues in year one. The output of the formula is $101.80, or it may look like 101.8 if you havent formatted the cell to display monetary values.

Step 3: Calculate compound interest for subsequent years

In the cell marked Year 2, you will create a formula that begins to calculate compound interest. For the purposes of this example:

Lets imagine this takes place in cell D2, which helps to complete the row of data we set out to create in the beginning. In cell D2, to calculate the compound interest you will want to input = C2 x 1.08. In this case, C2 contains the value $101.80, so the Excel worksheet will calculate $101.80 x 1.08 and will display $109.17.

You can follow this same logic to create the formula that calculates year 3 interest in cell E2, = D2 x 1.08. The output of this equation will display $117.90. In subsequent years, with exponential growth due to compound interest, the investor gained $17.90.

How does compound interest work?

To understand how compound interest works, we must first explain simple interest. Simple interest is calculated based on the original amount of money you saved, otherwise known as the “principal amount”. To demonstrate, you might place $20 in your savings account at the bank. If you earn interest at a 5% rate, in one year your deposit would be worth 20 x 1.05, leaving you with an additional $1.00.

However, if you were to use the compound interest system, your principal would change each calculating period. Instead of putting the interest in your account, your bank would add it to your original investment. The newly-increased principal will provide the figures for the next calculation period and your profits will increase exponentially. Therefore, when you make use of compound interest, you are earning interest not just on the original principal, but also on any interest you have accumulated during each compounding period.

In our original example, the bank would add the $0.50 of interest that you earn each year to the original principal amount of $20. After two years of compounding periods with a 5% interest rate, your deposit would be worth $21.53 and would continue to grow at the same rate.

Advantages and disadvantages of compound interest

There are several pros and cons to consider when it comes to compound interest. As with most financial processes, how well compound interest works for you will depend on how proficiently you manage it. Here are some of the most common advantages and disadvantages of using compound interest:

Advantages

Compound interest has the potential to be extremely profitable if the original principal is sizable and if the amount is given a long time to grow. Also, if you schedule frequent compounding periods, which take place monthly or quarterly, your principal amount can grow exponentially at a more accelerated rate. Taking advantage of compound interest allows you to earn interest on your interest and make the most of every dollar you have in your account.

Disadvantages

The drawbacks of compound interest mostly relate to the interest that accrues on loans. When you take out a loan, odds are good that you will be paying compounding interest until your debt is paid off. Even if you made plans to afford the minimum payments, you may not be able to keep with the increased amount if the interest is regularly compounded. The best method for avoiding this issue is to pay a little more than the minimum payment each month to offset the interest that will continue to accrue.

Example of compound interest in Excel

Below is an example of compound interest in Excel.

Happy Corp invested $1,800 in a peer-to-peer loan that gains 12% interest per year. Based on the example above, your worksheet will look like this initially because we havent begun to calculate interest yet:

To find compound interest on this investment, we need to first calculate the year 1 simple interest by entering the following formula into the Year 1 cell. For simplicity in this example, this row represents the 2nd row of a spreadsheet, and the column headings start with Investment in column A. So the value of $1,800 appears in cell A2.

Once calculated, that will output as follows. In cells D2 and E2, or Year 2 and Year 3, you will need to follow the same process building on the previous years interest value:

FAQ

How do you compound interest annually in Excel?

The mathematical formula for calculating compound interest, A=P(1+r/n)^nt, uses four simple numbers to allow you to see how much money plus interest you’ll have after the number of time periods, or compound periods. ‘A’ represents the accrued amount of your principal plus interest, which is the total.

How do I compound interest monthly in Excel?

You can download the free Excel template from here and practice on your own.
  1. Calculate Monthly Compound Interest.xlsx.
  2. =C5*(1+(C6/12))^(12*C7)-C5.
  3. =FV(rate,nper,pmt,[pv],[type])
  4. =FV(C6/12,C7*12,0,-C5)-C5.
  5. =FVSCHEDULE(principal, schedule)

How do you calculate compound interest in Excel quarterly?

Keep in mind, if it’s an annual rate, then the number of compounding periods per year is one, which means you’re dividing the interest rate by one and multiplying the years by one. If compounding occurs quarterly, you would divide the rate by four, and multiply the years by four.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *