Updated August 21, 2023

## Calculate Compound Interest in Excel

Compound Interest is the interest amount payable at a fixed interest rate for any fixed/variable term of investment/loan period on borrowed loan or invested amount. We can calculate the Compound Interest in Excel if we know the mathematical expression of it. A mathematical expression that we have already learned in school, Compound Interest, is shown below.

**Compound Interest = P (1+r)**

^{n}Where,

**P**= Invested or Borrowed amount**r**= Yearly rate of Interest**n**= Invested or Loan term

### How to Calculate Compound Interest in Excel?

To calculate compound interest in Excel is very simple and easy. Let’s understand how to calculate with some examples.

#### Excel Calculate Compound Interest – Example #1

We have data on borrowed loan detail from any bank. For example, a person has borrowed a loan of Rs. 3000000/- from a bank with an annual interest rate of 8.85% for 30 Years. Detailed data is shown below.

Mostly compounding is done yearly. But it may vary from bank to bank and the type of investment is being done. In this case, we are considering the Compounding Period as 1.

Now let’s consider the mathematical formula for Excel calculating Compound Interest, which we have seen above.

**Compound Interest = P (1+r) ^{n}**

If we frame the formula with the above-mentioned value, then,

P = Rs. 3000000/-

r = 8.85%

n = 30 Years

For calculating compound interest, go to the cell where we want to see the output and type the “=” sign. And assign the above-mentioned value in a sequence of Compound Interest formulas in Excel, as shown below.

Here we have framed Compound interest; now press the Enter key to see the result.

As we can see, we have tallied our calculated compound interest. Rs.38192163.07/-. For 30 years, the person who borrowed the loan from the bank will be liable to pay Rs.38192163.07/- of compound interest.

We can assign the currency to calculate compound interest in Excel. For this, go to that cell and press **Ctrl+1** or right-click to select **Format Cells**.

Format Cells dialog box will open. Go to **Number** and from there, under the category of **Accounting.**

Select any applicable currency from the **Symbol** dropdown. Here we have selected **INR** (Indian Rupees). After that, click on Ok, as shown in the below screenshot.

As we can see, our calculated compound interest is showing the currency **INR** in the below screenshot.

#### Excel Calculate Compound Interest – Example #2

There is another method by which we can Calculate Compound Interest for any borrowed loan or invested amount. For this, we have considered the same data set that we have seen in Example-1.

Microsoft Excel has an inbuilt function named FV or Future Value, by which we can calculate the future value in terms of Compound Interest, Applicable loan with interest, and monthly EMI with one formula. To access the FV function, we can go to Insert Function beside the formula bar or type the “=” sign where we need to see the result. It will enable all the inbuilt functions of Excel. Now search and select **FV** from there, as shown below.

Where

**rate**= Rate of interest,**nper**= Number of payments to be made. It can be monthly or years,**pmt**= fixed amount which needs to be paid monthly or yearly,**pv**= Present value of borrowed or invested amount (Optional),**type**= Use 1 if the EMI is paid at the start of the month, 0 if the EMI is paid at the end of the month. In Excel, automatically, it will consider as 0 (Optional).

Now let’s frame the values in the above syntax of FV.

As we can see, we have considered the rate per compounding basis. So we have divided the 8.85% interest rate with compounding period 1 and multiplied nper, which is 30 with compounding period 1.

**Note:**If there is another value for compounding other than 1, we will get a different result.

Once we select all the required parameters and complete our syntax, press the Enter key to see the result.

As we can see, the calculated compound interest as 38192163.07/- is without currency. We can add the required currency with the process seen in Example-1. For that, go to **Format Cells** or press **Ctrl+1**.

Format Cells dialog box pops up. Go to **Number** and from there, under the category of **Accounting.**

Select any applicable currency from the **Symbol** dropdown. Here we have selected **INR** (Indian Rupees). After that, click on Ok, as shown in the below screenshot.

We will get the selected currency as shown below.

### Pros

- Compound interest is easy to calculate and calculate the values in Excel’s mathematical syntax.
- For real-life cases, we can easily calculate the interest payable on any loan or investment.
- We can use any of the mentioned methods for calculating compound interest for banking purposes.

### Cons

- Compound interest is difficult to understand when and where to put a minus (“-“) sign before PV in syntax.

### Things to Remember

- Calculating the compound interest with the formula and comparing the outcome is better. So that comparison can be made between the two outcomes.
- It is recommended to change/insert/update the currency to understand regional scenarios better.
- Understand the cases where we must put a minus sign before PV if we calculate compound interest from FV. Adding a minus sign indicates that we are liable to pay the amount to the bank. And plus sign shows the profit gained by the investor.

### Recommended Articles

This is a guide to Calculate Compound Interest in Excel. Here we discuss how to calculate compound interest in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –