Efficient Portfolio of Assets (The Optimization for Risk, Return and Probability)

What is the Efficient Portfolio of Assets? It is the balance of the risk and the return.

The approach is to maximize the return for a given level of the risk or to minimize the risk for a given level of the return which is the financial manager’s goal.

As you know, the total risk can be divided in two parts as follows:

Total risk = Nondiversifiable risk + Diversifiable risk

The firms can eliminate Diversifiable risk of the assets through diversification.

In this article, I am willing to present the method to decrease the risk for a given level of the return or to increase the return for a given level of the risk into the limited range of the probabilities assigned to the outcomes in two sections:

-Section 1: To optimize the risk, the return and probability for the portfolio of assets

-Section 2: To analyze the behaviour of a single asset during a period of the time

In fact, in the end of this article, you will have a spreadsheet of excel as the template of the designing for the efficient portfolio of assets.

At the first, let me have th debate on Section 1 as follows:

Let me start by using an example. Assume that we have the data for the forecasted returns of assets A, B, C, D, E, and F from 2012 to 2017 as follows:

				Assets Return (%)
Year		A	B	C	D	E	F
2012		7	19	7	25	8	17
2013		9	16	11	21	10	15
2014		11	14	13	19	12	13
2015		14	12	16	15	14	11
2016		18	10	20	12	16	9
2017		21	8	23	9	18	6

As we can see, to diversify the risk of this portfolio, we have Negatively Correlated of the time series. It means that we will be able to diversify the risk of this portfolio to closest to zero.

We are willing to calculate the proportions of each asset which should be combined into our portfolio (A, B, C, D, E, F) to reach the optimization for Expected Portfolio Return Annually, Expected Value of Portfolio Return (2012 -2017), Risk (Coefficient of Variation) and Probability. The procedure of the analysis is step by step as follows:

Step 1) Definitely we can use from the method accompanied by the conditions (1), (2), (3) exempt (4) and (5) included in my previous article of “EMFPS – Construction Works: How Can We Mix Several Types of Aggregates to Find Out the Best Size Gradation?”

Therefore, we can find the proportions of each asset as variables by using of the solving a Matrix Inverse as follows:

Assume we have Matrixes of S, X and P as follows:

S = Matrix (m*n)   Where: m = n

X = Matrix (m*1)

P = Matrix (m*1)

S.X = P

S’ * S * X = S’ * P

S’ * S = I

I * X = S’ * P

I * X = X

X  = S’ * P

We can solve above function by useing of Excel in which Matrix (S) is a (6*6) for asset returns – time, Matrix (X ) is included the proportions of each asset and  Matrix (P) is referred to Expected Portfolio Return Annually (Rp).

Step 2) we should make the numbers for Matrix (P) as our assumptions. The first try is to find the average of all assets A, B, C, D, E, and F for each year as arrays of Matrix (P) where we will have the same the proportions of each asset which is 16.7% (100 / 6) as follows:

Matrix (P) =

Year		Rp%
2012		13.83
2013		13.67
2014		13.67
2015		13.67
2016		14.17
2017		14.17

Step 3) we should assume Probability distributions for six outcomes. I have mentioned my assumptions below cited:

 Outcomes                      Probability (%)

-Outcome (1)                   10

-Outcome (2)                   13

-Outcome (3)                   15

-Outcome (4)                   17

-Outcome (5)                   22

-Outcome (6)                   23

Step 4) we should obtain Expected Value of Portfolio Return (2012 -2017) which is calculated as follows:

Rv = SUM [Rp(i) * P(i)]

Step 5) we will get Standard Deviation of expected portfolio returns (Qr) and Coefficient of Variation (CV) by using of below formula:

Qr = {SUM [(Rp(i) – Rv)^2) * P(i)]}^0.5

 CV = Qr / Rv

Step 6) we should evaluate the impact of two independent variables on one dependent variable by using of Sensitivity Analysis method. In this case, (Rp) and (Rv) have been considered as independent variables and CV has been considered as dependent variable. 

Step7) we should find and link the closest CV to zero by using of excel formula as follows:

=INDEX(cell(1):cell(final),MATCH(MIN(INDEX(ABS(cell(1):cell(final),),0,1)),INDEX(ABS(cell(1):cell(final),),0,1),0))

Now, our template for designing is ready. We can use from try and error method in three categories as follows:

A. we fix Rp and change probabilities into limited range for instance, I consider below constant Rp(i):

 Rp(i) = (13.5, 13.55, 13.6, 13.65, 13.7, 13.75, 13.8, 13.85, 13.9, 13.95, 14, 14.05, 14.1)

