Md. Ahaduzzaman
In our everyday life calculating the accrued interest amount from the fixed deposit is a very common task. People nowadays prefer to make savings from their income in bank accounts. Most of them prefer to make a fixed deposit so that they can get profit from it in the future. Some of the interest rates are simple but some can be quarterly or half-yearly compound rates depending on the account type. So the calculation procedures may vary. This article will discuss how to calculate accrued interest on fixed deposit in excel.
📁 Download Excel File
Download the practice file from here.
Learn to Calculate Accrued Interest on Fixed Deposit in Excel with These 3 Methods
In this article, we will learn three easy and user-friendly methods to calculate accrued interest on fixed deposits in Excel. We will see how to calculate them with and without using the functions. Let’s assume we have some fixed deposit amounts on which a fixed amount of interest is applied. We want to calculate the amount of interest applied after a certain time. The sample dataset for our demonstration purpose is as follows.
1. Calculate Accrued Interest on Fixed Deposit & Download Excel Sheet
In this section, we will discuss how to manually calculate the accrued interest on fixed deposit in excel. We will use both the Simple Interest Rate and Compound Interest Rate for demonstration.
1.1 Simple Interest on a Loan
In this particular section, we will use only the simple interest rate to calculate accrued interest on a loan on fixed deposits in Excel. Therefore the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the formula box of cell C8.
- Following that, we write this formula in the formula box.
=C5*C6*C7
- Finally, we get the interest amount in cell C8.
1.2 Compound Interest on a Bond
Here we will discuss how to calculate accrued interest on a bond on fixed deposit in Excel using the compound interest rate. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we know the compound interest rate to be as this.
I=P(1+r/n)^(n*t)-P
🔨 Formula Breakdown
Here,
👉 P = Deposit Amount
👉 r = Annual interest rate
👉 n = The number of interest accrued in a time period.
👉 If accrued annually, then n=1
👉 If accrued semiannually, then n=2
👉 If accrued monthly, then n=12
👉 If accrued daily, then n=365
👉 t = Elapsed time period
- So we write this formula in the formula box of cell C9 and hit Enter.
=(C5*(1+C6/C7)^(C7*C8))-C5
- Finally, we get the result in cell C9.
📕 Read More: Create and Download Late Payment Interest Calculator in Excel
2. Applying ACCRINT Function for Accrued Interest Calculator
In this section, we will apply the ACCRINT function to calculate accrued interest on fixed deposit in Excel. Hence the breakdown steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C13 and type this formula in the formula box and press Enter.
=ACCRINT(C5,C6,C7,C8,C9,C10,C11,C12)
🔨 Formula Breakdown
👉 Here C5 is the Deposition Date.
👉 C6 denotes the First interest applied date.
👉 Settlement date is denoted by C7.
👉 C8 means the interest rate.
👉 C9 is the Fixed deposit amount.
👉 C10 indicates the yearly frequency.
👉 C11 is our basis which is 0 which denotes US(NASD 30/360).
👉 C12 is our calculation method.
- Finally, we get the result in cell C13.
3. Applying ACCRINT & DATE Functions Combined
In our final section here we will use both the ACCRINT & DATE functions together to calculate accrued interest on fixed deposit in Excel. So the stepwise procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell C10 formula box and write this formula and press Enter.
=ACCRINT(DATE(2022,1,1),DATE(2022,1,1),DATE(2023,1,1),C5,C6,C7,C8,C9)
- Here Date function gives returns us a particular date.
- Other arguments are the same as the previous method.
- Finally, we get the result in cell C10.
📕 Read More: 2 Approaches to Calculate Interest Between Two Dates in Excel
How to Calculate Monthly Interest with Formula in Excel
In this section, we will discuss how to calculate the monthly interest for a loan using the RATE function for the formula in Excel. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to cell C9 and type this formula.
=RATE(C6,-C7,C8)
🔨 Formula Breakdown
👉 Here C6 indicates the number of periods.
👉 -C7 means the payment that needs to be paid monthly. That’s why the (-) sign is added.
👉 C8 is the amount of loan taken.
- Finally, we get the result in cell C9.
📄 Important Notes
🖊️ When we use the Excel function we have to use the function arguments carefully.
🖊️ When using the compound interest rate we have to check whether accrued interest is monthly, yearly or quarterly.
📝 Takeaways from This Article
📌 We can use both simple interest rates and compound interest rates to calculate accrued interest on fixed deposit in excel when necessary.
📌 Also we can apply the ACCRINT function to calculate accrued interest on fixed deposit in excel.
📌 Apart from these methods we can use both the ACCRINT function and the DATE functions to do the same thing.
Conclusion
Here in this article, we have learned about three different methods to calculate accrued interest on fixed deposit in Excel. We can apply simple interest or compound interest rates on them. We can also use Excel functions like ACCRINT , DATE, etc. Users can either use or avoid the functions if they want. If you if any queries regarding this article feel free to put a comment in the comment box. The writer will try his best to come up with an answer. For further excel related problems and solutions you can visit our website www.Excelden.com.
Related Articles
- 4 Approaches to Calculate Home Loan Interest in Excel
- 3 Quick Steps to Calculate Credit Card Interest in Excel
- 2 Easy Ways to Calculate Gold Loan Interest in Excel
- 6 Steps to Create Car Loan Calculator in Excel Sheet
