Excel Calculate Compound Interest (Table of Contents)
Calculate Compound Interest in Excel
Compound Interest is the interest amount which is 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. Mathematical expression which we have already learned in our school, of 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 of borrowed loan detail from any bank. A person has borrowed a loan Rs. 3000000/- from a bank with the annual interest rate of 8.85% for 30 Years. Detailed data is shown below.
In this case, we are considering the Compounding Period as 1. Mostly compounding is done on a yearly basis. But it may vary from bank to bank and type of investment is being done.
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 above-mentioned value then,
P = Rs. 3000000/-
r = 8.85%
n = 30 Years
For calculating compound interest go the cell where we want to see the output and type “=” sign. And assign the above-mentioned value in a sequence of Compound Interest formula in excel as shown below.
4.9 (5,813 ratings)
View Course
As per mathematical formula, here we have framed Compound interest now press the Enter key to see the result.
As we can see, our calculated compound interest is tallied as Rs.38192163.07/-. Which means for 30 years that 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 Symbol dropdown. Here we have selected INR (Indian Rupees). After that, click on Ok as shown in below screenshot.
As we can see, our calculated compound interest is showing the currency INR in 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 which we have seen in Example-1.
Microsoft Excel has inbuilt function names as FV or Future Value, by which we can calculate the future value in terms of Compound Interest, Applicable loan with interest, monthly EMI with one formula. To access FV function, we can go to Insert Function which is beside the formula bar or type “=” 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 payment 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 be considered 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 would 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, here also the calculated compound interest as 38192163.07/- is without currency. We can add the required currency with the process which we have 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 Symbol dropdown. Here we have selected INR (Indian Rupees). After that, click on Ok as shown in below screenshot.
We will get the selected currency as shown below.
Pros
- Compound interest is easy to calculate and formulate the values in mathematical syntax in excel.
- We can calculate interest payable of any loan or investment easily for our real-life cases as well.
- For banking purpose, we can use any of the mentioned methods for calculating compound interest.
Cons
- Compound interest is a bit difficult to understand when and where we need to put a minus (“-“) sign before PV in syntax.
Things to Remember
- It is better to calculate the compound interest with both the formula and compare the outcome. So that comparison can be made between two outcomes.
- It is recommended to change/insert/update the currency to have a better understanding with regional scenarios.
- Understand the cases where we need to put minus sign before PV if we are calculating compound interest from FV Adding 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 has been a guide to Calculate Compound Interest in Excel. Here we discuss how to calculate compound interest in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
