วิธีคำนวณ IRR ใน Excel: 4 วิธีที่ดีที่สุดในปี 2023

หากคุณต้องการตัดสินใจเกี่ยวกับแผนการลงทุนอย่างเป็นระบบ (SIP) หรือลงทุนในโครงการธุรกิจในฐานะผู้ร่วมทุน คุณต้องเรียนรู้วิธีคำนวณ IRR ใน Excel

มีหลายวิธีในการคำนวณผลตอบแทนจากการลงทุน (ROI) สำหรับการลงทุนในโครงการทางการเงินหรือธุรกิจ แม้ว่าการคำนวณตามอัตราคงที่จะค่อนข้างง่าย แต่ก็ไม่ใช่ทางเลือกของเครื่องมือสำหรับการคำนวณ ROI ในธุรกิจขนาดใหญ่หรือวาณิชธนกิจเสมอไป

สมมติว่าคุณจะลงทุนเงินจำนวนหนึ่งในแผนการลงทุนสำหรับ 20 ปีข้างหน้า แผนบอกว่าคุณจะได้รับผลตอบแทนทุกไตรมาส ตอนนี้ ไม่ว่าจะฉลาดที่จะลงทุนในแผนดังกล่าวหรือไม่ ก็สามารถตัดสินใจได้จากค่า IRR ของ SIP IRR ยิ่งสูงก็ยิ่งมีกำไรมากขึ้นสำหรับแผนการลงทุน เช่นเดียวกับการลงทุนที่เน้นเงินทุนอื่นๆ ทั้งหมดในธุรกิจ บัญชีออมทรัพย์ของธนาคาร กองทุนรวม 401(k) เป็นต้น

เพื่อให้คุณไม่ต้องพึ่งพาผู้อื่นเมื่อต้องลงทุนเงินที่หามาได้ยากลำบาก ฉันช่วยให้คุณเรียนรู้ว่า IRR ใน Excel คืออะไร สูตรที่ปรับเปลี่ยนหลายอย่าง และวิธีการคำนวณ IRR ใน Excel ด้วยวิธีง่ายๆ ต่างๆ ด้านล่างนี้

อัตราผลตอบแทนภายใน (IRR) คืออะไร?

IRR หรืออัตราผลตอบแทนภายในเป็นเมตริกทั่วไปที่นักวางแผนการเงินใช้ในการคำนวณหรือทำนายความสามารถในการทำกำไรในอนาคตของโครงการธุรกิจที่มีศักยภาพหรือการลงทุนประเภทอื่นๆ ในด้านการเงิน เป็นที่รู้จักกันแพร่หลายว่าเป็นอัตราผลตอบแทนทางเศรษฐกิจหรืออัตราผลตอบแทนของกระแสเงินสดคิดลด

อัตรา IRR หรืออัตราคิดลดของการลงทุนทำให้มูลค่าปัจจุบันสุทธิของกระแสเงินสดทุกประเภท เช่น การไหลออกและการไหลเข้ามีค่าเท่ากับศูนย์ ผู้เชี่ยวชาญด้านการเงินเรียกอัตราผลตอบแทนภายในนี้เนื่องจากการคำนวณนี้ไม่ได้พิจารณาปัจจัยอื่นๆ ที่อาจส่งผลกระทบในเชิงบวกหรือเชิงลบต่อธุรกิจใน���ีต่อๆ ไป

ตัวอย่างเช่น IRR ไม่ได้คำนึงถึงต้นทุนของเงินทุน อัตราเงินเฟ้อเมื่อเวลาผ่านไป ความเสี่ยงทางการเงิน ฯลฯ เมื่อคาดการณ์ความสามารถในการทำกำไรของโครงการธุรกิจหรือการลงทุนทางการเงิน

โดยสรุป เมื่อใดก็ตามที่คุณตัดสินใจลงทุนเป็นระยะเวลานานในโครงการเชิงพาณิชย์ กองทุนรวม หรือแผนการลงทุนเพื่อการเกษียณอายุ ให้ค้นหา IRR ของตัวเลือกการลงทุนเหล่านั้น จากนั้นเลือกตัวเลือกที่ให้ IRR สูงสุดจากตัวเลือกทั้งหมด

ฟังก์ชัน IRR ใน Microsoft Excel คืออะไร

ด้วย Microsoft คุณไม่จำเป็นต้องสร้างสูตรหลายสูตรหรือผ่านการคำนวณที่ซับซ้อนเพื่อรับ IRR ของแผนการลงทุน Excel มีฟังก์ชันการคำนวณอัตราผลตอบแทนภายในสามแบบโดยเฉพาะ เรียนรู้เกี่ยวกับฟังก์ชันเหล่านี้ที่นี่:

1. IRR สำหรับอัตราผลตอบแทนภายในอย่างง่าย

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

