Problem #1:

Problems 1 and 2 require the use of Excel’s “Solver” add-in. This may not be immediately available on your
Problem #1(a):
Problem #1(b):
installation of Excel. To get to “Solver”, you want to click on “Add-Ins” under in the “Developer” tab (or sometimes “Add-Ins” appears as a tab of its own). In “Add-Ins”, click the box to enable the “Solver Add-in”. “Solver” should then appear under the “Data” tab (probably on the far right). Alternatively you may be able to click on the Solver add-in through the sequence “File” “Options” “Add-ins”, then at the bottom, for Manage: Excel Add-ins, click “Go”. There you can click on “Solver” and “Ok”.
If you do not have the “Developer” tab, you can add it to your ribbon. From “File” or “Home”, click on “Options”, where you can click on “Customize Ribbon”, in which you can click on “Developer”.
A bond with face value $1496 and a term of 9 years pays quarterly coupons of 8% per annum. The bond is offered at a price of $2070. You are to enter the above values into a spreadsheet, along with

• an initial wild guess at what the yield would be, and
• a calculation of the bond price using your guess as the yield.
  (a) Use Excel’s “Solver” (which is different from “Goal Seek”) to solve for the actual yield that produces the correct bond price. Take a screen shot of your computer with “Solver” open showing clearly the entries that you put into Solver. Paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below.
  (b) What is the yield calculated by Solver?
  Upload Now
  Answer as a percentage correct to 2 decimals

Problem #4:

Referring to Problem #3 above, make a line graph of the bond value month by month over the life of the bond.
Manually set the minimum value on the vertical axis of your graph to be 0. You should see a “shark fin” or “saw tooth” pattern as coupons come off. Take a screen shot clearly showing your chart, paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below.
Problem #4: 选择文件 未选择任何文件
Upload Now
Save

Problem #5:

Consider this data file which lists fictitious companies and their fictitious credit ratings (that look like those of S&P) at the beginning and end of a year. Use Excel’s Pivot Table function (under the “Insert” tab) to create transition matrices. Transition matrices contain the percentages of firms that moved from one credit rating to another in a year.
The transition matrix should show the rating at the beginning of the year down the left edge, and the rating one year later along the top. Once the pivot table is created, drag and drop the rows and columns to put the ratings in the order AAA, AA, A, BBB, BB, B, CCC, Def. The entries in the table are to be percentages of the row total. Some additional notes on working with pivot tables are provided here.
(a) Use the Pivot Table to produce the transition matrix for Retail companies globally from all years (that is, from all countries together). What percentage of AA rated firms were still rated AA one year later?
(b) Use the Pivot Table to produce the transition matrix for Retail companies in England for all years together. Take a screen shot of the resulting pivot table. Paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below.
(c) Use a pivot table to determine the average and total assets of defaults in England for each industry category in 2004. In 2004 what was the average size of the defaulted assets in MM for defaulting companies in the Recreation industry?