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

## 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.

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

The three entities are multiplied using the PRODUCT function.

• Press ENTER.

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

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 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.

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.

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

### 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.

For 11 months the amount is: \$4583.33.

### 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.

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

### 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.

• 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.

## 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 here.

## Related Articles

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF