need help with duration weighted average and the parts 3&4

new rate 6%

current rate 5%

change 1%

Assume you have already gathered the below information. Duration (years) WAvg Duration (years) WAvg Duration Balance Sheet Assets Reserves S 2,000,000 $ 4,000,000 $ 4,000,000 Securities 4,000,000 0.60 24,000.00 5,000,000 5,000,000 15,000 00 84,000,00 2,000,000 1.60 32,000.00 0.30 1.20 4.00 7,000,000 8,000,000 5.00 400,000.00 9,000,000 360,000.00 11,000,000 0.40 44,000 00 21,000,000 21,000,000 0.90 189,000.00 15,000,000 750,000 5.50 825,000.00 17,000,000 850,000 4.40 748,000.00 15,000,000 15,000,000 135,000.00 30,000,000 30,000,000 180,000.00 37,000,000 0.90 1.80 6.00 22,000,000 0.60 1.40 308,000.00 5.40 1,620,000 00 666,000.00 2,820,000.00 47,000,000 30,000,000 Prop Plant & Equip 9,000,000 25,000,000 Total Assets 150,000,000 $ 32,750,000 4,946,000.00 $ 170,000,000 $ 60,850,000 3,504,000.00 Checkable Deposits 900,000 1,400,000 140,000.00 9,000,000 $ 6,000,000 Money Mkt Deposit Accts 1.00 0.60 1.00 90,000.00 $ 36,000 00 14,000,000 $ 9,000,000 6,000,000 1.00 0.50 1.00 Savings Deposits 9,000,000 8,000,000 12,000,000 6,000,000 45,000 00 150,000.00 120,000,00 16,000,000 CDs Variable Rate 6,000,000 6,000,000 24,000.00 12,000,000 12,000,000 72.000.00 Less than 1 year 18,000,000 060 0.50 18,000,000 54,000 00 14,000,000 040 0.30 1.10 2.90 14,000,000 1-2 years 70,000.00 8,000,000 88,000.00 10,000,000 180,000.00 Greater than 2 years 16,000,000 464,000.00 1.80 2.20 10,000,000 220,000.00 10,000,000 10,000,000 14,000,000 14,000,000 Less than 1 year 11,000,000 11,000,000 44,000.00 0.40 1.20 18,000,000 1-2 years 18,000,000 0.70 126,000.00 8,000,000 12,000,000 080 96,000.00 Greater than 2 years 36,000,000 2.90 96,000 00 1,044,000 00 2,000,000 00 $ 31,000,000 3.80 42 Total Liabilities S 140,000,000 $ 57,900,000 160,000,000 $ 76,400,000 1,178,000.00 2,287,000 00 43 44 Assumptions to use in your analysis. 45 Past experience shows that 5% of fixed rate mortgages are paid off early (assume same for competitor) 10% of the checkable deposits are interest earning accounts (assume same for competitor) 46 47 ww 50% of the savings deposits are interest earning accounts (assume same for competitor) D 1 2 3 24 25 Less than 1 year 1-2 years Greater than 2 years Residential Mortgages Variable Rate Fixed rate Commercial Loans Less than 1 year 1-2 years Greater than 2 years 26 28 Liabilities 29 30 31 32 33 34 35 36 37 38 39 40 41 Federal Funds Borrowings $ $ YOUR BANK Rate Sensitive S amounts 2,000,000 4,000,000 11,000,000 $ COMPETITOR Rate Sensitive S Duration amounts M 3) in columns H and M above calculate the weighted average duration of assets and of the liabilities. In your formulas, use the -ROUND function to round to fwo decim. 51 points Your Bank Asset Duration 4,946,000.00 2,060,000.00 53 Your Bank Liab. Duration Competitor Competitor 3,504,000.00 2,287,000.00 54 4) Perform a Duration Gap Analysis to estimate the change in Your Bank and Competitor's Net Worth if rates increase by 1% Assume rates are going from 5% to 6%, In 65 your formulas, utlize the cells with the interest rates to the right (an up a little) 66 Your Bank Assets che $ 27,804,000 $ 57,346,000 67 Your Bank Liabilities chg S 55,840,000 Competitor's Assets chg Comp Liabilities chg Comp. Net Worth chg $ 74,113,000 68 Your Bank Net Worth chg 69 5) After you have completed the above, copy this sheet into another sheet. Change the name of that sheet to WHAT IF. In the WHAT IF sheet, change only the new rate" cell 70 to assume the current rate of 5% is expected to go down to 4% 73 Lalalal 52 Assume you have already gathered the below information. Duration (years) WAvg Duration (years) WAvg Duration Balance Sheet Assets Reserves S 2,000,000 $ 4,000,000 $ 4,000,000 Securities 4,000,000 0.60 24,000.00 5,000,000 5,000,000 15,000 00 84,000,00 2,000,000 1.60 32,000.00 0.30 1.20 4.00 7,000,000 8,000,000 5.00 400,000.00 9,000,000 360,000.00 11,000,000 0.40 44,000 00 21,000,000 21,000,000 0.90 189,000.00 15,000,000 750,000 5.50 825,000.00 17,000,000 850,000 4.40 748,000.00 15,000,000 15,000,000 135,000.00 30,000,000 30,000,000 180,000.00 37,000,000 0.90 1.80 6.00 22,000,000 0.60 1.40 308,000.00 5.40 1,620,000 00 666,000.00 2,820,000.00 47,000,000 30,000,000 Prop Plant & Equip 9,000,000 25,000,000 Total Assets 150,000,000 $ 32,750,000 4,946,000.00 $ 170,000,000 $ 60,850,000 3,504,000.00 Checkable Deposits 900,000 1,400,000 140,000.00 9,000,000 $ 6,000,000 Money Mkt Deposit Accts 1.00 0.60 1.00 90,000.00 $ 36,000 00 14,000,000 $ 9,000,000 6,000,000 1.00 0.50 1.00 Savings Deposits 9,000,000 8,000,000 12,000,000 6,000,000 45,000 00 150,000.00 120,000,00 16,000,000 CDs Variable Rate 6,000,000 6,000,000 24,000.00 12,000,000 12,000,000 72.000.00 Less than 1 year 18,000,000 060 0.50 18,000,000 54,000 00 14,000,000 040 0.30 1.10 2.90 14,000,000 1-2 years 70,000.00 8,000,000 88,000.00 10,000,000 180,000.00 Greater than 2 years 16,000,000 464,000.00 1.80 2.20 10,000,000 220,000.00 10,000,000 10,000,000 14,000,000 14,000,000 Less than 1 year 11,000,000 11,000,000 44,000.00 0.40 1.20 18,000,000 1-2 years 18,000,000 0.70 126,000.00 8,000,000 12,000,000 080 96,000.00 Greater than 2 years 36,000,000 2.90 96,000 00 1,044,000 00 2,000,000 00 $ 31,000,000 3.80 42 Total Liabilities S 140,000,000 $ 57,900,000 160,000,000 $ 76,400,000 1,178,000.00 2,287,000 00 43 44 Assumptions to use in your analysis. 45 Past experience shows that 5% of fixed rate mortgages are paid off early (assume same for competitor) 10% of the checkable deposits are interest earning accounts (assume same for competitor) 46 47 ww 50% of the savings deposits are interest earning accounts (assume same for competitor) D 1 2 3 24 25 Less than 1 year 1-2 years Greater than 2 years Residential Mortgages Variable Rate Fixed rate Commercial Loans Less than 1 year 1-2 years Greater than 2 years 26 28 Liabilities 29 30 31 32 33 34 35 36 37 38 39 40 41 Federal Funds Borrowings $ $ YOUR BANK Rate Sensitive S amounts 2,000,000 4,000,000 11,000,000 $ COMPETITOR Rate Sensitive S Duration amounts M 3) in columns H and M above calculate the weighted average duration of assets and of the liabilities. In your formulas, use the -ROUND function to round to fwo decim. 51 points Your Bank Asset Duration 4,946,000.00 2,060,000.00 53 Your Bank Liab. Duration Competitor Competitor 3,504,000.00 2,287,000.00 54 4) Perform a Duration Gap Analysis to estimate the change in Your Bank and Competitor's Net Worth if rates increase by 1% Assume rates are going from 5% to 6%, In 65 your formulas, utlize the cells with the interest rates to the right (an up a little) 66 Your Bank Assets che $ 27,804,000 $ 57,346,000 67 Your Bank Liabilities chg S 55,840,000 Competitor's Assets chg Comp Liabilities chg Comp. Net Worth chg $ 74,113,000 68 Your Bank Net Worth chg 69 5) After you have completed the above, copy this sheet into another sheet. Change the name of that sheet to WHAT IF. In the WHAT IF sheet, change only the new rate" cell 70 to assume the current rate of 5% is expected to go down to 4% 73 Lalalal 52