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.
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.
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
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,
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,
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
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,
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)
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
- How to Calculate Monthly Interest Rate in Excel (3 Simple Methods)
- Calculate Interest on a Loan in Excel (5 Methods)
- Simple Interest Formula in Excel (With 3 Practical Examples)
- How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
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,
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
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.
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.
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])