Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Monthly Accrued Interest Calculator in Excel

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.

Monthly Accrued Interest Calculator in Excel

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


Download Excel Template

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 calculate accrued interest 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)

Accrued Interest Formula

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.

Read More: How to Calculate Accrued Interest on a Loan in Excel (3 ways)


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

ACCRINT Function to be used as Monthly Accrued Interest Calculator in Excel

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

Read More: How to Calculate Accrued Interest on a Bond in Excel (5 Methods)


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 ValueDaily Interest Rate and 30 (days in a month) result in the monthly accrued interest.

Monthly Accrued Interest Calculator in Excel-Daily 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.

Monthly Accrued Interest Calculator in Excel-Annual 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.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo