Use this MS Excel function to calculate return uncertainty in personal finances
MS Excel’s random number functions can be used to effectively handle rate of return ambiguity in calculations. Although the concept of random numbers is a pure mathematical concept but it can also be applied using MS Excel’s built-in functions.
Ever wondered how experts incorporate such return uncertainty in the calculations? Any unrealistic assumption on such returns could jeopardise the entire financial planning. The concept of random numbers could prove effective in tackling return uncertainty. A random number is generated using a process whose outcome is difficult to predict. In other words, a random number cannot be used to predict any other random number.
Although the concept of random numbers is a pure mathematical concept but it can also be applied using MS Excel’s built-in functions. Excel sheets prove very useful in personal finance calculations that include present values, future values, investment planning, cost of living estimation and computation of the retirement fund. Besides, the built-in random number function provides an added advantage to include the integrated turbulence in the markets.
Such functions are ‘RANDBETWEEN’, ‘RAND’ and ‘NORM.INV’. The first two functions generate random numbers and are volatile functions, which means the numbers that are generated by these functions will change as and when a user makes changes to the excel sheet. The first function generates random numbers between the defined lower and upper limits, whereas the second function generates random numbers between 0 and 1. The third function is not a random number function, but can be used in combination with RAND function to capture randomness in normally distributed variables.
One rudimentary way of making an assumption about the returns is looking at the highest and lowest returns that an asset has generated in the past and assuming a return somewhere in this range.
Let us look at an example to understand the usage of RANDBETWEEN function that generates range based random numbers. BSE Sensex has delivered the highest and lowest annual returns of 81.03% and -52.45% over the last 25 years. What should be the value of `1 lakh invested for a period of one year in the equity market?
The calculation is simple compounding which is 1 lakh X (1+(R/100) ^n. Here, n is the tenure which is 1 year and R is the random return between the lower and higher limits. The ‘RANDBETWEEN’ function takes only two values, the lower limit and higher limit, as shown in the excel screenshot1. The same return is used in the formula given above that is placed in B11 cell of the Excel screenshot 1 that shows that Rs 1 lakh will be worth Rs 88,000 in the next one year. As stated above, the random functions are volatile functions, therefore, the returns will keep changing as the user tries to modify the Excel sheet. Moreover, one can also use the F9 key and observe that the return variable takes a different value between the defined limits as the F9 key triggers the random process.
Tackling return uncertainty in personal finance
Random number functions provide an added advantage to include the integrated turbulence in the markets
Another sophisticated approach to include uncertainty in the calculations is to use a combination of NORM.INV and RAND functions. Returns generated by financial market assets are assumed to follow a bell-shaped or normal distribution where the average and standard deviation defines return and risk profile. It is believed that most of the returns are clustered around the long term average and 99.7% of such returns are within the range of three times standard deviations to the average.
NORM.INV function requires three inputs–probability, average and standard deviation. Therefore, given the historical data on the standard deviation and average returns of an asset, the NORM.INV function can generate random returns if RAND function is used in the probability input. As probabilities take the value between 0 and 1, the RAND function can be used to generate such probabilities.
Using the BSE Sensex annual time series returns data for the past 25 years, the average return and standard deviation are worked out as 14.4% and 31.8% respectively. The applicability of NORM.INV function in combination with RAND function is displayed in screenshot 2. Pressing the F9 key, one can observe different values of randomly driven returns.
Using the above two methods, one can run hundreds or thousands of simulations on an Excel sheet and can use the average returns obtained from such simulations as an input in the personal finance calculations. There are techniques available in MS Excel that allow to do such simulations but such explanations is beyond the scope of this article.