# How to Create a Monthly Accrued Interest Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

Accrued Interest refers to the payable or receivable interest on a loan or bond at a certain period of time. Accrued interest doesnâ€™t compound during the coupon period, so the interest occurs on a daily basis. Multiple formulas can find accrued interest and users can use them to build a calculator. Afterward, the monthly accrued interest calculator in Excel can find the accrued interest upon data insertion.

In this article, we demonstrate multiple variants of the monthly accrued interest calculator in Excel.

We kind of designed the Excel worksheets as a Template to simply find the monthly accrued interest upon entering the required data.

## Accrued Interest

As we know, accrued interest is the amount that is payable or receivable but not paid or received yet. For bonds, interest accrues from the time of their issuing. However, the interest gets paid as coupons at regular intervals (i.e., Quarterly, Semi-annually, or Annually). So, for the period, the unpaid accumulated interest is referred to as accrued interest. The fundamental formula of accrued interest is

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

## 4 Easy Ways to Make a Monthly Accrued Interest Calculator in Excel

Users can create an accrued interest calculator using multiple methods. These methods may be used as different variants of monthly accrued interest calculators in Excel. In this article, we use two approaches to build an interest calculator: Daily Accrued Interest and Annual Accrued Interest. A simple division (by 12) of annual accrued interest results in monthly accrued interest.

Go through the following section to find calculator variants for monthly accrued interest.

### 1. Applying Accrued Interest Formula

From the accrued interest formula, users need the 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
• Type the below 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.

### 2. Using 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: This is the date when a loan or bond is issued.

First_interest: This argument means 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 loan amount.

Frequency: This is the annual number of loan payments. 1 for Annual payments, 2 for Semi-annual payments, 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 First_interest date to Settlement date). [Optional]

• Write the below formula in a blank cell.
`=ACCRINT(C3,C5,C7,C9,C11,C13,C15,C17)/12`

• Hit ENTER to display the accrued interest as shown below.

### 3. Counting Days Using DAYS360 Function

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

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

Multiplying the functional outcome with Daily Interest Rate and Par Value will result in monthly accrued interest. Make sure the difference between the two dates is one (1) month.

• Type the latter formula to find the accrued interest.
`=DAYS360(C5,C7,FALSE)*C9*C13`

• Use the ENTER key to display the amount.

### 4. Finding Year Fraction by YEARFRAC Function

Excelâ€™s YEARFRAC function calculates the fraction of days to the year. The syntax of the function is,

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

Users need to multiply the returned value with 365, Par Value, and Annual Rate to display the accrued interest. And for monthly accrued interest, the two dates must be one month apart.

• Place the below formula in a cell.
`=YEARFRAC(C5,C7,0)*365*C9*C13`

• Tab on the ENTER key to display the accrued interest outcome.

## Cross-checking the Accrued Interest Value

You see, we get two different values of monthly accrued interest. Thatâ€™s before we used two different approaches. They are:

### a. Daily Interest Rate

It takes the annual interest rate and divides it by 365. So, the Annual Interest Rate becomes the Daily Interest Rate. Multiplying the returned days with Par Value,Â Daily Interest Rate and 30 (days in a month) result in the monthly accrued interest.

### b. Annual Interest Rate

In this case, the ACCRINT formula calculates the annual accrued interest and, by dividing it by 12, gives the monthly accrued interest.

## Conclusion

This article demonstrates multiple variants of the monthly accrued interest calculator in Excel. We hope this article sheds enough light to satisfy your cravings. You can use the attached workbook as a Template to conduct show operations.

## Related Articles

<< Go Back to Interest Calculator | Finance Template | Excel Templates

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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