datanalysis4layman

Monte Carlo simulation in MS Excel We can evaluate possible scenarios in MS Excel using the help of simulation. To understand this we need to understand what is simulation & how can this be implemented in Excel. If we want to model a process, a process is typically represented as: Y = f(x1, x2…) Now in a simulation exercise, we try to estimate the ranges of the input variables and vary the input variables in this range for a sufficient number of iterations and then try to understand the distribution of the output Y. For example if we try to calculate the corpus accumulated during a period of 20 years wherein the investment amount varies between 1000 to 3500 per month and the growth expected in the corpus lies between -5% and 12%. We use the Excel formula RANDBETWEEN(1000,3500) to generate random investment amount  per month and then multiply by 12 to calculate yearly investments. The interest rate per year is also generated using RANDBETWEEN(-5,12)...

There was this news article recently about a man who received few share certificate from his grandfather. The shares were that of MRF Ltd. and were supposedly worth INR 130 Crores now. Apparently the shares were bought sometime in 1990 or 1991 and were long forgotten by his grandfather. To illustrate the wealth creating impact of MRF Ltd, let us look at the returns given the share over the years. For simplicity, I have considered only the closing prices and have ignored the stock splits and the dividends the stock may have given. As can be seen from above the stock has exhibited a stupendous CAGR 34% since 1991 till 2017. All data is sourced from BSE website. Hypothetically speaking if someone had invested Rs 5000 in 1991 in this stock, he would have had a capital of Rs 1.01 Crores at present valuation. This illustrates the wealth creation potential of good businesses.

I am sure many of us would have got e-mails or other forms of solicitation wherein offers such as paying Rs 10000 per month for 20 years and in turn receiving monthly payout of Rs 50000 per month is presented for next 30 years. Let us take the example of a typical 30 years old in evaluation this proposal. Yearly cash flow out Month Cash flow out 1 10000 2 10000 .... .... 240 10000 Monthly Cash flow In for next 30 years Monthly Cash flow In Month Cash flow in 1 50000 2 50000 .... .... 360 50000 Let us understand the returns from the cash flow. We use the Excel function XIRR to find out the cash flow as below. Age Month Date Cash flow 30 1 31-Dec-17 -10000 XIRR 8.477% 30 2 31-Jan-18 -10000 30 3 28-Feb-18 -10000 30 4 31-Mar-18 -10000...