บันทึกพอร์ตหุ้นไทยด้วย Excel: ต้นทุนเฉลี่ย กำไร ปันผล (ไฟล์ฟรี)
วิธีทำไฟล์ Excel บันทึกพอร์ตหุ้นและกองทุน คิดต้นทุนเฉลี่ยรวมค่าคอม กำไรขาดทุน กำไรที่ขายแล้ว และปันผลหลังหักภาษี 10% พร้อมตัวอย่างและไฟล์ฟรี
แอปโบรกเกอร์บอกกำไรขาดทุนของแต่ละบัญชีได้ก็จริง แต่ถ้าลงทุนหลายโบรกเกอร์ ซื้อกองทุนผ่านอีกแอป หรืออยากรู้ว่าปีนี้ได้ปันผลรวมเท่าไร การรวมทุกอย่างไว้ในไฟล์เดียวจะเห็นภาพชัดกว่ามาก บทความนี้จะพาทำไฟล์บันทึกพอร์ตที่คิดต้นทุนเฉลี่ย กำไรขาดทุน และปันผลได้เอง
หลักคิด: จดทุกการซื้อขาย แล้วให้สูตรสรุปเอง
อย่าแก้ตัวเลขในพอร์ตตรง ๆ ทุกครั้งที่ซื้อเพิ่ม เพราะจะไม่รู้ที่มาของต้นทุน ให้จด ทุกการซื้อและขายเป็น 1 แถว ในหน้ารายการซื้อขาย แล้วให้หน้าพอร์ตคำนวณหน่วยคงเหลือ ต้นทุนเฉลี่ย และกำไรขาดทุนจากรายการเหล่านั้น
| วันที่ | สัญลักษณ์ | ซื้อ/ขาย | จำนวน | ราคา | ค่าคอม | ยอดเงิน |
|---|---|---|---|---|---|---|
| 12 ก.พ. | PTT | ซื้อ | 300 | 34.25 | 23 | 10,298 |
| 15 มิ.ย. | PTT | ซื้อ | 200 | 32.00 | 15 | 6,415 |
คิดต้นทุนเฉลี่ยให้ถูก (รวมค่าคอม)
ต้นทุนเฉลี่ยต่อหน่วย = เงินที่จ่ายซื้อทั้งหมด (รวมค่าธรรมเนียม) ÷ จำนวนหน่วยที่ซื้อทั้งหมด จากตัวอย่าง:
- ซื้อรวม 300 + 200 = 500 หุ้น
- จ่ายรวม 10,298 + 6,415 = 16,713 บาท
- ต้นทุนเฉลี่ย 16,713 ÷ 500 = 33.43 บาทต่อหุ้น
ใน Excel ใช้ SUMIFS รวมเฉพาะรายการซื้อของหุ้นตัวนั้น (สมมติชีตชื่อ "รายการ" คอลัมน์ B สัญลักษณ์, C ซื้อ/ขาย, D จำนวน, G ยอดเงิน และชื่อหุ้นอยู่ในช่อง A2 ของหน้าพอร์ต):
=SUMIFS(รายการ!G:G, รายการ!B:B, A2, รายการ!C:C, "ซื้อ") / SUMIFS(รายการ!D:D, รายการ!B:B, A2, รายการ!C:C, "ซื้อ")ส่วน หน่วยคงเหลือ = หน่วยที่ซื้อรวม − หน่วยที่ขายรวม ใช้ SUMIFS แบบเดียวกันแต่เปลี่ยนเงื่อนไขเป็น "ขาย" แล้วเอามาลบกัน
กำไรขาดทุนที่ยังไม่ขาย
มูลค่าตลาด = หน่วยคงเหลือ × ราคาปัจจุบัน และ กำไร/ขาดทุน = มูลค่าตลาด − (หน่วยคงเหลือ × ต้นทุนเฉลี่ย) เช่น ราคาวันนี้ 33.50 บาท จะได้มูลค่า 16,750 บาท เทียบต้นทุน 16,713 บาท = กำไร 37 บาท (+0.2%)
ราคาปัจจุบันใส่เองได้เมื่ออยากอัปเดต ถ้าใช้ Google Sheets ดึงราคาหุ้นไทยด้วยฟังก์ชัน GOOGLEFINANCE ได้ (ราคาอาจล่าช้าจากตลาดจริง และใช้กับกองทุนรวมไม่ได้ กองทุนต้องใส่ NAV เอง):
=GOOGLEFINANCE("BKK:PTT", "price")กำไรที่ขายแล้ว (Realized)
ตอนขาย กำไรที่ได้จริง = จำนวนที่ขาย × (ราคาขาย − ต้นทุนเฉลี่ย) − ค่าคอมขาย เช่น ขาย CPALL 80 หุ้นที่ 61.25 บาท ต้นทุนเฉลี่ย 58.63 บาท ค่าคอม 11 บาท จะได้ 80 × (61.25 − 58.63) − 11 = 198.60 บาท การขายไม่ทำให้ต้นทุนเฉลี่ยของหุ้นที่เหลือเปลี่ยน
ปันผลและภาษีหัก ณ ที่จ่าย
ปันผลจากหุ้นไทยโดยทั่วไปถูกหักภาษี ณ ที่จ่าย 10% ก่อนเข้าบัญชี จดเป็นยอดก่อนภาษีแล้วให้สูตรคิดยอดสุทธิ:
=ปันผลก่อนภาษี * (1 - 10%)เช่น ถือ PTT 300 หุ้น ปันผลหุ้นละ 1 บาท = 300 บาท หักภาษี 10% ได้รับจริง 270 บาท ตัวเลขที่ช่วยเทียบหุ้นปันผลคือ Yield on cost = ปันผลสุทธิรวม ÷ ต้นทุนของหุ้นตัวนั้น บอกว่าเงินที่ลงไปให้ผลตอบแทนเป็นปันผลกี่เปอร์เซ็นต์
ลงทุนแบบ DCA แล้วพอร์ตเบี้ยว ทำยังไง
ถ้าตั้งสัดส่วนเป้าหมายไว้ เช่น หุ้นไทย 40% กองทุนหุ้นโลก 35% ตราสารหนี้ 15% ETF 10% พอเวลาผ่านไปสัดส่วนจริงจะเบี้ยวจากเป้า วิธีที่ไม่ต้องขายคือ เอาเงิน DCA เดือนถัดไปเติมตัวที่ต่ำกว่าเป้ามากที่สุดก่อน พอร์ตจะค่อย ๆ กลับมาใกล้เป้าเอง
ไฟล์บันทึกพอร์ตสำเร็จรูป
พอร์ตลงทุนเวอร์ชันฟรีมีหน้ารายการซื้อขาย หน้าพอร์ตที่คิดหน่วยคงเหลือ ต้นทุนเฉลี่ย มูลค่าตลาด และกำไรขาดทุนรายตัวให้เอง ใช้ได้ทั้ง Excel และ Google Sheets
พอร์ตลงทุน หุ้นไทย / กองทุน (ฟรี)ดาวน์โหลดฟรีเวอร์ชัน Premium เพิ่มกำไรที่ขายแล้ว บันทึกปันผลพร้อมภาษีหัก ณ ที่จ่ายและ yield on cost แผน DCA ที่บอกว่าเงินเดือนนี้ควรเติมตัวไหน และแดชบอร์ดผลตอบแทนรวมกับสัดส่วนพอร์ต
คำถามที่พบบ่อย
ต้นทุนเฉลี่ยหุ้นควรรวมค่าคอมไหม?
ควรรวม เพราะค่าคอมและค่าธรรมเนียมเป็นเงินที่จ่ายจริงเพื่อได้หุ้นมา ต้นทุนเฉลี่ย = เงินที่จ่ายซื้อทั้งหมดรวมค่าธรรมเนียม ÷ จำนวนหุ้นที่ซื้อทั้งหมด
ขายหุ้นบางส่วนแล้วต้นทุนเฉลี่ยเปลี่ยนไหม?
ไม่เปลี่ยน ต้นทุนเฉลี่ยของหุ้นที่เหลือยังเท่าเดิม สิ่งที่เกิดขึ้นคือมีกำไรหรือขาดทุนที่ขายแล้ว (realized) จากส่วนที่ขายไป
ดึงราคาหุ้นไทยอัตโนมัติใน Google Sheets ได้ไหม?
ได้ด้วย =GOOGLEFINANCE("BKK:สัญลักษณ์", "price") แต่ราคาอาจล่าช้าจากตลาดจริง และใช้ดึง NAV กองทุนรวมไม่ได้ ใน Excel ให้ใส่ราคาเองเมื่อต้องการอัปเดต
ปันผลหุ้นไทยถูกหักภาษีเท่าไร?
โดยทั่วไปถูกหักภาษี ณ ที่จ่าย 10% ก่อนเข้าบัญชี ส่วนกองทุนรวมบางประเภทไม่มีการหัก ให้ดูจากหนังสือรับรองการหักภาษีที่ได้รับ