=IRR(ค่าหรือช่วงเซลล์, [เดาอาร์กิวเมนต์])

• ค่าเป็นอินพุตที่จำเป็นสำหรับฟังก์ชัน IRR อาจเป็นชุดตัวเลข อาร์เรย์ของตัวเลข หรือช่วงเซลล์อ้างอิงที่มีกระแสเงินสด
• ข้อโต้แย้ง Guess เป็นทางเลือก หากคุณไม่ได้ระบุค่า Guess จากนั้น Excel จะพิจารณาอัตราผลตอบแทนภายใน 0.1, 10% หรือ 10 คะแนนตามเกณฑ์ที่กำหนดโดยแผนการลงทุน หากคุณระบุอาร์กิวเมนต์ Guess ไว้ Excel จะเรียกใช้การคำนวณซ้ำสูงสุด 20 เซสชันเพื่อค้นหาค่า IRR ที่ใกล้เคียงกับค่า Guess

2. XIRR สำหรับระยะเวลาการคืนสินค้าไม่เท่ากัน

หากคุณต้องการแยกตัวประกอบในกำหนดการกระแสเงินสด คุณต้องใช้ฟังก์ชัน XIRR ไวยากรณ์เป็นดังนี้:

=XIRR(ค่าหรือช่วงเซลล์ วันที่ [guess argument])

3. MIRR สำหรับการลงทุนซ้ำของกองทุนโครงการ

MIRR ให้ค่า IRR ที่เชื่อถือได้สูงเมื่อเปรียบเทียบกับฟังก์ชัน IRR แบบง่าย IRR ไม่พิจารณาต้นทุนของเงินทุนที่คุณลงทุน ดังนั้น ค่าสุดท้ายของ IRR จึงไม่ถูกต้อง คุณสามารถใช้ฟังก์ชัน MIRR เพื่อรวมต้นทุนของตัวประกอบทุนในการคำนวณ IRR ได้ ดังนั้น ไวยากรณ์จะกลายเป็นดังที่แสดงด้านล่าง:

=MIRR(ค่าหรือช่วงเซลล์,finance_rate,reinvest_rate)

• Values ​​คืองบกระแสเงินสดซึ่งประกอบด้วยกระแสเงินสดบวกและลบอย่างน้อยหนึ่งรายการ
• Finance_rate คือดอกเบี้ยที่คุณจ่ายให้กับผู้ให้กู้เทียบกับเงินที่ยืมมาสำหรับโครงการธุรกิจ
• Reinvest_rate คืออัตราดอกเบี้ยที่ธุรกิจหรือเงินลงทุนของคุณได้รับจากกระแสเงินสดจากการลงทุนใหม่

วิธีคำนวณ IRR ใน Excel: การใช้ไวยากรณ์ IRR

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

• เปิดแผ่นงาน Excel และสร้างส่วนหัวของคอลัมน์สามรายการ: ระยะเวลาคำอธิบายและกระแสเงินสด
• เติมข้อมูลที่คุณต้องการคำนวณภายใต้ส่วนหัวของคอลัมน์ที่เกี่ยวข้อง

การใช้สูตร IRR ใน Excel

• การลงทุนครั้งแรกควรมีค่าเป็นลบ เนื่องจากคุณจ่ายเงินให้กองทุนรวมหรือผู้ประกอบการธุรกิจเพื่อใช้เงิน
• หากคุณจะได้รับรายได้ประจำหลังจากหนึ่งเดือนหรือหนึ่งปี รายได้เหล่านั้นควรเพิ่มเป็นค่าบวก

เรียนรู้วิธีคำนวณ IRR ใน Excel โดยใช้ไวยากรณ์ IRR

• ตอนนี้ ใช้สูตรต่อไปนี้ในเซลล์ที่คุณต้องการให้ Excel คำนวณค่า IRR:

=IRR(C2:C8)

• กดEnterเพื่อรับ ROI จากการลงทุนที่มีศักยภาพ

การคำนวณ IRR นี้ขึ้นอยู่กับอาร์กิวเมนต์ Guess เริ่มต้นที่ 10% สมมติว่าคุณกำลังมองหาผลตอบแทน 12% ต่อไปนี้คือวิธีที่คุณต้องป้อนค่าเดา:

=IRR(C2:C8,30%)

• ในตารางด้านบน คุณเห็นว่ากระแสเงินสดล่าสุดเป็นค่าลบ ดังนั้นกระแสเงินสดจะต้องเปลี่ยนจากบวกเป็นลบหรือลบเป็นบวก

ดูวิธีการคำนวณ IRR ใน Excel โดยใช้ค่า IRR Guess

