ACCRINT Function
Calculates the accrued interest for periodic interest-paying security
What Is The ACCRINT Function In Excel?
The ACCRINT function in Excel calculates the accrued interest for periodic interest-paying security.
Generally, a bond will have two different prices - clean and dirty price. The dirty price is the actual market price of the bond, including the accrued interest that has accumulated since the last coupon payment date.
The clean price, on the other hand, excludes the accrued interest from the bond’s price.
Accrued interest is the interest the bondholder has earned since the last coupon payment date but has not yet been paid. It is calculated as a fraction of the bond’s coupon rate and the days since the last coupon payment.
It is this very interest that can be calculated with the ACCRINT function in Excel. This article will guide you on the function, its syntax, and a couple of examples.
Key Takeaways
- The ACCRINT is a financial function that calculates the accrued interest between two different periods.
- The function takes several arguments, such as the security’s issue and maturity date, the first coupon payment date, the annual coupon rate, the number of coupons each year, and the day count basis.
- If the dates are mentioned in text format, the DATE function can be used to input the dates individually in the function.
- If the referenced dates are invalid, the function will return a #VALUE! Error.
- The function may also return #NUM! Error if the user inputs frequency or the basis argument beyond the acceptable values.
- The ACCRINTM is another function that looks similar to ACCRINT but, in reality, calculates the accrued interest for a security that pays interest at the time of maturity.
- In contrast, the ACCRINT function accepts the security’s issue and maturity date, the annual coupon rate, the par value, and the day count basis.
- In simple terms, the major distinction between both functions is that ACCRINTM calculates the accrued interest for a security that makes coupon payments directly at maturity. In contrast, the former calculates the accrued interest for regular coupon-paying securities.
Understanding the ACCRINT Function
The ACCRINT is categorized as a Financial function that calculates the accrued interest for interest-paying security.
The function takes several arguments, such as the security’s issue and maturity date, the first coupon payment date, the annual coupon rate, the number of coupons each year, and the day count basis.
The syntax for the Accrint Function is:
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method])
where,
- issue - (required) the settlement date or the date on which security is issued to the investor
- first_interest - (required) the date on which the security makes the first coupon payment
- settlement - (required) the settlement date of the security
- rate - (required) annual coupon rate of the security
- par - (required) the par value of the security
- frequency - (required) the number of times coupon payments are made each year
- basis - (optional) day count basis used for calculating the accrued interest on the security. The argument can accept different values, as illustrated below:
| Basis | Day Count Basis |
|---|---|
| 0 | US(NASD) 30/360 |
| 1 | Actual / actual |
| 2 | Actual / 360 |
| 3 | Actual / 365 |
| 4 | European 30 / 360 |
- calc_method - (optional) argument to calculate the accrued interest from first_interest date to settlement_date(value is equal to 0) or from the issue_date to the settlement_date(value is equal to 1)
The function calculates the accrued interest using the formula given below:
where,
- par - the par value of the security
- rate - the annual rate of the coupon on security
- frequency - number of coupon payments made each year
- Ai - number of accrued days for the ith quasi-coupon period within the odd period
- NLi - normal length in days of the quasi-coupon period within the odd period
- NC - number of quasi-coupon periods that fit in the odd period
Next, let’s see an example of how the function works using an example.
How to use the ACCRINT Function in Excel?
Suppose we purchase a bond from TreasuryDirect with a face value of $1,000 with an issue date of 1st January 2022, which settles on 14th February 2022 and has a maturity date of 31st December 2025. The annual interest rate on the bond is equal to 5%, which makes a semi-annual coupon payment.
We want to calculate the accrued interest for the first interest period ending on 30th June 2022.
The data looks as illustrated below:
We will calculate the accrued interest between the settlement date and the first coupon date. The formula will be =ACCRINT(C2,C4,C3,C6,C7,2,0,0), giving the accrued interest $5.97.
Let’s say the first coupon payment was made to the investors on 30th June 2022. You buy another lot of these bonds with a settlement date of 7/14/2022. The updated data looks as illustrated below:
There will be a bit of a change in the formula such that it becomes =ACCRINT(C4,C4,C3,C7,C8,2,0,0), to give the accrued interest between the previous coupon date of 30th June 2022 and the settlement date of 14th July 2022 as $1.94.
This way, you can easily manipulate between the dates and find the accrued interest for different periods of time.
Practical Examples of ACCRINT Function
This example won’t be that different from what you saw in the previous section, but this will provide you an opportunity again to look at a similar example in depth.
Suppose you purchase a bond from a person ‘X’ with a face value of $1000, a coupon rate of 6%, and a maturity date of December 31, 2023. The bond makes two coupon payments, i.e., on June 30 and December 31.
If the settlement date of the bond is 31st July 2023, then the accrued interest of the bond between the time period 31st July 2023 and 31 December 2023 can be calculated using the ACCRINT function.
The data looks as illustrated below:
If we are to calculate the accrued interest from the previous coupon date and the settlement date, then the formula will be =ACCRINT(C3,C3,C4,C6,C7,2,0,0), which gives the result as $5.
On the other hand, if the idea is to calculate the accrued interest from the settlement date, i.e., 31st July 2023 till the maturity date of 31st December 2023, then the formula will be =ACCRINT(C4,C4,C5,C6,C7,2,0,0), which gives us $25.
The numbers do look correct, i.e., $30 coupon payments are made on a semi-annual basis if we add both of those numbers. Another way to confirm this is to take the product of the rate and par value of the bond, which will give us $60.
Again, this is the annual coupon payment, which, if broken down, gives $30 semi-annual payments made on 30th June and 31st Dec, respectively.
ACCRINTM Function vs. ACCRINT Function
You might encounter another function while using the infamous ACCRINT in Excel spreadsheets. It looks and sounds very similar to our function but has an ‘M’ at its end.
The function we are speaking about is the ACCRINTM, which calculates the accrued interest for a security that pays interest at the time of maturity.
This means that the interest gets accrued over the entire tenure of the bond and is paid only after maturity. The zero-coupon bonds are a perfect example of a security that pays the accrued interest at maturity.
The syntax for the function is:
=ACCRINTM(issue, settlement, rate, par, [basis])
where,
- issue - (required) the issue date of the security
- settlement - (required) the settlement date of the security
- rate - (required) the annual coupon rate of the security
- par - (required) the par value of the security
- basis - (required) day count basis used for calculating the accrued interest on the security. The argument can accept different values as illustrated below:
| Basis | Day Count Basis |
|---|---|
| 0 | US(NASD) 30/360 |
| 1 | Actual / actual |
| 2 | Actual / 360 |
| 3 | Actual / 365 |
| 4 | European 30 / 360 |
Example Of Accured interest;
Suppose you purchase a zero coupon bond with the settlement date as 29th June 2025 which was issued on 12th April 2023. The maturity date of the bond is on 16th April 2027, and it pays a 5% annual coupon rate at the time of maturity.
The par value of the bond is equal to $1,000. The data looks as illustrated below:
To calculate the accrued interest that will be paid to the investor at the time of maturity, we will use the formula =ACCRINTM(C2,C3,C6,C5,0), which gives the result as
Thus, the accrued interest between the issue date and the settlement date is equal to $110.69. If someone were to purchase the zero coupon bond from you, they would have to pay the additional $110.69 of accrued interest during the transaction.
That’s how both ACCRINT and ACCRINTM functions differ from each other!
or Want to Sign up with your social account?