P(i) = (0.1, 0.13, 0.15, 0.17, 0.22, 0.23)

To change the probabilities into above limited range, we should obtain all Permutations without Repetition by using of VB codes in excel. The number of Permutations without Repetition can be calculated by below formula:

P (n,r) = n! / (n – r)!

Here is: P = 720

B. we fix the probabilities and change Rp(i) into limited range for instance, I consider below assumptions:

P(i) = (0.1, 0.13, 0.15, 0.17, 0.22, 0.23)

Rp(i) = (13.4, 13.5, 13.6,13.7, 13.8, 13.9)

To change Rp(i) into above limited range, we should obtain all Permutations without Repetition by using of VB codes in excel. The number of Permutations without Repetition can be calculated by below formula:

P (n,r) = n! / (n – r)!

Here is: P = 720

C. we change Rp(i) and probabilities simultaneously into limited range for instance, I consider below data:

Rp(i) and P(i) = (13.4, 13.5, 13.6,13.7, 13.8, 13.9, 0.1, 0.13, 0.15, 0.17, 0.22, 0.23)

To change Rp(i) and probabilities simultaneously into above limited range, we should obtain all Permutations without Repetition by using of VB codes in excel. The number of Permutations without Repetition can be calculated by below formula:

P (n,r) = n! / (n – r)!

Here is: P = 479001600

Then we should link and replace all Permutations without Repetition generated into Rp(i) and Probabilities cells in excel and track CV(min). Finally we will obtain Expected Portfolio Return Annually, Expected Value of Portfolio Return (2012 -2017) and Probability which are referred to minimum CV (risk). It will be the Efficient Portfolio of Assets.

I think this method can be utilized and expanded for many industries such as Food industry, Chemical industry, Pharmacology industry, Alloy industry, saving energy industry especially heat exchangers and so on.

Note:  “All spreadsheets and calculation notes are available.

The people, who are interested in having my spreadsheets

of this method as a template for further practice,

do not hesitate to ask me by sending an email to:

 soleimani_gh@hotmail.com or call me on my cellphone:

 +989109250225.

 Please be informed these spreadsheets are not free of charge.”

TO BE CONTINUED .......

EMFPS: How Can We Mix Several Types of Aggregates to Find Out the Best Size Gradation?

As you know, one of the most important tests to qualify the aggregates used in construction jobs such as Concrete, Pavement (Asphalt), Embankment at Roads and Yards and so on is sieve analysis which determines the particle-size distribution of an aggregate. We have so many tests to qualify the aggregates for instance, Alkali-Aggregate Reaction, Stripping (for chemical properties) and Gradation and size, Toughness and abrasion resistance (Los Angeles Abrasion Test), Durability and soundness, Particle shape and surface texture, Specific gravity, Cleanliness and deleterious materials (for physical properties).

What is different between grading test of aggregate and other tests?

When other tests do not obtain the appropriate results, we have to reject the aggregate. But if we have not a good sorting of sieve analysis which is not compatible with the standards, we will be able to improve the gradation of aggregates by mixing several types of them. As the matter of fact, in the real job, we cannot usually find the aggregate generated in river mining (especially fine aggregate for concrete) in which the particle size distribution of the aggregate (sorting) will be completely compatible with the standards. What can we do?

In this article, I have brought a simple calculation accompanied by an example to solve this problem. Why do I bring you this article? Because I am willing to establish a link between Engineering fields and Financial Management so that in my next article you will see to determine the risk minimizing combination (Risk Diversification), we can use the same method.

Here, I explain this method with an example in the field of Concrete Technology.

Assume we are willing to produce the fresh concrete at the site. One of the most important components of the concrete to gain high workability and economic savings is good sorting of fine aggregate. Two important factors are controlling the fine aggregate of the concrete:

1) Sandy Equivalent (SE) > 75%

2) Fineness Modulus (FM) that should be:  2.4 < FM < 3.2

If Fineness Modulus increases more than 3.2, we have to use more cement to reach our specific compressive strength of concrete accompanied by high workability (75mm < slump < 100mm) in which we will not have any economic savings.

The formula of FM is as follows:

FM = SUM (cumulative percentage on specified sieves) / 100

Where:

F.M =fineness modulus


As we can see, F.M is completely referred to sieve analysis of the fine aggregate.

specified sieves

=

0.150 mm (No. 100), 0.30 mm (No. 50), 0.60 mm (No. 30), 1.18 mm (No. 16), 2.36 mm (No. 8), 4.75 mm (No. 4),  9.5 mm (0.375-in.)

Now, let me bring an example of an available sample of fine aggregate (A) that the sieve analysis is as follows:

Size of sieves    3/8”  No.4    No.8    No.16    No.30     No.50     No.100

Passing (%)       100   85         63         47         28           12            7

We can calculate:      F.M = 3.58

According to ASTM C 33, fine - aggregate grating limits is below cited:

Size of sieves   3/8”    No.4     No.8      No.16     No.30    No.50    No.100

Passing (%)     100     95-100   80-100    50-85     25-60      5-30       0-10           

We can see that fine aggregate (A) is not compatible with ASTM C 33 and so F.M is outside of the limit.

Now, we want to mix two other fine aggregates of (B) and (C) with fine aggregate of (A) so that we will have a new fine aggregate which will cover all standard requirements. The sieve analysis of these two fine aggregates is as follows:

Sieve Analysis of Fine- aggregate (B):

Size of sieves    3/8”      No.4      No.8       No.16      No.30      No.50      No.100

Passing (%)        100        93          85           70            43           23             12

Sieve Analysis of Fine- aggregate (C):

Size of sieves    3/8”      No.4      No.8       No.16      No.30      No.50      No.100

Passing (%)        100      100         97           83           65            43              22

What will be the volume percentages of A, B and C for our mixed aggregate?

Now, we have three variables which are “x” as the volume percentage of A, “y” as the volume percentage of B and “z” as the volume percentage of C.

It is clear, we can easily find these variables by using of the solving aMatrix Inverse as follows:

Assume we have Matrixes of S, X and P as follows:

S = Matrix (m*n)   Where: m = n

X = Matrix (m*1)

P = Matrix (m*1)

S.X = P

S’ * S * X = S’ * P

S’ * S = I

I * X = S’ * P

I * X = X

X  = S’ * P

You can solve above function by useing of Excel (Please see my spreadsheet of Excel).

There are some conditions to solve the matrix extracted from our example (mixed aggregates) below cited:

 1) The number of rows, which are the  the size of sieves, should be equal to the number of the variables (x, y, z) or columns in matrix S. In fact,we have: m = n

The number of rows in matrix X and P are equal to matrix S.

The number of columns in matrix X and P are equal to 1.

2) In this example, “x” is related to aggregate type A and respectively “y” for B and “z” for C.

3) Elements of matrixes (aij) are the same the percentages of passing

4)We should  have enough date issued by the standards such as ASTM, AASHTO and so on.

5) We should use from the best data issued by the standards as Index Data which are compatible with our requirements to solve this matrix. What is the meaning? What are the  Index Data? Here, the Index Data is to choose the size of sieves. One the the most important conditions is to choose the best size of sieves to decrease the variances (Diversification) that it could  be also used to reduce the risk of assets in the field of Financial Management. I will depict it in my next article.

For the better perception, let me continue the example as follows:

According to above mentioned, we have three variables and we should choose three size of sieves included in below table:

                                                       Passing (%)

Size of Sieves      A (x)         B (y)     C (z)       Average of Limits

3/8”                           100            100             100            100

No.4                          85              93               100            97.5

No.8                          63              85               97              90

No.16                        47              70               83              67.5

No.30                        28              43               65              42.5

No.50                        12              23               43              17.5

No.100                       7               12               22              5

     

How can we choose the best Size of Sieves?

Definitely we should diversify and decrease the variances. I suggest you below method as follows:

V1 = [(85- 97.5) +(93 - 97.5) +(100- 97.5)] / 3

V2= ----------------------------------------------

V3 = -----------------------------------------------

V4 = -----------------------------------------------

V5 = -----------------------------------------------

V6 = ----------------------------------------------                               

 Then we should find the least amount of SUM (Vi) which is reaching to zero.

(In formula of SUM (Vi), we should know that n = 3 because we have only three variables and should choose three Size of sieves among six Size of Sieves).   

Of course, it will be very hard and we should obtain all permutations and combinations. I will explain this method completely in my next article about Risk Diversification of Assets in the field of Financial Management.

Now, the size of sieves: No.8, No.16 and No.100 have the least amount of

SUM (V) but when we try to find the variables, we find below percentages:

x = 141%

y = -113%

z = 72% 

F.M = 3.3

As we can see, the results are not logic. Therefore, we should use from next SUM (V) which give us the size of sieves: No.8, No.16 and No.50

The results are as follows:

x = 96%

y = 4%

z = 0%

F.M = 3.5

It means that we should use 96% of aggregate type A and 4% aggregate type B. We do not need to use from aggregate type C.

In fact, to find the appropriate results, you can utilize from try and error method by using of my Excel spreadsheet.

Note:  “All spreadsheets and calculation notes are available.

The people, who are interested in having my spreadsheets

of this method as a template for further practice,

