ODDFYIELD: Calculating the yield of a security with an odd first period

- [Voiceover] The bond-related formulas and functions in the previous chapters assumed that every period is the same length. For example, that coupon payments occur every three, six, or twelve months. Some bonds have irregular first or last periods, where the payment dates don't fit any of those patterns. So, Excel includes a set of functions that you can use to evaluate those securities. In this movie, I will show you how to calculate the yield of a security with an odd first period. My sample file is "OddFirstYield" and you can find it in the chapter six folder of the exercise files collection.

To calculate the yield of a bond or other security with an odd first period, we need to know a fair bit of information. First, in cell C-3 we need to know the settlement date, and that is the date that you actually take possession of the security. And it might be different from the date that the security was issued. Next is the maturity date, when the security and its original principle plus accumulated interest come due. Issue date is the date the security was created.

The first coupon date is the date of the first interest payment to you. Next is the rate, and that is the interest rate by which coupon payments are calculated. Price is the price that you pay per $100 of face value. And in this case, that is over the redemption value of 100, which is the principle that you get in return, or the face value of the bond, but that's because you're receiving coupon payments, as well. Frequency is the number of coupon payments per year. In this case it's semi-annual, or two.

And basis is how Excel looks at the calendar, I'll get into that in a moment. Let's go down to cell C-13 and create our formula. We're trying to calculate the yield of a security with an odd first period. So I'll take an equal sign, and the function I'll use is ODDFYIELD. And again, that's short for odd first period yield. Now we can just enter in our arguments in C-3. Settlement date, and then a comma; C-4 is the maturity date, comma; C-5, the issue date, comma; C-6, the first coupon date, then a comma; the rate in cell C-7, then a comma; price in C-8, comma; redemption value's in C-9, comma; and then frequency can be either four, two, or one.

Four for quarterly, two for semi-annual, and one for annual; any other value generates an error. In this case, we're getting our payments every six months, so cell C-10 contains a two, then a comma; and finally, we need to tell Excel how to look at the calendar. There are five different methods that you can choose, most of them make some sort of simplifying assumption, such as 30-day months, 360-day years, or both. In this case, I've selected to use option number one as our basis, which is the actual month length and actual calendar.

Because Excel does the math behind the scenes, we can let it do it, and we don't have to do the calculations by hand. So I'll click cell C-11, type a right parenthesis, and press Enter. And when I do, I see that the annual yield for this investment with an odd first period is 5.79%.