Moving Average Formula

Formula for Moving Average Calculation (Table of Contents)

• Formula
• Examples

What is Moving Average Formula?

Moving Average (MA) which is commonly used in capital markets and 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.

Where,

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

Explanation of the Moving Average Formula

Moving average is a type of arithmetic average. The only difference here is that moving average 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 for moving average which is an exponential moving average, however, we have discussed here only a simple moving average equation.

Examples of Moving Average Formula (with Excel Template)

Let’s see some simple to advanced examples of moving average equation to understand it better.

Example #1

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

the moving average.

Solution

Use the following data for calculation of moving average

Moving Average can be calculated using the above formula as,

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

Moving Average for the trending 5 days will be –

• Moving Average = 148.40

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

Now, to calculate the moving average for the 6^th 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 has been 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.

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 moving average for 1^st 10 days will be as follows,

=124102856.26/10

The moving average for 1^st 10 days will be –

• The moving average for 1^st 10 days = 12410285.63

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

•  Moving Average for 11th Day = 12473945.03

Similarly, we can calculate the moving average for remaining days

Example #3

Mr. Vivek wants to compute the estimated price of the onion for tomorrow based on an average of the last 10 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

Therefore, 7 days moving average in excel will be as follows,

• 7 Days Moving Average = 21.14

Therefore, the next 7 days moving average will be as follows,

• = 22.14

Similarly, we can calculate 7 days moving average 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

Relevance and Uses

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 Formula. Here we discuss the calculation of moving average in excel along with practical examples and downloadable excel template. You can learn more about financial analysis from the following articles –

• Calculation of Marginal Product Formula
• Weighted Average in Excel
• AVERAGE Function in Excel
• Average Rate of Return Formula
• Average vs Weighted Average