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.

Monthly Accrued Interest Calculator in Excel

 


Download the Excel Template

Download the Template.


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)

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 Create FD Interest Calculator in Excel


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

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

  • Press ENTER to display the accrued interest.

Read More: How to Make TDS Interest Calculator in Excel


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.

Read More: Create Late Payment Interest Calculator- Download for Free


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

Monthly Accrued Interest Calculator in Excel-Daily Interest


b. Annual Interest Rate

The ACCRINT formula calculates the annual accrued interest and, by dividing it by 12, returns the monthly accrued interest.

Monthly Accrued Interest Calculator in Excel-Annual Interest

Read More: How to Create TDS Late Payment Interest Calculator in Excel


 

Related Articles


<< Go Back to Finance Template | Excel Templates

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo