Start learning with our library of video tutorials taught by experts. Get started
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 of the same length. For example, that coupon payments occur every 3, 6 or 12 months. Some bonds have irregular first or last periods though which means that the payments dates don't fit any of those patterns. For those securities Excel includes a set of functions you can use to evaluate them. In this movie, I'll show you how to calculate the price for security with an odd, meaning unusual, last period. For this calculation we need to no the following items. The first is the settlement date and that that is the date that you purchase and gain control of the security.
Next is the security's maturity date. That is the last day of the investment and the day that all proceeds and return of principle or due to you. Then you have the last coupon date. That is the last time interest is paid. Then, you have the coupon interest rate. That's the amount of money you get for each payment every time you paid interest. The annual yield is the interest rate applied to your principle. Redemption value is the amount of money you get back per $100 of face value for the bonds. It's almost always going to be $100.
Next, you have the frequency and frequency is the number of coupons that you receive per year. That can either be 1 for annual, 2 for semiannual, or 4 for quarterly. And then finally, there is basis. Basis refers to how you count the number of days and month in a year. Basis 0, which is the North American standard, assumes all month with 30 days long and that there is a 360 day year. In this case, we're using basis number 1 and what that means is they are counting actual days. So in a non-leap year February has 28 days and in a leap year February has 29.
So with all that information in place we can calculate the price. I'll click in cell C13, type an equal sign, and then I'll type in the name of the function we're going to use and that is as oddlprice. So that means odd, l for last, and we're calculating price. Type a left parenthesis and we can start filling in the arguments. First argument is the settlement date that's in C3, comma, maturity date C4, comma, last coupon date is in C5, comma, coupon interest rate C6, comma, annual yield C7, redemption value C8, comma, frequency is in C9, type a coma, and then we have the basis in the C10.
Then type a right parenthesis to close out the function, make sure that all my references line up, and press Tab. When I do, I see that the price for the security should $99.84 per $100 of face value. So would you want to calculate the price of a bond with an odd, meaning unusual, last period, use the oddlprice 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.