While working in Microsoft Excel sometimes we need to determine some financial calculations. You can calculate the Accused, Fixed Deposit Interest using Excel. What if I offer you a calculator where you can provide your information and get the value within seconds? Today, I am sharing an FD interest calculator in Excel in this article.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Simple Steps to Create FD Interest Calculator in Excel
In the following, I have shared 3 simple and easy steps to create an FD interest calculator in Excel. Stay tuned!
Step 1: Create a Dataset with Proper Information
First, let’s create a dataset with some information to determine the fixed deposit interest and total amount.
- Here, we have “Deposit amount”, “No. of Period”, “Frequency”, and “Interest Rate”. Using these values we will determine the interest amount.
- Hence, we will create two cells with “Total Maturity Amount” and “Total Interest Amount” where we will collect the total amount.
- Now, suppose we have a scenario where we have a “Deposit Amount” of $50,000, “No. of Period” is 3 years and an “Interest Rate” of 6.2%. Using these values we will generate our total interest amount.
Read More: How to Calculate Interest Rate in Excel (3 Ways)
Step 2: Categorize Deposit Frequency
In this step, we need to categorize the deposit frequency. This can be days, months, or years. Let’s start.
- As the fixed deposit can be calculated over days, months, and years. Thus we will build a drop-down list for calculation advantage.
- For that, choosing a cell (D5), choose the “Data Validation” option from the “Data” feature.
- A new dialog box will pop up named “Data Validation”.
- From the box, choose “List” from the drop-down list and type “Months, Days, Years” in the “Source” section.
- To finish, click OK.
- Now coming back to the worksheet, our drop-down list will be ready in our hands.
- Similarly, we will make a drop-down list for the cell (C6) so that we can change the frequency for calculation.
- Choosing cell (C6), go to the “Data Validation” feature.
- In the same fashion, choose “List” in the “Allow” section and type “SimpleInterest, Monthly, Quarterly, Halfyearly, Annually” in the source section.
- Gently, hit OK.
- In summary, our second drop-down list will be ready.
Read More: How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)
Similar Readings
- How to Use Cumulative Interest Formula in Excel (3 Easy Ways)
- How to Create Money Market Interest Calculator in Excel
- Overdraft Interest Calculator in Excel (with Example)
- How to Make HELOC Payment Calculator Using Principal and Interest in Excel
- How to Create CD Interest Calculator in Excel (2 Easy Methods)
Step 3: Apply Formula to Calculate FD Interest
After all the prerequisites we have met now it’s time to calculate the final output, the end of the day that matters. We are prepared to apply a simple formula and get our desired result.
- Now, select a cell (C9) and apply the following formula down-
=IF(C6="SimpleInterest",(C4*(1+((C7*5)/((IF(D5="years",1,IF(D5="months",12,IF(D5="days",365))))*100)))),IF(C4="Annually",(C4*(1+(C7/100))^(C5*(1/(IF(D5="years",1,IF(D5="months",12,IF(D5="days",365))))))),IF(C6="Halfyearly",(C4*(1+(C7/(100*2)))^(C5*(2/(IF(D5="years",1,IF(D5="months",12,IF(D5="days",365))))))),IF(C6="Quarterly",(C4*(1+(C7/(100*4)))^(C5*(4/(IF(D5="years",1,IF(D5="months",12,IF(D5="days",365))))))),IF(C6="Monthly",(C4*(1+(C7/(100*12)))^(C5*(12/(IF(D5="years",1,IF(D5="months",12,IF(D5="days",365))))))))))))
Formula Breakdown:
- =IF(C6=”SimpleInterest”,(C4*(1+((C7*5)/((IF(D5=”years”,1,IF(D5=”months”,12,IF(D5=”days”,365))))*100))))) → Here, the IF function will display the calculated amount if the statement (C4*(1+((C7*5)/((IF(D5=”years”,1,IF(D5=”months”,12,IF(D5=”days”,365))))*100)) is true otherwise it will move to the next argument.
- Similarly, the IF function will run the argument for “Annually, Halfyearly, Quarterly, Monthly” selections from the drop-down list in cell (C6) until it finds the proper selection. After matching the selection from the cell (C6), it will calculate the amount matching the criteria from this argument (IF(D5=”years”,1,IF(D5=”months”,12,IF(D5=”days”,365))))*100) which explains if the value in cell (D5) is in years it will multiply the cell (C5) value with 1. Otherwise, move to the next loop search for the proper selection, and display output according to it.
- Therefore, select another cell (C11) and apply the below formula where we will get the total interest amount by subtracting the “Total Maturity Amount” from the “Deposit amount”-
=C9-C4
- In conclusion, we have got out the “Total Interest Amount” for our Fixed Deposit amount. You can also calculate the FD interest from the Excel sheet placing your desired values. Enjoy!
Read More: Simple Interest Formula in Excel (With 3 Practical Examples)
Things to Remember
- Don’t forget to put closing brackets at the end of the formula. Otherwise, the formula won’t work.
Conclusion
In this article, I have tried to cover all the methods to create an FD interest calculator in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- How to Create Prejudgment Interest Calculator in Excel
- How to Make TDS Late Payment Interest Calculator in Excel
- Create Post-Judgment Interest Calculator in Excel (With 2 Cases)
- How to Calculate GPF Interest in Excel (with Easy Steps)
- How to Split Principal and Interest in EMI in Excel (with Easy Steps)
- Perform Carried Interest Calculation in Excel (with Easy Steps)