This is the sample dataset. It showcases Fixed Deposit (P), Annual Rate (r), Interest accrued per Year (n) and Years elapsed (t).
To calculate the accrued interest on a fixed deposit:
Method 1 – How to Calculate the Accrued Interest on a Fixed Deposit Manually
1.1. Simple Interest
The formula to calculate the simple interest is:
I = P*t*rP = Amount of Fixed Deposit
t = Years Elapsed
r = Annual Interest Rate.
- Select C5. Enter the formula.
=C2*C3*C4
- Press ENTER to see the simple interest.
The Fixed Deposit (P), Annual Rate (r) and Years elapsed (t) were multiplied to calculate the Interest Amount: $1400.00.
1.2. Compound Interest
- To calculate the accrued interest on a fixed deposit, use the compound interest formula:
I=P(1+r/n)^(n*t)-PP = 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
The dataset showcases the time period as one year and interest being accrued semiannually. So, n=2.
To calculate the interest amount:
- Select C6. Enter the following formula.
=C2*(1+C3/C4)^(C4*C5)-C2
- Press ENTER to see the compound interest.
The Fixed Deposit (P), Annual Rate (r), Interest accrued per year (n) and Years elapsed (t) were entered in the compound interest formula and the output is $1,584.05.
Note: Notice that the compound interest is greater than the simple interest.
Read More: How to Calculate Accrued Interest on a Loan in Excel
Method 2 – Calculating the Accrued Interest on a Fixed Deposit using the ACCRINT Function
- Select C11. Enter the formula.
=ACCRINT(C2,C3,C4,C5,C6,C7,C8,C9)Date of Deposition: refers to the date of the deposit. Here, 1st January 2022 in C2.
First Interest Date: indicates the date the first interest will be accrued. Here, 1st January 2023 in C3.
Settlement Date: is the date of the deposit withdrawal. Here, 1st January 2024 in C4.
Annual Interest Rate: is the interest rate on an annual basis: 7%.
Par Value: the deposited amount. Here, $5000 in C6.
Frequency/Payment Mode: the number of times interest is accrued per year. Here, 1 in C7.
Basis Days: (optional argument) the day count used to calculate interest. If we omit the argument, the base is set to 0. Here, 0.
0- US (NASD 30/360)
1- Actual/Actual
2- Actual/360
3- Actual/365
4- European 30/360
Calculation Method: (optional argument) the date of settlement is later than the date of the first interest. This logical value indicates how to calculate the total accrued interest.Here, 0 in C9 to calculate the accrued interest from the first interest to settlement.
- Press ENTER. Excel will calculate the interest amount: $700.00.
Read More: How to Calculate Accrued Interest on a Bond in Excel
Method 3 – Calculating the Accrued Interest on a Fixed Deposit using the ACCRINT Function and the DATE Function
You do not have the dates in the dataset.
- Select C8. Enter the following formula:
=ACCRINT(DATE(2022,1,1),DATE(2023,1,1),DATE(2024,1,1),C3,C2,1,0,1)
- Press ENTER. Excel will return the accrued interest for the period between 1st January 2022 and 1st January 2024.
The Accrued Interest is $700.00 for a Par Value of $5000.00. The Frequency/Payment Mode is 1:interest will be accrued annually.
Practice Workbook
Practice here.
Practice the ACCRINT function.
Download Practice Workbook
Related Articles
- How to Calculate Principal and Interest on a Loan in Excel
- How to Calculate Home Loan Interest in Excel
- How to Calculate Interest on a Loan in Excel
- How to Calculate Gold Loan Interest in Excel
- How to Calculate Credit Card Interest in Excel
<< Go Back to Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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])