Excel offers us multiple configuration options. As an office automation tool, it allows you to perform thousands of calculations in minutes and simplifies daily work tasks. Thanks to Excel, tasks like calculating the annual growth rate are very easy to do with percentage formulas .
- How do you calculate the annual growth rate in Excel?
- Calculation of the average of each month with respect to the total
- Add graphic icons
How do you calculate the annual growth rate in Excel?
The calculation of the growth percentages takes place automatically and very practical only with the application of formulas. There are many ways to do the calculation, but this is the simplest formula to apply.
- % Growth = ((present value – previous month value) / (previous value)
When you apply this formula in Excel to the cell where you want to display the percentage results, the operation will be done.
- = ((B3-B2) / B2)
B2 represents the value obtained from the previous month, which is the cell preceding the month under evaluation. Cell B3 represents the current balance, whose growth percentage you want to evaluate.
Note: To display the percentage value in the cell, you need to change the format to percentage. Right click on the cell, select the Format Cell option and finally select the percentage category.
Alternative formula (IRR or XIRR function)
Usually this formula is used to regularly evaluate the value of the cash flow . The meaning of its syntax is TIR.NO.PER and it means “values.date.estimate”. In case the Excel version is in English, the function name is XIRR.
- Values:is the data range used to measure the rate.
- Dates:data evaluation period.
- Estimate: This is optional and is used to find an estimated value close to the IRR.
All aggregated values must have a minus sign (-) at the end for the calculation to be successful. Applying this formula in Excel would look like this:
- = NO.TIR.PER (B2: B13; C2: C13)
Where column B represents the value and column C represents the date.
Finally, you can calculate the average of the results obtained by comparing the result of one month with other monthly values . It is a default function available and can only be accessed by writing the word average and entering the values to be calculated.
- = AVERAGE (B2: B13)
Calculation of the average of each month with respect to the total
Once calculated the total sum of all the values to be evaluated, you can calculate the percentage of sales made in that period of time relative to the entire year. The formula is as simple as the one used to calculate the mean. The total sum of annual sales is divided by the grand total of sales for that month.
- = B2 / $ B $ 14
Note: The sum calculation formula is simple = Sum (B2: B13). What is stated in this formula is that the sum of all values between B2 and B3 must be done.
Add graphic icons
To show the results in a more graphic and understandable way to the naked eye, you can add colored arrow icons . Each arrow has a meaning, if it is green and upwards it means that there has been a growth in sales, if it is yellow there have been no changes compared to the previous period and if it is red and downwards it means that there have been losses.
To apply this change you need to add another formula in another cell . The result obtained from the formula is only 1 = there has been growth, 0 = remains the same and -1 = there have been losses. You have to start from the second period, because the first month is unmatched.
- = SIGNO (B3-B2)
Then select the result obtained in all cells and from the Home tab click on the Conditional Formatting option . Select the icon set you prefer and the changes will be applied immediately.
To remove the numbers next to the icon you need to change the formatting rules , from the same Conditional Formatting option you can manage the rules. Just select show symbol, apply the changes and everything will be perfect.