Excel Hack

CAGR formula in Excel

CAGR stands for Compound Annual Growth Rate and is calculated as the average annual growth rate of earnings over a specific interval. CAGR can also be calculated in Excel using the RRI function, the POWER function, the GEOMEAN function, etc. We will show you other functions that can be used to calculate CAGR include the IRR function and the XIRR function.

Function to calculate CAGR

XIRR fuction

=XIRR(values,dates,[guess])

IRR function

=IRR(values,[guess])

Use XIRR function

When calculating CAGR using the XIRR function, the start value must be negative. You must also create a two-column lists that references the start value and the end value, the start date, and the end date.

Total Time: 2 minutes

Enter a formula to refer to the starting value

Enter a formula to refer to the starting value

Enter =B2 in B7.

Refers to the start value

Refers to the start value

Press Enter, the start value can be referenced in B7. Enter =A2 in C7.

Refers to the start date

Refers to the start date

Press Enter, the start value can be referenced in C7.

Refers to the end value and end date

Refers to the end value and end date

Similarly, enter =B5 for B8 and =A5 for C8, then press Enter to see the end values and end data.

Enter a XIRR function

Enter a XIRR function

Enter =XIRR(B7:B8,C7:C8) in any cell.

CAGR is calculated

CAGR is calculated

Press Ctrl + Enter to calculate CAGR.

Set the percent style

Set the percent style

Press Ctrl + Shift + 5 to set a percent style to make it easier to understand.

Use IRR function

Calculate CAGR using the IRR function. When using the IRR function, the starting value must be negative. Also, all intermediate values other than the start and end values should be replaced with 0 (zero).

Enter IRR function

Enter =IRR(B2:B5) in any cell.

This time “0” is entered in cells B3 and B4. If accounting number format are set, a hyphen will be displayed.

CAGR is calculated

Press Ctrl + Enter to calculate CAGR.

Set the percent style

Press Ctrl + Shift + 5 to set a percent style to make it easier to understand.

Articles related to CAGR & Formula

Comments

Please send us your comments on this article.

0 comments
Inline Feedbacks
View all comments