Maruf Islam
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.
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
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.
- 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.
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
- 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 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.
Do check out our awesome website, Exceldemy, to find interesting articles on Excel.
