## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

### Step 1: Create a Dataset with Proper Information

First, create a dataset with some information to determine the fixed deposit interest and total amount.

- As an exampel, this calculator will have “Deposit amount”, “No. of Period”, “Frequency”, and “Interest Rate”. These values will determine the interest amount.

- Create two cells with “Total Maturity Amount” and “Total Interest Amount” to collect the total amount.

- For an example of how the values will be filled, consider a “Deposit Amount” of
**$50,000**, “No. of Period” is**3 years**and an “Interest Rate” of**6.2%**.

**Read More: **How to Create an Accrued interest calculator in excel

### Step 2: Categorize Deposit Frequency

Before you can calculate the interest, you need to determine the deposit frequency and its duration.

As the fixed deposit can be calculated over days, months, and years, you can use a drop-down list to allow the user to pick the corresponding option and avoid confusion.

- Start with the cell next to the period number (
**D5**). - Choose the “
**Data Validation**” option from the “**Data**” toolbar on the top.

- 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. Each term should be separated by a comma. Spaces between terms are ignored. - To finish, click
**OK**.

- When you get back to the worksheet, you can see that the cell will contain a drop-down with the listed options.

- Make a drop-down list for the cell
**C6**to allow for a more customizable input for interest frequency calibration in the same way.

- For the allowed values, type “
**SimpleInterest, Monthly, Quarterly, Halfyearly, Annually**” in the source section. Make sure to remember the exact spelling you used for compound words. - Hit
**OK**to confirm.

- You can test the C6 drop-down or change the terms to be more human-readable.

**Read More:** Create Monthly Accrued Interest Calculator in Excel

### Step 3: Apply Formula to Calculate FD Interest

With the drop-downs and list terms ready, you can create the bulk of the calculator. This will be a single, quite large formula. If you’ve used the exact terms for the list units above, copy the following formula directly to cell **C9**:

`=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:**

- This is essentially a nested IF formula that returns a specific interest calculation based on the value find in C6.
**=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.

Hit **ENTER **to apply the formula and get the “**Total Maturity Amount**” based on the values you chose or put into the table.

The actual accrued interest (**C11**) is calculated by subtracting the “**Total Maturity Amount**” from the “**Deposit amount**” by using the simple formula:

`=C9-C4`

You can play around with different values.

**Read More: **How to Make TDS Interest Calculator in Excel

## Things to Remember

- Don’t forget to put
**closing parenthesis**at the end of the function. Otherwise, the formula won’t work.

## Conclusion

This is a relatively simple and brute-force implementation of the interest calculator. If you need to introduce additional interest variables (such as different frequencies), you will need to modify the IF function accordingly. Alternatively, you can replace the nested IFs with **SWITCH **functions, which can make the formula more readable overall.

**Related Articles**

- Create Late Payment Interest Calculator- Download for Free
- Create TDS Late Payment Interest Calculator in Excel
- Create a Post-Judgement Interest Calculator in Excel
- How to Create a Money Market Interest Calculator in Excel
- How to Generate GST Interest Calculator in Excel
- Make Service Tax Late Payment Interest Calculation in Excel
- Create a Simple Interest Loan Calculator with Excel Formula

**<< Go Back to Finance Template | Excel Templates**