(Local Valuation VaR) ? Excel Project

I need to choose and download three stock prices from YahooFinance and then calculate the means, standard deviations, correlations and co-variances of their daily returns.

USE EXCEL

To Do

1.Pick your style first. Then pick your favorite three stocks based on your style. You choose your own style. You may refer to Wall Street Journal for possible stocks under your style.

2.Calculate the means, standard deviations, correlations and co-variances of their daily returns. Do not annualize; keep everything in daily terms. For calculating co-variances, you may calculate correlations among three stock returns and then calculate co-variances such as Covariance (asset1, asset2)(asset1, asset2)*(Std Dev 1)*(Std Dev 2).

3.For stock returns, you need to download stock prices from YahooFinance website. For example, suppose you want to download the stock prices for ?IBM?. Then search ?IBM?. On the left hand column, find ?Historical Prices? and click it. For the data period, select September 1^st 2016 as an end date and September 1^st 2015 as a begin date. Then get daily prices for one year.

Then, find ?Download to Spreadsheet? on the bottom of the page. Among many variables, you need ?Adj Close?. From the adjusted close prices, you will calculate daily returns such as (Price1 ? Price0)/Price0 = Return1. Using daily return for three stocks, you will calculate returns, standard deviation and co-variances as inputs for calculating 99% daily VaR.

4.You will make the same table as in the Baring case except that you have three stocks instead of two. You assume that you have 1,000 shares for the first stock, 2,000 shares for the second stock, and 3,000 shares for the third stock.

5.Now you read your results. As of September 1^st 2016 how much is your portfolio position, 99% daily VaR, marginal VaR for each stock, and component VaR for each stock.

On the "EXAMPLE 1 ? Baring?s case? I uploaded, you can have an idea what it needs to be done.

PLEASE FOLLOW THE ?INSTRUCTIONS? PAPER I UPLOADED.

READ THE LECTURE 1.

Financial Risk Management - Fall 2016 PROJECT 1: Local Valuation VaR Overview The purpose of this assignment is to introduce you to the "science" of calculating the VaR, especially local valuation VaR. Your task will be to choose your three favorite stocks under your management (actually your \"style\"), and then determine how much risk your portfolio is facing. You calculate 1 day VaR at the 99% confidence level. Then you calculate marginal VaR and component VaR for each stock in your portfolio. Data You will download three stock prices from YahooFinance under your style. Your stocks should be chosen according to an investment \"style\". We will use two dimensions of style: \"growth versus value\" and \"small versus large\". We will simplify the definitions of these styles for this assignment. In the real world, these dimensions, especially value and growth, are much more complicated. For this project, a \"value\" stock is a stock with a price to book value ratio of 2.0 or less. A growth stock has a price to book value of 2.0 or more. You then need to decide whether you will manage a \"small cap\" or a \"large cap\" portfolio. For this assignment a \"small cap\" is any stock with less than $1 billion in market value (price times number of shares). A large cap is any stock with more. The information for the style is readily available from the Yahoo Finance website. For example, let's pick IBM. From YahooFinance, you search \"IBM\" then go to \"Key Statistics\" and \"More Key Statistics\". Then you will find \"Market cap\" and \"Price/Book\" ratio. Therefore IBM should be a \"large (more than $1 billion) growth (price to book ratio more than 2.0) stock.\" To Do 1. Pick your style first. Then pick your favorite three stocks based on your style. You choose your own style. You may refer to Wall Street Journal for possible stocks under your style. 2. Calculate the means, standard deviations, correlations and co-variances of their daily returns. Do not annualize; keep everything in daily terms. For calculating co-variances, you may calculate correlations among three stock returns and then calculate co-variances such as Covariance (asset1, asset2)=correlation(asset1, asset2)*(Std Dev 1)*(Std Dev 2). 3. For stock returns, you need to download stock prices from YahooFinance website. For example, suppose you want to download the stock prices for \"IBM\". Then search \"IBM\". On the left hand column, find \"Historical Prices\" and click it. For the data period, select September 1st 2016 as an end date and September 1st 2015 as a begin date. Then get daily prices for one year as shown below. Then, find \"Download to Spreadsheet\" on the bottom of the page. Among many variables, you need \"Adj Close\". From the adjusted close prices, you will calculate daily returns such as (Price1 - Price0)/Price0 = Return1. Using daily return for three stocks, you will calculate returns, standard deviation and co-variances as inputs for calculating 99% daily VaR. 4. You will make the same table as in the Baring case except that you have three stocks instead of two. You assme that you have 1,000 shares for the first stock, 2,000 shares for the second stock, and 3,000 shares for the third stock. 5. Now you read your results. As of September 1st 2016 how much is your portfolio position, 99% daily VaR, marginal VaR for each stock, and component VaR for each stock. Relevant Spreadsheet Functions In order: 1. To calculate the mean, type: 2. To calculate the standard deviation of the sample, type: 3. (to calc. the std dev of the population,) 4. To calculate. the correlation, type: 5. To calculate. the sum, type Excel Functions =AVERAGE (C3:AZ3) =STDEV(C3:AZ3) =STDEVP(C3:AZ3) =CORREL(C3:AZ3,C4:AZ4) =SUM(BO3:BO6) Local valuation VaR Welcome to Financial Risk Management Week 2. As a first step toward risk management, we are going to spend the next three weeks on equity portfolio risk management. There are two ways of measuring value at risk (VaR): local valuation and full valuation. In week 2, we will focus on local valuation VaR. Weeks 3 and 4 will be devoted to full valuation. Please open your MS Excel sheet and practice your calculation. It's really a hands-on class. Then you can finish Project #1 successfully. 2.1 Normal Distribution Recall the definition of VaR: the expected maximum loss (worst loss) over a target horizon within a given confidence level. We assume that the return on a portfolio is normally distributed with mean and variance. If we plot the distribution of expected returns, the value of the portfolio at the chosen probability level is the VaR. ~ , In a standard normal distribution, 95% of the values lie within 1.65 standard deviations from the mean. Therefore, for a VaR estimate to be accurate to the 95% confidence level, the VaR will lie on the distribution -1.65 standard deviation away from the expected return. Similarly, 99% of the values in a standard normal distribution lie within 2.33 standard deviation from the mean, to find the 99% confidence level. 2.1 Portfolio VaR ... , For the 95% confidence interval, =1.96 and =2.33 for the 99% confidence interval. mmult(A,B) is operator for the matrix multiplication, matrix A and matrix B, in Excel. Weight could be either portfolio % weight or dollar amount. When it's portfolio % weight, it should be multiplied by \"W 2\" to get the variance in dollar amount where W stands for total portfolio dollar value. 2.2 Diversified and Undiversified VaR | | (Example) Compute the VaR (for confidence interval 99% and holding period 10 days) for each of the two shares: Share Value($ mil) One day volatility (%) A 10 2 B 5 1 Also calculate the diversified and undiversified VaR if the correlation coefficient is 0.3. (Answer) -Undiversified VaR A: 2.33 $10 (0.02) 10 = $1.4736 mil B: 2.33 $5 (0.01) 10 = $0.3684 mil Total undiversified VaR = $1.8420 mil -Diversified VaR Portfolio variance: 2 1 0.3 0.02 0.02 0.01 0.3 0.02 0.01 0.01 2 1 = mmult (x, (mmult (,x))) [shift][Ctrl][Enter] = 0.00194 Diversifier VaR = 2.33 sqrt (0.00194) sqrt (10) = $0.3245 mil 2.3 VaR Tools Marginal VaR (VaR): The change in portfolio VaR resulting from taking an additional dollar of exposure to a given component. It is also the partial derivative with respect to the component weight. Component VaR (CVaR): A partition of the portfolio VaR that indicates how much the portfolio VaR would change approximately if the given component was deleted. By construction, component VaRs sum to the portfolio VaR. Now let's take a look at the Baring's case. The table in the previous page shows the positions of the portfolio Baring had before it collapsed. The data in the upper portion should be given. Using the data, you can replicate the lower portion using Excel program. One thread is open on the Black Board. Please feel free to post questions. You are supposed to make an expanded table for your own portfolio in Project #1. Additional information for calculation is as follows: Please consider the following formulas in Excel. For matrix multiplication, you need to enter simultaneously [SHIFT][Ctrl][Enter] keys at the end