Stock Analysis, IPO, Mutual Funds, Bonds & More

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.

, ET Bureau|
Nov 04, 2019, 06.30 AM IST
Getty Images
Random number functions provide an added advantage to include the integrated turbulence in the markets
Most of the calculations in personal finance are based on an assumed rate of return. However, the return that an asset is expected to deliver is subjective and can be swayed by an individual’s outlook towards future market conditions. It is also influenced by the changes in the macroeconomic environment. Looking at the macro data for the past few quarters, indicators suggest an increased uncertainty that has weakened investors’ confidence.

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.

Also Read

Taking steps for smooth functioning of branches on Jan 8: Syndicate Bank

Navy to merge functions of workforce to tide over crisis

FM Sitharaman assures smooth functioning of Economy amidst growing political unrest

8 information panels function at reduced capacity: Study

EC functioning like an "agent of BJP", says Congress

Add Your Comments
Commenting feature is disabled in your country/region.

Other useful Links

Copyright © 2020 Bennett, Coleman & Co. Ltd. All rights reserved. For reprint rights: Times Syndication Service