=(B2/A2)^(1/C2)-1は、A2の開始値からB2の終了値までのC2年間の年平均成長率(CAGR)を返します。開始値を終了値に変える、一定の1つの年率です。=RRI(C2,A2,B2)も同じ結果を返します。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years | CAGR | RRI |
| 2 | $50,000 | $80,000 | 4 | 12.47% | 12.47% |
売上は4年で50,000から80,000に伸び、CAGRは12.47%です。結果の表示形式をパーセントにしてください(ホーム > パーセントスタイル、またはCtrl+Shift+%、MacではControl+Shift+%)。そうしないと0.1247のような小数で表示されます。C2を2に変えると、同じ成長を半分の期間で達成したことになり、年26.49%になります。
CAGRの数式を1ステップずつ
CAGR = (end / start) ^ (1 / years) - 1
end/startは全体の成長倍率です。80,000 / 50,000 = 1.6なので、売上は以前の1.6倍です。^(1/years)はその倍率の4乗根を取ります。4回掛け合わせると1.6になる年ごとの倍率です。1/4乗が4乗根です。-1で倍率を率に変えます。1.1247は12.47%になります。
かっこが大切です。=B2/A2^(1/C2)-1はA2だけをべき乗します。^は/より先に計算されるからで、意味のない結果になります。
RRI(Excel 2013以降とGoogleスプレッドシート)は同じ3つの数を別の順番で受け取ります：=RRI(nper, pv, fv)、つまり年数、開始値、終了値です。
年ごとの値の表からCAGRを求める
1年1行の表なら、最初と最後の値を取り、期間の数は年そのものから数えます。2021年から2026年の6行は5期間の成長で、6を使ってしまうのがよくある間違いです。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Users | Growth | Measure | Rate | |
| 2 | 2021 | 12,000 | CAGR | 16.72% | ||
| 3 | 2022 | 18,000 | 50.0% | Average growth | 18.86% | |
| 4 | 2023 | 15,300 | -15.0% | |||
| 5 | 2024 | 19,900 | 30.1% | |||
| 6 | 2025 | 24,500 | 23.1% | |||
| 7 | 2026 | 26,000 | 6.1% |
ユーザー数はCAGR 16.72%で伸びましたが、5年分の成長率の平均は18.86%です。平均は2022年の50%の急増に引き上げられ、2023年の減少を十分に数えていません。CAGRこそ正直な1つの数です。=B2*(1+F2)^5はちょうど26,000になります。B3を8000に変えると、平均は大きく下がりますが、CAGRはまったく動きません。CAGRは最初と最後の値だけで決まるからです。1年だけの変化については変化率を参照してください。
CAGRで将来の値を予測する
逆に使えば、成長率から値の行き先がわかります：=start*(1+rate)^years。ここでは10,000が年8%で成長します：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Value | Rate | |
| 2 | 0 | $10,000 | 8% | |
| 3 | 1 | $10,800 | ||
| 4 | 2 | $11,664 | ||
| 5 | 3 | $12,597 | ||
| 6 | 4 | $13,605 | ||
| 7 | 5 | $14,693 |
年8%で5年たつと、10,000は$14,693になります。その終了値を5年でCAGRの数式に戻すと、また8%になります。同じ複利のしくみが、PMTのページの積立計画やローンも動かしています。
2つの日付の間のCAGR
期間が整数の年数でないときは、正確な年数を指数に使います。YEARFRAC(start,end,1)は2つの日付からそれを返します。1は実際の日数で数え、既定では1か月を30日として数えます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bought | Sold | Paid | Sold for | Years | CAGR |
| 2 | 2022-03-15 | 2026-09-30 | $8,000 | $11,500 | 4.55 | 8.31% |
2022年3月から2026年9月までは約4.55年で、CAGRは8.31%です。途中でお金の出し入れ(毎月の入金、一部の売却)があると、CAGRは使えません。NPVとIRRのページのXIRRを使ってください。
やってみよう: 都市の人口の成長
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Years | Pop 2016 | Pop 2026 | CAGR |
| 2 | 10 | 412,000 | 538,000 |
やってみよう: D2に、A2の年数をかけてB2の人口からC2の人口になったときの年平均成長率を計算してください。
ヒント: (end/start)^(1/years)-1です。割り算と指数のまわりのかっこを残してください。
CAGRと平均成長率とIRRの使い分け
| 手元にあるもの | 使うもの | 数式 |
|---|---|---|
| 開始値、終了値、年数 | CAGR | =(end/start)^(1/years)-1または=RRI(years,start,end) |
| ある年から次の年への変化 | 変化率 | =new/old-1 |
| まとめたい年ごとの率のリスト | 幾何平均 | =GEOMEAN(1+C3:C7)-1で、CAGRと等しくなります |
| 時間とともに出入りするお金 | IRRまたはXIRR | =IRR(values)、=XIRR(values,dates) |
売上、価格、投資のように複利で増えるものについて、年ごとの成長率をそのままAVERAGEした値を「年平均成長率」として報告するのは避けてください。+50%の後に-50%なら平均は0%ですが、値は開始時の75%に下がっています。
よくある質問
ExcelのCAGRの数式は？
=(終了/開始)^(1/年数)-1で、たとえば=(B2/A2)^(1/C2)-1です。結果の表示形式をパーセントにします。Excel 2013以降では=RRI(C2,A2,B2)も同じ結果を返します。
CAGRには何年を使いますか？
値の個数ではなく、最初の値と最後の値の間の期間の数です。2021年から2026年までは、表が6行あっても5年です。
CAGRと年平均の成長率の違いは何ですか？
平均成長率は、各年の変化率をそのまま平均したものです。CAGRは、開始値を終了値に変える一定の1つの率です。下落の後に回復すると、平均は成長を大きく見せます。+50%の後に-50%なら平均は0%ですが、開始時の75%に減っていて、CAGRは約-13.4%です。
Excelで2つの日付の間のCAGRを求めるには？
正確な年数を指数に使います：=(B2/A2)^(1/YEARFRAC(C2,D2,1))-1。C2とD2は開始日と終了日です。1は実際の日数で数えます。
マイナスの数でCAGRを計算できますか？
意味のある結果にはなりません。開始値が0なら#DIV/0!になり、開始と終了の符号が違うと数式は#NUM!か意味のない数を返します。その場合は変化を絶対額で報告してください。
