Portfolio analysis - Excel and VBA

Portfolio analysis Copyright: Dr Ian O Connor, CPA Page 1 of 16 Portfolio analysis A Portfolio can be viewed as a combination of assets held by an investor. For each asset held, such as company stocks, the logarithmic or continuously compounded rate of return r at time t is given by = where is the stock price at time t, and is the stock price in the prior period. The volatility of stock returns, over period N is often estimated by the sample variance = 1 where r is the return realized in period t, and N is the number of time intervals. As the variance of returns is in units of percent squared, we take the square root to determine the standard deviation . Example (file: ) Suppose an investor has a four stock Portfolio comprising of shares on the Australian stock market as listed in Figure 1.

3. Covariance with VBA The Excel 2007 COVAR function returns the population covariance. To estimate the sample covariance, the custom function Covar_s has been developed. Here is the code. VBA: Covar_s (Available in the XLFProject.XLF_Module) 10 20 30 40 50 60 70 80 90 100 Function Covar_s(InArray1 As Variant, InArray2 As Variant) As Variant

Loading..

Tags:

  Excel

Information

Related search queries