Investment Portfolio: Salient Facts
	Face Value: Bonds A, B, and C	$5,000.00
	Coupon Rate: Bond A	3.75%
	Coupon PMT: Bond A
	Coupon Rate: Bond B	12.50%
	Coupon PMT: Bond B
	Coupon Rate: Bond C	2.25%
	Coupon PMT: Bond C	Variable
	Principal Recovery Rate	75.00%
	Bankruptcy Costs (% of Principal)	6.25%
	Mortgage Principal	$10,000.00
	Mortgage Rate	6.35%
	Mortgage Payment (annual)
	Portfolio Periods (Years)	6
	Market Interest Rate @ T0	4.25%
	Annual Mkt. Rate Inc. (bps)	15

Section 2 Excel Module (20 pts. total)

General Instructions:

• Round all dollar-figure and percentage answers to two decimal places where appropriate (e.g., $5.25, 1.38%, etc.)
  • Note: the template should be formatted this way already, so this hopefully wont be an issue.
• Use the given information to fill in the highlighted cells (same as usual).

• Perform all calculations from the standpoint of the bank where applicable (you know the drill).
• The cash flow schedule can be found on the Excel Module sheet.
• Upload your file using the following naming convention: ECN 322 Exam 2 Excel
  • No need to put your name in the filename

Scenario Analysis

A bank has the following four securities in its investment portfolio: three coupon bonds (bonds A, B, and C) and a mortgage. The following are the salient facts about each financial instrument:

• The principal for each bond is $5,000; the principal for the mortgage is $10,000.
  • Total portfolio value: $25,000
• The term of each security is six years; the coupons and mortgage payments are all paid annually
• Interest rates charged:
  • Bond A: 3.75%
  • Bond B: 12.50%
  • Bond C: variable rate 225 basis points over the market interest rate
    • The coupon payment for this bond will readjust annually based upon the change in market interest rates.
    • Example: if the market interest rate is 2.00% in year 1, the coupon rate for the payment in year 1 would be 2.00% + 2.25% = 4.25%. If the market interest rate were to increase to 2.25% in year 2, the coupon rate for the payment would jump to 2.25% + 2.25% = 4.50%
  • Mortgage: 6.35% (even annual payments)
• The initial market interest rate is 4.25%. This rate will increase annually by 15 basis points throughout the length of this analysis. Calculate the market interest rate for years 1 through 6 in row 9.

For this section, you will find the present value of this investment portfolio under two different scenarios: 1) a base case where all borrowers pay off their loans completely, and 2) a bond default scenario where Borrower B defaults on his loan in a particular time period. Each row is worth 1 point (i.e., all or nothing no partial credit).

Salient Facts (you know this drill by now)

• Calculate the coupon payments for bonds A and B (cells C8 and C10) as well as the annual mortgage payment (cell C17)
• Calculate the present value discount factor (PVDF) for each year (row 10) using the prevailing market interest rate for that particular time e.g., if the market interest rate in year 2 is 7.00%, calculate the PVDF for year 2 using that market interest rate; then, if it increases to, say, 8.00% in year 3, calculate the PVDF for year 3 using 8.00%.
• You will use the same PVDFs for each scenario.

Base Case Scenario

• Calculate the appropriate cash inflows and outflows (from the lenders perspective) for the face values and coupon payments for each bond assuming that all borrowers pay off their full borrowing obligations on time (rows 14-20).
  • Note: not every highlighted cell in this section will have a number in it i.e., dont be surprised if some highlighted cells are supposed to be blank.
    • Part of this exercise is testing you on whether you know which cells to leave blank (i.e., which time periods do not have cash flows for a given row).
• Calculate the PV of each cash flow (row 26) and the PV of the whole portfolio (row 28) for this scenario.

Bond Default Scenario

• Assume that the borrowers of Bonds A and C as well as the borrower of the mortgage do not default on their obligations; input those cash flows accordingly (rows 33-39).
• Borrower B defaults in time period 3, having met only his first two coupon payments. The bank takes Borrower B to bankruptcy court in time period 3, which takes that whole year (that is, the full length of time period 3) to work itself out. The bank is only able to recover 75% of the original principal of the loan (it is unable to recover any outstanding interest that Borrower B still owes), which the bankruptcy court has determined is made payable in time period 4. Input these cash flows accordingly (rows 34 and 37).
• The banks bankruptcy court litigation costs amounted to 6.25% of the original principal. Enter this figure in row 37 as a negative cash flow that the bank incurs in year 3.
• Calculate the PV of each cash flow (row 45) and the PV of the whole portfolio (row 47) for this scenario.

Note: pay very close attention to the figures that you calculate earlier on in the analysis. If your figures at the top are incorrect, they will cause your figures at the bottom to be incorrect, and you will lose credit in both places (even if the formula at the bottom is correct).