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

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

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

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

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

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

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

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

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

อ่านเพิ่มเติม:เทมเพลตงบประมาณ Excel ที่ดีที่สุด

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

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

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

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

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

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

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

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

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

อ่านเพิ่มเติม:วิธีใช้สูตรการลบ Excel

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

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

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

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

=IRR(C2:C8)

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

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

=IRR(C2:C8,30%)

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

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

• เดอะ เดา อาร์กิวเมนต์ควรเป็น ROI การลงทุนที่คุณคาดหวัง

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

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

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

=XIRR(C2:C8,D2:D8)

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

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

• เหล่านี้เป็น ต้นทุนของเงินทุน และ อัตราการลงทุนซ้ำ.

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

=MIRR(C2:C8,G2,G3)

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

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

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

=NPV(C9,C3:C8)+C2

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

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

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

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

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

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

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

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

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

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