Risk Management and Derivatives Final Q1. You work for a UK building society (ie. mortgage bank) which is considering to launch a 10 year fixed mortgage based on the potential demand for such a...

Please see attached prompt.The excels are examples to help with calculations if need be- I need a high level tutor so willing to pay for best levelthe "cwdata" excel file is for this final


Risk Management and Derivatives Final Q1. You work for a UK building society (ie. mortgage bank) which is considering to launch a 10 year fixed mortgage based on the potential demand for such a product and similar products which are available from some of your competitors. but in the future it is expected that rates are going to rise (possible due to inflationary pressure). The Board of Directors knows that you have just completed a module in Derivatives and Risk Management and they want you to present to them the case of offering such a product with the potential risks to your institution highlighted and how you could deal with them. How would you launch a 10 year fixed rate mortgage product? [35 marks] Q2. Use the BOPM to price a call and put option. Use Excel to build a 10 nodes tree. The time to expiry should be 1 year and interest rates are 1% per annum. You need to choose your own stock price, strike price and volatility. (Important : You need to choose different parameters than given in the Excel spreadsheet provided for the module). Price a European Call, European Put, American Call and American Put option. Compare your results against the Black-Scholes model and make suggestions how you could improve on the calculations undertaken in the BOPM. [30 marks] Q3.You are a UK based asset management company and you hold the following international portfolio. On the 20th of December 2018 you set up the following international portfolio. You investment is just less than £10,000. Use data provided in ‘cw Data 2020.xlsx’. Country   Company Number of Shares US   Bank of America   85 Germany   BMW 30 Germany   Lufthansa 130 US Amazon 3 UK Tesco 273 (a.) Calculate the 10 day VaR of your portfolio at the 95% confidence level based on the variance-covariance method on the 10th June 2019, 24th June 2019, and 8th July 2019. (b.) Repeat the calculations above, but use the exponential moving average for your volatility forecast in the calculation of the VaR using the variance-covariance method. (c.) Repeat the calculations of the VaR of your portfolio using historic simulation. (d.) Calculate the actual change in the value of your portfolio over the following 10 days. Compare your calculations with your VaR calculations performed in (a.) to (c.) and critically asses them. Comment critical on the practical implications. Explain what we understand by backtesting, how you would do it and what is the significance of it. (Make any reasonable assumptions you need for the calculations.) [35 marks] Hint : You have to draw standard normally distributed random variables. In Excel that can be done with the following command : NORMSINV(RAND()) Normal Distribution Cumulative Normal Distribution : N(0,1) z00.010.020.030.040.050.060.070.080.09 00.50.5040.5080.5120.5160.51990.52390.52790.53190.5359 0.10.53980.54380.54780.55170.55570.55960.56360.56750.57140.5753 0.20.57930.58320.58710.5910.59480.59870.60260.60640.61030.6141 0.30.61790.62170.62550.62930.63310.63680.64060.64430.6480.6517 0.40.65540.65910.66280.66640.670.67360.67720.68080.68440.6879 0.50.69150.6950.69850.70190.70540.70880.71230.71570.7190.7224 0.60.72570.72910.73240.73570.73890.74220.74540.74860.75170.7549 0.70.7580.76110.76420.76730.77040.77340.77640.77940.78230.7852 0.80.78810.7910.79390.79670.79950.80230.80510.80780.81060.8133 0.90.81590.81860.82120.82380.82640.82890.83150.8340.83650.8389 10.84130.84380.84610.84850.85080.85310.85540.85770.85990.8621 1.10.86430.86650.86860.87080.87290.87490.8770.8790.8810.883 1.20.88490.88690.88880.89070.89250.89440.89620.8980.89970.9015 1.30.90320.90490.90660.90820.90990.91150.91310.91470.91620.9177 1.40.91920.92070.92220.92360.92510.92650.92790.92920.93060.9319 1.50.93320.93450.93570.9370.93820.93940.94060.94180.94290.9441 1.60.94520.94630.94740.94840.94950.95050.95150.95250.95350.9545 1.70.95540.95640.95730.95820.95910.95990.96080.96160.96250.9633 1.80.96410.96490.96560.96640.96710.96780.96860.96930.96990.9706 1.90.97130.97190.97260.97320.97380.97440.9750.97560.97610.9767 20.97720.97780.97830.97880.97930.97980.98030.98080.98120.9817 2.10.98210.98260.9830.98340.98380.98420.98460.9850.98540.9857 2.20.98610.98640.98680.98710.98750.98780.98810.98840.98870.989 2.30.98930.98960.98980.99010.99040.99060.99090.99110.99130.9916 2.40.99180.9920.99220.99250.99270.99290.99310.99320.99340.9936 2.50.99380.9940.99410.99430.99450.99460.99480.99490.99510.9952 2.60.99530.99550.99560.99570.99590.9960.99610.99620.99630.9964 2.70.99650.99660.99670.99680.99690.9970.99710.99720.99730.9974 2.80.99740.99750.99760.99770.99770.99780.99790.99790.9980.9981 2.90.99810.99820.99820.99830.99840.99840.99850.99850.99860.9986 30.99870.99870.99870.99880.99880.99890.99890.99890.9990.999 3.10.9990.99910.99910.99910.99920.99920.99920.99920.99930.9993 3.20.99930.99930.99940.99940.99940.99940.99940.99950.99950.9995 3.30.99950.99950.99950.99960.99960.99960.99960.99960.99960.9997 3.40.99970.99970.99970.99970.99970.99970.99970.99970.99970.9998 BS Option Pricing Model Calculating the BS option price Put-call parity concept is used here to calculate the Put premium. only change the values in the yellow shaded area. Do not change anything else. S120 K125 r0.02 T0.4520547945165days d0 Sigma0.2 B-S Call Premium4.7804469136 B-S Put Premium8.6554034039 Auxillary inputs to calculate B-S premia d1d2N -"dash" (d1) -0.1691068335-0.30357687390.393278568 Imp. Vol. Trial and Error Calculating the Implied Volatility (using Trial and Error) Data Inputs S164 K165 r0.0521 T0.0959 d0 Quoted C Premium5.75 Trial and Error calculations Choose different values for "sigma" and calculate the "theoretical" call premium using B-S until the latter equals the actual quoted call premium Triald1d2N -"dash"Call values for(d1)Premium Sigmausing B-S 0.2810.0310680922-0.05595121540.39874979215.6062731196 0.2820.0312670499-0.0560619350.39874731945.6265243901 0.2830.0314656957-0.05617296640.39874483495.6467755348 0.2840.031664033-0.05628430630.39874233865.667026553 0.2850.0318620651-0.05639595150.39873983045.6872774441 0.2860.0320597952-0.05650789870.39873731065.7075282075 0.2870.0322572263-0.05662014480.39873477895.7277788427 0.2880.0324543617-0.05673268670.39873223565.748029349 0.2890.0326512043-0.05684552130.39872968075.7682797258 Sigma=0.288 gives quoted call premium Imp. Vol. Sover Calculating the Implied Volatilities (Using "Solver") Data Inputs only change the values in the yellow shaded area. Do not change anything else. S164 K165 r0.0521 T0.0959 d0 Actual Call Premum5.75 B-S Call Premium3.9655619293 Cell to be minimised by SOLVER( Actual premium - B-S Premium)^2 =3.1842192281 Start value (and final value) for Sigma for use in SOLVER =0.2 Auxillary inputs to calculate B-S premia d1d2N -"dash" (d1) 0.0134873311-0.04844811920.3989059966 Daily ExR Returns 32450 32451 32454 32455 32456 32457 32458 32461 32462 32463 32464 32465 32468 32469 32470 32471 32472 32475 32476 32477 32478 32479 32482 32483 32484 32485 32486 32489 32490 32491 32492 32493 32496 32497 32498 32499 32500 32503 32504 32505 32506 32507 32510 32511 32512 32513 32514 32517 32518 32519 32520 32521 32524 32525 32526 32527 32528 32531 32532 32533 32534 32535 32538 32539 32540 32541 32542 32545 32546 32547 32548 32549 32552 32553 32554 32555 32556 32559 32560 32561 32562 32563 32566 32567 32568 32569 32570 32573 32574 32575 32576 32577 32580 32581 32582 32583 32584 32587 32588 32589 32590 32591 32594 32595 32596 32597 32598 32601 32602 32603 32604 32605 32608 32609 32610 32611 32612 32615 32616 32617 32618 32619 32622 32623 32624 32625 32626 32629 32630 32631 32632 32633 32636 32637 32638 32639 32640 32643 32644 32645 32646 32647 32650 32651 32652 32653 32654 32657 32658 32659 32660 32661 32664 32665 32666 32667 32668 32671 32672 32673 32674 32675 32678 32679 32680 32681 32682 32685 32686 32687 32688 32689 32692 32693 32694 32695 32696 32699 32700 32701 32702 32703 32706 32707 32708 32709 32710 32713 32714 32715 32716 32717 32720 32721 32722 32723 32724 32727 32728 32729 32730 32731 32734 32735 32736 32737 32738 32741 32742 32743 32744 32745 32748 32749 32750 32751 32752 32755 32756 32757 32758 32759 32762 32763 32764 32765 32766 32769 32770 32771 32772 32773 32776 32777 32778 32779 32780 32783 32784 32785 32786 32787 32790 32791 32792 32793 32794 32797 32798 32799 32800 32801 32804 32805 32806 32807 32808 32811 32812 32813 32814 32815 32818 32819 32820 32821 32822 32825 32826 32827 32828 32829 32832 32833 32834 32835 32836 32839 32840 32841 32842 32843 32846 32847 32848 32849 32850 32853 32854 32855 32856 32857 32860 32861 32862 32863 32864 32867 32868 32869 32870 32871 32874 32875 32876 32877 32878 32881 32882 32883 32884 32885 32888 32889 32890 32891 32892 32895 32896 32897 32898 32899 32902 32903 32904 32905 32906 32909 32910 32911 32912 32913 32916 32917 32918 32919 32920 32923 32924 32925 32926 32927 32930 32931 32932 32933 32934 32937 32938 32939 32940 32941 32944 32945 32946 32947 32948 32951 32952 32953 32954 32955 32958 32959 32960 32961 32962 32965 32966 32967 32968 32969 32972 32973 32974 32975 32976 32979 32980 32981 32982 32983 32986 32987 32988 32989 32990 32993 32994 32995 32996 32997 33000 33001 33002 33003 33004 33007 33008 33009 33010 33011 33014 33015 33016 33017 33018 33021 33022 33023 33024 33025 33028 33029 33030 33031 33032 33035 33036 33037 33038 33039 33042 33043 33044 33045 33046 33049 33050 33051 33052 33053 33056 33057 33058 33059 33060 33063 33064 33065 33066 33067 33070 33071 33072 33073 33074 33077 33078 33079 33080 33081 33084 33085 33086 33087 33088 33091 33092 33093 33094 33095 33098 33099 33100 33101 33102 33105 33106 33107 33108 33109 33112 33113 33114 33115 33116 33119 33120 33121 33122 33123 33126 33127 33128 33129 33130 33133 33134 33135 33136 33137 33140 33141 33142 33143 33144 33147 33148 33149 33150 33151 33154 33155 33156 33157 33158 33161 33162 33163 33164 33165 33168 33169 33170 33171 33172 33175 33176 33177 33178 33179 33182 33183 33184 33185 33186 33189 33190 33191 33192 33193 33196 33197 33198 33199 33200 33203 33204 33205 33206 33207 33210 33211 33212 33213 33214 33217 33218 33219 33220 33221 33224 33225 33226 33227 33228 33231 33232 33233 33234 33235 33238 33239 33240 33241 33242 33245 33246 33247 33248 33249 33252 33253 33254 33255 33256 33259 33260 33261 33262 33263 33266 33267 33268 33269 33270 33273 33274 33275 33276 33277 33280 33281 33282 33283 33284 33287 33288 33289 33290 33291 33294 33295 33296 33297 33298 33301 33302 33303 33304 33305 33308 33309 33310 33311 33312 33315 33316 33317 33318 33319 33322 33323 33324 33325 33326 33329 33330 33331 33332 33333 33336 33337 33338 33339 33340 33343 33344 33345 33346 33347 33350 33351 33352 33353 33354 33357 33358 33359 33360 33361 33364 33365 33366 33367 33368 33371 33372 33373 33374 33375 33378 33379 33380 33381 33382 33385 33386 33387 33388 33389 33392 33393 33394 33395 33396 33399 33400 33401 33402 33403 33406 33407 33408 33409 33410 33413 33414 33415 33416 33417 33420 33421 33422 33423 33424 33427 33428 33429 33430 33431 33434 33435 33436 33437 33438 33441 33442 33443 33444 33445 33448 33449 33450 33451 33452 33455 33456 33457 33458 33459 33462 33463 33464 33465 33466 33469 33470 33471 33472 33473 33476 33477 33478 33479 33480 33483 33484 33485 33486 33487 33490 33491 33492 33493 33494 33497 33498 33499 33500 33501 33504 33505 33506 33507 33508 33511 33512 33513 33514 33515 33518 33519 33520 33521 33522 33525 33526 33527 33528 33529