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
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
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,
- (150+155+142+133+162)/5
The moving Average for the trending five days will be –
- = 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.
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.
4.9 (1,067 ratings) 250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
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 –
- Cumulative Total for Day 10 = 124102856.26
Cumulative Total for Day 11 will be –
- Cumulative Total for Day 11 = 124739450.26
Similarly, we can calculate the cumulative total for the remaining days.
Therefore, the simple MA for 1st 10 days will be as follows,
=124102856.26/10
The MA for 1st 10 days will be –
- The MA for 1st 10 days = 12410285.63
Therefore, the simple MA for 11th day will be as follows,
- MA for 11th Day = 12473945.03
Similarly, we can calculate the moving average for the remaining days
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,
- 7 Days MA = 21.14
Therefore, the next 7 days MA will be as follows,
- = 22.14
Similarly, we can calculate 7 days MA as shown below
Estimated Price on Day 15
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 –
- Capital Market Line
- Weighted Average in Excel
- Average Rate of Return Formula
- Average vs Weighted Average
- Quantitative Research Examples
- 250+ Courses
- 40+ Projects
- 1000+ Hours
- Full Lifetime Access
- Certificate of Completion