Please show your working in the excel please!

You are a senior manager in the Risk Management Department of ABC Bank. A newly published analyst report from a local brokerage firm argues that your bank is too aggressive in investing in U.S. Treasury debt securities and taking too high interest rate risk exposure. The report argues that the future interest rate increase may induce financial instability to your bank. Your boss, a Vice President of ABC Bank, has read the report. In order to have a better understanding about the situation, he asked you to prepare a detailed analysis to investigate the financial position and the interest rate risk exposure of your bank. "ABC Bank Balance Sheet ASSET Market Value (in HK$B) Interest Rate (Discount Rate) (in term of HK$B Market Value) Macaulay Liabilities and Duration Equity Market Value (in HK$B) Macaulay Duration Interest Rate (Discount Rate) 2.00% 3.20% Savings & Time Deposit Certificates of Deposit Total Liabilities 1.50 years 3.50 years Cash Commercial Loan Mortgage Loan US Treasury Debts Building and Equip Total 740 420 1,160 11.00% 4.50% 1.80% 400 350 400 100 50 1,300 2.50 years 12.00 years 4.50 years Equity Total 140 1,300 To simplify your analysis, you can start from the following assumptions: I The exchange rate between HK$ and US$ is HK$7.80/US$. There is NO exchange rate risk between HK$ and US$. All (HK$ and US$) interest rates are quoted in semi-annually compounding form. All (HK$ and US$) interest rates will change in the same magnitude. In other words, any market interest rate change (Ay) will be applied to all interest rates. I PART (0) To analyze the interest rate risk on the Net Worth" (market value of equity), let's answer the following questions step-by-step: Calculate the overall Macaulay Duration for (i) the bank's assets, > (ii) the bank's liabilities and > (iii) the bank's net worth Calculate the Duration Gap for ABC Bank (0) Is it positive or negative? > (ii) What are the implications from the sign of the Duration Gap for ABC Bank? What will happen if the market interest rate increases? What will happen if the market interest rate decreases? Perform a Scenario Analysis to investigate the Change in Net Worth" under the different hypothetical interest rate changes (Ay). For simplicity of your analysis, we further assume that in the coming future, the interest rate will not drop by more than 0.5% and will not rise by more than 3%. You have to perform a Scenario Analysis for the following range of (Ay): (-0.5%, 0%, +1%, +2%, +3%) Let's assume that each hypothetical interest rate change (Ay) is applied to ALL interest rates (discount rates) of all balance sheet items. Plot a graph to show the variations of market values for Assets, Liabilities and Equity with respect to different interest rate changes. Summarize and interpret your results. With reference to the diagram from Scenario Analysis, estimate the magnitude of interest rate increase to induce the market value of equity to drop from $140B to $100B. Part (0) I To calculate the Macaulay Duration values for Assets or Liabilities D Portfolio Macaculay W x D Rond (1) Alacaulay i=1 DURGAP Macaulay Afacmelay x D Liabilities Alacaulay * Dry Alacaulay DOP 1 To calculate the Macaulay Duration value for Equities, Use the Duration Gap Concept L E Disser A A L is the Market Value of Total Liabilities. is the Market Value of Total Assets. is the Duration Gap (in term of Macaulay Duration). Macaulay D Asser is the Macaulay Duration for Asset; D Am where w weighting for individual asset item (i) in the asset side (Total Assets). D is the Macaulay Duration for Liabilities; Disabilities where is the weighting for individual liability item (j') in the liabilities side (Total Liabilities). Note: (LIA) is the percentage of assets funded by the liabilities. ${w. Macaulay x D Arses (1) is the Macaulay lasel (1) Alacanla Assut (1) i=1 Llabilities Macaulay {W urine D Liabilnes ( Liabiliny (1) 7 -D Macawler To Perform a Scenario Analysis . Ay Given a particular (Ay), using the relationship of P 1+(y/2) Calculate the "Change in Market Value of Total Assets" Calculate the "Change in Market Value of Total Liabilities And then, calculate the Change in Market Value of Equity" Hint: Work out the change in MV for an individual asset item, and the change in MV for an individual liability item. Repeat the work for other (Ay). Summarize and comment your results. You are a senior manager in the Risk Management Department of ABC Bank. A newly published analyst report from a local brokerage firm argues that your bank is too aggressive in investing in U.S. Treasury debt securities and taking too high interest rate risk exposure. The report argues that the future interest rate increase may induce financial instability to your bank. Your boss, a Vice President of ABC Bank, has read the report. In order to have a better understanding about the situation, he asked you to prepare a detailed analysis to investigate the financial position and the interest rate risk exposure of your bank. "ABC Bank Balance Sheet ASSET Market Value (in HK$B) Interest Rate (Discount Rate) (in term of HK$B Market Value) Macaulay Liabilities and Duration Equity Market Value (in HK$B) Macaulay Duration Interest Rate (Discount Rate) 2.00% 3.20% Savings & Time Deposit Certificates of Deposit Total Liabilities 1.50 years 3.50 years Cash Commercial Loan Mortgage Loan US Treasury Debts Building and Equip Total 740 420 1,160 11.00% 4.50% 1.80% 400 350 400 100 50 1,300 2.50 years 12.00 years 4.50 years Equity Total 140 1,300 To simplify your analysis, you can start from the following assumptions: I The exchange rate between HK$ and US$ is HK$7.80/US$. There is NO exchange rate risk between HK$ and US$. All (HK$ and US$) interest rates are quoted in semi-annually compounding form. All (HK$ and US$) interest rates will change in the same magnitude. In other words, any market interest rate change (Ay) will be applied to all interest rates. I PART (0) To analyze the interest rate risk on the Net Worth" (market value of equity), let's answer the following questions step-by-step: Calculate the overall Macaulay Duration for (i) the bank's assets, > (ii) the bank's liabilities and > (iii) the bank's net worth Calculate the Duration Gap for ABC Bank (0) Is it positive or negative? > (ii) What are the implications from the sign of the Duration Gap for ABC Bank? What will happen if the market interest rate increases? What will happen if the market interest rate decreases? Perform a Scenario Analysis to investigate the Change in Net Worth" under the different hypothetical interest rate changes (Ay). For simplicity of your analysis, we further assume that in the coming future, the interest rate will not drop by more than 0.5% and will not rise by more than 3%. You have to perform a Scenario Analysis for the following range of (Ay): (-0.5%, 0%, +1%, +2%, +3%) Let's assume that each hypothetical interest rate change (Ay) is applied to ALL interest rates (discount rates) of all balance sheet items. Plot a graph to show the variations of market values for Assets, Liabilities and Equity with respect to different interest rate changes. Summarize and interpret your results. With reference to the diagram from Scenario Analysis, estimate the magnitude of interest rate increase to induce the market value of equity to drop from $140B to $100B. Part (0) I To calculate the Macaulay Duration values for Assets or Liabilities D Portfolio Macaculay W x D Rond (1) Alacaulay i=1 DURGAP Macaulay Afacmelay x D Liabilities Alacaulay * Dry Alacaulay DOP 1 To calculate the Macaulay Duration value for Equities, Use the Duration Gap Concept L E Disser A A L is the Market Value of Total Liabilities. is the Market Value of Total Assets. is the Duration Gap (in term of Macaulay Duration). Macaulay D Asser is the Macaulay Duration for Asset; D Am where w weighting for individual asset item (i) in the asset side (Total Assets). D is the Macaulay Duration for Liabilities; Disabilities where is the weighting for individual liability item (j') in the liabilities side (Total Liabilities). Note: (LIA) is the percentage of assets funded by the liabilities. ${w. Macaulay x D Arses (1) is the Macaulay lasel (1) Alacanla Assut (1) i=1 Llabilities Macaulay {W urine D Liabilnes ( Liabiliny (1) 7 -D Macawler To Perform a Scenario Analysis . Ay Given a particular (Ay), using the relationship of P 1+(y/2) Calculate the "Change in Market Value of Total Assets" Calculate the "Change in Market Value of Total Liabilities And then, calculate the Change in Market Value of Equity" Hint: Work out the change in MV for an individual asset item, and the change in MV for an individual liability item. Repeat the work for other (Ay). Summarize and comment your results