A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA

Thursday, November 3, 2011

Efficient Portfolio of Assets: Application of Markov Chain (CON)


In this article, I am willing to explain why I wrote previous article of “Efficient Portfolio of Assets: Application of Markov Chain”. The reasons are as follows:

1) As you can see, the most crucial thing to apply the model of Markov Chain for the transactions in finance is to assume the constant rate for Risk and Expected Return. In fact, key factors are to be fixed them. But how can we secure a constant rate for risk and expected return? It can be done by injecting the money as fuel. Let me bring you a real example about the transactions of Crude Oil as an asset during the period of 24 hours on May 7, 2010 as follows:
Crude Oil Price
May 7, 2010
Hour
Minute
Second
Pr (USD)
Ra
11
43
19
75.41
12
1
20
76.18
1.02%
12
14
32
75.23
-1.25%
12
55
30
76.04
1.08%
23
40
35
75.41
-0.83%

Where we have:
Real Price (USD) = Pr
Expected Price (USD) = Pe
Return Rate (%) = Ra
Expected Return (%) = Re
Referring to above mentioned, we have variable return rate on each exact time. If we want to have the constant return rate (Re), the Expected Prices (Pe) will be changed as follows:
Pr
Pe
Re
75.41
76.18
76.18
1.02%
75.23
76.96
1.02%
76.04
76
1.02%
75.41
76.815
1.02%

In fact, in the staring of deals, the price is $75.41 and after the first transaction, the return rate will be revealed by the price of $76.18. If we want to control and have a constant returning rate, we should take the action as follows:

We have formula of return rate:
Re = (Pe -76.18) / 76.18
Regarding to above table, to fix expected return rate and to have a constant Re, we have two states as follows:
-Sometimes Pr > Pe: Our strategy in action will be the injection and investment of the money on others assets to fall down the real prices
-Sometimes Pr < Pe: Our strategy in action will be the injection and investment of the money on Crude Oil asset to increase of the real prices.
Of course, this is a game and maybe the application of the Game Theory will help us to find the best analysis. Because we have the limited internal resource and are not be able to purchase so many numbers of the shares to increase or decrease of the share prices (Which asset is the better to buy? How much percentage should we buy?).
Application of the Game Theory after PEST, Industry and SWOT analysis will guide us to find how much percentage of the shares and which ones should be purchased in which the total action will be affected on Pr and Pe.
As the result of this model, a fluctuation will be raised among the whole of the stock markets when there is the lack of the Value Chain throughout the world in which it can be linked the scarce resources. In this case, we have not enough money (Energy) to handle the model of Markov Chain. As the matter of fact, it means there is the lack of liquation on assets to reach the critical points.
This model can be also expanded for corporate strategy where the shareholders move among the Corporations.

2) Referring to above mentioned (Reason 1), we are speaking about the constant rates (numbers) such as Risk and Expected Return, the types of the states (Assets), money, liquation and critical points.
Now, I can remind you about my first article of “Actually, what is the problem?” that I sent this article on https://www.xing.com/net/pri46ffacx/mathe/general-interest-remarks-and-links-5223/actually-what-is-the-problem-14781292/on Oct 25, 2008 (link of this blog: http://emfps.blogspot.com/2010/10/actually-what-is-problem-part1.html).
My question was: Can we join Gibbs’s formula (F = C – P + 2) and phase diagrams in thermodynamic to all of systems in the world?”
The answer is yes. Because of below cited:


-In above model of Markov Chain, we have three variables of the Time, Risk and Expected Return
- In phase diagrams, we have three variables matched by Risk to Pressure and Expected Return to Temperature and the time
-We have the money matched to the Energy, the types of the states for both of them, material matched to assets, liquation and critical points for both of them.
- The most important thing is that there are the critical points for each two systems in which the variables will be the constant numbers (boundaries)in this critical points.
You can see both systems are completely matched together.

3) Referring to Reason (2), I found an example or application for my first article after 36 months. Three days ago, I posted the article of “The Constant Issues, Universal Laws and Boundaries Conditions in Physics Theory” on the link of “http://emfps.blogspot.com/2011/10/constant-issues-universal-laws-and.html “and I told that I am working on a new constant natural number. Maybe I will find an example or application during the period of the next 36 months???

