can someone please help me answer the first question, draw a cash flow ladder for me?

Question 1 (50 marks in total) You have been asked to prepare a Report to Management on the current risk profile of the bank. (a) (b) (C) Draw the cashflow ladder for CBC's interest rate sensitive assets and liabilities. You should use the following time buckets; Time 0 for Call or overnight exposures and then six-monthly buckets up to 4 years (e.g. 6 months, 12 months, 18 months etc.). (10 marks) Using the Zero-Coupon equivalent interest rates calculated from market interest rates shown, in Table 2; calculate the PVBP for each of the "time bucket" cashows in the Cashow Ladder and the total PVBP, for all interest rate sensitive assets and liabilities? (10 marks) You are required to undertake an assessment of past daily changes in interest rates for each of the "timebuckets" set out in Cashflow Ladder assuming that the assessed interest rate changes for each "timebucket" are independent i.e. uncorrelated. You have decided to base this assessment on the past six months daily changes in interest rates, i.e. from 1 September 2017 to 28 February 2018 and you are required to source the appropriate data. Note; depending on the data you have sourced, you may need to interpolate data to estimate interest rate changes for each "timebucket". A spreadsheet has been provided to assist with this process. (i) The Bank's policy is to assess its risk using a 95% condence level based on an assumption of that future interest rate changes are normally distributed. What is the bank's DEAR? (ii) The Bank's policy also requires its risk to be assessed over a 10 daytime horizon. What is the Bank's Value at Risk (VaR)? (iii) You should explain the approach that you used, the assumptions that were made to obtain and derive the data and the results and any limitations that you consider exist with the approach or the methodology used. You should also include the workings for this calculation as an Appendix to your paper. (20 marks) Commercial Banking Company of Australia Limited Shareholders Funds Capital Liabilities - Call Deposits - 6 months (180 days) - 2 years (semi-annual) - 4 years (semi-annual) Balance Sheet as at 28 February 2018 Current Rate %pa 1.50 1.90 1.95 2.15 1 1 ,250,000 62,000,000 34,700,000 4,700,000 3,175,000 104,575,000 1 15,825,000 Short Term investments 6 month Interbank Loans Loans - Overdrafts (daily rate reset) - 10 year Housing (6mth rate reset) - 2 years (semi-annual) - 4 years (semi-annual) Current $ Rate %pa 2.00 10,000,000 3.50 9,500,000 4.00 20,500,000 4.05 30,125,000 4.25 45,700,000 1 15,825,000 Table 2 Call 1 year 1.5 2 years 2.5 years 3 years 3.5 years 4 years %pa years Isa-mm- Mimi-Elm