How to Calculate CAGR with Negative Number in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to calculate the CAGR (Compound Annual Growth Rate) in Excel with negative numbers. Basically, it’s very easy to calculate the CAGR when the data are positive. Which means no loss involved in the business. However, the mathematical formula for the CAGR prohibits generating an output when the loss is in consideration. Fortunately, this article will pave the way to get rid of this problem.


What Is CAGR?

A mutual fund or bond’s rate of return is measured by a Compound Annual Growth Rate (CAGR) over an investment period of five or ten years. The CAGR assesses the growth of an investment as if it had increased at a constant rate on an annually compounded basis, hence the name “smoothed” rate of return.


How to Calculate CAGR with Negative Number in Excel: 2 Perfect Ways

In the dataset, you will see the profit data of a company for 4 years. The last one is the target profit (profit data in 2021). To achieve this, we will calculate what the CAGR value should be.

how to calculate cagr in excel with negative number

The mathematical formula to calculate the CAGR is given below.

Read More: How to Calculate End Value from CAGR in Excel


1. Using Excel ABS Function to Calculate CAGR with Negative Number

As the fractional root of a negative number returns complex values, we will use the ABS functions to calculate the absolute values of negative profit (loss in business) to calculate the CAGR. Let’s go through the process below.

Steps:

  • First, insert the data of profit in the observing years.
  • Next, type the following formula in the cell.

=((C5-F5+ABS(F5))/ABS(F5))^(1/(C4-F4))-1

Using Excel ABS Function to Calculate CAGR with Negative Number

The formula is derived from the CAGR formula we showed in the previous section. The ABS function makes the loss value positive and thus removes the complex number dilemma.

  • After that, press the ENTER button and you will see the CAGR value for the data we use in this sheet.

Thus you can calculate the CAGR by using the Excel ABS function with negative numbers.

Read More: How to Calculate Future Value When CAGR Is Known in Excel


2. Applying Goal Seek Analysis to Calculate CAGR with a Negative Number

Another effective way to calculate the CAGR is to use the Goal Seek analysis feature from the Data Tab. Let’s follow the instructions below.

Steps:

  • First, set the initial profit and target profit. Also, choose a CAGR of your choice. As our topic focuses on negative numbers, we are considering that the company made a loss in the first year.
  • After that, type the following formula in cell C5.

=C10+ABS(C10)*$C$12

Applying Goal Seek Analysis to Calculate CAGR with Negative Number

The formula will return the principal after a year with a CAGR of 15% referenced by cell C12. Note that we are using the ABS function to get rid of negative values.

  • Later, type another similar formula in cell C6.

=C5+ABS(C5)*$C$12

This formula also returns the principal in the second year.

  • Next, go to Data >> Forecast >> What if Analysis >> Goal Seek.

  • Thereafter, select C8 as the Set Cell. Because we want to find out the CAGR by making the value of this cell to our target value (12000).
  • As a result, the ‘To Value’ will be 12000.
  • The CAGR will be determined in cell C12. So we selected it for the ‘By Changing Cell’ section.
  • After that, click OK.

  • Finally, this operation will provide you with the CAGR based on this dataset.

Applying Goal Seek Analysis to Calculate CAGR with Negative Number

Thus you can calculate CAGR in Excel with a negative number.

Read More: Excel Formula to Calculate Average Annual Compound Growth Rate


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own and gain expertise.


Download Practice Workbook


Conclusion

In the end, we can surmise that you will learn effective ways to calculate the CAGR in Excel with negative numbers. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Compound Interest in ExcelExcel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo