Please find the questions in the word document. Find attached supporting documents and the expert should complete the excel file as well and show all workings in the excel file and write the short report of 3 pages.
ORANGE COUNTY FINANCIAL CASE-Value At Risk Calculations Please complete the project by using the information in the attached excel file below: Required: 1. Use the excel file to compute the following informations and provide workings in the excel file-for e.g, calculation of z values, dy, VAR ect…Draw histogram ect.. 2. Write a short report of max 3 pages to explain your answers on excel. Section 1-Duration approximation The average duration of the securities is about 2.74 years for a portfolio with leverage ratio of 2.7. The portfolio size is $20.25 billion with capital of $7.5 billion. The interest rates went up about 3% in 1994 Question 1: Compute the loss predicted by the duration approximation and compare your result with the actual loss of $1.64 billion.) Section 2-Computation of portfolio VAR The monthly yield data is provided. Using this information (last 5 years monthly rate changes (60 points)) to compute the portfolio VaR as of December 1994. Risk should be measured over a month at the 95% level. Question 2: Report the distribution and compute the VaR using a Delta-Normal method. Question 3: Report the distribution and compute the VaR using a Historical-Simulation method. Question 4: Compare VaR obtained by these two methods Section 3-Interpretation of VAR Question 5: Convert the monthly VAR into an annual figure. Is the latter number consistent with the $1.6 billion loss? Question 6: From December 1994 to December 1995, interest rates fell from 7.8% to 5.25%. Compute the probability of such an event (rates fell more than this) based on the Delta-Normal method. Case Study: Orange County Bankruptcy Fundamentals of Futures and Options Markets, 9th Ed, Ch 25, Copyright © John C. Hull 2016 Case Study: Orange County Bankruptcy FIN 5623 Final Project 1 Fundamentals of Futures and Options Markets, 9th Ed, Ch 25, Copyright © John C. Hull 2016 Orange County Bankruptcy Case ⚫ https://financetrain.com/orange-county-case/ ⚫ https://merage.uci.edu/~jorion/oc/case.html 2 Fundamentals of Futures and Options Markets, 9th Ed, Ch 25, Copyright © John C. Hull 2016 Introduction ⚫ $7.5 billion Capital and was leveraged to $20.5 billion ⚫ Mostly 5-year agency bonds (Fannie, Freddie) ⚫ Borrowing in short term (Repo) with rate < 3% and investing in medium maturity bond with rate ~ 5.2% ⚫ in december of 1994, the average duration of individual security is 2.74 year, and the leverage ratio is 2.7. the effective duration is 7.4 year. ⚫ in february 1994, fed started a series of six consecutive interest rate increases and rates went up by about 3% ⚫ the portfolio had lost $1.5 billion by nov. 1994 and finally went to bankruptcy. 3 interest curve 12/1993 fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 4 interest rates movement fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 5 repurchase agreement (repo) ⚫ a repo is a form of short-term borrowing for dealers - - a dealer sells government securities to investors and buys them back the following day at a slightly higher price. it can be viewed as a collateral loan ⚫ a reverse repo is the opposite of a repo (ml had a reverse repo with orange county) ⚫ repo have term repo and open repo ⚫ if the collateral falls in value, a margin call can take effect to ask the borrower to amend the securities offered. fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 6 risk factors and loss distribution ⚫ the value of financial instruments depends on a few fundamental factors – risk factors, such as interest rates, inflation, exchange rate ?? = ?(?, ??) ⚫ the randomness of loss distribution is due to the change of risk factors ??+1: = ??+1 − ?? ??+1 = − ? ? + 1, ?? + ??+1 − ? ?, ?? = ? ? (??+1) ⚫ linearization of loss distribution ??+1 ≈ −(?? + ?? ′ ⋅ ??+?)~?(−?? − ?? ′ ⋅ ?, ?? ′σ??) fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 7 fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 interest rate sensitivity ⚫ fixed-income assets valuation: ?? = ?(?, ??) ⚫ interest rate sensitivity – effective duration: ? = − 1 ?? ? ?, ?? + δ? − ? ?, ?? − δ? 2δ? δ? ? ≈ −? ∗ δ?; ? ≈ ? ∗ ? ∗ ?? ⚫ duration and var of investments ???$? = ? ∗ ? ∗ ????? 8 methods to measure var ⚫ delta-normal method ⚫ risk factor and portfolio return are normal distributed ⚫ going back 5 years to compute distribution parameters for all risk factors ⚫ apply to the portfolio to compute var ⚫ historical-simulation method ⚫ take 5 years of historical data of risk factors as the realization of the probability distribution of the risk factor to compute var (non- parametrized estimation) ⚫ apply to the portfolio to compute var ⚫ annualized var fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 9 final project (1) ⚫ duration approximation ⚫ the average duration of the securities is about 2.74 years for a portfolio with leverage ratio of 2.7 ⚫ the portfolio size is $20.25 billion with capital of $7.5 billion ⚫ the interest rates went up about 3% in 1994 ⚫ compute the loss predicted by the duration approximation and compare your result with the actual loss of $1.64 billion. fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 10 final project (2) ⚫ computation of portfolio var ⚫ the monthly yield data is provided. using this information (last 5 years monthly rate changes (60 points)) to compute the portfolio var as of december 1994. risk should be measured over a month at the 95% level. ⚫ report the distribution and compute the var using a delta-normal method. ⚫ report the distribution and compute the var using a historical-simulation method. ⚫ compare var obtained by these two methods fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 11 final project (3) ⚫ interpretation of var ⚫ convert the monthly var into an annual figure. is the latter number consistent with the $1.6 billion loss? ⚫ from december 1994 to december 1995, interest rates fell from 7.8% to 5.25%. compute the probability of such an event (rates fell more than this) based on the delta-normal method. ⚫ it seems that both in 1994 and 1995, interest rate swings were particularly large relative to the historical distribution. suggest two interpretations for this observation. fundamentals of futures and options markets, 9th ed, ch 25, copyright © john c. hull 2016 12 3%="" and="" investing="" in="" medium="" maturity="" bond="" with="" rate="" ~="" 5.2%="" ⚫="" in="" december="" of="" 1994,="" the="" average="" duration="" of="" individual="" security="" is="" 2.74="" year,="" and="" the="" leverage="" ratio="" is="" 2.7.="" the="" effective="" duration="" is="" 7.4="" year.="" ⚫="" in="" february="" 1994,="" fed="" started="" a="" series="" of="" six="" consecutive="" interest="" rate="" increases="" and="" rates="" went="" up="" by="" about="" 3%="" ⚫="" the="" portfolio="" had="" lost="" $1.5="" billion="" by="" nov.="" 1994="" and="" finally="" went="" to="" bankruptcy.="" 3="" interest="" curve="" 12/1993="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 4="" interest="" rates="" movement="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 5="" repurchase="" agreement="" (repo)="" ⚫="" a="" repo="" is="" a="" form="" of="" short-term="" borrowing="" for="" dealers="" -="" -="" a="" dealer="" sells="" government="" securities="" to="" investors="" and="" buys="" them="" back="" the="" following="" day="" at="" a="" slightly="" higher="" price.="" it="" can="" be="" viewed="" as="" a="" collateral="" loan="" ⚫="" a="" reverse="" repo="" is="" the="" opposite="" of="" a="" repo="" (ml="" had="" a="" reverse="" repo="" with="" orange="" county)="" ⚫="" repo="" have="" term="" repo="" and="" open="" repo="" ⚫="" if="" the="" collateral="" falls="" in="" value,="" a="" margin="" call="" can="" take="" effect="" to="" ask="" the="" borrower="" to="" amend="" the="" securities="" offered.="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 6="" risk="" factors="" and="" loss="" distribution="" ⚫="" the="" value="" of="" financial="" instruments="" depends="" on="" a="" few="" fundamental="" factors="" –="" risk="" factors,="" such="" as="" interest="" rates,="" inflation,="" exchange="" rate="" =="" (?,="" )="" ⚫="" the="" randomness="" of="" loss="" distribution="" is="" due="" to="" the="" change="" of="" risk="" factors="" +1:="??+1" −="" +1="−" +="" 1,="" +="" +1="" −="" ,="" =="" (??+1)="" ⚫="" linearization="" of="" loss="" distribution="" +1="" ≈="" −(??="" +="" ′="" ⋅="" +?)~?(−??="" −="" ′="" ⋅="" ,="" ′σ??)="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 7="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" interest="" rate="" sensitivity="" ⚫="" fixed-income="" assets="" valuation:="" =="" (?,="" )="" ⚫="" interest="" rate="" sensitivity="" –="" effective="" duration:="" =="" −="" 1="" ,="" +="" δ?="" −="" ,="" −="" δ?="" 2δ?="" δ?="" ≈="" −?="" ∗="" δ?;="" ≈="" ∗="" ∗="" ⚫="" duration="" and="" var="" of="" investments="" $?="?" ∗="" ∗="" 8="" methods="" to="" measure="" var="" ⚫="" delta-normal="" method="" ⚫="" risk="" factor="" and="" portfolio="" return="" are="" normal="" distributed="" ⚫="" going="" back="" 5="" years="" to="" compute="" distribution="" parameters="" for="" all="" risk="" factors="" ⚫="" apply="" to="" the="" portfolio="" to="" compute="" var="" ⚫="" historical-simulation="" method="" ⚫="" take="" 5="" years="" of="" historical="" data="" of="" risk="" factors="" as="" the="" realization="" of="" the="" probability="" distribution="" of="" the="" risk="" factor="" to="" compute="" var="" (non-="" parametrized="" estimation)="" ⚫="" apply="" to="" the="" portfolio="" to="" compute="" var="" ⚫="" annualized="" var="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 9="" final="" project="" (1)="" ⚫="" duration="" approximation="" ⚫="" the="" average="" duration="" of="" the="" securities="" is="" about="" 2.74="" years="" for="" a="" portfolio="" with="" leverage="" ratio="" of="" 2.7="" ⚫="" the="" portfolio="" size="" is="" $20.25="" billion="" with="" capital="" of="" $7.5="" billion="" ⚫="" the="" interest="" rates="" went="" up="" about="" 3%="" in="" 1994="" ⚫="" compute="" the="" loss="" predicted="" by="" the="" duration="" approximation="" and="" compare="" your="" result="" with="" the="" actual="" loss="" of="" $1.64="" billion.="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 10="" final="" project="" (2)="" ⚫="" computation="" of="" portfolio="" var="" ⚫="" the="" monthly="" yield="" data="" is="" provided.="" using="" this="" information="" (last="" 5="" years="" monthly="" rate="" changes="" (60="" points))="" to="" compute="" the="" portfolio="" var="" as="" of="" december="" 1994.="" risk="" should="" be="" measured="" over="" a="" month="" at="" the="" 95%="" level.="" ⚫="" report="" the="" distribution="" and="" compute="" the="" var="" using="" a="" delta-normal="" method.="" ⚫="" report="" the="" distribution="" and="" compute="" the="" var="" using="" a="" historical-simulation="" method.="" ⚫="" compare="" var="" obtained="" by="" these="" two="" methods="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="" 11="" final="" project="" (3)="" ⚫="" interpretation="" of="" var="" ⚫="" convert="" the="" monthly="" var="" into="" an="" annual="" figure.="" is="" the="" latter="" number="" consistent="" with="" the="" $1.6="" billion="" loss?="" ⚫="" from="" december="" 1994="" to="" december="" 1995,="" interest="" rates="" fell="" from="" 7.8%="" to="" 5.25%.="" compute="" the="" probability="" of="" such="" an="" event="" (rates="" fell="" more="" than="" this)="" based="" on="" the="" delta-normal="" method.="" ⚫="" it="" seems="" that="" both="" in="" 1994="" and="" 1995,="" interest="" rate="" swings="" were="" particularly="" large="" relative="" to="" the="" historical="" distribution.="" suggest="" two="" interpretations="" for="" this="" observation.="" fundamentals="" of="" futures="" and="" options="" markets,="" 9th="" ed,="" ch="" 25,="" copyright="" ©="" john="" c.="" hull="" 2016="">