• ในสถานการณ์สมมตินี้ ใช้Guessเพื่อค้นหาผลลัพธ์ที่ถูกต้อง เนื่องจาก IRR จะให้ผลลัพธ์สองรายการ

วิธีคำนวณ IRR บน Excel เมื่อใช้ค่า Guess ที่เป็นลบ

• ข้อ โต้แย้ง Guessควรเป็น ROI ของการลงทุนที่คุณคาดหวัง

วิธีคำนวณ IRR ใน Excel: การใช้ไวยากรณ์ XIRR

เมื่อคุณได้รับผลตอบแทนในวันที่ต่างกันหลังจากการลงทุนครั้งแรก การใช้ไวยากรณ์ IRR จะให้ผลลัพธ์ที่มีข้อบกพร่อง ในสถานการณ์สมมตินี้ คุณต้องใช้ไวยากรณ์ XIRR ต่อไปนี้เป็นขั้นตอนที่คุณสามารถลองใช้กับแผ่นงาน Excel ของคุณเองได้:

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

ค้นพบวิธีคำนวณ IRR ใน Excel โดยใช้ไวยากรณ์ XIRR

• เมื่อเสร็จแล้ว ให้ใช้ไวยากรณ์ XIRR ต่อไปนี้ ในเซลล์ใดๆ เพื่อรับอัตราผลตอบแทนภายในสำหรับการลงทุนที่มีกำหนดการชำระผลตอบแทนที่ไม่สม่ำเสมอ:

=XIRR(C2:C8,D2:D8)

วิธีคำนวณ IRR ใน Excel: การใช้ไวยากรณ์ MIRR

เพื่อให้ค่า IRR เป็นจริงมากขึ้น คุณสามารถใช้สูตร MIRR ที่นี่ คุณสามารถรวมปัจจัยบังคับสองประการ ได้แก่ ต้นทุนของเงินทุนและอัตราการลงทุนซ้ำ ค้นหาขั้นตอนด้านล่างเพื่อใช้ MIRR ใน Excel:

• ในเวิร์กชีตปัจจุบัน ให้เพิ่มข้อมูลสองแถวตามที่แสดงในภาพ

การใช้ฟังก์ชัน MIRR ใน Excel

• นี่คือต้นทุนของเงินทุนและอัตราการลงทุนซ้ำ

เรียนรู้วิธีคำนวณ IRR ใน Excel โดยใช้ไวยากรณ์ MIRR

• ในเซลล์ที่เกี่ยวข้อง ให้ป้อนค่าด้วย
• ตอนนี้ ใช้สูตรต่อไปนี้เพื่อรับอัตราผลตอบแทนภายในที่เป็นจริง:

=MIRR(C2:C8,G2,G3)

วิธีคำนวณ IRR ใน Excel: การใช้ Goal Seek Excel

IRR และสูตรที่แก้ไขจะทำการคำนวณซ้ำ 20 ครั้งก่อนที่จะแสดงผล หากคุณต้องการความแม่นยำมากกว่านี้ คุณสามารถใช้ฟังก์ชันค้นหาเป้าหมายของ Excel เพื่อคำนวณ IRR ในวิธีนี้ Excel จะทำการคำนวณซ้ำได้มากถึง 32,000 ครั้งก่อนที่จะสร้างค่าที่ส่งกลับ นี่คือคำแนะนำในการใช้ Goal Seek และคำนวณ IRR ใน Excel:

• ตั้งค่า IRR เป็น 11% หรืออย่างอื่นด้วยตนเอง

การคำนวณ NPV บน Excel

• คำนวณ NPV ต่ำกว่า IRR โดยใช้สูตรนี้:

=NPV(C9,C3:C8)+C2

• ตอนนี้ คลิก แท็บ ข้อมูลจากนั้นเลือกการวิเคราะห์แบบ What-Ifใน กลุ่มคำสั่ง การคาดการณ์ในเมนู Ribbon ของ Excel
• เลือกGoal Seekจากเมนูบริบทที่ปรากฏขึ้น
• ที่นี่เซลล์ Setควรเป็นค่า NPV จากนั้นค่า Toควรเป็น 0

ดำเนินการ Goal Seek เพื่อคำนวณ IRR บน Excel

• ตั้งค่าโดยเปลี่ยนเซลล์เป็นค่า IRR
• คุณเพียงแค่ต้องป้อนการอ้างอิงเซลล์ตามที่แสดงในภาพ

Goal Seek ผลลัพธ์สำหรับการคำนวณ IRR บน Excel

• ตอนนี้ คลิกตกลงเพื่อคำนวณ IRR ผ่าน Goal Seek
• ตามชุดข้อมูลปัจจุบัน NPV จะเป็น 0.00 หาก IRR เท่ากับ 12%

