# How to Create a Monthly Accrued Interest Calculator in Excel – 4 Methods

The Accrued Interest is the payable or receivable interest on a loan or bond after a period of time.

## Accrued Interest

The formula to calculate the accrued interest is:

Accrued Interest = Loan Amount or Par Value âœ– ( Yearly Interest/365)âœ– Period of Interest Accrued

### Method 1 – Applying the Accrued Interest Formula

Provide Loan Amount, Annual Interest Rate, and Accrued Interest Period to find the accrued interest amount.

Accrued Interest = Loan Amount or Par Value âœ– ( Yearly Interest/365)âœ– Period of Interest Accrued
• Enter the formula into any cell.
`=PRODUCT(C3,C7,C9)`

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.

### Method 2 – Using the ACCRINT Function

The ACCRINT function returns the accrued interest of a loan or bond that pays interest periodically. The syntax of the function is

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

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.
`=ACCRINT(C3,C5,C7,C9,C11,C13,C15,C17)/12`

• Press ENTER to display the accrued interest.

### Method 3 – Counting Days Using the DAYS360 Function

The DAYS360 function returns the number of days between two dates. The syntax of the function is,

`Days360(start_date,end_date,[method])`

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.
`=DAYS360(C5,C7,FALSE)*C9*C13`

• Press ENTERÂ to display the amount.

### Method 4 – Finding the Year Fraction using the YEARFRAC Function

The YEARFRAC function calculates the fraction of days in a year. The syntax of the function is:

`YearFrac(start_date, end_date, [basis])`

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.
`=YEARFRAC(C5,C7,0)*365*C9*C13`

• Press ENTER to display the accrued interest.

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

## Related Articles

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF