XIRR และ XNPV ใน Excel คิดผลตอบแทนเมื่อเงินเข้า-ออกไม่เป็นจังหวะ

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

รูปแบบของสูตร

=XNPV(อัตราคิดลดต่อปี, ช่วงกระแสเงิน, ช่วงวันที่) → มูลค่าปัจจุบันสุทธิ
=XIRR(ช่วงกระแสเงิน, ช่วงวันที่, [ค่าคาดเดา]) → อัตราผลตอบแทนต่อปีที่ทำให้ XNPV เป็นศูนย์

กระแสเงินต้องมีทั้งค่าลบ (เงินที่จ่ายออก/ลงทุน) และค่าบวก (เงินที่ได้รับ) อย่างน้อยอย่างละหนึ่ง

ตัวอย่างที่ 1: ผลตอบแทนกองทุนที่ทยอยซื้อ

ภาพจริงจาก Excel (Microsoft 365): ตัวอย่าง XIRR XNPV จากคลิปสอน ข้อมูลในภาพเป็นตัวอย่างสมมติ อาจต่างจากตัวเลขในข้อความเล็กน้อย แต่หลักการเหมือนกัน

ตัวอย่างสูตร =XIRR(B2:B4, A2:A4) ได้ผลลัพธ์ 18.6%

ลงทุน 100,000 บาทต้นปี เติมอีก 50,000 กลางปี แล้วขายได้ 190,000 บาทในปีถัดมา XIRR คำนวณให้ว่าผลตอบแทนเทียบเท่าประมาณ 18.6% ต่อปี โดยคิดจากระยะเวลาที่เงินแต่ละก้อนอยู่ในตลาดจริง

ตัวอย่างที่ 2: XNPV ตัดสินใจว่าโครงการคุ้มไหม

ตัวอย่างสูตร =XNPV(8%, B2:B5, A2:A5) ได้ผลลัพธ์ +50,700

ถ้า XNPV ที่อัตราคิดลดเป้าหมาย (8% ต่อปี) ออกมาเป็นบวก แปลว่าโครงการให้ผลตอบแทนสูงกว่า 8% คุ้มที่จะทำ ถ้าเป็นลบแปลว่าไม่ผ่านเกณฑ์

XIRR ต่างจาก IRR อย่างไร

ในทางปฏิบัติ XIRR แม่นยำกว่าเกือบทุกกรณีของการลงทุนจริง จึงเป็นตัวที่กองทุนและโบรกเกอร์ใช้รายงานผลตอบแทน

ข้อควรรู้

• ได้ #NUM! — มักเกิดเมื่อไม่มีการสลับเครื่องหมาย (ไม่มีทั้งบวกและลบ) หรือสูตรหาคำตอบไม่เจอ ให้ใส่ค่าคาดเดา เช่น =XIRR(B2:B4, A2:A4, 0.1)
• วันที่ต้องเป็นวันที่จริง — ถ้าเป็นข้อความหรือ พ.ศ. ต้องแปลงก่อน ตาม บทความแปลงวันที่
• ผลลัพธ์เป็นอัตราต่อปี — XIRR ให้ตัวเลขต่อปีเสมอ ไม่ว่าช่วงเวลาจริงจะสั้นหรือยาว
• วันแรกในลิสต์คือฐานเวลา — ลำดับวันที่ไม่จำเป็นต้องเรียง แต่ Excel ใช้วันแรกที่พบเป็นจุดเริ่มคิดลด