Fantastic video. I much prefer to create models like this one rather than to rely on online calculators. I find understanding the construction of models like this one to be much more informative and helps really drive the concepts home. Thank you for taking the time to share your knowledge!
Awesome video. Would be extremely helpful if you could also share a copy of the excel sheet in the description. With a template of the fields and formulas ready to go & adjust the numbers for each custom scenario. Would that be doable?
Wonderful video! So helpful for someone who prefers to create and have control of the calculations. Thanks so much for taking the time to put this video together Very valuable 😊
It seems the model should stop once the balance dips negative. For example, if you pause the video at 9:54 The last six years shown are negative amounts. There should be no amount in year 30, because they went belly up 5 years earlier. Also, the market gains are shown as loses with the negative balance. I don't have a good answer unless you can answer what year you go bust.
Excellent video! I was able to follow and setup a working simulation even though I have not used Excel functions much in the past. One question I’m wrestling with is this is…. I’ve setup 1500 rows in my replication table. When I hit F9 I see that the replicat😊ion table along with my 30 year table are updated. My question is… how are the data return and inflation % fields updates in my 30 year retirement data table updated? Are they reflecting the average result of 1500 random calculations or do they reflect the last random calculation made on cycle 1500? Sorry I must be in the slow class 😢
Not sure if your question is understood, but I think you are asking how does the % field for Return and inflation get updated?. So each time you hit F9 (calculate sheet), Excel calculates all the formulas in the worksheet, this includes the % for "Return" and "inflation" fields in the 30 year table and the replication table on the right side. The % field in 30 year table is based on a random number generated and with Average and Std Deviation paramenters declared above (Top Left). The most important thing to watch for is the % < 0 number on cell K12, this will tell you how many times the portfolio did not last until year 30. I have seen the same Monte Carlo simulation on T Rowe Price website with their retirement calculator, where they say one is in the "confident zone" if the portfolio failures are less than 20%, which means the portfolio survived 80% of the time in doing 1500 simulations. Hope this helps
The rand() function generates the numbers between 0 and 1, but the norminv() generates the values based on a normal distribution. The rand function is just used so norminv generates a different random variable each time.
Thank you sir for your video. I have a question I am a finance student, and I am trying to calculate the CoVaR( Conditional value at risk) and VaR(Value at risk) using quantile regression, in order to analyze systemic risk for the banking system. So I already computed the coefficients alpha and beta for the CoVaR equation, using quantile regression in Eviews. Now i have to estimate the VaR for every bank when p=0.05 , So according to this approach, the VaR is equal to the total of quantiles computed for p=0.05 , using Rankit-cleveland definition. I should get a result that look like this, but I do not know how to do it : Descriptive Statistics for RATJ Categorized by values of RATJ Date: 11/13/17 Time: 00:57 Sample: 1/05/2010 11/03/2017 Included observations: 1956 RATJ Quant.* Obs. [-0.1, -0.05) NA 4 [-0.05, 0) -0.023874 816 [0, 0.05) 0.000000 1132 [0.05, 0.1) NA 4 All -0.017742 1956 *Quantiles computed for p=0.05, using the Rankit-cleveland definition. RATJ is the time serie for daily stock returns of the bank ATJ. Thank you so much for your time sir.
Excellent video, thank you so much Mr Ronald!
Thank you very much Dr. 🎉🎉🎉
Fantastic video. I much prefer to create models like this one rather than to rely on online calculators. I find understanding the construction of models like this one to be much more informative and helps really drive the concepts home. Thank you for taking the time to share your knowledge!
Awesome video. Would be extremely helpful if you could also share a copy of the excel sheet in the description. With a template of the fields and formulas ready to go & adjust the numbers for each custom scenario.
Would that be doable?
Such a great video and very easy to follow and recreate. Thank you so much!
Wonderful video! So helpful for someone who prefers to create and have control of the calculations. Thanks so much for taking the time to put this video together
Very valuable 😊
Excellent! Thanks very much for explaining how to do this. Just what I was looking for.
excellent
It seems the model should stop once the balance dips negative. For example, if you pause the video at 9:54 The last six years shown are negative amounts. There should be no amount in year 30, because they went belly up 5 years earlier. Also, the market gains are shown as loses with the negative balance. I don't have a good answer unless you can answer what year you go bust.
Excellent video! I was able to follow and setup a working simulation even though I have not used Excel functions much in the past. One question I’m wrestling with is this is…. I’ve setup 1500 rows in my replication table. When I hit F9 I see that the replicat😊ion table along with my 30 year table are updated. My question is… how are the data return and inflation % fields updates in my 30 year retirement data table updated? Are they reflecting the average result of 1500 random calculations or do they reflect the last random calculation made on cycle 1500? Sorry I must be in the slow class 😢
Not sure if your question is understood, but I think you are asking how does the % field for Return and inflation get updated?. So each time you hit F9 (calculate sheet), Excel calculates all the formulas in the worksheet, this includes the % for "Return" and "inflation" fields in the 30 year table and the replication table on the right side. The % field in 30 year table is based on a random number generated and with Average and Std Deviation paramenters declared above (Top Left). The most important thing to watch for is the % < 0 number on cell K12, this will tell you how many times the portfolio did not last until year 30. I have seen the same Monte Carlo simulation on T Rowe Price website with their retirement calculator, where they say one is in the "confident zone" if the portfolio failures are less than 20%, which means the portfolio survived 80% of the time in doing 1500 simulations. Hope this helps
Thanks Prof for wonderful information. Can we do this in Google sheets? Please illustrate the replication method in Google sheets.
Wouldn’t this be a monte carlo sim based on a uniform distribution rather than a normal distribution because rand() picks a random number between 0-1?
The rand() function generates the numbers between 0 and 1, but the norminv() generates the values based on a normal distribution. The rand function is just used so norminv generates a different random variable each time.
@@RonaldMoy Gotcha, I tested this in excel and that does seem to be the case. Thanks a lot for the explanation
I am trying to create the replication table but all the values are the same, how do i solve this?
I have the same issue. After hitting F9 to recalc, the data table values and thus the mean/min/max are staying constant.
We might not be waiting long enough for the model iterations to run. I'm running 5K iterations and it took almost 30 seconds for all to refresh.
Range specified in K9 (25%) is incorrect
Should be L18:L1017
For K12 (%
Thank you sir for your video. I have a question
I am a finance student, and I am trying to calculate the CoVaR( Conditional value at risk) and VaR(Value at risk) using quantile regression, in order to analyze systemic risk for the banking system. So I already computed the coefficients alpha and beta for the CoVaR equation, using quantile regression in Eviews.
Now i have to estimate the VaR for every bank when p=0.05 , So according to this approach, the VaR is equal to the total of quantiles computed for p=0.05 , using Rankit-cleveland definition.
I should get a result that look like this, but I do not know how to do it :
Descriptive Statistics for RATJ
Categorized by values of RATJ
Date: 11/13/17 Time: 00:57
Sample: 1/05/2010 11/03/2017
Included observations: 1956
RATJ Quant.* Obs.
[-0.1, -0.05) NA 4
[-0.05, 0) -0.023874 816
[0, 0.05) 0.000000 1132
[0.05, 0.1) NA 4
All -0.017742 1956
*Quantiles computed for p=0.05, using the Rankit-cleveland definition.
RATJ is the time serie for daily stock returns of the bank ATJ.
Thank you so much for your time sir.