Math 2FM3
Problem #1:
Problems 1 and 2 require the use of Excel's "Solver" add-in. This may not be immediately available on your 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 $1078 and a term of 11 years pays quarterly coupons of 10% per annum. The bond is offered at a price of $801. 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?
Problem #2:
Consider an investment where the cash flows are:
-$1106.59 at time t =0 (negative since this is your initial investment)
$322 at time t = 1 in years
$485 at time t=2 in years
$439 at time t = 3 in years
(a) Use Excel's "Solver" to find the internal rate of return (IRR) of this investment. Take a screen shot showing Solver open with your entries for the function clearly visible. Paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below.
(b) What is the value of IRR found by Solver?
Problem #3:
A 3 year bond has semiannual coupons of 12% per annum. The continuously compounding yield is 17%. The bond has a face value of $100. You will be pricing the bond initially, and at future times throughout the life of the bond as it pulls to par at maturity, using the same continuously compounding yield throughout.
Since the yield is given with continuous compounding, the usual formulas will not work without changing the yield to the equivalent discrete frequency. Instead, string out the cash flows (each of the coupons separately plus the final redemption value) in the columns of a spreadsheet similar to the one shown here. Each row will compute the bond price at a different point in time:
You are to compute the value of the bond for each month in column B by adding up the outstanding cash flowsl along that row, each discounted from the time of its payment, back to the time at which you are valuing the bond.l For the values in the table, use an "if" statement to set the value to zero if the "Time of payment" is less than thel "Time by month", otherwise discount that cash flow using the continuously compounding rate. In order to copyl your "if" statement containing the formula to every cell in the table, place a "S" in front of the column letterl and/or the row number in the cell references as appropriate.
What is the initial value of the bond?
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 #5:
Consider this data file which lists fictitious companies and their fictitious credit ratings (that look like those ofl 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 Germany 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?