How to calculate CAGR in Excel

The composite annual growth fee, CAGR, is your fee of return on funding over a period of time.

Manually calculating CAGR is a alternatively complex method, so under we can examine how you may quick calculate CAGR in Excel.

Excel CAGR formula

The formulation for calculating the CAGR in Excel is:

= (final cost / initial cost) ^ (1 / number of years) – 1

The equation makes use of three unique values:

• final value this is the quantity of cash you’ll have after the duration expires.
• preliminary feethat is the quantity of cash you started out with.
• number of yearsthat’s the entire quantity of past years.

underneath is an example of how to calculate the CAGR for a five-yr time body in Excel the use of the sample facts set shown under:

1. decide the numbers you’ll use in your equation. The usage of the above pattern set,

• The final cost is 2143 (in mobile B6).
• The initial value is a thousand (in cell B2).
• The variety of years is 5 (in cell A6).

2. input your values ​​in the method.

Excel gives some of shortcuts, so you can effortlessly input cellular numbers within the equation that contain each of your values. The use of the above sample records set might be an equation

= (B6 / B2) ^ (1 / A6) -1

that is what it seems like on my Excel worksheet:

word that the equation adjustments shade to healthy the cells you are the usage of, so you can appearance returned and verify that your entries are correct before running the equation.

you may also input actual values ​​instead of mobile numbers inside the components. The equation would then appear like this:

= (2143/a thousand) ^ (1/five) – 1

three. once you have entered your values, click enter and run the equation. Your end result will appear within the cellular containing the equation as shown inside the image beneath.

CAGR system in Excel as a percentage

Your default score may be displayed as a decimal. To view it as a percentage, right-click on the cellular containing your score, choose shape the cells and then percent inside the dialog.

Your rating will be transformed to a percentage as shown within the picture under.

Now let’s go over the shortcut to calculate CAGR in Excel with the fee feature.

how to calculate CAGR using charge characteristic

The fee function helps you calculate the hobby price for an funding over a time period. The formulation for calculating the CAGR is:

= rate (nper ,, pv, fv)

• nper is the full wide variety of intervals inside the time body you’re measuring. Because you are calculating the once a year growth charge, this will be 12.
• pv is the prevailing fee of your funding (have to constantly be supplied as negative)
• fv is the destiny value.

note that the standard rate equation consists of several variables, however you simplest want the pinnacle 3 to calculate the CAGR.

We begin the equation the use of the sample desk below, where nperi is 12, pv 100 and fv 500.

1. select the cell on your sheet in which you need to comprise your CAGR. I chose cellular B5.

2. input the price method and input your numbers. Observe which you constantly you ought to specific your modern-day fee as negative, in any other case you’ll get hold of an errors message.

this is what my formulation looks as if.

note that you can additionally easily input the cellular numbers that comprise your values. With my pattern desk, the formula would appear like this:

= prevent (A2 ,, – B2, C2)

3. click enter and run your equation. The use of pattern records, my CAGR is 14%.

Now that you realize the way to quickly and easily calculate your CAGR in Excel, manual calculations aren’t required.