residual variance function?

  • Thread starter Thread starter Kristen
  • Start date Start date
K

Kristen

I have an assignment that suggests I use the Portfolio of Securities
worksheet from the solvsamp.xls workbook to optimise a portfolio.

The instructions in solvsamp.xls suggest the use of residual variance.

I presume it is residual variance calculated for the fit of the stock
to the market index.

Is there a function that will estimate residual variance in Excel?

Thanks for all your help
Kristen
 
=SUMPRODUCT((y-predicted)^2)/(n-parms)

Where
predicted = fit of the stock to the market index
n = number of data points
parms = number of parameters estimated from the data to fit to market index

Jerry
 
Jerry

Thankyou for your reply

Would you mind helping me some more with this

I'm not sure how to calculate predicted or parms

Thanks for your help
Kristen
 
Reliably modeling the stock market is an extremely complex problem, and
it is unlikely that any two people will completely agree on an approach.
SolvSamp.xls does not address this problem. It assumes that you
already have reduced the performance of the stocks to betas with
residual variances, and given that, shows how to adjust your portfolio
holdings for various investing objectives (maximizing return or
minimizing risk).

Jerry
 
SolvSamp.xls refers to modeling the the individual stocks by the Sharpe
single-index model. A Google search on "Sharpe single-index model" will
give you hundreds of hits. Sharpe's model seems to to sidestep the
question of modeling the market, by just modeling the differential
performance of these stocks relative to a market index portfolio. It
uses a linear mixed effects model of this differential performance.
Since the model includes multiple variance terms, there is no simple way
to fit Sharpe's model in Excel, though theoretically it would be
possible to program Excel to do it.

Jerry
 
Fine thanks

I have calculated beta, but even though the search for Sharpe
single-index model gives plenty of hits, it has not really helped me
much.

Wonder why they have not made it more simple, like returns to standard
deviation?

Kristen
 
Back
Top