วิธีสร้างสูตร CAGR ใน Excel

เรียนรู้วิธีคำนวณอัตราการเติบโตประจำปีแบบผสม (CAGR) ใน Excel โดยใช้ตัวดำเนินการหรือฟังก์ชัน Excel ต่างๆ

CAGR ย่อมาจาก Compound Annual Growth Rate ซึ่งวัดอัตราการเติบโตประจำปี (อัตราที่ราบรื่น) ของการลงทุนทุกปีในช่วงเวลาที่กำหนด ตัวอย่างเช่น สมมติว่าคุณซื้อหุ้นมูลค่า 100 ดอลลาร์ในปี 2554 และมีมูลค่า 400 ดอลลาร์ในปี 2564 CAGR จะเป็นอัตราที่การลงทุนของคุณในหุ้นนั้นเติบโตทุกปี

ตลาดหุ้นมีความผันผวน การเติบโตของการลงทุนอาจแตกต่างกันไปในแต่ละปี ผลตอบแทนจากการลงทุนอาจเพิ่มขึ้นหรือลดลง CAGR ช่วยให้ผลตอบแทนจากการลงทุนเป็นไปอย่างราบรื่นเช่นเดียวกับการเติบโตในอัตราที่สมดุลทุกปี

แม้ว่าจะไม่มีฟังก์ชัน CAGR ใน Excel แต่มีหลายวิธีที่คุณสามารถคำนวณ CAGR ใน Excel บทความนี้แสดงวิธีสร้างสูตร CAGR ที่ชัดเจนและเข้าใจง่ายโดยใช้ฟังก์ชัน Excel ต่างๆ

วิธีการสร้าง อัตราการเติบโตประจำปีแบบทบต้น (CAGR) สูตรใน Excel

อัตราการเติบโตต่อปีแบบทบต้น (CAGR) มีประโยชน์มากสำหรับธุรกิจ การวางแผนทางการเงิน แบบจำลองทางการเงิน และการวิเคราะห์การลงทุน สูตร CAGR คำนวณการเติบโตประจำปีของการลงทุนที่สามารถนำไปเปรียบเทียบกับการลงทุนอื่นๆ

ในการคำนวณ CAGR คุณต้องมีอินพุตหลักสามตัว: ค่าเริ่มต้นของการลงทุน มูลค่าสุดท้าย และจำนวนงวด (ปี)

สูตร CAGR

ไวยากรณ์ของสูตร CARG คือ:

CAGR =(มูลค่าสิ้นสุด/มูลค่าเริ่มต้น)1/n - 1

ที่ไหน:

• ค่าสิ้นสุด – ยอดคงเหลือปลายงวดของการลงทุน ณ วันสิ้นงวดการลงทุน
• ค่าเริ่มต้น – ยอดคงเหลือต้นงวดของการลงทุนเมื่อเริ่มต้นงวดการลงทุน
• น – จำนวนปีที่คุณลงทุน

การคำนวณ CAGR ใน Excel

ตอนนี้คุณได้เรียนรู้เลขคณิตเบื้องหลังดอกเบี้ยทบต้นแล้ว มาดูกันว่าคุณจะคำนวณ CAGR ใน Excel ได้อย่างไร มี 5 วิธีในการสร้างสูตร Excel เพื่อคำนวณ CAGR:

• การใช้ตัวดำเนินการเลขคณิต
• การใช้ฟังก์ชัน RRI
• การใช้ฟังก์ชัน POWER
• การใช้ฟังก์ชัน RATE
• การใช้ฟังก์ชัน IRR

คำนวณ CAGR ใน Excel โดยใช้ตัวดำเนินการ

วิธีการคำนวณ CAGR โดยตรงคือการใช้ตัวดำเนินการ ใช้สูตรทั่วไปข้างต้นในการคำนวณ CAGR

สมมติว่าเรามีข้อมูลการขายสำหรับบริษัทบางแห่งในสเปรดชีตด้านล่าง

คอลัมน์ A มีปีที่มีรายได้ คอลัมน์ B มีรายได้ของบริษัทในปีนั้น ๆ ด้วยสูตร CAGR ใน excel คุณสามารถคำนวณอัตราการเติบโตประจำปีสำหรับรายได้

ป้อนสูตรด้านล่างเพื่อคำนวณ CAGR โดยวิธีโดยตรง:

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

