Cách tính CAGR trong Excel : tỷ lệ tăng trưởng luỹ kế hàng năm

21/08/2021

2041 Views

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 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ư trôi chảy 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.

Xem nhanh

Cách tạo công thức tỷ lệ tăng trưởng hàng năm tổng hợp (CAGR) 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 toán CAGR, bạn cần ba đầ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 =(Ending value/Beginning value)^1/n - 1

Ending value– Số dư cuối kỳ của khoản đầu tư cuối kỳ.
Beginning value– 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 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ó những năm, trong đó các khoản thu đượ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 được 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ụ dưới đây, 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 đầu tư là ‘9’

Kết quả sẽ là số thập phân không theo 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 ta 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. Hàm POWER thay thế toán tử ^ trong hàm CAGR.

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

=POWER(number,power)

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

number – Là số cơ bản được tìm thấy bằng cách chia giá trị kết thúc (EV) cho giá trị đầu (BV) (EV / BV).
power – Nó là nâng kết quả lên thành số mũ của một chia cho khoảng thời gian (1 / số khoảng thời gian (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],[guess])

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 / đầu tư (giá trị ban đầu (BV))
[Fv] – Điều này xác đị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 cùng (EV))
[Type] – Điều này chỉ định khi nào 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).
[Guess]– 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 ^st (nper), 3 ^rd (pv) và 4 ^th (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:

=RATE(A11,,-C2,C11)

Nếu bạn không muốn tính toán số chu kỳ 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 nprcho bạn.

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

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

IRR là 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(values,[guess])

values– 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.
[guess](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: