=(B2/A2)^(1/C2)-1 trả về tốc độ tăng trưởng kép hằng năm (CAGR) từ giá trị đầu ở A2 đến giá trị cuối ở B2 trong C2 năm. Đó là một tỷ lệ tăng ổn định duy nhất mỗi năm để biến giá trị đầu thành giá trị cuối. =RRI(C2,A2,B2) cho ra cùng kết quả.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years | CAGR | RRI |
| 2 | $50,000 | $80,000 | 4 | 12.47% | 12.47% |
Doanh thu tăng từ 50.000 lên 80.000 trong 4 năm, tức CAGR là 12,47%. Hãy định dạng kết quả theo kiểu phần trăm (Home > Percent Style, hoặc Ctrl+Shift+%, trên Mac là Control+Shift+%), nếu không nó sẽ hiện dưới dạng số thập phân như 0,1247. Đổi C2 thành 2 thì cùng mức tăng đó trong một nửa thời gian tương ứng 26,49% mỗi năm.
Công thức CAGR, từng bước
CAGR = (end / start) ^ (1 / years) - 1
end/startlà hệ số tăng trưởng tổng: 80.000 / 50.000 = 1,6, nghĩa là doanh thu bằng 1,6 lần trước đây.^(1/years)lấy căn bậc 4 của hệ số đó, tức hệ số hằng năm mà nhân với chính nó 4 lần sẽ được 1,6. Lũy thừa 1/4 chính là căn bậc 4.-1đổi hệ số thành tỷ lệ: 1,1247 trở thành 12,47%.
Dấu ngoặc rất quan trọng. =B2/A2^(1/C2)-1 chỉ nâng A2 lên lũy thừa, vì ^ được tính trước /, và cho ra một kết quả vô nghĩa.
RRI (Excel 2013 trở lên, và Google Sheets) nhận đúng ba con số đó nhưng theo thứ tự khác: =RRI(nper, pv, fv), tức số năm, giá trị đầu, giá trị cuối.
CAGR từ bảng giá trị theo năm
Khi mỗi năm là một hàng, hãy lấy giá trị đầu tiên và cuối cùng, rồi đếm số kỳ dựa vào chính các năm. Sáu hàng từ 2021 đến 2026 là 5 kỳ tăng trưởng, và một lỗi hay gặp là dùng số 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% |
Số người dùng tăng với CAGR là 16,72%, nhưng trung bình của năm mức tăng trưởng hằng năm lại là 18,86%. Trung bình bị kéo lên bởi cú tăng 50% năm 2022 và không phản ánh đủ mức giảm năm 2023. CAGR mới là con số duy nhất trung thực: =B2*(1+F2)^5 cho ra đúng 26.000. Đổi B3 thành 8000: trung bình giảm mạnh, còn CAGR không hề thay đổi, vì nó chỉ phụ thuộc vào giá trị đầu và giá trị cuối. Để xem mức thay đổi của riêng một năm, hãy xem phần trăm thay đổi.
Dự báo giá trị tương lai bằng CAGR
Làm ngược lại, một tỷ lệ tăng trưởng cho bạn biết một giá trị sẽ đạt đến đâu: =start*(1+rate)^years. Ở đây 10.000 tăng 8% mỗi năm:
| 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 |
Sau 5 năm với 8%, 10.000 thành 14.693. Đưa giá trị cuối đó trở lại công thức CAGR với 5 năm, bạn lại được 8%. Cũng chính lãi kép này chi phối các kế hoạch tiết kiệm và khoản vay ở trang PMT.
CAGR giữa hai ngày
Khi khoảng thời gian không tròn số năm, hãy dùng đúng độ dài tính theo năm làm số mũ. YEARFRAC(start,end,1) trả về con số đó từ hai ngày; số 1 nghĩa là đếm số ngày thực tế, còn mặc định thì tính mỗi tháng 30 ngày.
| 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% |
Từ tháng 3 năm 2022 đến tháng 9 năm 2026 là khoảng 4,55 năm, tương ứng CAGR 8,31%. Khi có tiền vào và ra trong suốt quá trình (gửi tiền hằng tháng, bán bớt một phần), CAGR không còn áp dụng được nữa: hãy dùng XIRR ở trang NPV và IRR.
Thử ngay: tăng trưởng dân số của một thành phố
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Years | Pop 2016 | Pop 2026 | CAGR |
| 2 | 10 | 412,000 | 538,000 |
Đến lượt bạn: Trong D2, tính tốc độ tăng trưởng kép hằng năm từ dân số ở B2 đến dân số ở C2, trong số năm ở A2.
Gợi ý: (end/start)^(1/years)-1. Giữ dấu ngoặc quanh phép chia và quanh số mũ.
CAGR, tăng trưởng trung bình và IRR
| Bạn có | Dùng | Công thức |
|---|---|---|
| Một giá trị đầu, một giá trị cuối và số năm | CAGR | =(end/start)^(1/years)-1 hoặc =RRI(years,start,end) |
| Mức thay đổi từ năm này sang năm sau | Phần trăm thay đổi | =new/old-1 |
| Một danh sách tỷ lệ hằng năm cần tóm tắt | Trung bình nhân | =GEOMEAN(1+C3:C7)-1, bằng đúng CAGR |
| Dòng tiền vào và ra theo thời gian | IRR hoặc XIRR | =IRR(values), =XIRR(values,dates) |
Đừng báo cáo trung bình cộng AVERAGE thông thường của các tỷ lệ tăng trưởng hằng năm như là "tăng trưởng trung bình hằng năm" cho bất cứ thứ gì có tính lãi kép, như doanh thu, giá cả hay khoản đầu tư. Sau +50% rồi -50%, trung bình là 0%, nhưng giá trị đã giảm còn 75% so với lúc đầu.
Câu hỏi thường gặp
Công thức CAGR trong Excel là gì?
=(end/start)^(1/years)-1, ví dụ =(B2/A2)^(1/C2)-1. Định dạng kết quả theo kiểu phần trăm. =RRI(C2,A2,B2) cho cùng kết quả trong Excel 2013 trở lên.
Tính CAGR thì dùng bao nhiêu năm?
Số kỳ giữa giá trị đầu tiên và giá trị cuối cùng, không phải số lượng giá trị. Từ 2021 đến 2026 là 5 năm, dù bảng có 6 hàng.
CAGR và tăng trưởng trung bình hằng năm khác nhau thế nào?
Tăng trưởng trung bình là trung bình cộng thông thường của mức thay đổi phần trăm từng năm. CAGR là một tỷ lệ ổn định duy nhất biến giá trị đầu thành giá trị cuối. Sau một lần giảm rồi hồi phục, trung bình sẽ phóng đại mức tăng trưởng: +50% rồi -50% có trung bình là 0% nhưng chỉ còn lại 75% giá trị ban đầu, tức CAGR khoảng -13,4%.
Làm sao để tính CAGR giữa hai ngày trong Excel?
Dùng đúng độ dài tính theo năm làm số mũ: =(B2/A2)^(1/YEARFRAC(C2,D2,1))-1, trong đó C2 và D2 là ngày bắt đầu và ngày kết thúc. Số 1 nghĩa là đếm theo số ngày thực tế.
Có tính được CAGR với số âm không?
Không ra kết quả có ý nghĩa. Giá trị đầu bằng 0 sẽ cho #DIV/0!, còn khi giá trị đầu và cuối trái dấu, công thức trả về #NUM! hoặc một con số vô nghĩa. Khi đó hãy báo cáo mức thay đổi theo giá trị tuyệt đối.
