提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何计算Excel中的平均/复合年增长率?

本文正在讨论如何计算Excel中的平均年增长率(AAGR)和复合年增长率(CAGR)。

快速将CAGR表保存为迷你模板,并在将来只需单击一次即可重复使用

引用单元格并应用每次计算平均值的公式一定非常繁琐。 Kutools for Excel提供了一个可爱的解决方法 自动文本 实用程序来将范围保存为自动图文集词条,这可以保留范围内的单元格格式和公式。 然后你只需点击一下就可以重复使用这个范围。 全功能免费试用60天!
doc cagr autotext

Kutools for Excel - 包括多个用于Excel的300便捷工具。 全功能免费试用60天,无需信用卡! 现在加入


用Excel计算复合年增长率

要在Excel中计算复合年增长率,有一个基本公式 =((结束值/起始值)^(1 /周期)-1。 我们可以很容易地应用这个公式如下:

1。 选择一个空白单元格,例如Cell E3,在其中输入以下公式,然后按 输入 键。 看截图:

=(C12/C3)^(1/(10-1))-1

注意:在上面的公式中,C12是具有结束值的单元格,C3是具有起始值的单元格,10-1是起始值和结束值之间的时间段,您可以根据需要更改它们。

2。 在某些情况下,计算结果可能不会格式化为百分比。 请继续选择计算结果,单击 百分比样式 按键 主页 选项卡将数字更改为百分比格式,然后单击更改其小数位数 增加十进制 按键 or 减小十进制 按键 。 查看屏幕截图:


用Excel中的XIRR函数计算复合年增长率

实际上,XIRR函数可以帮助我们很容易地计算Excel中的复合年增长率,但是它要求您创建一个新的表格,其中包含起始值和结束值。

1。 创建一个新的表格,其起始值和结束值如下面的第一个屏幕截图所示:

注意:在Cell F3中输入= C3,在单元格G3中输入= B3,在单元格F4中输入= -C12,在单元格G4中输入= B12,或者您可以直接在此表格中输入原始数据。 顺便说一句,您必须在结束值之前添加减号。

2。 选择此表下方的空白单元格,在其中输入以下公式,然后按 输入 键。

= XIRR(F3:F4,G3:G4)

3。 要将结果更改为百分比格式,请选择具有此XIRR功能的单元格,然后单击 百分比样式 按键 主页 选项卡,然后单击更改其小数位 增加十进制 按键 or 减小十进制 按键 。 查看屏幕截图:


在Excel中计算平均年增长率

要计算excel中的平均年增长率,通常我们必须用公式计算每年的年增长率 =(结束值 - 开始值)/开始值,然后平均这些年增长率。 你可以这样做:

1。 除原始表格外,在空白单元格C3中输入以下公式,然后将填充句柄拖动到范围C3:C11。

=(C4-C3)/ C3

2。 选择Range D4:D12,单击 百分比样式 按键 主页 选项卡,然后单击更改其小数位 增加十进制 按键 or 减小十进制 按键 。 看截图:

3。 将下面的公式输入Cell F4,平均所有年增长率,然后按 输入 键。

= AVERAGE(D4:D12)

到目前为止,平均年增长率已经计算并显示在Cell C12中。


演示:在Excel中计算平均/复合年增长率

在这个视频中, Kutools Kutools Plus 选项卡添加 Kutools for Excel。 如果需要,请点击 60天免费试用,不受限制!

相关文章:


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    pp · 5 months ago
    Your period is wrong in CAGR formula ?
    • To post as a guest, your comment is unpublished.
      HB · 1 months ago
      I agree, there's only 9 periods from 1/1/11 to 1/1/20. Ten periods would be 1/1/11 to 12/31/20. Please explain
  • To post as a guest, your comment is unpublished.
    DQ · 1 years ago
    Hi - I'm trying to work backwards to find the highest price I can buy a share at when I have a total expected return. Are you able to please help me by reverse engineering the formula to work this out. Using your example - I'm trying to work out what the "3" should be 2.43443 =(3200/x)^(1/(40-8))-1
  • To post as a guest, your comment is unpublished.
    Robert · 1 years ago
    Hi,

    I have 7 fiscal years of foot traffic data for a retail store:

    FY12 FY13 FY14 FY15 FY16 FY17 FY18
    2653 2848 2871 2925 2685 2923 3000

    My question is: while traffic is increasing, is it increasing at a decreasing rate? Is the growth slowing?


    Your first example "(B11/B2)^(1/(10-1))-1" takes the end value and beginning value to get the CAGR. The part I don't understand is that, what about the values in the middle? How does only taking the end and beginning value determine the growth rate accurately? Is there another method where it takes all the fiscal year values into account?
    • To post as a guest, your comment is unpublished.
      Robert · 1 years ago
      FY12 - 2653
      FY13 - 2848
      FY14 - 2871
      FY15 - 2925
      FY16 - 2685
      FY17 - 2923
      FY18 - 3000
      • To post as a guest, your comment is unpublished.
        Tang Kelly · 1 years ago
        Hi Robert,
        You can use this formula = (Ending Value - Beginning Value) / Beginning Value to calculate the growth rate of each year, and then compare those growth rates one by one.
  • To post as a guest, your comment is unpublished.
    Wayne Hurt · 1 years ago
    Can someone help with this problem using excel?

    The following data show average growth of the human embryo prior to birth.
    EMBRYO AGE IN WEEKS WEIGHT IN GRAMS
    8 3
    12 36
    20 330
    28 1000
    36 2400
    40 3200

    a). Find the quadratic function of “best fit” for this data. Write this function in standard form: f(x) = ax2 + bx + c.
    b). Make a sketch of the scatter plot and the parabola. Plot the quadratic function found above the Y = menu.
    c). According to your model, what would a 32-week embryo weigh?
    d). According to your model, what week would an embryo weigh 3000 grams?
    e). Could the model be used for weight of an embryo for any number of weeks age (such as 100 or 200)? Explain
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi Wayne,
      You can calculate the Compound Annual Growth Rate with the second method:
      =(3200/3)^(1/(40-8))-1
  • To post as a guest, your comment is unpublished.
    McKing · 2 years ago
    There is a new tool that will fit to your planning software.
    MS Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS.
    It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
    Thus, it can do Bookkeeping/Accounting, Budgeting (Existing Year and Long Term) and Data Analysis.
  • To post as a guest, your comment is unpublished.
    Shailender · 2 years ago
    In the CAGR formula, why we are using -1 at the end.
    I am using two formulaes 1) (I5/I4)^(1/(25-1))-1
    2)(I5/I4)^(1/(25)-1) which one is correct..?
    Please help me out on this thank you.
    • To post as a guest, your comment is unpublished.
      Sergio · 2 years ago
      The first formula you are using is the correct one!
  • To post as a guest, your comment is unpublished.
    Daniel · 2 years ago
    The formula is actually CORRECT, but the explanation is incorrect. There are 10 dates that represent 9 periods. It should say that "n-1" means "dates-1", not "periods - 1".

    You can verify it yourself by increasing each year by this example's n-1 CAGR and you will get the final result.
  • To post as a guest, your comment is unpublished.
    Daniel · 2 years ago
    The original formula is CORRECT, but its explanation is wrong; It confuses the number of dates shown with the number of periods. In the example, there are ten dates listed but they extend over only 9 years (periods). That is why there is a minus 1.Try it yourself by calculating each year's increase at the CAGR of 6.5257% and you will get the correct ending answer.
    • To post as a guest, your comment is unpublished.
      HB · 1 months ago
      thank you. Strange only two others seem to have noticed this. I'd like to know why doesn't the formula just say to put in the actual number of periods, such as the 9 periods shown instead of ten minus one? Actually gave the correct formula n=number of periods) which is smart, because then eveeryone can use it for say 4/1/xx to 9/31/xx and actually list the number of periods in years with a decimal to represent the part of the year related to thte number of months between the beginning of April to the end of Sept
  • To post as a guest, your comment is unpublished.
    Andrew · 2 years ago
    As noted above, this formula is INCORRECT and misleading (especially since it's the first Google result). Please correct!
  • To post as a guest, your comment is unpublished.
    ramsy foss · 2 years ago
    I need the amount $20,000 @ 8% & 10% would be after 30 years compounded growth please. Thank you, Ramsy
  • To post as a guest, your comment is unpublished.
    Dan · 3 years ago
    Agree with MANOJ - the formula is wrong. it is not n-1
  • To post as a guest, your comment is unpublished.
    Manoj · 3 years ago
    Hi Bro,

    Your Formula for CAGR: [quote][b]=((End Value/Start Value)^(1/(Periods - 1)) -1[/b] [/quote] highlighted in blue is incorrect.

    The correct CAGR formula is:

    = ((FV/PV)^(1/n)) - 1

    Where,
    FV: Future Value
    PV: Present Value
    n: Number of years