3 Ways to Calculate Accrued Interest on Fixed Deposit in Excel

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.

Dataset for accrued interest

method

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.

Approach

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.

Multiplying cell values for applied interest

Approach

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.

Calculating compound accrued interest

πŸ“• Read More: Create and Download Late Payment Interest Calculator in Excel

method

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.

Using the ACCRINT function for applied interest

method

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.

Using the ACCRINT & DATE functions for applied interest

πŸ“• 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.

Using the RATE function for monthly interest


πŸ“„ 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

(Visited 24 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo