View Question
Q: Statistics and financial modelling ( Answered,   1 Comment )
 Question
 Subject: Statistics and financial modelling Category: Business and Money > Finance Asked by: mjstuehler-ga List Price: \$200.00 Posted: 02 Mar 2006 18:28 PST Expires: 01 Apr 2006 18:28 PST Question ID: 703068
 ```Question about semi-sophisticated statistics and financial modeling - lets say you are forming a porfolio comprised of N mutual funds - each fund has a mean rate of return and a standard deviation of returns. How do you determine the probability that the portfolio might produce the a particular rate of return over P periods? (Assuming you know the weight of each fund in the portfolio?) I'm trying to create an invesmtment plan in Excel, so I'd like to avoid using something like iterations (Monte Carlo sim) - is there a a formula for this?``` Clarification of Question by mjstuehler-ga on 24 Mar 2006 06:42 PST ```A clarification: here are the inputs: 1. the number of funds 2. the proportion of each fund in the portfolio 3. the mean return of each fund for each period 4. the standard deviation of returns for each fund for each period 5. the number of periods Based on these, I'd like to determine what the mean return and standard deviation of returns is for the portfolio after N periods``` Request for Question Clarification by hedgie-ga on 24 Mar 2006 23:15 PST ```Well, mjstuehler You would have to make some additional assumptions, such as the funds are statisticaly independent, (or provide additional inputs, such as cross-corelations) ... With few such mods, question would be: I have N stochastic processes (time series) P.i, characterised by parameters p.i.1 p.i.2 ... What would be such parameters of a combined process formed from these with weight w.i ? Something like that could be answered without need for Monte-Carlo simulation. for a simple process (simple model of a time series). Is that what you want?``` Clarification of Question by mjstuehler-ga on 26 Mar 2006 20:09 PST ```Hedgie, Thanks for your response, and my apologies for my delayed clarification. I would be satisfied with an answer that assumed that the funds were statistically independent, although a solution that allowed me to incorporate a correlation matrix would really be appreciated - a significant improvement. (Because I don't know what's possible, I don't know how much I can ask for!!) Please don't hesitate to let me know if there's any additional clarification or information I can provide for you.```
 ```mjstuehler I will divide the answer into three sections A) simplest case : independent stationary variables B) few words on time-series models C) stationary correlated variables A) We will employ the simplest statistical model: Value describing each component (a fund) is a random variable with normal distribution (described by mean and sigma). Then, the theorem which provides the answer is here: if X and Y are independent random variables that are normally distributed, then X + Y is also normally distributed You can skip the proof (which is here) http://en.wikipedia.org/wiki/Sum_of_normally_distributed_random_variables and general background on normal (= Gaussian or 'bell-like curve) distribution (which is here) http://en.wikipedia.org/wiki/Normal_distribution and just use the result: means are additive, and variances (not sigmas) are additive meaning: for N mutual funds (components) (lets say N=2) we have fund 1 has mean.1 and sigma.1 (average and standard deviation) fund 2 has mean.2 and sigma.2 (average and standard deviation) for a particular property (such as price, or dividend, current market value, or ROI ...) Let's pick an additive property (such as current market value = V) Than, when we compose a portfolio, property of the total is V.t = w.1 * V.1 + w.2 * V.2 (market value of the portfolio IS Sum of the Values of the components, That is not so for all properties true, e.g. not for ROI) -------------- mwaning of weights w Here V1 can be 100 shares of Goog , V2 1000 shares of GERN ... if weight w.1 = 2 and w.2=5, then the composite (portfolio) would have 200 shares of Goog and 5000 shares of Gern , OK? ----------------------------------- So, if V1 and V2 are Gaussian with parameters shown above, then V.t will be Gaussian with parameters mean.t= w.1 * mean.1 + w.2 * mean.2 and std. deviation sigma.t defined by sigma.t ^2 = (w.1 * sigma.1) ^2 + (w.2 * sigma.2) ^2 where ^ means power: 3^2 = 9, 2^3 =8, ... B) When you say (in your clarification) "standard deviation of returns is for the portfolio after N periods .." You are using symbol N in a second meaning. Let's talk about N funds, which we will keep for M time-periods (e.g. weeks, or months..) i = 1, 2, ... N and time l=1,2, .... M The simplest assumption is that the values V.i.l (value of dund i at time l) and independent from each other and of their past. In such a case, Values (V.1.1 , V.1.2, V.1.3 ) for find i=1 are just samples taken from the same distribution (N(mean.1, sigma.1), and so on for other i's. That assumes the fundamentals did not change over time and proces have no memory. That is a gross oversimplification. The commonly accepted model a 'random walk ' (aka Markov chain) in which prices do not have long term memory, but the next period price (V(t+1) depends on previous price V(t). The 'Nobel prize- winning' application based on this model (applied to options) is described here http://en.wikipedia.org/wiki/Black-Scholes_formula Books are available with details and generalizations, but the formulas are too complex for a spread sheet. Search Term: Black_sholes Popular expose of the model: ( a classic by Burton Malkiel ) http://www.amazon.com/gp/product/0393315290/ref=nosim/102-7446460-2049751?n=283155 http://www.defaultrisk.com/bk_g_arwdws.htm simpe summary http://www.ithadtobeyou.net/carpe/archives/000135.html C) We will stay with memory-less model and just remove the assumption of independence: We have N time-series V.i (i =1 , 2 ... N) which we treat is M samples from N distributions. We construct composite time series V.t as before We calculate 'expectation value' of square M2= and get M2= sum over i Sum over j < V.i * V.j > We need to understand relation of M2, variance and sigma, defined here: http://mathworld.wolfram.com/StandardDeviation.html and correlation coefficients r.i.j defined here http://mathworld.wolfram.com/CorrelationCoefficient.html and in (too many) details, here http://www.mega.nu/ampp/rummel/uc.htm and a bit more general way - as a Cross Correlation here http://astronomy.swin.edu.au/~pbourke/other/correlate/ For our purposes: M2 = second moment Variance is M2 - mean ^2 sigma is variance^.5 (square root) Inserting cross-correlations s.i.j and sigmas.i into the above expression, we obtain formula for means of the sigma.t in terms of sigmas and r's. mean.t is same as in the case A). If all cross-correlations s.i.j (for i != j) are zero, then this formula is reduced to the previous (independent variables) case. (Here the symbol != means 'not equal' ). I did try to make it as simple as possible, but there is always a space for improvement: Please do ask for clarification (RFC) if what I wrote is nor clear. After all is clear, rating is appreciated. Hedgie```
 ```There is a major flaw in the proposed logic. Fund movements are not independent. Most US funds have a very high correlation to the S&P 500. Thus, the Standard deviation of the portfolio will be much higher than if the components were independent. http://www.fasttrack.net provides a good tool box for this kind of modeling.```