Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
Bonds that pay interest prior to maturity are know as coupon bonds. When you purchase a bond you should know how far into a coupon period you are buying the bond so you can tell how long enough you'll have to wait to receive your first interest payment. You can calculate the number of days between the coupon beginning and settlement date using the coupon days between beginning settlement function, which is abbreviated COUPDAYBS. So to create the formula you need to now the following are four values. The first is the settlement date. That is the date that you take possession of the investment or you basically by in.
Then you have the maturity date, which is the date that proceeds from the investment are due to you. Then you have coupon frequency. Now I mentioned that coupons are payments for a bond. Coupons can occur within Excel either once a year, twice a year, or four times a year. So annually, semiannually or quarterly. And then finally we have the basis and the basis is the method that you use for counting days. In North America typically we use a 30- day month and with 12 months that means that we have at least in financial terms a 360-day year. That is option 0.
In this case I'm using option 1 which is actual. That means you count the actual days. So 28 days in February, 31 days in January, and so on. And with those arguments in place we can create our formula. So I'll click in cell C9, type in equal sign and then type coupdaybs, left parenthesis. And then the cell addresses for our arguments. So we have settlement date in C3, type a comma. Maturity date in C4, type a comma. Coupon frequency C5, type a comma, and then the basis and basis is in cell C6.
And in the formula autocomplete list you see the other options that are available to you. So with all those arguments in place I'll type right parenthesis to close out the formula and press Enter. When I do, we see that the days between beginning and settlement is 135 and the reason that the days between beginning and settlement are 135 it's because we have 31 days in January, 28 in February, 31 of March, 30 of April, and then the additional 15 in May which lead up to the settlement date.
If I were to change the maturity date from the 12/31/2012 to 09/30/2012, so I just type 9/30/2012 and press Enter, we see that the number of days changes to 45. In this case the coupon period now starts on April 1 based on our semiannual coupon frequency. So that means that there are 30 days in April and the 15 days in May leading to the total of 45. Calculating the number of days between the coupons periods beginning and settlement date gives you valuable information on how soon you'll start seeing your return on your investment.
Depending on your goals and your need for cash flow you should consider buying bonds with staggered coupons dates and schedules.
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.