spreadsheet assignment problems in the attachment.

A 180 Day Treasury Bill is priced at $991,850 (Assume $1,000,000 par value). What is the discount Yield? YTM equivalent (Bond equivalent) yield? Effective Annual Rate? Discount Yield = YTM equivalent Yield = Effective Annual Rate GRADE out of 5 You have purchased a bond with five years to maturity for $1,000 par value. The bond pays a 7% coupon (paid semi-annually). You expect to hold the bond until maturity. Calculate your expected total return if you re-invest all coupons at 5 percent. Total Return = A $10,000 two year Treasury Note carries a fixed 7 percent coupon rate and currently sells for $9,750. Assume you can buy the bond and strip the coupon and principal payments and sell them as individual zero coupon investments. Given the zero coupon rates below, what is value of the bonds payments? Maturity 6-month 1-year 18-month 2-year Zero coupon yield 5.80% 6.20% 6.80% 7.90% Value = GRADE out of 10 A 30 year, $200,000 mortgage has a 6.4% rate (assume annual payments to keep things simple). Historically 30 year mortgages were expected to mature in 8 years (borrowers would refinance, sell etc.). What is the expected duration of this loan? What is the modified duration? Using the loans duration, how much would the value of the loan change if rates rise 1% (Assume the expected maturity remains at 8 years)? What if they fall 1%? Mortgage Rate Years PMT Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 GRADE Current 200,000 6.40% 30 $15,157.03 Beg Bal 200,000 197,643 195,135 192,467 189,628 186,607 183,392 179,973 176,334 172,462 168,343 163,960 159,296 154,334 149,054 143,437 137,460 131,100 124,333 117,134 109,473 101,322 92,650 83,422 73,604 63,158 52,043 40,217 27,634 14,245 Duration Chg Value Rates Up 1% Chg Value Rates down 1% PMT $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 $15,157.03 out of 20 Int $12,800.00 $12,649.15 $12,488.65 $12,317.87 $12,136.16 $11,942.83 $11,737.12 $11,518.24 $11,285.36 $11,037.57 $10,773.93 $10,493.41 $10,194.94 $9,877.36 $9,539.47 $9,179.94 $8,797.41 $8,390.39 $7,957.33 $7,496.55 $7,006.28 $6,484.63 $5,929.59 $5,339.04 $4,710.69 $4,042.12 $3,330.77 $2,573.88 $1,768.56 $911.70 Principal $2,357.03 $2,507.88 $2,668.39 $2,839.16 $3,020.87 $3,214.20 $3,419.91 $3,638.79 $3,871.67 $4,119.46 $4,383.10 $4,663.62 $4,962.09 $5,279.67 $5,617.56 $5,977.09 $6,359.62 $6,766.64 $7,199.70 $7,660.48 $8,150.76 $8,672.40 $9,227.44 $9,817.99 $10,446.35 $11,114.91 $11,826.27 $12,583.15 $13,388.47 $14,245.33 End Bal $197,642.97 $195,135.09 $192,466.70 $189,627.54 $186,606.67 $183,392.47 $179,972.55 $176,333.77 $172,462.10 $168,342.64 $163,959.54 $159,295.92 $154,333.83 $149,054.16 $143,436.59 $137,459.51 $131,099.88 $124,333.24 $117,133.54 $109,473.06 $101,322.30 $92,649.90 $83,422.46 $73,604.47 $63,158.12 $52,043.21 $40,216.94 $27,633.80 $14,245.33 ($0.00) The following re-pricing information is available for First Simple Bank's Balance Sheet. First Simple Bank (Report of Condition) Securities Deposits 7 day Treasury Bills 100 DDA's 90 Treasury Bills 150 MMDA's** 360 day Treasury Bills 300 Other* 10 Year Treasury Notes 500 Coming Week 8 to 30 days Loans 31 to 360 Days Floating Rate 500 More than 1 year Non Floating Loans* Coming Week 400 Equity 8 to 30 days 300 31 to 360 Days 900 More than 1 year 2500 Non Earning Assets Total Assets 700 1250 500 1225 1100 750 750 625 6275 Total Liabs and Equity 6275 * Represent principal amounts either repricing or maturing for a given time period ** Assume immediately repriceable Fill in the following GAP table and calculate the periodic GAP and Cumulative GAP for First Simple: (Assume MMDAs reprice immediately) 1 week 30 Days 31 to 360 days More than 360 days Total Assets Securities Loans RSA Deposits DDAs MMDAs Other RSLs Per GAP Cumulative GAP GAP Ratio (Cum. GAP)/EA What risk does the bank face? What will happen if rates fall? If Rates Rise? What can the bank do to reduce this risk? Answer Here: GRADE out of 20 GBB Bank and Trust's Board of Directors has asked you to evaluate the bank's interest rate risk. In particular, the board is concerned about how changing interest rates will impact the market value of the bank's equity (its economic value of equity EVE). To address this, you will produce a DGAP analysis of the bank. The bank's current balance sheet (book values) follows: Details about GBB's assets and liabilities: (all dollar values are in $000's) 1. Treasury securities have a face value of $250,000, and average coupon of 6% (paid semi-annually), a maturity of 4.5 years, and the current yield to maturity is 4.0%. 2. Business loans have a face value of $150,000, an average interest rate of 8%, and an average maturity (and amortization period) of 3.5 years (payments are made monthly). The current market rate on loans of this risk and maturity is 6 percent. 3. Mortgage loans have a face value of $100,000, and average maturity of 15 years (assume monthly payments), and an average interest rate of 4.5 percent. The current market rate on mortgage loans of similar risk and maturity is 4 percent. 4. Demand deposits are zero interest deposits 5. Money Market deposits have an average yield of 0.5% percent and are fully variable. 6. Small CDs have a face value $150,000, an average maturity of 15 months, and an average rate of 0.75%. The current market rate for similar CDs is 0.50%. (All interest and Principal is paid at maturity) 7. Large CD deposits have a face value of $175,000, an average maturity of 18 months, and an average rate of 1.0%. The current market rate for similar CDs is 0.75%. (All Interest and Principal is paid at maturity) 8. GBB has subordinate bonds outstanding with a face value of $25,000. The coupon rate on this debt is 6 percent (paid semi-annually), and the bonds mature in 10 years. The current yield to maturity on these bonds is 7 percent. GBB Bank and Trust Report of Condition 31-Dec-12 Assets Cash and Due From Banks Treasury Securities Business Loans Mortgage Loans Other Fixed Assets* Total Amount (000's) 75,000 250,000 150,000 100,000 50,000 625,000 * Non Interest Bearing Liabilities and Equity Demand Deposits MMDAs Small CDs $250k Bonds Total Liabilities Total Equity Total Liabilities and Equity Amount (000's) 85,000 150,000 150,000 175,000 25,000 585,000 40,000 625,000 A. B. C. D. E. Complete the GBB Bank and trust Template - submit the completed Excel file through Moodle. (Note: Label your assignment LastName-DGAP.xlsx) Calculating the duration and market values of each of the balance sheet accounts. Complete the market value balance sheet (first tab), and calculate the base case value of equity (EVE). Calculate the Duration of assets, duration of liabilities and DGAP for GBB. Calculate the modified duration for each balance sheet account and use this to estimate the values of assets and liabilities if: i. Rates decrease 100 bp (not: not all deposit rates can fall a full 100 bp). ii. Rates increase 100 bp iii. Rates increase 200 bp iv. Calculate the new EVE under each interest rate scenario. v. Calculate the EVE/MV Assets ratio (capital ratio) under each scenario. HINT: Start with TABs 7-12 first!!! (HINT 2: I gave you check figures for TAB 7) Assets Cash and Due From Banks Treasury Securities Business Loans Mortgage Loans Other Fixed Assets Total Market Value ($000's) Current Rate $ 75,000.00 0.00% $ 270,405.59 4.00% $ 6.00% $ 4.00% $ 50,000.00 NA $ 395,405.59 Duration (Years) 0 4.03 0.00 0.00 0 Liabilities and Equity Demand Deposits MMDAs Small CDs $250k Bonds Total Liabilities Economic Value of Equity Total Liabilities and Equ Market Value ($000's) Current Rate $ 85,000.00 0.00% $ 150,000.00 0.50% $ 0.50% $ 0.75% $ 7.00% $ 235,000.00 $ 160,405.59 NA $ 395,405.59 Duration (Years) 0 0 0 0.00 0.00 Key Statistics EVE/MV Assets Duration of Assets Duration of Liabilities DGAP What is the bank's duration GAP and what risk is the bank face? What could the bank do to mitgate the risk? (Put Answer Here) GRADE out of 45 ote: Label your assignment LastName-DGAP.xlsx) VE). s of assets and liabilities if: -1% Modified Duration 1% 2% Rates decrease 100bp Rates increase 100bp Rates increase 200 bp $ $ $ 3.88 Modified Duration e risk? - - - Rates decrease 100bp Rates increase 100bp Rates increase 200 bp $ $ $ $ $ $ $ $ $ - Rates decrease 100bp - Rates increase 100bp - Rates increase 200 bp Face Value Avg Coupon Rate Maturity Pmts/Yr Annual Coupon Current Market Rate Market Value Duration (years) Month Total $ $ $ 250,000 6% 4.5 years 2.0 15,000 4.00% 270,406 4.032 Cash Flow 1 $7,500.00 2 $7,500.00 3 $7,500.00 4 $7,500.00 5 $7,500.00 6 $7,500.00 7 $7,500.00 8 $7,500.00 9 $257,500.00 PV $7,352.94 $7,208.77 $7,067.42 $6,928.84 $6,792.98 $6,659.79 $6,529.20 $6,401.18 $215,464.48 $270,405.59 weight 0.027192267 0.026659086 0.026136359 0.025623881 0.025121452 0.024628874 0.024145955 0.023672505 0.79681962 $1.00 weight *t 0.027192267 0.053318172 0.078409076 0.102495524 0.12560726 0.147773247 0.169021687 0.189380042 7.171376583 8.06 Face Value Average Rate Maturity (Years) Pmts/Yr Payment Current Market Rate Market Value Duration (years) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Total $ 150,000 8% 3.5 years Cash Flow PV weight weight *t Face Value Average Rate Maturity Pmts/Yr Payment Current Market Rate Market Value Duration (years) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 $ 100,000 4.50% 15 years Cash Flow PV weight weight *t 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 Total Face Value Average Rate Maturity (Years) Pmts/Yr Payment Current Market Rate Market Value Duration (years) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Total $ 150,000 0.75% 1.25 years Cash Flow PV weight weight *t Face Value Average Rate Maturity (Years) Pmts/Yr Payment Current Market Rate Market Value Duration (years) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Total $ 175,000 1.00% 1.50 years Cash Flow PV weight weight *t Face Value Avg Coupon Rate Maturity Pmts/Yr Annual Coupon Current Market Rate Market Value Duration (years) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total $ 25,000 6% 10.0 years Cash Flow PV weight weight *t