How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)

Excel is an obvious tool for our day-to-day activities. We can largely bank on Excel for a myriad of purposes that involve calculations. For instance, Excel can show us how to calculate accrued interest on a fixed deposit within a specific timeframe. In this article, I am going to show you how to calculate accrued interest on fixed deposit in Excel. I will use three different ways to explain the cases.

Accrued Interest

The accrued interest is the interest that has accrued on a liability (such as a loan) or other financial obligation but is yet to be paid.
Accrued Interest can be of two types.
⇒ Simple Interest
⇒ Compound Interest
We will describe these types shortly in the following sections.

This is the dataset that I am going to use to explain how to calculate accrued interest on fixed deposit in Excel. In case of calculating the accrued interest manually, we have taken Fixed Deposit (P), Annual Rate (r), Interest accrued per Year (n) and Years elapsed (t).

I will also show you how to calculate accrued interest on fixed deposit using the ACCRINT function. For this, I have taken the Date of Deposition, First Interest Date, Settlement Date, Annual Interest Rate, Par value, Frequency/Payment Mode, Basis Days, and Calculation Method.
I will explain these terminologies in the upcoming sections.

3 Methods on How to Calculate Accrued Interest on Fixed Deposit in Excel

1. How to Calculate Accrued Interest on Fixed Deposit in Excel Manually

1.1. Simple Interest

Calculating accrued interest using simple interest is easy. The formula to calculate the simple interest is

I = P*t*r

Where,
P = Amount of Fixed Deposition
t = Years Elapsed
r = Annual Interest Rate.
In our dataset, we have set all these parameters. To calculate simple interest from this dataset,
First, select cell C5. Then write down the formula,

=C2*C3*C4

How to calculate accrued interest on fixed deposit in Excel

Then press ENTER. Excel will return the simple interest.

Here, I have multiplied Fixed Deposit (P), Annual Rate (r) and Years elapsed (t) to calculate the Interest Amount. The amount is $1400.00.

1.2. Compound Interest

Now I will discuss how to calculate accrued interest on fixed deposit in Excel using the compound interest formula. The formula is given below,

I=P(1+r/n)^(n*t)-P

Where,
P = The deposit amount
r = Annual interest rate
n = number of times interest accrued per time period.
→ If interest is accrued annually, n=1
⇒ If interest is accrued semiannually, n=2
⇒ If interest is accrued monthly, n=12
→ If interest is accrued daily, n=365
t = number of time periods elapsed
In our dataset, we have taken the time period as one year and interest is being accrued semiannually. So, n=2 in our case.
Now, to calculate the interest amount,
First, select cell C6. Then write down the following formula

=C2*(1+C3/C4)^(C4*C5)-C2

Then press ENTER. Excel will return you the compound interest.

Here, I have put the Fixed Deposit (P), Annual Rate (r), Interest accrued per year (n) and Years elapsed (t) in the compound interest formula and the output is $1,584.05.
Note: Notice that compound interest is greater than simple interest. That’s because of the compounding factor. In compound interest, we calculate interest considering the compounding of the deposit every time.

2. Calculating Accrued Interest on Fixed Deposit in Excel using ACCRINT Function

In this section, we are going to use the ACCRINT function and understand how to calculate accrued interest on fixed deposit in Excel. To use the ACCRINT function,
First, select C11. Then write down the formula,

=ACCRINT(C2,C3,C4,C5,C6,C7,C8,C9)

Here,
Date of Deposition: It refers to the date when you deposited the money. In our case, it is 1st January 2022 and it is in cell C2.
First Interest Date: It indicates the date when the first interest will be accrued. In our case, it is 1st January 2023 and it is in cell C3.
Settlement Date: This is the date when one will withdraw the deposit, that is, the time will end. It is 1st January 2024 and in cell C4.
Annual Interest Rate: This is the interest rate on an annual basis. It is 7%.
Par Value: The amount of money that you have deposited. For our case, $5000 is the amount and it is in cell C6.
Frequency/Payment Mode: This refers to the number of times interest is accrued per year. Since interest is accrued annually in our case, the value is 1 in cell C7.
Basis Days: This is an optional argument. This is the day count that we use to calculate interest. If we omit the argument, the base is set to 0. We have taken it as 0.
0- US (NASD 30/360)
1- Actual/Actual
2- Actual/360
3- Actual/365
4- European 30/360
Calculation Method: This is also an optional argument. When the date of settlement is later than the date of first interest, this logical value indicates how to calculate the total accrued interest. I have put the value 0 in cell C9 as I want the accrued interest from first interest to settlement.

Now press ENTER. Excel will calculate the interest amount. And it will be $700.00.

3. Calculating Accrued Interest on Fixed Deposit in Excel using ACCRINT Function and DATE Function

Now, I am going to show you that you can also calculate accrued interest if you do not have the Date of Deposition, First Interest Date, and Settlement Date. In that case, you just have to use the DATE function.
In this method, you will not have the dates in the dataset. To apply this method,
First, select C8. Then write down the following formula,

=ACCRINT(DATE(2022,1,1),DATE(2023,1,1),DATE(2024,1,1),C3,C2,1,0,1)

Notice that this formula is exactly like the one in method 2, just we have given the dates in Excel using the DATE function.

Then press ENTER. Excel will return the accrued interest for the period of 1st January 2022 to 1st January 2024.

Here, Excel calculates the Accrued Interest as $700.00 for a Par Value of $5000.00. The Frequency/Payment Mode is 1, that is, interest will be accrued annually.

Practice Workbook

Excel has made it easier for us to calculate accrued interest on a fixed deposit. Though it is easy, it needs practice. That’s why I have attached practice sheets for you so that you can internalize the methods that you find easy and useful.
This one is for calculating accrued interest using a compound formula.

This one is for the ACCRINT function.

Download Practice Workbook

How_to_Calculate_Accrued_Interest_in_Excel.xlsx

Conclusion

In this article, I have shown some of the easiest ways on how to calculate accrued interest on fixed deposit in Excel. I hope these methods will come in handy for you. Lastly, if you have any remarks or suggestions, please leave them in the comment box.

<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

1 Comment

K.RAJAGANAPATHY Apr 12, 2023 at 7:34 PM

sir, i have deposit 21,00,00,000 on 04-04-2022 with rate of interest 6.30%,interest mode is quarterly,i had received 3 quarterly interest amount(last interest date 04-01-2023),i want to sum accrued interest for 31-03-2023 how to arrive in excel sir ,(ans is 3810483.87112)please help me sir ([email protected])