ในวิธี Goal Seek คุณไม่จำเป็นต้องคำนวณ IRR จากกระแสเงินสดและจำนวนเงินลงทุน แต่คุณตั้งค่า IRR ที่คาดไว้ในเซลล์ IRR และใช้เพื่อคำนวณ NPV โดยใช้กระแสเงินสดและจำนวนเงินลงทุน จากนั้น คุณใช้ความสามารถในการคำนวณซ้ำของ Excel เพื่อคาดการณ์เมื่อ NPV กลายเป็น 0.00 ที่ค่า IRR

เคล็ดลับในการคำนวณ IRR บน Excel อย่างถูกต้อง

จดจำเคล็ดลับและข้อเท็จจริงเหล่านี้เมื่อคุณไปคำนวณ IRR บน Excel เพื่อรับเปอร์เซ็นต์ IRR ที่เชื่อถือได้:

• อาร์กิวเมนต์สำหรับส่วนค่าต้องมีรายได้ (กระแสเงินสดเป็นบวก) และค่าใช้จ่าย (กระแสเงินสดเป็นลบ) เพื่อให้ฟังก์ชันทำงานได้อย่างถูกต้อง
• ฟังก์ชัน IRR จะประมวลผลเฉพาะตัวเลขในอาร์กิวเมนต์ Values ​​เท่านั้น โดยจะไม่สนใจองค์ประกอบอื่นๆ เช่น ค่าตรรกะ เซลล์ว่าง และข้อความ
• ต้องมีช่วงเวลากระแสเงินสดสม่ำเสมอ เช่น รายสัปดาห์ รายเดือน รายไตรมาส รายปี ฯลฯ ไม่จำเป็นว่ากระแสเงินสดจะต้องเท่ากันในสกุลเงินดอลลาร์
• ให้แน่ใจว่าคุณเตรียมตารางกระแสเงินสดตามลำดับเวลา ตัวอย่างเช่น หากคุณจ่าย $100 ในแต่ละเดือน ให้จัดเรียงมูลค่ากระแสเงินสดและวันที่จากเดือนมกราคม กุมภาพันธ์ มีนาคม เป็นต้น
• ในการคำนวณ IRR อย่างง่าย ไม่จำเป็นต้องป้อนอาร์กิวเมนต์ Guess
• เมื่อใช้ค่า Guess สูตร IRR จะแสดงค่าที่ใกล้เคียงกับอาร์กิวเมนต์ Guess มากที่สุด หากแสดง #NUM! เกิดข้อผิดพลาด เปลี่ยนค่าอาร์กิวเมนต์ Guess

ข้อ จำกัด ของ IRR ใน Excel คืออะไร?

ก่อนที่คุณจะสามารถใช้ค่า IRR ที่คำนวณได้จาก Excel ในสถานการณ์จริง คุณควรทราบข้อจำกัดโดยธรรมชาติของสูตรอัตราผลตอบแทนภายในใน Excel ด้วย:

• IRR คือการแสดงออกของ ROI เป็นเปอร์เซ็นต์ ไม่พิจารณาค่าสัมบูรณ์ ดังนั้นจึงอาจแสดงเปอร์เซ็นต์ผลตอบแทนที่สูงขึ้นสำหรับโครงการที่มีมูลค่าต่ำ อย่างไรก็ตาม นักลงทุนร่วมทุนและนักลงทุนรายย่อยมองหาโครงการที่มีมูลค่าเงินดอลลาร์สูงกว่า แม้ว่าเปอร์เซ็นต์ ROI จะน้อยกว่าโครงการขนาดเล็กก็ตาม
• สูตร IRR ส่งกลับค่าโดยพิจารณาว่าโครงการจะนำเงินทุนหรือกระแสเงินสดไปลงทุนใหม่ในอัตราเดียวกับ IRR ที่คำนวณได้ สิ่งนี้ไม่สามารถทำได้เสมอไป เนื่องจากอัตราผลตอบแทนมีความผันผวนอย่างมากในตลาดทุน
• หากแผนการลงทุนหรือโครงการเกี่ยวข้องกับกระแสเงินสดที่เป็นบวกและลบสลับกัน คุณอาจได้รับ IRR มากกว่าหนึ่งสำหรับโครงการเดียวกัน

บทสรุป

ตอนนี้คุณรู้วิธีคำนวณ IRR บน Excel โดยใช้ไวยากรณ์ IRR เฉพาะต่างๆ นอกจากนี้ คุณยังได้เรียนรู้วิธีใช้การค้นหาเป้าหมายของ Excel เพื่อคำนวณ IRR ใน Excel หากฉันพลาดวิธีอื่น ๆ อย่าลืมพูดถึงสิ่งนั้นในช่องแสดงความคิดเห็นด้านล่าง

ต่อไป  วิธีใช้สูตร IF-THEN ของ Excel