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 .......