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. But the mathematical formula for the **CAGR **prohibits generating an output when loss is in consideration. Fortunately, this article will pave the way to get rid of this problem.

**Table of Contents**hide

## Download Practice Workbook

## 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.

## 2 Perfect Ways to Calculate CAGR with Negative Number in Excel

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.

The mathematical formula to calculate the **CAGR **is given below.

**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 use 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 cell

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

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 5 Year CAGR Using Excel Formula**

**2. Applying Goal Seek Analysis to Calculate CAGR with 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`

The formula will return the principal after a year with a **CAGR **of **15% **referenced by the 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.

- Thereafter, use the
**Fill Handle**to**AutoFill**the lower cells.

- 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.

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

**Read More: ****How to Calculate End Value from CAGR in Excel (6 Methods)**

## 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.

## 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. For more queries, kindly visit our website **ExcelDemy**.

**Related Articles**

**How to Create CAGR Graph in Excel (2 Easy Ways)****Calculate Future Value When CAGR Is Known in Excel (2 Methods)****Methods to Apply Continuous Compound Interest Formula in Excel****Formula for Monthly Compound Interest in Excel (With 3 Examples)****A Daily Compound Interest Calculator in Excel (Template Attached)****Compound Interest Formula in Excel: Calculator with All Criteria****How to Use Compound Interest Formula in Excel (4 Easy Ways)**