Using Excel formulas please

Refer to the content of Table 1 and solve the problems given in 7a and 7b. 1 2 Table 1 3 Years Par $1,000 Market Value % Coup Mat. YTM Dur 100 100 Cash Earning assets 3-yr Commercial loan 6-yr Treasury bond Total Earning Assets Non-cash earning assets Total assets 12.00% 8.00% 3 6 700 200 900 01 1000 12.00% 8.00% 11.11% 2.69 4.99 700 200 900 0 1000 10.00% 2.88 4 5 Assets 6 7 8 9 10 11 12 13 14 Liabilities 15 16 17 18 19 20 21 22 23 5.00% 7.00% 1 3 5.00% 7.00% 5.65% 1 2.81 Interest bearing liabs. 1-yr Time deposit 3-yr Certificate of deposit Tot. Int Bearing Liabs. Tot. non-int. bearing Total liabilities Total equity Total liabs & equity 620 300 920 01 920 80 1000 620 300 920 0 920 80 1000 5.65% 1.59 E 7a) Duration Gap Calculations 26 Total points 5 27 28 29 30 31 32 33 34 Duration of Assets Duration of Liabilities Liabilities / Assets ratio DGAP = Dur assets - (assets/liab)*Dur liab DGAP* = Dur assets - DGAP 7b) Approximate Impact from a Change in Rates 35 Total points 6 1.00% Expected change in rates Average rate on Earning Assets Average rate on Int Bearing Liab 36 37 38 39 40 41 42 43 44 45 46 Approx Change in Mkt Value of Assets Approx Change in Mkt Value of Liabilities Approx Change in Mkt Value of Equity (Difference) Approx Change in Mkt Value of Equity using Duration GAP method 17 Refer to the content of Table 1 and solve the problems given in 7a and 7b. 1 2 Table 1 3 Years Par $1,000 Market Value % Coup Mat. YTM Dur 100 100 Cash Earning assets 3-yr Commercial loan 6-yr Treasury bond Total Earning Assets Non-cash earning assets Total assets 12.00% 8.00% 3 6 700 200 900 01 1000 12.00% 8.00% 11.11% 2.69 4.99 700 200 900 0 1000 10.00% 2.88 4 5 Assets 6 7 8 9 10 11 12 13 14 Liabilities 15 16 17 18 19 20 21 22 23 5.00% 7.00% 1 3 5.00% 7.00% 5.65% 1 2.81 Interest bearing liabs. 1-yr Time deposit 3-yr Certificate of deposit Tot. Int Bearing Liabs. Tot. non-int. bearing Total liabilities Total equity Total liabs & equity 620 300 920 01 920 80 1000 620 300 920 0 920 80 1000 5.65% 1.59 E 7a) Duration Gap Calculations 26 Total points 5 27 28 29 30 31 32 33 34 Duration of Assets Duration of Liabilities Liabilities / Assets ratio DGAP = Dur assets - (assets/liab)*Dur liab DGAP* = Dur assets - DGAP 7b) Approximate Impact from a Change in Rates 35 Total points 6 1.00% Expected change in rates Average rate on Earning Assets Average rate on Int Bearing Liab 36 37 38 39 40 41 42 43 44 45 46 Approx Change in Mkt Value of Assets Approx Change in Mkt Value of Liabilities Approx Change in Mkt Value of Equity (Difference) Approx Change in Mkt Value of Equity using Duration GAP method 17