Compound Interest Formula in Excel

Have you ever wondered how to calculate compound interest in Excel? It can be confusing even for a seasoned finance expert. That's why we've written up this step-by-step tutorial on how to use the compound interest formula in Excel to get the job done.

Before we begin, let us see what compound interest is.

Compound interest is the interest that is earned not only on the initial principal but also on accumulated interest from previous periods.

The total amount of money in an account after 't' years with simple interest and with compound interest depends on the principal (the initial amount of money deposited), the annual nominal interest rate (the stated percentage rate, usually quoted as an annual percentage), and how often the interest is compounded per year.

You can calculate compound interest by using the following formula:

A = P(1 + r/n) (nt)

Where:

A = amount after t years including interest earned

P = principal amount (initial investment)

r = annual nominal interest rate

n = amount of interest compounded per year

t = number of years

Now let us see 3 different ways to calculate compound interest in Excel.

Method 1:

Using Mathematical Compound Interest Formula in Excel

Let us consider the following example with the following data present.

Compound Interest Formula in Excel

Using a similar formula as described above, let us now calculate the Future Value for the principal amount for the given period and interest rate.

Step 1:

Cell E2 contains the principal amount (also known as the present value). Multiply this amount by the interest rate.

= E2 * Interest

Step 2:

Since the interest rate is to be compounded quarterly (D5), we need to divide the annual interest rate with cell D5.

= E2*(1+E3/E5)

Step 3:

Since interest compounds four times a year, we must refer to a cell containing the number of years so that we can multiply 4 by the number of years. The formula would look like this:

=E2*(1+E3/E5)^(E4*E5)

formula for compound interest and simple interest

Step 4:

With compound interest, we receive Rs. 24628.79 as the future value.

formula in excel for compound interest

Now you have a compound interest calculator in Excel. We can compound interest for any data by changing the value for the Annual Interest Rate, the number of years, and compounding periods per year.

Method 2:

Calculating Compound Interest Using FVSCHEDULE Excel Formula

FVSCHEDULE is the most commonly used compound interest formula in Excel.

Let us consider the same data set as used in the previous example.

Using FVSCHEDULE

Calculate future value by using the FVSCHEDULE function. After applying a series of compound interest rates to an initial principal, the FVSCHEDULE formula provides its future value.

The formula used to calculate FVSCHEDULE is

=FVSCHEDULE(Principal, Schedule)

Step 1:

The first step is to write the FVSCHEDULE function in cell C6. This function takes two arguments, i.e., principal and schedule.

  • Where the principal is the investment amount.
  • Schedule is a series of interest rates enclosed in curly braces.

Step 2:

The principal value is provided with the reference of the C2 cell, and schedule is the value we get when we divide the annual interest rate by compounding frequency.

=C3/C5/p>

formula for compound interest excel

which gives the value 0.0175.

what is formula for compound interest

Apply the FVSCHEDULE formula now.

=FVSCHEDULE(C2, H3:H14)

where H3:H14 is the series of interest rates per quarter.

formula for compound interest monthly

The compound interest at the end of 3 years using the compound interest formula in Excel is as shown below.

formula for compound interest rate

Method 3:

Calculating Compound Interest Using FV Excel Formula

Compound interest can also be calculated using the FV Excel formula.

Let us consider the same data set as used previously.

Using FV – Example 1

FV (Future Value) gives the future value of an investment based on periodic, constant payments and a constant interest rate.

Here's the formula to calculate the FV function:

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

Where the arguments used are as follows:

  • Rate - The interest rate for each period.
  • nper - The number of payments per period.
  • pmt - The payment made per period. This value must be negative.
  • pv - The present value of future payments. Omitted values are assumed to be zero. Else, enter as a negative number.
  • type - When a payment is due. 0 = period end, 1 = period beginning. The default value is 0.

Either the PMT or PV argument must be mentioned.

The rate is calculated as ‘Annual Interest Rate (C3)/ Compounding frequency (C5)’.

nper is calculated as 'Years (C4) * Compounding frequency (C5)'.

There is no additional payment to the initial investment. Hence pmt is considered as 0.

PV is the present value (C2) denoted with a negative sign.

So the final formula for FV function would be,

=FV(C3/C5,C4*C5,0,-C2)

Using FV – Example 1

The final amount calculated using the FV compound interest formula in Excel is shown below.

Using FV – Example 1

In this tutorial, we learned how to use the compound interest formula in Excel. This formula is extremely useful for calculating the value of an investment based on interest over time. We hope that after reading this tutorial, you have a better understanding of how it works and when you will use it in your daily life.

Download


Get Your FREE Excel Shortcut Keys e-BOOK