do not hesitate to ask me by sending an email to:

 soleimani_gh@hotmail.com or call me on my cellphone:

 +989109250225.

Please be informed these spreadsheets are not free of charge.”

EMFPS: How Can We Obtain “Beta” to Analyze CAPM? (CON)

Referring to my previous article of "EMFPS: How Can We Obtain “Beta” to Analyze CAPM?" on link: http://emfps.blogspot.com/2011/09/emfps-how-can-we-obtain-beta-to-analyze.html, let me explain you the next steps by an example as follows:

1) Without any dividends:

Assume the data of Company “M” from Jan 3, 2005 to Dec 1, 2010 is below cited:

Date	Open	High	Low	Close	Avg Vol	ADj Close*
Jan 3, 2005	4.74	4.92	4.7	4.88	792,600	4.21

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

Dec 1,           2010

5.74

6.52

5.74

6.23

4,193,000

6.23

The data of the related Stock Index of the company is as follows:

Date	Open	High	Low	Close	Avg Vol	Adj Close*
Jan 3, 2005	907.02	940.94	897.13	916.27	54,441,400	916.27

--------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

Dec 1, 2010

1,482.69

1,529.95

1,477.57

1,518.91

141,888,300

1,518.91

-To calculate the return rate for both Company and Stock Index, we can use below formula because there is not any dividends:

Ra = (Pt – Pt-1) / Pt-1

Ra = the return rate for Stock Index and Company

P t-1 = Close price on Jan 3, 2005

Pt = Close price in the next month

-On excel spreadsheet, calculate one cell by using of above formula and copy on all cells related to next months until Dec 1, 2010.

-Now, you have all rate of return for each month (Company and Stock Index). Calculate the average for Stock Index only

-Multiply the average of Stock Index by 12 to obtain the rate of return annually (Rm) which is utilized to analyze CAPM

-Use from excel formula to calculate covariance of the return rate for both Company and Stock Index:    =COVAR (Array1, Array2)

 Where:

Array 1 = all return rate of Company

Array 2 = all return rate of Stock Index

-Use from excel formula to variance of return rate for Stock Index:  

= VAR (number1, [number2]…..)

-Calculate the Beta of Company: COVAR (Ri,Rm) / VAR (Rm)

According to my spreadsheet, I obtained the Beta to be equal 1.501137

2) With dividends:

Now, we assume that the company had paid the dividends as follows:

Date			Dividends
17-Jan-07	0.05
27-Jul- 07	0.05
29-Oct-08	0.25
29-Jul-09	0.05
28-Jul-10	0.11

We should look at the date of dividend and improve the company’s return rate accordingly on our spreadsheet by using of below formula:

Ra = [D + (Pt – Pt-1)] / Pt-1

Where:

Ra = the return rate of Company

D = dividend

Pt = Close price for month after dividend date

Pt-1 = Close price for month before dividend date

I calculate the Beta of the Company which is equal to 1.499519

Why are they approximately the same (without and with dividend)?

Because when the company paid the dividend accordingly its share price fell down which shows us the dividend policy of the company. I can say to you this dividend policy is not wrong because the Beta of with dividend is not more than the beta of without dividend.

Conclusion

As the result of this article, I would like to compare the market return of this company with historical market return of the world (see sheet 4 of my spreadsheet).

I calculated annually return rate of Company “M” by adding all monthly return rate (with dividends) then I used from historical returns mentioned on below link:

 http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/histret.html  (By: Aswath Damodaran)

To analyze the Beta, I got the gradient as follows:

Beta = Delta (company’s return) / Delta (Market return)

Here is my data:

Year		Rc	Rm
2005		-7.67%	4.83%
2006		64.12%	15.61%
2007		-3.31%	5.48%
2008		-49.42%	-36.58%
2009		39.61%	25.92%
2010		41.53%	14.86%

Where:

Rc = the return rate of Company

Rm= the return rate the whole of the market

Year	Beta
2006	6.659839
2007	6.656976
2008	1.096235
2009	1.424453
2010	-0.17352
B (av)	3.132797

As we can see, in this case, the average of Beta is not important but the trend of the Beta is very contemplative. I remember that I wrote an article of “ New Economic System” on below link:   http://emfps.blogspot.com/2010/10/new-economic-system-part-1.html Maybe this method to analyze the Beta will lead us toward the better  perception and analysis of economic systems.   Note:  “All spreadsheets and calculation notes are available.  The people, who are interested in having my spreadsheets  of this method as a template for further practice, do not  hesitate to ask me by sending an email to : soleimani_gh@hotmail.com or call me on my cellphone:  +989109250225. Please be informed these spreadsheets are not free of charge.”