The team projects are designed to reflect the potential professional environment in which students in finance find themselves after graduation.
In order for the team to be productive, the objectives of the team and the responsibilities of each member must be clearly defined. For each project, the team is encouraged to break the project down into distinctive tasks (gather data, data entry, data verification, regression analysis, interpretation, word processing, etc), to assign clearly specified tasks to each member, and to meet on a regular basis to ensure that these tasks are completed. Each member should be able to understand the project as a whole and how each component of the project fits into that team effort. Each team member should be able to explain clearly and succinctly why and how each task was performed and to understand why and how tasks were performed by other members of the team.
Working as team is drawing on the strengths of each of its members, and each member learns from the others. Tom writing the introduction, Anne writing the company profile, Mary doing the data analysis, John writing the conclusion, and meeting to assemble the four different files an hour before deadline is NOT an example of working as a team. More than an assignment grade, a well written report can become a very valuable example of your capabilities to a recruiter in a job interview.
- The Project Description specifies minimum requirements for a project to be acceptable for submission
- The raw score out of 40 then depends on how well the project presents and communicates key concepts and findings. Grades are thus granted on a "relative" basis.
- Target audience is a sophisticated individual but not a sophisticated investor (non-finance background); don't use financial or statistical terms and tools without explanation.
- Use standard format, double spaced, 12 point font, and 1 inch margins.
A group submission consists of:
- A typed report together with an executed group assignment contract which is handed in to your instructor by the date and time specified in the course schedule. The report consists of the cover page, executive summary, body, and all appendices, in that order. Each section of the paper should have a heading; there is no need for a table of contents. The last Appendix is the original Value Line Sheet.
Print the report on standard weight paper and staple it in the upper left corner.
DO NOT bind the report; DO NOT place it in a laminated folder; DO NOT add fancy covers or other paraphernalia.
- ONE Word file (*.doc not Word 2007 format) that is the ENTIRE written report, and
- ONE Excel file (*.xls not Excel 2007 format) that is all of your data and statistical work, submitted electronically.
Electronic submission is activated from the Project Information page of the course website.
DO NOT lose or delete your files. Failure to submit the report in electronic form will result in a reduced grade for the report.
The project will not be accepted or graded unless it includes
1. The engagement contract in the form of the original FINRA Bond Quote sheet that you were given as the project assignment,
2. A group assignment contract signed and dated by each member of the group.
Remember that your target audience is a potential investor; a well educated person sophisticated enough to want to manage his own investments but not trained in finance. This means you will need to explain the meaning of financial terms which might not be familiar to the general population. Try reading your report to a colleague in another college. Whenever you need to explain something verbally this is an indication that your report is not clear.
Remember that a financial report is not a mystery novel. Although you begin by gathering data, reducing this data into information, and then finally coming to a recommendation, the report should begin with your recommendation, why you came to that conclusion, and then presentation of your supporting evidence.
Finally, readers of your report expect value added. You are not providing a search engine report of the work of others with hundreds of footnotes or a clipping service. A report is not a compilation of information but the presentation of that information in a smooth coherent report. Put the pieces together, judge, draw conclusions. Give the reader the benefit of your insight and judgment. Then give the reader a professional, easy-to-read presentation.
Make sure that embedded graphs are well labeled and large enough to be comfortable to the reader. The fonts within the graph should match the surrounding text. Place graphs within the text that discusses what it means to the investor. Then put a full page version of the graph in the appendix. Tiny graphs surrounded by a sea of white space irritate the reader almost as much as the does the calisthenics workout that results from the constant suggestion that he “see appendix x”.
The following outlines the requirements of the paper. Make sure that you don't lose points unnecessarily by failing to follow simple instructions.
Along with concepts covered in class, specifics shown at the evening set-up meetings, and reminders/examples posted on the course website, you should have adequate opportunity to create a fine project. Bear in mind that the page suggestions in each section, with the exception of the Executive Summary, are guidelines. If your Introduction runs 2˝ pages, then examine it for wordiness, but don’t randomly cut sentences just to get it to two pages. If your Introduction runs 1˝ pages, then
make sure you are completing the task, but don’t arbitrarily add words to expand it. In the sections of the report that require output from Excel, the page guidelines are for the written discussion of the tasks in this section and do not include the necessary pages of output from Excel. Those Excel pages should be appendices to your report.
When you are asked to include specific statistics then an embedded table is a good way to highlight these statistics. The text of your report can then discuss what these statistics mean to the reader as an investor rather than being a listing of key statistics.
Information is derived from sources. These sources must be appropriately footnoted and referenced throughout the report. Footnotes should be at the bottom of each page where citation is made. The point to a reference is that if the reader questions the information, the reference gives sufficient information to go directly to the original source. A word of caution, information on a web page is as durable as the server it is stored on, print sources are more lasting. We will use the Chicago/Turabian style of footnotes throughout the paper. This is a professional report: use references not a bibliography or works cited page.
(1 page - 5 points)
These points are allocated for the overall quality of the report. This is the category under which you will see deductions for incorrect/incomplete citation, poor coordination between sections, and lack of professionalism in presentation.
- Make sure that your title page identifies
- The bond you are analyzing
- The Company Name
- The names of all the members of the group (correctly spelled). These are the people who will get credit for the report submitted.
- Your name of your professor (correctly spelled), and
- Date of submission
(1 page - 5 points)
The executive summary is designed for someone who reads only the first page of the report. Since he is only reading the first page of the report the executive summary should never be more than one page. It should present your principal findings and recommendations in a straightforward and easy-to-read summary. The Executive Summary is the first page of the report (beneath the cover page), but it is the last page you should write.
(2 pages - 5 points)
You are analyzing a bond of a corporate issuer. The company has borrowed money and the bond is evidence of this indebtedness. If you invest in the bond, you need to think like a lender. Although you may get capital gains or losses in the bond’s value due to changes in market interest rates, the bond itself is a set of cantractual promises to pay you periodic interest, and the principal back at maturity.
Unlike investing in the equity of the firm (ownership), you have little chance of hitting a homerun (as you may with ownership) but still have the chance to strike out! This analysis demands you know your borrower, its business, its prospects for success or failure, and the terms of the contract (bond indenture agreement). A thorough review of the financial health of the company responsible for making the promised payments under the bond is essential in the analysis of a company’s debt.
You should provide the name and principal characteristics of the company and the environment in which it operates; its relative size, market share, asset value, and importance in the industry, principal difficulties it must overcome and opportunities it might seize over the next year or so. Remember to cite your sources whenever necessary and to provide a complete list of references by using formal footnotes.
Market share is commonly assessed by comparing the sales of the subject to industry total sales. Does the company have significant concentration in the industry?
Assets = Liabilities + Owners’ Equity.
Does your company operate with a lot of borrowed money? That may enhance returns to owners, but a bond holder is a lender. More debt may make your position riskier.
Examine things like the company’s leverage and coverageratios. Look at the changes in these ratios as well as competitors’ ratios. Source documents for things like financial measures are the company’s quarterly and annual reports, file with the SEC’s EDGAR database.
Bond Issue Characteristics
(1 page - 5 points)
This section should provide the basic features of the bond (such as coupon rates, payment dates, and maturity) as well as the relevant terms of the indenture agreement (such as, secured/unsecured, protective covenants, call features, dates, and prices, conversion features, etc.).
You should also provide at least two ratings (Moody's and S&P's for example). Explain why they might differ. Mention any past changes in these ratings and the main reasons for these changes.
Remember to cite all sources.
The bond indenture agreement (or prospectus) is a filing available through the SEC’s EDGAR database. Access it and READ IT. Things like “protective covenants” are explained in the filing, and address features that are contractual that protect the bond holder (the investor). They require the bond issuer (the borrower) to do certain things, or not do certain other things that would threaten the security of the loan. For example, is the bond covered by a sinking fund and if so, what does that mean? Is your bond callable but non-refundable, and if so, what does that mean? Must the company maintain some standards of financial health, and if they do not, what remedies are the bondholders granted?
Yields and Duration
(2 pages - 8 points)
This section is one of the core components of this project, and is weighted accordingly. You are not expected to teach duration and convexity calculation, but you do need to have a clear understanding of the manipulations you are asked to perform in order to interpret and explain it to the target audience. Do not feel that you are explaining what your calculations mean by explaining how you manipulated numbers in making the calculations.
You should provide the nominal and current yield, yield-to-maturity, yield-to-call, and conversion particulars (if applicable) as well as the duration of the bond issue. The text should provide an interpretation for each value (what does this mean for the prospective bondholder).
Remember to include accrued interest and partial periods in your calculations.
The appendices should include all source data and the calculation details for each value. Include the spreadsheet printout. A financial calculator should be used to verify your results, but is not to be used to generate your results.
As you discuss the yield to call, remember that there are a variety of call dates and perhaps even different call prices. Include a table of the call schedule in the text. An easy way to present complete information to a potential investor is to calculate the yield to each call date, and present it in the same table. The lowest possible yield calculated is then referred to as the yield-to-worst.
A note of caution is due here: no amount of calculation makes up for a lack of common sense. Suppose that you calculate the yield-to-maturity of your bond as 9%, but the yield-to-call, just five years earlier is 22%. Other bonds, very similar to your bond, but maturing on or near your call date, show a yield-to-maturity of only 11%. Do you really think your bond is going to be called on that date? Remember, a call is an option of the issuing firm, not a requirement. It assumes that the firm will have the money to payoff the bonds early, but if they are strapped for cash, the call may not occur. Markets have a way of telling you things, but you do need to know how to listen to what they are saying.
(1 page - 4 points)
Illustrate how sensitive your bond is to the interest rate environment. Provide price-yield curve and indicate the modified duration at the current yield. Your plot should clearly show the calculated price of the bond at different market yields in a range from 1% to 20% as the convex curve. Use some common sense; if your bond is yielding 23% then show yields to 35 or 40% so that you can see both the price-yield curve and the tangency line.
The straight line tangent to the function at the current Yield to Maturity is constructed by plotting the predicted price P' of the bond using:
P' = (1 - ΔYld * Modified Duration ) * Price
Appendices should show this graph and its driving data filling an entire page, a thumbnail sketch of it within the written work to illustrate duration is prudent.
(1 page - 8 points)
This is the other core component of the project. It requires concise, constructive writing to accomplish the goal in the limited space suggested.
Evaluate the investment outlook for the bond in the current interest rate environment. What is the probability that the bond will be called?
Make a recommendation (sell, hold, buy, buy only for aggressive investors) for the bond you analyze. Your recommendation should be based on the bond's different yields, duration, company risk, default risk, interest rate environment, and a comparison of the expected yield with that of other similarly rated bonds in the market.
Use the bond search function at the FINRA site and try to find bonds as close in major characteristics to your bond as you can.
If their yields are very different, this may be indication of something very important in your bond.
If they are all very close, how would you view the attractiveness of your bond relative to the others?
Bonds are not just yield comparisons only, remember, you researched the issuer enough to form an opinion on the company’s health and future prospects.
Integrate this information into your yield comparisons as you make the buy, hold sell recommendation.
Data and calculations should be in the appendix, and presented in user-friendly form. Don't just hit the print button. Spend some time making sure that the appendices are properly labeled and that the font is consistent and easy to read. Full page graphs and minimizing the white space on pages adds value, the number of pages does not. If you want to extract key components of your data and present it as a small table or thumbnail sketch within the body of your paper, it is your choice so long as it improves the presentation. This does not relieve you of the need to have the full scope of your quantitative work presented in the appendix.
Appendices should be consecutively labeled and presented (Appendix A - Yield-to-Maturity, Appendix B - Yield-to-Call, et cetera.)
Get an early start, consistent effort and good luck!
- Sources of information (Library)
Business Library provides an extensive list of web sites of Investment data and information.
- Sources of Information (Internet)
Sample Bond Calculations
Sample Bond Calculations for
Polymer Group Inc
February 1, 2019
The sample Bond Calculation provided is a pdf. The comments are there to help you, use them. Most of what you see in the Bond Project Excel requirement is included - in agonizing detail - in Models 15.1 & 15.2 in Oltheten & waspi 2012. The templates are available at Excel
- In the spreadsheet specify the bond you are analyzing: the Issuer, the coupon rate, and the maturity date. Independent data is coded blue.
- To calculate prices, yields, and durations we need the last date on which the bond traded, the Settlement Date, and the price on that date. Everything else is calculated by the spreadsheet from this data. When you are finished verify your calculations with your business calculator, but do not use the calculator as a source.
- Use the built in functions to calculate the days to accrue, to the next coupon date, and the total days in the coupon period. This information is used to calculate the accrued interest and the partial period for your Net Present Value calculations. Test your spreadsheet by changing the settlement date and coupon rate; all the relevant results should change automatically.
- The NPV formula (build your own rather than looking for an excel preprogrammed function) should reference the Yield to Maturity (Cell F15 in the example).
- Use the spreadsheets from chapters 14 and 16 so that you can use the formulae you developed and tested in the spreadsheet exercises. Test your spreadsheet on the bond in the bond project example so that you have the formulae adjusted for the partial period.
- Calculate the Yield to Maturity
- Under [Tools] choose [solver]
- If there is no [solver] under tools then go to [Tools] [Add-Ins...] and make sure solver add-in is clicked.
- Ask solver to adjust the total Net Present Value (F19) to make it equal to the invoice price of the bond by adjusting the Yield to Maturity (F15).
- Verify that the Duration you calculate equals the duration calcualted with the =DURATION() function in excel.
- Calculate the Yield to First Call the same way.
- Note that there may be a discrepancy between the duration calculated the long way (from your table) and the duration calculated by the excel duration function. This is because the duration function in excel assumes a redemption value of 100.00 and so will be accurate only when the call premium is 0%. (Moral of the story: ALWAYS verify formulae - no matter who gives them to you.)
- Ref chapter 15 Duration
- Ref chapter 13 Buying and Selling Bonds
- Ref chapter 19 Corporate Bonds
- Useful Excel functions include
- =COUPNCD (next coupon date),
- =COUPNUM, (number of coupons remaining),
- =COUPDAYSBS (days from last coupon to settlement date),
- =COUPDAYSNC (days from settlement to next coupon),
- =COUPDAYS (days in the coupon period).
- You should look at the list of financial functions provided by Excel. If you have no financial functions use Tools/Add-ins and make sure Analysis ToolPak and Solver Add-in are turned on.
- A note on format: Excel automatically reduces the fraction so that 30/180 comes out as 1/6. The only way to circumvent this is to use the Format Cell Custom and specify # ???/180.
References are from Elisabeth Oltheten & Kevin G. Waspi, Financial Markets, A Practicum,GRT, 2012)
You are required to include in the appendix of your report:
- The FINRA printout. Double check that you have the right bond by verifying the maturity date, the coupon rate, and the call schedule against the FINRA printout. Analyzing the wrong bond will get you 0/40.
Projects without the FINRA printout attached will not be accepted.
Non-callable bonds and bonds with anything other than semi-annual payments will not be accepted. So please double check your bond as soon as you receive your printout.
Use the last transaction price and date information from the FINRA Bond Information Printout.
All bonds are callable and pay semi-annually. If you get a bond that is not callable and pays annually, quarterly, or monthly then throw it away. Get another bond from your professor.
All graphs included in the report must be generated from data and your own work. Simply pasting graphs from Yahoo!, CNBC, or the Federal Reserve is not acceptible.
Please note that there is no guarantee as to the availability of corporate information. It is the responsibility of the group to determine whether or not there is enough information on the issuing company to complete the project before it is too late to switch to another bond.