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.
A coupon bond is a bond that pays interest before the bond matures. That interest is paid on a regular schedule and in Excel that can be either annually, which is one-time a year, semiannually for two times a year, or quarterly, which is four times a year. If you're thinking about investing in a coupon bond before its initiation date, that is the first day that the bond is available, then you can calculate the calendar date of the most recent coupon payment by using the COUPPCD function. To make that calculation, you need to know the following information.
The first is the settlement date and that is the date that you take possession of the bond. Then next you have the maturity date and that is the date that the bond investment ends and you'll receive your final coupon payment and any other money due to you. Then you have the coupon frequency and again that can be annual, which once a year, semiannual which is twice, and quarterly which is four times a year and then basis. Basis is the way that you calculate the number of days in a month and a year.
In North America, the standard is to assume the 30 day month, which means a 360 day year. That would be option 0 and that's the default if you leave this argument blank. In this case, I'm going to use the actual days. That is option 1, and actual counts as the name indicates the actual number of days in a month. So for example, January would have 31 and in a regular year, February 28 and then in a leap year February would have 29 days. So, with that information we can create our function. So, I will click in cell C9, type an equal sign, and then the function again is COUPPCD for coupon previous coupon date, then a left parenthesis, and start filling in the arguments.
Settlement is the settlement date that's in C3, maturity is the maturity date that's in C4, then coupon frequency is in C5 and then the basis is in C6. Type a right parenthesis. Make sure all our references look good. They do and press Enter. So, based on a settlement date of September 13, 2011, we see that the previous coupon date would have been December 31 of 2010 and the reason that occurs is because the coupon frequency is 1 or annual and the maturity date, which marks the end of the year of a bond investment, is December 31.
Now if I change the coupon frequency to 2 for semiannual and press Enter, we see that the previous coupon date changes to June 30th. And if I were to change the frequency to 4, then nothing should change because the previous coupon date would have been June 30th. But if we change the settlement date to 10/13, a date in quarter four and press Enter, then the previous coupon would've been paid in September of 2011. When you own a coupon bond, you will receive a payment every time a coupon comes due.
If you'd like to know when the most recent coupon payment occurred, you can use the COUPPCD function.
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.