Now, the earlier mentioned calculations, when done by hand are painstaking. We calculated all these parameters within the span of the framework of just the four scenarios as per example, so that the meaning of the concept is assimilated well. But this operation can be done quicker practically. Generally it so happens that we would have to collect the historic data about the rate of return, and then copies the same on to some spread sheet such as MS Excel or Open Office. With spread sheets various functionalities that are already embedded in the system can be explored, and by now we are aware of exactly which operation to pick. Practically the following might be used:

  • Average (range): used for calculation of the mean degree of income return.
  • Varp (range): used for calculation of the variance here it is population. In case if we are considering historical data in place of already known cases, we would be using the function var(range). In the latter function, we divide by N-1, not N, which is explained later.
  • Stdevp (range): used for calculation of the standard deviation, variance being population. If make use of the data from the past in place of already known cases stdev (range) would have been used.
  • covar (range1, range2): is used for calculation of covariance of population amid twofold series. Depending upon the use of Excel, this function would be called as covarp.
  • correl (range1, range2): used for calculation of the correlation in the middle of two given series.
  • Slope (range-Y, range-X): used for calculation of the value of beta. Here, range-Y contains the rate of return on an investment, while range-X contains rate of return on market.

Table 8.3 has shown values post calculation of the parameters discussed here.

8.5A STATISTICAL NUANCES

In this section of our discussion we have under the presumption that historical information provides us with an unprejudiced monitor to the period yet to come, as is just the case in section 7.1E. This assumption is for computing means, variance, covariance and also betas. Although it is a simplified version, it can also hurl difficulties on our way. This assumption would be more problematic when we deal with mean than any other parameters as covariance, variances and also betas. Ironically dealings with these terms would be at the risk of your own.

Again, there is another minor statistical problem that needs to be brought to light. Statisticians frequently make use of a covariance function, which is mathematically divides by N-1, and not by N. Looking into it sternly, we see that by dividing N-1 is apt if we deal with data from past, also termed as historical database.  It is meant for analysis purpose and do not provide us with total population that has likely outcomes. Given a sample finding the de mean wouldn’t provide us with the value of the true average.

The arithmetic division operation using a lesser numerical value, N-1, gives us a larger and an unprejudiced covariance measure. It is at the same time frequently termed as the sample covariance. Against mathematically dividing the same with N is proper if we work with samples rather scenarios that we are aware of to be existent and are similarly probable. In such case, statistics is more often termed the population covariance. Now the alteration infrequently makes a difference in finance. Here one is in general given many a number of observations, apart from the example we cited- the one where we had four scenarios.
Say for example we notice that dividing N=1000 and N=1001 has no difference in the results.

Spreadsheet Calculations for Risk and Reward 43

The beta value will not be affected by any of these circumstances. Hence, there is no need for the statistical ways of computation. Hence, it might be called and referred to as the statistical deviation.

There are much more deviations that are caused by the unknown statistical evaluations. Hence there is a change in the denominator values.

Summary

Spreadsheet Calculations for Risk and Reward 44
Spreadsheet Calculations for Risk and Reward 45

Key terms

Spreadsheet Calculations for Risk and Reward 46

Solve now! Solutions

Spreadsheet Calculations for Risk and Reward 47
Spreadsheet Calculations for Risk and Reward 48
Spreadsheet Calculations for Risk and Reward 49
Spreadsheet Calculations for Risk and Reward 50
Spreadsheet Calculations for Risk and Reward 51

Problems

Spreadsheet Calculations for Risk and Reward 52
Spreadsheet Calculations for Risk and Reward 53
Spreadsheet Calculations for Risk and Reward 54
Spreadsheet Calculations for Risk and Reward 55

Submit Your Assignment