Calculate Accrued Interest on a Bond in Excel – 3 Ways

Typically, in finance textbooks, bond valuation is discussed with a hidden assumption: that you are valuing the bond on a coupon payment date. If that is the case, then you can use the PV function to find the price (though there is another hidden assumption in there: that the yield curve is flat). The problem with this is that usually there are only one or two coupon dates per year. This means that, if you use the PV function, your valuation will be incorrect on the other 363 (or so) days each year.

Bonds prices are quoted “clean.” That is, they are quoted without accrued interest, which must be paid in addition to the quoted price. Think about it. If you own a bond, and some time has passed since the last coupon payment then you have earned some interest. If you sell the bond, you will want to get the interest that you have earned, and you can’t get it from the issuer. Therefore, the buyer of the bond must pay the accrued interest to you, and then they will get a full period of interest at the next coupon date.

How to Calculate Accrued Interest

Interest does not compound during the coupon period. Instead, it accrues evenly each day. For example, if a bond pays \$25 in interest each six months, then you would earn about \$0.1389 each day (assuming 180 days in six months). So, the concept is pretty simple. Mathematically, we would do the calculation as:

$${\rm{Accrued\, Interest}} = \frac{{\left[ {\frac{{{\rm{Coupon\, Rate}} \times {\rm{Face\, Value}}}}{{{\rm{Payment\, Frequency}}}}} \right]}}{{{\rm{Days\, in\, Payment\, Period}}}} \times {\rm{Days}}$$

In the example above, assume that we have a 5% coupon rate paid semiannually with a \$1,000 face value and 180 days in the six-month payment period. Further, let’s assume that 45 days have elapsed since the last coupon payment. In that case, the accrued interest would be:

$${\rm{Accrued\, Interest}} = \frac{{\frac{{0.05 \times 1,000}}{2}}}{{180}} \times 45 = 6.25$$

This is simply the amount of interest earned each day times the number of days.

Alternatively, we could restate our equation as:

$${\rm{Accrued\, Interest}} = \frac{{{\rm{Coupon\, Rate}} \times {\rm{Face\, Value}}}}{{{\rm{Payment\, Frequency}}}} \times \frac{{{\rm{Days}}}}{{{\rm{Days\, in\, Payment\, Period}}}}$$

In the above equation, the second term is known as the day count fraction:

$${\rm{Day\, Count\, Fraction}} = \frac{{{\rm{Days\, of\, Interest\, Earned}}}}{{{\rm{Days\, in\, Payment\, Period}}}}$$

and this is where things get a little bit more complicated. In both the numerator and denominator of the day count fraction we have to calculate the number of days between two dates. Primarily for historical reasons, there are several ways to do this. The particular rule used is known as the day count convention.

Day Count Conventions, Very Briefly

In a nutshell, we have to be able to count the number of days in a month and the number of days in a year. The obvious way to do this is to count the actual number of days, which gives us a day count convention known as “actual/actual.”

However, before computers it was a pain to count the actual number of days, so in some cases people agreed to assume that there were 30 days in every month (even in February). Further, since there were 12 months in a year, they agreed that there were 360 days in a year. This is the “30/360” day count convention. I’m leaving out some of the detailed rules but know that there is more than one 30/360 day count convention. The difference between them is in the details, and they often give the exact same day count.

There are other possibilities as well. We could have actual/360, actual/365, 30/365, or even 30/actual. The day count basis that is to be used will be specified in the indenture (i.e., the contract). In the U.S., we use 30/360 for corporate and municipal bonds, while U.S. Treasury notes and bonds use the actual/actual day count basis. The important thing for our purposes here is to understand that how you count the days will affect the day count fraction, and so it affects the amount of accrued interest.

The Example Bond

For our calculations below, I will use the Oracle 5.75’s of 2018 (CUSIP: 68389XAC9). Here is the relevant information (all data was retrieved from FactSet on 17 June 2015):

In the formulas below, all of the data will come from these cells in my sample spreadsheet.

Calculating Accrued Interest in Excel

There are at least three ways to calculate accrued interest in Excel while using the correct day count basis.

Calculate Accrued Interest Using the AccrInt Function

One would think that the AccrInt (which stands for accrued interest) function would do the job and that we wouldn’t ever want to do it any other way. Unfortunately, this is a weird little function that doesn’t do what you expect it to do. Instead of calculating the accrued interest on a bond at a specific point in time (as we did above), it calculates the amount of interest that has been earned since the day that the bond was issued. I have no idea who wants to know this, but somebody must.

Still, we can bend the AccrInt function to our will. First, the AccrInt function is defined as:

AccrInt(Issue, First_Interest, Settlement, Rate, Par, Frequency, Basis, Calc_Method)

This should all be familiar to anybody who has used the other bond functions in Excel. The one change that we need to make is to set the issue date to the date of the previous coupon payment (or to the issue date if we are in the first period). In addition, the first interest date should also be set to the previous coupon date (or to the actual first coupon date if we are in the first period). This tells Excel that the bond is in a regular first interest period, which is exactly what we want it to think most of the time.

Using the sample data for the Oracle bond from above, the formula is:

=ACCRINT(B5, B5, B4, B7, B8, B9, B10, B11)

which gives us a correct result of \$9.74. As a side note, if you just enter the data exactly as Excel asks for it, then you will get accrued interest of \$413.30. Again, that is the total amount of interest paid and accrued on this bond since it was issued, which is not what you generally want.

Calculate Accrued Interest Using the YearFrac Function

In the equation section of this article, I mentioned the day count fraction. This is the number of days between two dates as a fraction of a year. Luckily, Excel has the YearFrac function that can calculate the day count fraction using any of the supported day count conventions. It is defined as:

YearFrac(Start_Date, End_Date, Basis)

Note that Basis is the day count convention and is specified with an integer between 0 and 4. Most commonly, you will use either 0 (30/360) or 1 (actual/actual). In this example, the Oracle bond is a corporate bond so 30/360 is the appropriate day count basis to use but you should always check the details.

To use the YearFrac function, we simply give it the start date (which is the previous coupon payment date), the end date (the settlement date), and the day count basis. This will give us the fraction of a year that has elapsed, and we can multiply that by the annual coupon payment:

=YEARFRAC(B5, B4, B10)*B7*B8

The result is that the accrued interest is $9.74, exactly as we found before. The advantage of the YearFrac function, in my mind, is that it is dramatically simpler than the AccrInt function.

Calculate Accrued Interest Using the Days360 Function

For bonds that use the 30/360 day count convention, we can calculate the day count fraction using the Days360 function:

Days360(Start_Date, End_Date, Method)

This function will calculate the number of days between two dates using the 30/360 convention. So, given the number of days, it is a simple matter to calculate the day count fraction, and thus the accrued interest:

=DAYS360(B5, B4, FALSE)/360*B7*B8

Again, the result is $9.74 as expected.

Other Useful Functions

Excel has some other functions that can be very useful in calculating accrued interest. In particular, the CoupPCD function calculates the previous coupon date so that you don’t need to do it some other way. It is defined as:

CoupPCD(Settlement, Maturity, Frequency, Basis)

Also, the CoupDayBS function calculates the number of days from the beginning of the coupon period to the settlement date:

CoupDayBS(Settlement, Maturity, Frequency, Basis)

In addition, there are several other Coup* functions that may be useful.

The screenshot below shows my sample spreadsheet with all of the data and the results using the three methods that I have discussed:

I hope that you have found this tutorial on how to calculate accrued interest to be useful. Please feel free to contact me if you have any questions.