In this tutorial, I am going to show you 5 easy methods to calculate compound interest in Excel in Indian rupees. You can use these methods even in large datasets to find out the compound interest for a certain time period. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.
What Is Compound Interest?
Compound interest is basically earning interest from interest. This means it earns on the initial deposit and also on each compounding period. The principal for each period is different from the previous one. So, if you are investing for the long term in compound interest, this may be a lucrative strategy to increase wealth.
How to Calculate Compound Interest in Excel in Indian Rupees: 5 Easy Methods
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we formatted all the cells containing monetary values in Accounting format which I will show you in just a minute. For all the datasets, we have 4 unique columns which are Initial Investment, Interest Rate, Balance In 1 year, and Balance after 2 years. However, we may vary the number of columns later on if that is needed.
1. Using Percentage Formula
In this first method, we will use the percentage formula to calculate compound interest in Excel in Indian rupees. Let us see how to do this.
- First, select the cells from D5 to E10 and press Ctrl+1.
- Now, in the Format Cells window, click on Accounting and select ₹ Hindi from the Symbol drop-down. Then click OK.
- Next, go to cell D5 and insert the following formula:
- Then, press Enter and copy this formula down using the Fill Handle.
- After that, go to cell E5 and insert the following formula:
- Finally, press Enter and this will calculate the compound interest values for the two years.
2. Calculating Earned Interest for Each Year
We can also calculate the compound interest in Excel in Indian rupees to show the year-by-year balance as it accumulates. Follow the steps below to do this.
- To begin with, double-click on cell C5 and enter the below formula:
=E5 + E5 * $E$7
- Then, press the Enter key go down to cell C6, and type this formula:
=C5 + C5 * $E$7
- Next, press the Enter key and drag the Fill Handle to copy this formula to the cells below.
3. Utilizing FV Function
The FV function in Excel basically calculates the future value of the current investment. Let us see how to use this function to calculate compound interest in Excel in Indian rupees.
- To start this method, navigate to cell C9 and type in the following formula:
=FV(C5/C6, C7*C6, , -C4)
- After that, press the Enter key or click on any blank cell.
- Immediately, this will give you the compound interest balance inside cell D10.
4. Applying Compound Interest Formula
There is also the compound interest formula that uses basic mathematical operators to calculate compound interest in Excel in Indian rupees. Below are the steps to do this.
- As previously, insert the below formula inside cell C9:
=C4 * (1 + C5 /C6) ^ (C7 * C6)
- Finally, press the Enter key and we should get the result of the compound interest formula.
5. Using VBA Code
If you are familiar with Excel VBA, then you can quickly calculate compound interest in Excel in Indian rupees. Follow the steps below to apply this.
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
Public Sub CompoundInterest() Range("C9").Value = Range("C4") * (1 + Range("C5") / Range("C6")) ^ (Range("C7") * Range("C6")) End Sub
- Then, open the macro from the Developer tab by clicking on Macros.
- Now, in the Macro window, select the CompoundInterest macro and click Run.
- As a result, the VBA code will calculate the total compound interest balance.
Things to Remember
- Use the shortcut Alt+F11 to open the VBA window as an alternative.
- Also, you can press Alt+F8 to open the Macro window to run it.
- You can open the Format Cells window by right-clicking on a cell and then selecting Format Cells.
Download Practice Workbook
You can download the practice workbook from here.
I hope that you were able to apply the methods that I showed in this tutorial on how to calculate compound interest in Excel in Indian rupees. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.