In this summative, you will build and calibrate a quantitative mortgage valuation model as described below by building up Excel spreadsheets. If you use Excel sheets, pleaseuse T$=5$ years and N$=1000$ simulations at all points applicable below. Please organize your assignment as follows: It$$is November $2023$ and you are working in the back$-$office of the University Building Society $($UBS$)$ providing mortgages to university students and employees all over UK and issuing mortgage backed securities based on those mortgages. UBS has just decided to market a novel product, a$$T$-$year fixed rate mortgage. Your boss asks you to develop a model which helps to come up with the right interest rate for this product. If the interest rate is too high, no one will buy the product. If$$it$$is too low, UBS will lose on the mortgages. with longer maturity and a different yield curve. $($a$)$ Constructing interest rate trees. you decide to experiment both with the Ho$-$Lee model $($HL$)$ and the Black$-$Derman$-$Toy $($BDT$)$ model, with semiannual $(=0\mathrm{.}5),$ continuously compounded short$-$rates. You estimate that the historical volatility of the level of short interest rates is$$$=1\mathrm{.}73\%$ while the volatility of the log$$of interest rates is$$$=21\mathrm{.}42\%.$ You should calibrate both type of trees to the current yield curve, which you can find below.$($Treat the yields as continuously compounded.$)$ Maturity Year Spot Rate $0\mathrm{.}50\mathrm{.}04479121\mathrm{.}00\mathrm{.}041412871\mathrm{.}50\mathrm{.}039577832\mathrm{.}00\mathrm{.}038603892\mathrm{.}50\mathrm{.}03800273\mathrm{.}00\mathrm{.}037579423\mathrm{.}50\mathrm{.}037257874\mathrm{.}00\mathrm{.}037006354\mathrm{.}50\mathrm{.}036811335\mathrm{.}00\mathrm{.}036666755\mathrm{.}50\mathrm{.}036569096\mathrm{.}00\mathrm{.}036515256\mathrm{.}50\mathrm{.}036501577\mathrm{.}00\mathrm{.}036523887\mathrm{.}50\mathrm{.}036577628\mathrm{.}00\mathrm{.}03665818\mathrm{.}50\mathrm{.}036760769\mathrm{.}00\mathrm{.}036881279\mathrm{.}50\mathrm{.}0370155810\mathrm{.}00\mathrm{.}0371600310\mathrm{.}50\mathrm{.}037311311\mathrm{.}00\mathrm{.}0374664411\mathrm{.}50\mathrm{.}0376229312\mathrm{.}00\mathrm{.}0377785612\mathrm{.}50\mathrm{.}0379313813\mathrm{.}00\mathrm{.}0380797513\mathrm{.}50\mathrm{.}0382223914\mathrm{.}00\mathrm{.}0383582814\mathrm{.}50\mathrm{.}0384866715\mathrm{.}00\mathrm{.}0386069915\mathrm{.}50\mathrm{.}0387188316\mathrm{.}00\mathrm{.}0388219516\mathrm{.}50\mathrm{.}0389161917\mathrm{.}00\mathrm{.}0390015117\mathrm{.}50\mathrm{.}0390779518\mathrm{.}00\mathrm{.}0391456218\mathrm{.}50\mathrm{.}039204719\mathrm{.}00\mathrm{.}0392554119\mathrm{.}50\mathrm{.}0392980420\mathrm{.}00\mathrm{.}039332920\mathrm{.}50\mathrm{.}0393603521\mathrm{.}00\mathrm{.}0393807421\mathrm{.}50\mathrm{.}0393944322\mathrm{.}00\mathrm{.}0394017422\mathrm{.}50\mathrm{.}0394029823\mathrm{.}00\mathrm{.}0393984723\mathrm{.}50\mathrm{.}0393884924\mathrm{.}00\mathrm{.}0393733224\mathrm{.}50\mathrm{.}0393532325\mathrm{.}00\mathrm{.}03932848$