Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
When you invest in a coupon bond, that is a bond that pays interest before maturity, you can evaluate your investment more accurately if you know the number of days in the coupon period. To find that value you can use the COUPDAYS function, which is short for coupon days. To make that calculation you need to know four things. The first argument for the function is the settlement date. That is the date that you take possession of the investment or basically when you buy it. Then we have the maturity date and that is the date that the bond investment ends.
Then we have the coupon frequency and that is the number of interest payments you get per year. So for the coupon frequency of 1 it's annual. You get one per year. The other possible values are 2, which is every six months or semiannual, and then 4, which is quarter. Any value other than the 1, 2 or 4 in that slot will generate a formula error. And then finally we have the basis and basis is how you count the days in the year. In North America the standard is to use a 30-day month and 12 months for 360-day year.
However, in this case we're going to be using basis number 1 which is actual. so that means in January we have 31, on most Februarys we have 28 days, leap years 29, and so on. So with all these arguments in place we can create our formula. So I click in cell C9, type equal and then our function is coupdays, then a left parenthesis, and I'll start filling in the cell references. Settlement is in cell C3, then a comma, maturity date in C4, then a comma, then we have our coupon frequency which is C5, then a comma, and the Basis which is in C6. Type a right parenthesis.
Just verify everything looks right. It does, press Enter, and we see that we have 365 days in the coupon settlement period. And that's because there are 365 days in the year 2011. So just to show the point of what happens if you're operating during a leap year I'll change the dettlement date from 5/ 15/2011 to 5/15/2012 and then press Enter. And when we do we see that the formula as expected returns a result of 366 because 2012 is a leap year and that means that there is an extra day in February.
One other thing you can do is change the basis. In North America the standard basis as I said is to use a 30 day month and 12 months for 360 day year, so if I would have to change the basis from 1 to 0 and press Enter, the days in the coupon settlement period change to 360. Knowing the number of days in a coupon period helps to evaluate your investment. Always be sure that you're using the correct basis. If you're not, the formula could give you an unintended result.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.