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 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.
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.