4) What will be the strategy in action?
Referring to Reason (1), it can be proved that we need to discover new resources of the energy other than the existing resources to eliminate the fluctuation on the stock markets.
One of the ideas to discover new resources of energy is to save the energy during the period of time accompanied by the constant conditions (refer to above model and the constant rate). In this case, each 1 KJ of energy saving will be just equal to 1 KJ discovered energy. What are the constant conditions? The methods, which are applied by energy –saving, should not be suffering the whole of the people in the world. In the other word, the people should feel that they are convenience before and after energy –saving. This is the constant conditions. Of course, this strategy needs that the people in the world cooperate to abandon their wrong habits. I had already brought an example of energy saving in article of “Saving Techniques: Optimization in Boiling Water Consumption on the link of “http://emfps.blogspot.com/2010/10/saving-techniques-part-1-optimization.html
As you can find on Recommendation of this paper:
“What is the Influence of energy saving by the people on environment and business in the world?
International Energy Outlook 2010 (IEO) stated that total world energy consumption rises by an average annual 1.4% from 2007 to 2035. If we assume only 61% population of these 39 countries care about energy saving on the case of boiling water, AE can be calculated just equal to 1.4%. It proves that this method as well as works”.



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

  

Wednesday, November 2, 2011

Application of Markov Chain


Following to the article of “EMFPS: Efficient Portfolio of Assets (The Optimization for Risk, Return and Probability)” on the link: http://emfps.blogspot.com/2011_10_09_archive.html
I would like to remind you about Step (7) – Part (A) where we fix Rp and change probabilities into limited range. But how can we fix the probabilities distribution into a limited range? It means that we should anticipate the probabilities distribution for the future. Here I have used from homogeneous Markov Chain as a tool to foresee the probabilities distribution.
Now, let me start again the problem with the example mentioned on above article as follows:
We had 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   

I consider the people in city or country or the location (M) who are the owners of assets A, B, C, D, E, and F. I assume the owners of each asset as a particular area or state are dealing as follows:
Owners of Assets                 Particular Area (State)
A                                                             S1
B                                                             S2
C                                                             S3
D                                                            S4
E                                                             S5
F                                                             S6
At the first, we need to obtain recent data of the total transactions for each particular area for instance, how many people who are the owners of asset “A “would like to deal only into area “A” or liquid their asset to go area B, C, D, E or F (in the period of exact time). The approach will be certainly based on the balance of Risk and Expected Return rate. Therefore, we have to highlight them that the conditions included Risk and Expected Return is equal for all six assets.
How can we find statistics data for the whole of the transactions?
It is clear; there are two types of the data to collect:
1) Primary data: By using of distributing the survey and questionnaire among the people to know their interest.
2) Secondary data:   Refer to historical data collected from internet and so many finance websites for instance, the Volume of transacted shares in a Stock Index during the period of the last time.
Definitely all people do not fill our questionnaire accurately. Therefore, we should have a cross –section of primary and secondary data. On the other hand, we should have a good PEST analysis and then a good Industry analysis for each asset to confirm the combination of primary and secondary data in which we should perceive all Economic, Politic, Society -Cultural and Technology indicators which are affecting on all transactions.
Now, assume we have made our final collected data as follows:
-In Area S1: 30% of people are interested in dealing into area S1, 12% of people are interested in liquating their asset and deal into area S2, 18% deals into S3, 20% deals into S4, 15% deals into S5, 0.05% deals into S6
- In Area S2: 10% of people are interested in dealing into area S1, 20% of people are interested in liquating their asset and go into area S2, 30% go to S3, 0.05% go to S4, 17% go to S5, 0.18% go to S6
-In Area S3: 20% of people are interested in dealing into area S1, 10% of people are interested in liquating their asset and go into area S2, 12% go to S3, 21% go to S4, 18% go to S5, 21% go to S6
-In Area S4: 10% of people are interested in dealing into area S1, 18% of people are interested in liquating their asset and go into area S2, 15% go to S3, 25% go to S4, 10% go to S5, 20% go to S6
-In Area S5: 10% of people are interested in dealing into area S1, 13% of people are interested in liquating their asset and go into area S2, 25% go to S3, 16% go to S4, 14% go to S5, 25% go to S6
-In Area S6: 20% of people are interested in dealing into area S1, 0.09% of people are interested in liquating their asset and go into area S2, 13% go to S3, 17% go to S4, 25% go to S5, 15% go to S6
To simplify above information, we can use from a matrix which is named as transition or movement matrix (S) as follows:
Matrix (S) =
S1
S2
S3
S4
S5
S6
S1
0.3
0.12
0.18
0.2
0.15
0.05
S2
0.1
0.2
0.3
0.05
0.17
0.18
S3
0.2
0.1
0.12
0.21
0.18
0.21
S4
0.1
0.18
0.15
0.25
0.1
0.2
S5
0.1
0.13
0.25
0.16
0.14
0.25
S6
0.2
0.09
0.13
0.17
0.25
0.15

We have the starting of the movement (deals) on rows and the ending of the movement (transactions) on columns in the period of the exact time.
Let me model the problem as a Markov Chain (homogeneous) to reach the fixed probabilities distributions in the future (at least until 2017 year in this example).
Of course, we should know that there are three conditions (properties) for each problem to be considered as a Markov Chain as follows:
-Each one of the deals done in this system stays a Risk and Expected Return exactly equal after the transaction for all assets in the period of the distinct time.
-The total percentage transactions made by the people into each area must be equal to 1.
- The primary probabilities distribution is not changed over the distinct time (Matrix (S)).
I as well as know that my assumptions are not exactly accurate. But to solve any problem, we should be able to simplify a complicated problem into boundaries conditions in which we need to sure if our assumptions are reasonable. In fact, there is the fundamental difference between the accurate and the reasonable.
If we consider that Matrix (S) is for the first transaction, we will be able to anticipate the probabilities for the second transaction as follows:

S^2 =
0.1794
0.138
0.1856
0.1863
0.1513
0.1594
0.1597
0.1293
0.1874
0.1633
0.1768
0.1835
0.1675
0.1337
0.1806
0.1832
0.1643
0.1707
0.16
0.1414
0.1821
0.177
0.1646
0.1749
0.1605
0.1289
0.1731
0.1779
0.1757
0.1839
0.1648
0.1328
0.1879
0.1818
0.1597
0.173




After the third transaction:
S^3 =
0.167449
0.135237
0.182456
0.179637
0.16344
0.171781
0.165079
0.132657
0.182574
0.178067
0.166625
0.174998
0.16546
0.134598
0.182678
0.179218
0.164359
0.173687
0.164409
0.134689
0.183509
0.17763
0.165285
0.174478
0.164464
0.133764
0.182849
0.178746
0.165509
0.174668
0.165867
0.134181
0.181737
0.179471
0.164906
0.173838

After the fifth transaction:
S^5 =
0.165483
0.134269
0.182655
0.178807
0.164941
0.173846
0.165455
0.13424
0.182622
0.17884
0.16496
0.173882
0.165456
0.134254
0.182647
0.178809
0.16496
0.173875
0.165446
0.134232
0.182638
0.178808
0.164981
0.173895
0.165443
0.134243
0.182634
0.178818
0.164971
0.173891
0.165457
0.134257
0.182643
0.178814
0.164955
0.173874

After the eighth transaction:
S^8 =
0.165457
0.134249
0.182641
0.178815
0.164962
0.173877
0.165456
0.134249
0.182641
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877

After the thirteenth transaction:
S^13 =
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877
0.165456
0.134249
0.18264
0.178815
0.164962
0.173877


As we can see, after 13 deals and the ending of the period of distinct time, the probabilities distribution is equal for all areas of S1 to S7 as follows:

Areas
S1
S2
S3
S4
S5
S6
Assets
A
B
C
D
E
F
Pr
16.55%
13.42%
18.26%
17.88%
16.50%
17.39%

It has been presented us that asset “C” will be the best option to invest in the future. And so we can take above fixed probabilities distribution for our analysis in said article.
Another application of this Markov Chain (homogeneous) is to track the number of the petitions and deals divided into each area after ending time of all transactions.
Assume we have 100 petitions to deal for each area in starting time of the transactions. We replace the number of petitions into a row vector (transposed vector) which is named “X” as follows:
S1          S2        S3       S4        S5      S6 
100       100      100     100       100    100
X = [100,100,100,100,100,100]
After the first deal, we will have below situation for the number of deals:
X * S = [98, 82, 113, 104, 99, 104]
After the thirteenth transaction, we will have below situation for the number of deals:
X * (S^13) = [99, 81, 110, 107, 99,104]
As we can see, the number of deals into each area in the starting time will be approximately equal to the ending time of the transactions.





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