How to Create an Accrued Interest Calculator in Excel

Download Practice Workbook

Download the Excel workbook and practice.

What Is the 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 the 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.

Method 2 – Using the ACCRINT Function

The formula 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, you will see 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.

Method 3 – Combining the ACCRINT and the DATE Functions

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.

Method 4 – Using the DAYS360 Function

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.

Method 5 – Utilizing the YEARFRAC Function

The syntax of the function is the following:

=YEARFRAC(start_date, end_date, [basis])

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

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

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.