How to Create an Accrued Interest Calculator in Excel – 5 Methods

 

Download Practice Workbook

Download the Excel workbook and practice.


What Is Accrued Interest?

The Accrued Interest is interest that has been incurred on financial transactions but has not yet been paid out. Investors regard it as profit.

Accrued Interest = Face Value * Daily Interest Rate * Days

Face Value                  = Initial Price of the Bond
Daily Interest Rate     =Annual interest/365
Days                            =Last Issue date- Settlement date


Method 1 – Calculating Accrued Interest Manually

You have a bond amount and an annual interest rate. To calculate accrued interest on this bond:

Steps:

  • Select D8 and enter the following formula.
=D6/365

D6 represents the Annual Interest Rate. 365 indicates the total days in a year.

  • Press ENTER.

accrued interest calculator excel

  • Go to D12 and enter the formula below.
=PRODUCT(D4,D8,D10)

The three entities are multiplied using the PRODUCT function.

  • Press ENTER.

Calculating Accrued Interest Manually

  • Alternatively, use the following formula in D12.
=D4*D8*D10

Using Simple Multiplication

The accrued interest for the amount of $1,00,000 on a 5% annual interest rate for a period of 45 days is $616.44.

Read More: How to Create Monthly Accrued Interest Calculator in Excel


Method 2 – Using the ACCRINT Function

The formula for the ACCRINT function is:

=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])

issue: the date when a loan or bond is issued.
first_interest: the date when the interest payment will first occur.
settlement: the date when the loan will be finished.
rate: annual or yearly Interest rate.
par: The bond/loan amount.
frequency:  the annual number of loan payments. 1 for Annual payments, 2 for Semi-annual payments, and 4 for Quarterly payments.
basis:  is set to 0 if the argument is omitted. [Optional]
0 Or Omiited- US (NASD 30/360)
1– Actual/Actual
2– Actual/360
3– Actual/365
4-European 30/360
calc_method: It’s either 0 or 1 (calculates accrued interest from first_interest date to settlement date). [Optional]

 

Steps:

  • Select D20 and enter the following formula.
=ACCRINT(D4,D6,D8,D10,D12,D14,D16,D18)
  • Press ENTER.

Inserting ACCRINT Function

The accrued interest amount is $5000 for 12 months from January 22 to January 23.

Interest is calculated by multiplying C10 by C12$5000.This value is divided by 12 as the basis is 0: $416.67. $416.67 is multiplied by 12 (months).

  • If you change the Settlement Date to 1 Dec 2022 this is the output.

Inserting ACCRINT Function to Create Accrued Interest Calculator in Excel

The interest amount is $4583.33 (11 months): $416.33 * 11 = $4583.33.

Read More: Create Late Payment Interest Calculator- Download for Free


Method 3 – Combining the ACCRINT and the DATE Functions

Use the ACCRINT and the DATE functions to change the date format.

Steps:

  • Select D20 and enter the following formula.
=ACCRINT(DATE(YEAR(D4),MONTH(D4),DAY(D4)),DATE(2022,2,1),DATE(2023,1,1),D10,D12,D14,D16,D18)

To use the cell reference apply the DATE and the YEAR, MONTH, and DAY functions.

  • Press ENTER.

Combining ACCRINT and DATE Functions

For 11 months the amount is: $4583.33.

Read More: How to Create FD Interest Calculator in Excel


Method 4 – Using the DAYS360 Function

The DAYS360 function considers every month as 30 days and the whole year as 360 days. The syntax of the function is:

=DAYS360(start_date,end_date,[method])

 

Steps:

  • Select D16 and use the following formula.
=DAYS360(D6,D8,FALSE)*D10*D14
  • Press ENTER.

Using DAYS360 Function

The accrued interest for 1 month from Dec 22 to Jan 23 is displayed.

Read More: How to Make TDS Interest Calculator in Excel


Method 5 – Utilizing the YEARFRAC Function

The YEARFRAC function represents the fraction value of the year calculating the difference between two dates. The syntax of the function is the following:

=YEARFRAC(start_date, end_date, [basis])

You must multiply the returned value by 365, Par Value, and Daily Interest Rate to display the accrued interest.

Steps:

  • Go to D14 and use the formula below.
=D12/365
  • Press ENTER.

Utilizing YEARFRAC Function

  • Select D16 and enter the following formula.
=YEARFRAC(D6,D8,0)*365*D10*D14
  • Press ENTER.

 


How to Calculate Accrued Interest on a Loan

Like in Method 2, you can use the ACCRINT function to calculate accrued interest on a loan.

Steps:

  • Go to D20 and enter the formula below.
=ACCRINT(D4,D6,D8,D10,D12,D14,D16,D18)
  • Press ENTER.

How to Calculate Accrued Interest on a Loan

Read More: How to Create TDS Late Payment Interest Calculator in Excel


Things to Remember

  • In the U.S., 30/360  is used for corporate and municipal bonds. U.S. Treasury notes and bonds use the actual/actual day count basis.
  • Only the ACCRINT formula accurately returns accrued interest.
  • The arguments for the first_interest date and settlement date should be valid dates.

Practice Section

Practice here.

Practice Section

 


Related Articles


<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo