Cách tạo công thức CAGR trong Excel

Tìm hiểu cách bạn có thể tính toán Tỷ lệ tăng trưởng hàng năm tổng hợp (CAGR) trong Excel bằng cách sử dụng các toán tử hoặc các hàm Excel khác nhau.

CAGR là viết tắt của Tỷ lệ tăng trưởng hàng năm tổng hợp, đo lường tốc độ tăng trưởng hàng năm (tỷ lệ làm mịn) của một khoản đầu tư hàng năm trong một khoảng thời gian nhất định. Ví dụ: giả sử bạn mua cổ phiếu trị giá 100 đô la vào năm 2011 và nó trị giá 400 đô la vào năm 2021, CAGR sẽ là tỷ lệ đầu tư của bạn vào cổ phiếu đó tăng lên hàng năm.

Thị trường chứng khoán luôn biến động, sự tăng trưởng của một khoản đầu tư có thể thay đổi hàng năm. Lợi tức đầu tư có thể tăng hoặc giảm. CAGR giúp lợi nhuận của một khoản đầu tư suôn sẻ như thể nó đã tăng trưởng với tốc độ cân bằng hàng năm.

Mặc dù không có hàm CAGR trong Excel, nhưng có một số cách bạn có thể tính CAGR trong Excel. Bài viết này hướng dẫn bạn cách tạo công thức CAGR rõ ràng và dễ hiểu bằng cách sử dụng các hàm Excel khác nhau.

Làm thế nào để tạo một Tỷ lệ tăng trưởng hàng năm tổng hợp (CAGR) công thức trong Excel

Tỷ lệ tăng trưởng hàng năm tổng hợp (CAGR) rất hữu ích cho hoạt động kinh doanh, lập kế hoạch tài chính, lập mô hình tài chính và phân tích đầu tư. Công thức CAGR tính toán mức tăng trưởng hàng năm của một khoản đầu tư có thể được sử dụng để so sánh với các khoản đầu tư khác.

Để tính CAGR, bạn cần ba yếu tố đầu vào chính: giá trị ban đầu, giá trị kết thúc của khoản đầu tư và số khoảng thời gian (năm).

Công thức CAGR

Cú pháp của công thức CARG là:

CAGR = (Giá trị kết thúc / Giá trị bắt đầu) 1 / n - 1

ở đâu:

Giá trị kết thúc – Số dư cuối kỳ của khoản đầu tư cuối kỳ.
Giá trị ban đầu – Số dư đầu kỳ của khoản đầu tư khi bắt đầu kỳ đầu tư.
n – Số năm bạn đã đầu tư.

Tính CAGR trong Excel

Bây giờ bạn đã học được số học đằng sau lãi kép, hãy xem cách bạn có thể tính CAGR trong Excel. Có 5 cách để tạo công thức Excel để tính CAGR:

Sử dụng toán tử số học
Sử dụng hàm RRI
Sử dụng chức năng POWER.
Sử dụng hàm RATE.
Sử dụng chức năng IRR.

Tính toán CAGR trong Excel bằng toán tử

Cách trực tiếp để tính CAGR là sử dụng các toán tử. Sử dụng công thức chung ở trên để tính CAGR.

Giả sử chúng ta có dữ liệu bán hàng cho một công ty nhất định trong bảng tính bên dưới.

Cột A có các năm mà doanh thu kiếm được. Cột B là doanh thu của công ty trong năm tương ứng. Với công thức CAGR trong excel, bạn có thể tính toán tốc độ tăng trưởng doanh thu hàng năm.

Nhập công thức dưới đây để tính CAGR theo phương pháp trực tiếp:

= (B11 / B2) ^ (1/9) -1

Trong ví dụ bên dưới, giá trị bắt đầu của khoản đầu tư nằm trong ô B2 và giá trị kết thúc trong ô B11. Số năm (khoảng thời gian) từ khi bắt đầu đến khi kết thúc giai đoạn đầu tư là 9. Thông thường, mỗi giai đoạn chu kỳ đầu tư bắt đầu một năm và kết thúc vào năm tiếp theo, do đó, kỳ đầu tiên của chu kỳ, trong trường hợp này là năm 2011. -2012 và chu kỳ cuối cùng là 2019-2020. Vì vậy, tổng số năm được đầu tư là '9'

Kết quả sẽ là số thập phân chứ không phải phần trăm như hình trên. Để chuyển đổi nó thành phần trăm, hãy chuyển đến tab "Trang chủ", nhấp vào menu thả xuống có nội dung 'Chung' trong nhóm Số và chọn tùy chọn 'Phần trăm'.

Bây giờ, chúng tôi nhận được tỷ lệ tăng trưởng kép hàng năm là '10 .77%' trong ô B13. Đây là tỷ lệ tăng trưởng ổn định duy nhất trong toàn bộ khoảng thời gian.

Tính CAGR trong Excel bằng hàm RRI

Hàm RRI đo lường lãi suất tương đương định kỳ đối với lợi tức của một khoản đầu tư hoặc khoản vay trong một khoảng thời gian cụ thể.

Lãi suất được tính dựa trên giá trị hiện tại và tương lai của khoản đầu tư và thời hạn.

Cú pháp:

= RRI (nper, pv, fv)

nper - tổng số kỳ (năm)
pv - Giá trị này xác định giá trị hiện tại của một khoản đầu tư hoặc một khoản vay (giống như giá trị ban đầu)
fv - Giá trị này xác định giá trị tương lai của một khoản đầu tư hoặc một khoản vay (giống như giá trị cuối kỳ)

Chúng tôi có giá trị cho nper trong ô A11, pv trong ô C2 và fv trong ô C11. Sử dụng công thức này:

= RRI (A11, C2, C11)

CAGR là '10 .77%', nằm trong ô B13.

Tính CAGR trong Excel bằng hàm POWER

Một phương pháp dễ dàng khác để tính tốc độ tăng trưởng kép hàng năm (CAGR) trong Excel là sử dụng hàm POWER. Chức năng POWER thay thế ^ trong hàm CAGR.

Cú pháp của hàm POWER:

= POWER (số, công suất)

Đối số của hàm POWER:

số - Là số cơ sở được tìm thấy bằng cách chia giá trị kết thúc (EV) cho giá trị đầu (BV) (EV / BV).
sức mạnh - Đó là nâng kết quả lên thành số mũ một chia cho khoảng thời gian (1 / số giai đoạn (n)).

Bây giờ, các đối số được định nghĩa theo cách này để tìm giá trị CAGR:

= POWER (EV / BV, 1 / n) -1

Hãy áp dụng công thức cho một ví dụ:

= POWER (C11 / C2,1 / A11) -1

Kết quả:

Tính CAGR trong Excel bằng hàm RATE

Hàm tỷ lệ là một hàm khác mà bạn có thể sử dụng để tìm CAGR trong Excel. Khi bạn nhìn vào cú pháp của hàm RATE, nó có thể trông hơi phức tạp với 6 đối số không hơn không kém, nhưng khi đã hiểu về hàm, bạn có thể thích phương pháp này hơn để tìm giá trị CAGR.

Cú pháp của hàm RATE:

= RATE (nper, pmt, pv, [fv], [type], [đoán])

Ở đâu:

nper – Tổng số kỳ thanh toán (thời hạn cho vay).
Pmt (tùy chọn) - Số tiền thanh toán được thực hiện trong mỗi kỳ.
Pv - Giá trị này xác định giá trị hiện tại của khoản vay / khoản đầu tư (giá trị ban đầu (BV))
[Fv]- Điều này chỉ định giá trị tương lai của khoản vay / đầu tư, tại lần thanh toán cuối cùng (giá trị cuối kỳ (EV))
[Loại] - Điều này chỉ định thời điểm các khoản thanh toán cho khoản vay / đầu tư đến hạn, nó là 0 hoặc 1. Đối số 0 có nghĩa là các khoản thanh toán đến hạn vào đầu kỳ và 1 có nghĩa là các khoản thanh toán đến hạn vào cuối kỳ (mặc định là 0).
[Đoán xem: V] - Dự đoán của bạn về tỷ lệ. Nếu bỏ qua, nó mặc định là 10%.

Lý do hàm RATE có sáu đối số là nó có thể được sử dụng cho nhiều phép tính tài chính khác. Nhưng chúng ta có thể chuyển đổi hàm RATE thành công thức CAGR, chỉ với ba đối số 1 (nper), 3 (pv) và 4 (fv):

= RATE (nper ,, - BV, EV)

Vì chúng tôi không thực hiện thanh toán thường xuyên (hàng tháng, hàng quý, hàng năm), chúng tôi để trống đối số thứ hai.

Để tính toán tỷ lệ tăng trưởng kép hàng năm bằng cách sử dụng hàm RATE, hãy sử dụng công thức sau:

= TỶ LỆ (A11 ,, - C2, C11)

Nếu bạn không muốn tính toán số khoảng thời gian theo cách thủ công, hãy sử dụng hàm ROW làm đối số đầu tiên của từ RATE. Nó sẽ tính toán npr cho bạn.

= RATE (ROW (A11) -ROW (A2) ,, - C2, C11)

Tính CAGR trong Excel bằng hàm IRR

IRR viết tắt của 'Internal Rate Return' là một hàm Excel tính IRR cho các khoản thanh toán và thu nhập xảy ra trong khoảng thời gian đều đặn (tức là hàng tháng, hàng năm).

Phương pháp IRR rất hữu ích khi bạn cần tính giá trị CAGR cho các dòng tiền định kỳ.

Cú pháp là:

= IRR (giá trị, [đoán])

Ở đâu:

giá trị - một loạt các khoản thanh toán. Phạm vi thanh toán phải có ít nhất một luồng tiền âm và một luồng tiền dương.
[đoán xem: V] (Tùy chọn) - Điều này thể hiện phỏng đoán của bạn về giá trị tỷ lệ. Nếu bỏ qua, nó mặc định là 10%.

Hàm IRR trong Excel yêu cầu bạn điều chỉnh lại tập dữ liệu theo cách sau:

Giá trị bắt đầu phải được chèn dưới dạng số âm, giá trị kết thúc là số dương và tất cả các giá trị khác là số không.

Đây là công thức cho ví dụ:

= IRR (C2: C11)

Chà, đây là những cách bạn có thể tính toán tỷ lệ tăng trưởng kép hàng năm (CGAR) trong Excel.