The Accrued Interest is the payable or receivable interest on a loan or bond after a period of time.
Download the Excel Template
Download the Template.
Accrued Interest
The formula to calculate the accrued interest is:
Method 1 – Applying the Accrued Interest Formula
Provide Loan Amount, Annual Interest Rate, and Accrued Interest Period to find the accrued interest amount.
- Enter the formula into any cell.
Formula Breakdown
Loan Amount or Par Value = C3 Yearly Interest = C7 Period of Interest Accrued = C9
- Press ENTER to find the monthly accrued interest on bonds or loans.
Read More: How to Create FD Interest Calculator in Excel
Method 2 – Using the ACCRINT Function
The syntax is:
The Arguments
Issue: The date when a loan or bond is issued.
First_interest: The date of the first interest payment.
Settlement: The end date of the loan.
Rate: Annual or Yearly Interest rate.
Par: The loan amount.
Frequency: The annual number of loan payments. 1 for Annual, 2 for Semi-annual, and 4 for Quarterly payments.
Basis: The basis is set to 0 if the argument is omitted. [Optional]
Calculation_method: It’s either 0 or 1 (calculates accrued interest from the First_interest date to the Settlement date). [Optional]
- Use the formula in a blank cell.
- Press ENTER to display the accrued interest.
Read More: How to Make TDS Interest Calculator in Excel
Method 3 – Counting Days Using the DAYS360 Function
The syntax is:
Multiplying the outcome by the Daily Interest Rate and Par Value will return the monthly accrued interest. Make sure the difference between the two dates is one (1) month.
- Enter the formula to find the accrued interest.
- Press ENTER to display the amount.
Method 4 – Finding the Year Fraction using the YEARFRAC Function
The syntax of the function is:
The returned value is multiplied by 365, Par Value, and Annual Rate to display the accrued interest. For the monthly accrued interest, the two dates must be one month apart.
- Enter the below formula in a cell.
- Press ENTER to display the accrued interest.
Read More: Create Late Payment Interest Calculator- Download for Free
Cross-checking the Accrued Interest Value
Two different values are returned for the monthly accrued interest because two different approaches were used:
a. Daily Interest Rate
It takes the annual interest rate and divides it by 365. This result is multiplied by the Par Value, Daily Interest Rate and 30 (days in a month).
b. Annual Interest Rate
The ACCRINT formula calculates the annual accrued interest and, by dividing it by 12, returns the monthly accrued interest.
Read More: How to Create TDS Late Payment Interest Calculator in Excel
Related Articles
- Create a Post-Judgement Interest Calculator in Excel
- How to Create a Money Market Interest Calculator in Excel
- How to Generate GST Interest Calculator in Excel
- Make Service Tax Late Payment Interest Calculation in Excel
- Create a Simple Interest Loan Calculator with Excel Formula
<< Go Back to Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!