Procter and Gamble

Only available on StudyMode
  • Download(s) : 36
  • Published : April 13, 2013
Open Document
Text Preview
Financial Institutions Management project

KIMEP University

Kosherbay Aldiyar 20101117
Babashov Abylay 20101801

Spring 2013

Description of the Excel file
I. In this project we are going to use two types of methods in order to calculate the 10 days VAR for our position in 10 year T-bonds with Risk Metrics method and position in Euros using Historic simulation. The models used to calculate VAR are Risk Metrics and Historic back simulation. And these are steps how we calculated VAR of 10 year T-bonds using Risk Metrics: 1. We got information about coupon rate, yield, and maturity of T-bonds as of March 15, 2011 from the website www.treasurydirect.gov/RI/OFNtebnd and attached copy of the table to the project as a proof of our calculations 2. We found Duration and Modified Duration. (When you open Excel, in the 1 sheet we begin to calculate duration and modified duration of the bond. Using the Excel formula, answers will be D equals to 9.03 years and MD equals to 8,942 years) 3. We found the Price of the bond. (To calculate the price of the bond from the table of the T-bond we take price for 10 years which is 99,585912 per 100$, then we converted it to actual price ( 0.99585912 * 1000= 995.85912) 4. We found a Standard Deviation. First of all we collected data about the 10 year T-bond from the website (http://www.federalreserve.gov/releases/h15/) and followed all the instructions how to download it. And also inserted all the data needed to excel file. Then in order to calculate standard deviation we used the Excel formula (go to the fx, open select function and put the formula STDEV) and the answer you will get SD = 0,200197 % 5. We estimated market risk over the next 10 days using the Risk Metrics VAR formula and the confidence level 99% (or 1% VAR). all the information we needed we got above so we got 13135.32 (the formula we used was MD * price * SD * (10day)^(0.5) * 2.33). And we also had to add that we are with 99% confidence level sure that we will not lose more than $13135.32 over the next 10 days if interest rates will increase by 0,200197 % during these 10 days. But there is still 1% chance that our losses will exceed this amount. II. The whole process of the calculations of 10 days 1% VAR (99% confidence level) in position of 10 million in Euro (Historic-simulation Method) shown below step by step and followed the instructions: 1. Data about $/Euro. (From http://www.federalreserve.gov/Releases/H10/Hist/ copy and paste exchange rates over 2 year period. When you open Excel file, there should be a lot of information about exchange rate, but you should select only 501 observations and all unnecessary information delete, for instance: breaks etc) 2. We converted $/Euro. (Euros to U.S$ at the last rate 1.3076 $/Euro, 2013-03-15). 3. Depreciated the exchange rate by 1%. (To calculate you should take the last exchange rate 1.3076 $/Euro * by 0.99) 4. Revalue. (You just need to revalue using new depreciation rate of 1,294524 $/Euro) 5. Find delta. (Calculate difference in $; when the spot exchange rate changed, or delta= $-130760) 6. Find % changes exchange rate. (To calculate change percentage exchange rates for 500 observations (actually you need take 501 observations), use formula (Using Excel the first calculation should be like this [(new/previous) – 1] * 100 or you can put another formula {(new-previous )/previous*100}) and result will be the same) 7. Find gain/loss. (To calculate the real gain/loss you should multiply delta by changes exchange rates for each observations.) 8. Find worst loss/best gain. (Using Excel function (sort), you should find worst loss and best gain.) 9. Find 1% VAR for 10 days. (To calculate 1% VAR for 10 days with 99% confidence level, {take fifth worst loss (-195602.78109418) multiply by day 10^(0.5)} and you will get answer $ 618550,3049) and we can say that there is a 99% confidence level that the losses would...
tracking img