Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The bond related formulas and functions in the previous chapters assume that every period is the same length. For example, that coupon payments occur every 3, 6 or 12 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 you can use to evaluate those securities. In this movie, I'll show you how to calculate the price for a security that has and odd or unusual first period. To do that we need to know the following things. The first argument for our function is the settlement date.
That is the date that you take possession of the security. And next, we have the maturity date and that is the last date of the investment when all monies are due to you. Then the issue date and that is the date that the security was initiated and again that can be different from the date you take possession of it. Then we have the first coupon date and that is the first day that interest is paid to you the bondholder. Then we have the coupon percentage and that is the amount of money paid based on your investment.
Then the percent yield and that is the amount that your investment appreciates over year. Then we have the redemption value and the redemption value is the amount of money you get for the security for $100 of face value. Next, we have frequency and that is the number of coupons paid per year. So that can be either annually which would be 1, semiannually which is 2, or in this case quarterly which is 4. and then finally, we have basis. Basis is the way that you count the number of days in a month and a year.
The standard which is option 0 is for a 30 day month and a 360 day year. In this case, we're going to use option number 1 and that is actual days. So for example, in February, you would count 28 days in a non-leap year and 29 days in a leap year. So now with all that information in place we can create our function. So click in cell C14 and type equal oddfprice and then a left parentheses and the function name means odd or unusual. f is first period and price means that we're trying to find the price that someone should pay for the security if it were priced at exactly market value.
So the first argument is the settlement date that is in cell C3, comma. Then the maturity date is in C4, comma. The issue date is C5 comma. The first coupon is in C6 comma. The rate is in C7 and the yield is in C8, then a comma. The redemption value per 100 is in C9, comma. Frequency and again that's the frequency of coupon and that is in C10, comma, and then we have the basis and that's in C11.
So now I'll type a right parentheses to close out the function and press Tab. When I do, we see that the price for this security should be $111.86. So when you want to calculate the price of a bond with an odd or usual first period, use the ODDFPRICE function.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64867 Viewers
80 Video lessons · 124403 Viewers
52 Video lessons · 60327 Viewers
59 Video lessons · 46153 Viewers