Moving Average

What is Moving Average?

Moving Average (MA), commonly used in capital markets, can be defined as a succession of mean that is derived from a successive period of numbers or values and the same would be calculated continually as the new data is available. This can be lagging or trend-following indicator as this would be based on previous numbers.

Moving Average Formula

Moving Average = C1 + C2 + C3…. Cn / N

Moving Average Formula

Where,

  • C1, C2…. Cn stands for the closing numbers, prices, or balances.
  • N stands for the number of periods for which average is required to be calculated.

Explanation

Moving average is a type of arithmetic average. The only difference here is that it uses only closing numbers, whether it is stock prices or balances of account etc. The first step is to gather the data of the closing numbers and then divide that number by for the period in question, which could be from day 1 to day 30 etc. There is also another calculation, which is an exponential moving average; however, we have discussed here only a simple equation.

Examples

You can download this Moving Average Formula Excel Template here – Moving Average Formula Excel Template

Example #1

Stock X was trading at 150, 155, 142, 133, 162, for the previous five trading days. Based on the given numbers, you are required to calculate the moving average.

Solution

Use the following data for calculation

Stock Closing Prices
Day 1 150
Day 2 155
Day 3 142
Day 4 133
Day 5 162

MA can be calculated using the above formula as,

Moving Average Formula Example 1.1png

  • (150+155+142+133+162)/5

The moving Average for the trending five days will be –

Moving Average Formula Example 1.2png

  • = 148.40

The MA for the five days for the stock X is 148.40

Now, to calculate the MA for the 6th day, we need to exclude 150 and include 159.

Example 1.3png

Therefore, Moving Average = ( 155 + 142 + 133 + 162 + 159 ) / 5 = 150.20 and we can continue doing this.

Example #2

Alpha Inc was incorporated as Bank last year, and now its almost year-end to report the financial statement of the firm. The norms of the central bank asked the banks to report the average balances of the account instead of closing balance as at the end of the year. The average balances should be done on a monthly basis. The financial analyst of the firm took a sample account #187, where below were the closing balances reported.

Example 2

Based on the above closing balances, you are required to calculate a simple moving average.

Solution

First, in this, we will calculate the sum of the closing balances in order to calculate the average.

Cumulative Total for Day 10 will be  –

Example 2.3png

  • Cumulative Total for Day 10 = 124102856.26

Cumulative Total for Day 11 will be  –

Example 2.1png

  • Cumulative Total for Day 11 = 124739450.26

Similarly, we can calculate the cumulative total for the remaining days.

Example 2.2png

Therefore, the simple MA for 1st 10 days will be as follows,

Example 2.4png

=124102856.26/10

The MA for 1st 10 days will be –

Example 2.5png

  • The MA for 1st 10 days = 12410285.63

Therefore, the simple MA for 11th day will be as follows,

Example 2.6png

  • MA for 11th Day = 12473945.03

Similarly, we can calculate the moving average for the remaining days

Example 2.7png

Example #3

Mr. Vivek wants to compute the estimated price of the onion for tomorrow based on an average of the last ten days. He believes that there is a margin of 10% upwards trends because of rising in the price of fuel. Also, he believes that the prices of onion are fluctuating based on moving averages. The last 10 days’ prices of the onion per kg are 15, 17, 22, 25, 21, 23, 25, 22, 20, 22. Based on the given criteria, you are required to compute the projected price of onion on day 11.

Solution

Use the following data for calculation of moving an average in excel

Day Prices
Day 1 15
Day 2 17
Day 3 22
Day 4 25
Day 5 21
Day 6 23
Day 7 25
Day 8 22
Day 9 20
Day 10 22
Day 11 21
Day 12 20
Day 13 17
Day 14 19

Therefore, 7 days MA in excel will be as follows,

Moving Average Formula Example 3.1png

  • 7 Days MA = 21.14

Therefore, the next 7 days MA will be as follows,

Example 3.2png

  • = 22.14

Similarly, we can calculate 7 days MA as shown below

Moving Average Formula Example 3.3png

Estimated Price on Day 15

moving average formula Example 3.4png

The 7-day MA for the onion price is 20.14

It’s given that there will be a price increase for fuel, which could result in inflating of prices of onion.

Therefore, the projected price of onion on day 15 will be 20.14 * 1.10 = 22.16, which can be rounded off to 22

Uses of Moving Average

These kinds of averages are most commonly used in capital markets for analyzing the prices of the stock while conducting technical analysis. By using the moving average, the analyst tries to uncover if there any trends hidden in it. These are usually used as lagged indicators as these are based on previous numbers, and hence these averages can never be greater than the closing prices. Further, it is also used in computing support and resistance level in technical charts.

Recommended Articles

This has been a guide to Moving Average and its definition. Here we discuss the calculation of moving average using its formula along with practical examples and a downloadable excel template. You can learn more about financial analysis from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *