In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
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 yield of a security with an odd, meaning unusual, first period. We're calculating yield, so we need to know the following things. In the worksheet you can see that we have the settlement date and that is the date that you actually take possession of a security.
The maturity date is when the investment ends and all monies and interest are due to you. Then the issue date. That is the date that the security was first offered. Then we have a first coupon date and that is the date when the first interest was paid to investors. Next have the rate that is the annual interest rates applied to the investment. Then we have price and price is the amount of money that you pay per $100 of face value for the bond. In this case, it's $104 which is higher than 100, but you have to remember that you're getting interest payments throughout the life of the bond so that is going to be a little bit higher than the face value.
Next, we have the redemption value and for $100 investment that's almost always going to be 100. Then next is frequency and frequency is the number of coupon payments that you get per year. That value can either be 1, meaning an annual payment, 2 meaning semiannual, or 4 meaning quarterly. In this case, we're going semiannual. Finally, we have the basis. Basis is how you count the days in a month and a year. The default is 0, which means that you assume a 30-day month for all 12 months and a 360-day year.
In this case, basis number 1 means that you count the actual days. So in a regular year, February would have 28 and in a leap year February would have 29. With all that information in place we can now create our yield formula. So I'll click in the cell C14, type in equal sign, and the name of the function we're going to use, which is oddfyield. So odd, f meaning first, and yield. Then a left parenthesis and we can start filling in the cells that contain the arguments.
So first is the settlement date that's in C3, comma. Maturity date is in C4, comma. Issue date C5 comma. First coupon date that is in C6, comma. The rate is in C7, comma. The price in C8, comma. The redemption value and again that's expressed per 100. That is in C9, comma. The frequency is in C10, comma and then the basis is in C11. Type a right parentheses. Make sure all of my arguments line up in the worksheet above.
They do so I'll press Tab and see that the annual yield for this investment is 5.79%. So, when you want to calculate the price of a bond with an odd meaning unusual first period, you can use the ODDYFIELD function.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth .
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.