How to build a (beta?) distribution based on limited data?

[joeu2004]

How can we build a distribution model based on the following limited data?

For a particular (Schwab) model portfolio, we have only the following historical data for some 44-year period:

worst return: -12.5%
best return: 27.0%
arithmetic avg return: 9.1%
#years with negative returns: 6
arithmetic avg negative return: -4.4%
arithmetic avg non-negative return: 11.2% [1]

One obvious way to build a distribution might be the following:

=IF(RAND()<6/44,
 IF(RAND()<0.5,-RANDBETWEEN(44,125),-RANDBETWEEN(0,44)),
 IF(RAND()<0.5,RANDBETWEEN(0,112),RANDBETWEEN(112,270)))/10

That assumes a uniform distribution of returns between the known data points. We really have no data to support that assumption -- or any other assumption, for that matter.

Nevertheless, I wonder: can we derive a beta distribution that "closely" fits the data?

I don't know much about how to work with the beta distribution. But I notice that
=BETADIST(A1,5,2,-12.5,27) produces a general shape that I would expect for the data above, where A1:A100 are uniformly distributed values between -12.5 to 27.

I arbitrarily chose alpha=2 and beta=5 based on the "beta distribution" wiki page. I notice that changing the ratio of alpha and beta changes the skew. And changing the magnitude of alpha and beta changes the kurtosis.

But I have no clue about how to manage alpha and beta so the resulting beta distribution "closely" fits the data -- other than to try random values.

(Even then, I don't know how to constrain the range of random alpha and beta so that the distribution curve maintains the desired general shape.)

I would appreciate any constructive thoughts about how to choose alpha and beta for BETADIST or about a different distribution that "closely" fits that data.

TIA.

Some motivations.... The segmented uniform distribution above clips values at the extremes. That distorts the average negative and non-negative returns, as well as the overall average. Also, in financial analysis, it is common to assume a normal distribution of returns in the long-run. So some kind of "bell-shaped" curve -- like a "normal-like" beta distribution -- seems like a reasonable choice.

PS.... What "closely fits" means is TBD. For now, assume that it is subjective.


-----
[1] The arithmetic avg non-negative return is derived from the other data based on a weighted arithmetic avg, to wit:
=(9.1 - (-4.4)*6/44)*44/38 / 100

 

[krausr79]

I keep getting things that are cumulative distributions when using BETADIST. Are you looking for that or a probability distribution? In order to model a uniform distribution from -12.5 to 27, I used the values -12 through 27 in one column and your betadist setup in the other and did a scatterplot.

 

[joeu2004]

I keep getting things that are cumulative distributions when using BETADIST.

My bad! I should have said
=BETADIST(A2,5,2,-12.5,27) - BETADIST(A1,5,2,-12.5,27)
produces the general shape that I expect, based on the wiki page.

PS.... I want to delete or otherwise terminate this discussion. It is not an Excel question. I might not participate further.

 

[Smitty]

PS.... I want to delete or otherwise terminate this discussion. It is not an Excel question. I might not participate further.

No worries. We'll leave it open since you already got a response.