ในตัวอย่างด้านล่าง ค่าเริ่มต้นของการลงทุนอยู่ในเซลล์ B2 และค่าสิ้นสุดในเซลล์ B11 จำนวนปี (รอบระยะเวลา) ระหว่างจุดเริ่มต้นและจุดสิ้นสุดของระยะเวลาการลงทุนคือ 9 โดยปกติ ระยะเวลาของรอบการลงทุนแต่ละรอบเริ่มต้นหนึ่งปีและสิ้นสุดในปีถัดไป ดังนั้น งวดแรกของรอบการลงทุนในกรณีนี้คือ พ.ศ. 2554 -2012 และรอบสุดท้ายคือปี 2019-2020 ดังนั้นจำนวนปีที่ลงทุนทั้งหมดคือ '9'

ผลลัพธ์จะเป็นตัวเลขทศนิยมไม่ใช่เปอร์เซ็นต์ตามที่แสดงด้านบน หากต้องการแปลงเป็นเปอร์เซ็นต์ ให้ไปที่แท็บ "หน้าแรก" คลิกเมนูแบบเลื่อนลงที่ระบุว่า "ทั่วไป" ในกลุ่มตัวเลข แล้วเลือกตัวเลือก "% เปอร์เซ็นต์"

ตอนนี้ เราได้อัตราการเติบโตต่อปีที่เท่ากับ '10.77%' ในเซลล์ B13 นี่เป็นอัตราการเติบโตที่ราบรื่นเพียงครั้งเดียวตลอดระยะเวลา

การคำนวณ CAGR ใน Excel โดยใช้ฟังก์ชัน RRI

ฟังก์ชัน RRI จะวัดอัตราดอกเบี้ยที่เทียบเท่ากันเป็นงวดสำหรับผลตอบแทนจากการลงทุนหรือเงินกู้ในช่วงเวลาหนึ่งๆ

อัตราดอกเบี้ยคำนวณจากมูลค่าปัจจุบันและอนาคตของการลงทุนและระยะเวลา

ไวยากรณ์:

=RRI(nper,pv,fv) 

• nper – จำนวนงวดทั้งหมด (ปี)
• pv – ระบุมูลค่าปัจจุบันของการลงทุนหรือเงินกู้ (เท่ากับมูลค่าเริ่มต้น)
• fv – ระบุมูลค่าในอนาคตของการลงทุนหรือเงินกู้ (เช่นเดียวกับมูลค่าสิ้นสุด)

เรามีค่า nper ในเซลล์ A11, pv ในเซลล์ C2 และ fv ในเซลล์ C11 ใช้สูตรนี้:

=RRI(A11,C2,C11)

CAGR คือ '10.77%' ซึ่งอยู่ในเซลล์ B13

การคำนวณ CAGR ใน Excel โดยใช้ฟังก์ชัน POWER

อีกวิธีง่ายๆ ในการคำนวณอัตราการเติบโตรายปีแบบทบต้น (CAGR) ใน Excel คือการใช้ฟังก์ชัน POWER ฟังก์ชัน POWER แทนที่ ^ ตัวดำเนินการในฟังก์ชัน CAGR

ไวยากรณ์ของฟังก์ชัน POWER:

=อำนาจ(จำนวน,กำลัง)

อาร์กิวเมนต์ของฟังก์ชัน POWER:

• ตัวเลข – เป็นจำนวนฐานที่พบโดยการหารค่าสิ้นสุด (EV) ด้วยค่าเริ่มต้น (BV) (EV/BV)
• พลัง – คือการเพิ่มผลลัพธ์เป็นเลขชี้กำลังหนึ่งหารด้วยระยะเวลา (1/จำนวนงวด (n))

ตอนนี้ อาร์กิวเมนต์ถูกกำหนดด้วยวิธีนี้เพื่อค้นหาค่า CAGR:

=พลังงาน(EV/BV,1/n)-1

ลองใช้สูตรกับตัวอย่าง:

=พลังงาน(C11/C2,1/A11)-1

ผลลัพธ์:

การคำนวณ CAGR ใน Excel โดยใช้ฟังก์ชัน RATE

ฟังก์ชันอัตราเป็นอีกฟังก์ชันหนึ่งที่คุณสามารถใช้ค้นหา CAGR ใน Excel เมื่อคุณดูไวยากรณ์ของฟังก์ชัน RATE อาจดูซับซ้อนเล็กน้อยด้วยอาร์กิวเมนต์ 6 ตัว แต่เมื่อเข้าใจฟังก์ชันแล้ว คุณอาจชอบวิธีนี้ในการค้นหาค่า CAGR

ไวยากรณ์ของฟังก์ชัน RATE:

=RATE(nper,pmt,pv,[fv],[type],[เดา]) 

ที่ไหน:

• nper – จำนวนงวดการชำระเงินทั้งหมด (ระยะเวลาเงินกู้)
• Pmt (ไม่บังคับ) – จำนวนเงินที่ชำระในแต่ละงวด
• Pv – ระบุมูลค่าปัจจุบันของเงินกู้/การลงทุน (มูลค่าเริ่มต้น (BV))
• [Fv]– ระบุมูลค่าในอนาคตของเงินกู้/การลงทุน เมื่อชำระครั้งสุดท้าย (มูลค่าสิ้นสุด (EV))
• [พิมพ์] – ระบุเมื่อการชำระเงินสำหรับเงินกู้/การลงทุนครบกำหนด เป็น 0 หรือ 1 อย่างใดอย่างหนึ่ง อาร์กิวเมนต์ 0 หมายถึงการชำระเงินจะครบกำหนดเมื่อเริ่มต้นของงวด และ 1 หมายถึง การชำระเงินจะครบกำหนดเมื่อสิ้นสุดงวด (ค่าเริ่มต้นคือ 0).
• [เดา] - เดาอัตราของคุณ หากละไว้ ค่าเริ่มต้นคือ 10%

เหตุผลที่ฟังก์ชัน RATE มีอาร์กิวเมนต์ 6 ประการคือสามารถใช้สำหรับการคำนวณทางการเงินอื่นๆ ได้มากมาย แต่เราสามารถแปลงฟังก์ชัน RATE เป็นสูตร CAGR โดยมีเพียงสามอาร์กิวเมนต์ที่ 1 (nper), 3 (pv) และ 4 (fv) เท่านั้น:

=RATE(nper,,-BV,EV)

เนื่องจากเราไม่ได้ชำระเงินเป็นประจำ (รายเดือน รายไตรมาส รายปี) เราจึงเว้นอาร์กิวเมนต์ที่สองว่างไว้

ในการคำนวณอัตราการเติบโตต่อปีแบบทบต้นโดยใช้ฟังก์ชัน RATE ให้ใช้สูตรนี้:

=อัตรา(A11,,-C2,C11)

หากคุณไม่ต้องการคำนวณจำนวนงวดด้วยตนเอง ให้ใช้ฟังก์ชัน ROW เป็นอาร์กิวเมนต์แรกของ RATE fromula มันจะคำนวณ นปร สำหรับคุณ.

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

คำนวณ CAGR ใน Excel โดยใช้ฟังก์ชัน IRR

IRR ย่อมาจาก 'Internal Rate Return' เป็นฟังก์ชัน Excel ที่คำนวณ IRR สำหรับการชำระเงินและรายได้ที่เกิดขึ้นในช่วงเวลาปกติ (เช่น รายเดือน รายปี)

วิธี IRR มีประโยชน์เมื่อคุณต้องการคำนวณค่า CAGR สำหรับกระแสเงินสดเป็นงวด

ไวยากรณ์คือ:

=IRR(ค่า,[เดา])

ที่ไหน:

• ค่า - ช่วงการชำระเงิน ช่วงของการชำระเงินควรมีกระแสเงินสดติดลบและบวกอย่างน้อยหนึ่งรายการ
• [เดา] (ไม่บังคับ) – หมายถึงการคาดเดามูลค่าอัตราของคุณ หากละเว้น ค่าเริ่มต้นคือ 10%

ฟังก์ชัน Excel IRR ต้องการให้คุณปรับชุดข้อมูลใหม่ด้วยวิธีนี้:

ควรแทรกค่าเริ่มต้นเป็นจำนวนลบ ค่าสิ้นสุดเป็นจำนวนบวก และค่าอื่นๆ ทั้งหมดเป็นศูนย์

นี่คือสูตรสำหรับตัวอย่าง:

=IRR(C2:C11)

นี่คือวิธีที่คุณสามารถคำนวณอัตราการเติบโตต่อปี (CGAR) ใน Excel