There are many ways of calculating your return probabilities. Before we start, please keep in mind that this is only the forecast based on the historical data. The actual figures may vary as no one knows what will happen in the future. But if your selected assets will not experience any significant shocks in the future (economic, geopolitical, internal company shocks if you buy stocks), then the below-described model will work with very high accuracy. I will be using the Monte Carlo Simulation to calculate the probabilities of returns. By the end of this article, you will be able to calculate your portfolio’s return probability and assign the possibility of your portfolio bringing you more than 5% return, more than 10% return, and more than 15% return. So let me start.
First, you need to calculate the portfolio’s return. Please read my other article on Medium to understand how to calculate the return. The next step is to calculate the portfolio’s standard deviation. You can also look at this article to learn how to calculate volatility. The next step is to calculate the income variability. This number will change randomly, but it will always take into account your portfolio’s variance (standard deviation) and portfolio return. The income variability will show how your return may vary from day to day. You have to calculate the inverse of the normal cumulative distribution to calculate this variance. It is a straightforward formula.
Where m or mu is the portfolio’s return, σ or sigma is the portfolio’s standard deviation, and x is the random variable. You can calculate the distribution manually or use Excel. In Excel, use the following formula to calculate
NORM.INV(RAND();portfolio’s return; portfolio’s standard deviation)
Rand() is the random variable. If you have problems calculating your variability, you can take my course, where I provide video tutorials on constructing a Monte Carlo Simulation model. If everything is clear, continue reading.
Your income variability result will continually change. Try refreshing the Excel page (press fn+F2 on MAC and hit enter), and you will see that your number has changed. Don’t worry; it should be this way. If it is changing, it means you did everything correctly. After you calculate your variability, the next step is to compute the simulation. The logic here is to add the income variability 10 000 times (each time, it will be a different random number, but this number will take into consideration the portfolio’s variance and return). We do this to assign a probability later, but we need a large sample to make our analysis highly reliable. That is why I extrapolate income variability 10K times. Don’t worry. You don’t have to do it manually. We will be using Excel. It is a straightforward procedure. First, copy your income variability number to the empty cell where you want your data to be placed. This is done to make everything more convenient. It would help if you had a table that looks like this.
9,39% in my example is a variability number. You will have a different number because it is a random number. The letters 1 through 5 are the days. You should have 10K days in your model. After creating 10K rows, highlight everything the same way as my picture shows.
Then in Excel, go to the Data tab, What-if Analysis, Data table. In the “Column input cell,” write any empty excel cell. This cell should not be inside your table. Then hit the “ok” button. You can highlight your calculated results and turn them into a percentage by pressing the % sign in the Excel’s Home tab. Now you have a random number for 10K different days. It is your 10K day income variability. The next step is to calculate the probabilities. Your excel table should look similar to mine.
>5% is the number that I wrote to help me see what would be the probability of my return exceeding 5% . Next, I calculate the probability for return exceeding 5%. To do this, use simple excel formula.
COUNTIF(G15:G10014;">0,05")/10000
G15:G10014 is my range, where my income variability was calculated. >0,05 is that I want to calculate the probability that my return would be higher than 5% and divide by 10K is the number of days. You do the same things for the other probabilities, 10%, and 15%, and finally, you will have a table that will look something like this.
Each time you refresh the excel page, these numbers will stay almost the same. And it is the high accuracy calculation that will tell you what the probability would be that your portfolio’s return would exceed 5%, 10%, and 15%.
I hope my explanations were clear, and now you can construct your model using the Monte Carlo Simulation and know what returns to expect from your portfolio. If you still do not understand the calculations, you can take my Udemy course, where I show video explanations on how to create this model and not only that. I have plenty of other topics inside of my course. You can also contact me for any questions or partnership opportunities. E: skobzhan@diversset.com Thanks.