Methodology
Financial forecasting is a critical aspect of portfolio management. The current report aims to present the risk and return expectation for a portfolio which comprise of monthly investment of 30000 for a period of 25 months in the share Apple Inc. The modelling of the data has been done by using the historical data of the stock from Yahoo Finance over a period of 5 years to determine the monthly return and standard deviation of the stock. (netsuite.com, 2020) For the purpose of analysis it has been assumed that the data of the stock is normally distributed. Based on collected data Palisade @ Risk Model has been applied to determine the expected movement of the portfolio over the period of 25 months using 5000 iteration in Monte Carlo Simulation. (|, 2018) The findings from the evaluation has been presented both graphically and via usage of tables as under:
The data which has been used for projections for investment in Apple Inc. is 5 year daily historical data to predict the goal of achieving 1 Million Euro at the end of the period of 25 Months. The company has been selected as company has very high volatility and return in the last 5 years. The plan is to invest Euro 30000 on monthly basis for a period of 25 months at the beginning of the month. Investment in a single stock is not a chosen method of investment but for achieving high returns in a short frame of time the stock investment in particular stock has been chosen and diversification benefit has not been considered. The historical risk and return obtained has been simulated to determine the probability of ending in value desired.
The data has been obtained from Yahoo Finance for a period of 5 years on daily basis to obtain monthly return and standard deviation. Further, add in @ Risk has been installed from web for a trial version for creating possible range of inputs and determine one possible outcome from the given parameters of investment. The application of method has been done using normal distribution from @Risk tab and results have been generated. Further, for application of @ Risk Model default return and risk has been amended by the computed risk and return of the Apple Inc. stock over the historical period. The normal distribution is the most probable event as it depicts equal distribution around the mean and the probability of the standard deviation pertains to the statistic denoting the likelihood the actual outcome that is being approximated will be some different value apart from, the most probable event, which is the mean. (palisade.com, 2020)
The above computation gives range of monthly return and SD for the Apple Inc Stock. and based on the data it may be inferred that the desired value of portfolio shall be achieved by end of 24 months and thus target may be achieved before the deadline. However, the above computation is an estimation and uncertainty persist in the said computation. For obtaining a higher degree of certainty in the above model, @ Risk parameter has been used to obtain a range of outcome through usage of Monte Carlo Simulation. In the above computation, the mean return has been obtained at 3.12% and the standard deviation has been considered at 8.967%. (wibas.com, 2021) Further, the data is perfectly normal as kurtosis is 3 and skewness is zero. The computation has been presented as under:
Results
|
Particular |
year |
Return |
Ending |
||
|
Present Investment |
30000 |
1 |
3.120% |
30936 |
|
|
Term Annual |
25 |
2 |
3.120% |
61901 |
|
|
Return |
3.120% |
=average() |
3 |
3.120% |
93833 |
|
SD |
9.0% |
= Stdev.s() |
4 |
3.120% |
126761 |
|
5 |
3.120% |
160716 |
|||
|
Ending Value |
1113183 |
6 |
3.120% |
195731 |
|
|
7 |
3.120% |
231839 |
|||
|
8 |
3.120% |
269073 |
|||
|
9 |
3.120% |
307469 |
|||
|
10 |
3.120% |
347063 |
|||
|
11 |
3.120% |
387892 |
|||
|
12 |
3.120% |
429996 |
|||
|
13 |
3.120% |
473413 |
|||
|
14 |
3.120% |
518186 |
|||
|
15 |
3.120% |
564355 |
|||
|
16 |
3.120% |
611965 |
|||
|
17 |
3.120% |
661060 |
|||
|
18 |
3.120% |
711687 |
|||
|
19 |
3.120% |
763895 |
|||
|
20 |
3.120% |
817731 |
|||
|
21 |
3.120% |
873247 |
|||
|
22 |
3.120% |
930495 |
|||
|
23 |
3.120% |
989530 |
|||
|
24 |
3.120% |
1050407 |
|||
|
25 |
3.120% |
1113183 |
Based on above table it may be inferred that the value of the portfolio at the end of the 25th month is at Euro 1.11 Million. Further, several possible iteration ending for every year are then run through usage of index formula in excel from @ Risk add in. Post above the output has been defined in the @ Risk Model post considering the time frame and range of possible values. The simulation is then put to run from the add in and the input distributions have been modified from default values to the values obtained from computation for data obtained using yahoo finance. The initial investment for the month stood at Euro 30000 and the intention shall be to add similar amount of money every month. Further, computation has been made to determine the probability of the value ending at least 1 Million Euro at the end of the investment horizon. Based on above the simulation results is as under:
The settings were adjusted and 5000 iterations have been run for Monte Carlo Simulation through usage of fixed set, (palisade.com, 2020) with one simulation run and obtained results has been presented as under:
Based on above simulation it may be inferred that there is 89.8% probability of ending the portfolio value above 1 Million Euro based on above computation. The minimum value of the portfolio based on simulation has been obtained at 94432.35 and the maximum value has been determined at 293.68 Million Euro. The mean of the data stood at 2.87 million and mode at 0.35 Million. The median of the model stood at 1.11 Million and skewness and kurtosis of the data are also much high. Further one may also infer that there is 10.2% probability of the value of the portfolio ending lower than 1 million and thus based on above computation it may be inferred that investment is favourable as more than 85% probability exists for value of the portfolio ending at value greater than 1 Million.
The percentile analysis has been carried out the range of data and has been predicted as under:
Based on above data, it may be inferred that 15 percentile of the data lies above 4 Million and 30 percentile of the data lies above 2 Million and 50 percentile of the data lies above 1.5 Million. Thus, based on above analysis, it may be inferred that there is high probability that the portfolio shall end up in high value post the end of the investment horizon. (statisticshowto.com, 2022)
Further, the diagrammatic analysis of the million values has been presented as under:
Based on above analysis also it has been inferred that the value of the portfolio shall be much high above the requirement and there shall be high probability in relation to the said estimation.
Stress Testing has been carried out using @ RISK Model to understand the risk of not earning the estimated amount by the end of the investment horizon and it has been inferred that there is high probability of the portfolio ending in the required value after the end of the investment horizon. (tutorialspoint.com, 2022) It has been estimated that there is higher likelihood that the portfolio shall end with a value higher than 1 Million Euro after the investment horizon of 25 months. Stress testing is used regularly to determine risks in portfolios, such as by hedge managers and they then determine hedging strategies to mitigate risks that can cause losses; the stress test done also shows the investment can weather external shocks and achieve the objective. (TechTarget Contributor, 2020)
Spearman Rank Correlation has been used to test the outcome and represents the non parametric version of the Pearson product- moment correlation. (simplilearn.com, 2021) It measures the strength and degree of association between the two ranked variables. The computation depicts there is a a significant, but weak positive correlation between ranks that the ending value of the investment will be a million Euros at least. (statisticshowto.com, 2022)
Conclusion
Based on above discussion, it may be inferred that data has been obtained from yahoo finance and @ Risk add in has been downloaded from Palisade to estimate the uncertainty involved in the portfolio. The model has been able to decode the probability of portfolio meeting the goal based on historical data. The probability has been determined to be near to 90% which is quite high and thus one may state that there is high chances of portfolio meeting the target. Further, portfolio shall comprise of one single stock i.e. Apple Inc which is an American Technological company listed on NASDAQ. The company is trading at 165.29 USD and has presence globally. The portfolio shall comprise of this stock only and the stock has delivered more than 100% return over the period of 5 years. Further, the model has also been tested using stress analysis and Pearson coefficient and all the model estimates a higher probability of ending up value being higher than 1 Million Euro.
References
|, P. L., 2018. Business Forecasting: Why You Need It & How to Do It. [Online]
Available at: https://www.projectmanager.com/blog/business-forecasting
[Accessed 22 April 2022].
Li, D., Jiang, S., Wu, S., Zhou, C. and Zhang, L., 2013. Modelling multivariate distributions using Monte Carlo simulation for structural reliability analysis with complex performance function. Proceedings of the Institution of Mechanical Engineers, Part O: Journal of Risk and Reliability, 227(2), pp.109-118.
netsuite.com, 2020. The Importance of Financial Forecasting. [Online]
Available at: https://www.netsuite.com/portal/resource/articles/financial-management/importance-financial-forecasting.shtml
[Accessed 18 April 2022].
palisade.com, 2020. What is Monte Carlo Simulation?. [Online]
Available at: https://www.palisade.com/risk/monte_carlo_simulation.asp
[Accessed 18 April 2022].
palisade.com, 2020. What is Monte Carlo Simulation?. [Online]
Available at: https://www.palisade.com/risk/monte_carlo_simulation.asp
[Accessed 22 April 2022].
simplilearn.com, 2021. Spearman’s Rank Correlation: The Definitive Guide To Understand. [Online]
Available at: https://www.simplilearn.com/tutorials/statistics-tutorial/spearmans-rank-correlation
[Accessed 18 April 202].
statisticshowto.com, 2022. Percentiles, Percentile Rank & Percentile Range: Definition & Examples. [Online]
Available at: https://www.statisticshowto.com/probability-and-statistics/percentiles-rank-range/
[Accessed 18 April 2022].
statisticshowto.com, 2022. What is Spearman Rank Correlation / Spearman’s Rho?. [Online]
Available at: https://www.statisticshowto.com/probability-and-statistics/correlation-coefficient-formula/spearman-rank-correlation-definition-calculate/
[Accessed 18 April 2022].
TechTarget Contributor, 2020. stress testing. [Online]
Available at: https://www.techtarget.com/searchsoftwarequality/definition/stress-testing#:~:text=Stress%20testing%20is%20the%20process,of%20errors%20or%20system%20crashes.
[Accessed 18 April 2022].
tutorialspoint.com, 2022. What is Stress Testing?. [Online]
Available at: https://www.tutorialspoint.com/software_testing_dictionary/stress_testing.htm
[Accessed 18 April 2022].
wibas.com, 2021. Define Risk Parameters. [Online]
Available at: https://www.wibas.com/cmmi/rskmsp-12-define-risk-parameters
[Accessed 18 April 2022].
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
