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.


Download Workbook

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).

How to calculate accrued interest on fixed deposit in Excel

I will also show you how to calculate accrued interest on fixed deposit using ACCRINT function and 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.

How to calculate accrued interest on fixed deposit in Excel


Accrued Interest

The accrued interest is the interest that has accrued on a liability (such as loan) or other financial obligation but 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.


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.

How to calculate accrued interest on fixed deposit in Excel

 

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

How to calculate accrued interest on fixed deposit in Excel

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 deposit every time.

Read more: How to Calculate Accrued Interest on a Loan in Excel


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 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 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 and 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.

Read more: How to Calculate Accrued Interest on a Bond in Excel


Similar Readings


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.

Read more: How to Calculate Interest Between Two Dates Excel


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.

How to calculate accrued interest on fixed deposit in ExcelThis one is for the ACCRINT function.


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 out in handy to you. Lastly, if you have any remarks or suggestions, please leave them in the comment box.
Excel with us.


Further Readings

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo