สร้างตารางผ่อนชำระหนี้ (Amortization) ใน Excel ทีละขั้น

ตารางตัดชำระหนี้ (amortization schedule) คือตารางที่แสดงว่าแต่ละงวดผ่อนเท่าไร แยกเป็นดอกเบี้ยเท่าไร เงินต้นเท่าไร และเหลือหนี้อีกเท่าไร สร้างได้เองด้วยสูตร PMT, IPMT, PPMT และ SEQUENCE บทความนี้จะพาสร้างทีละขั้นจนได้ตารางครบทุกงวด ใช้กับสินเชื่อบ้าน รถ หรือเงินกู้ทั่วไป

ตั้งค่าตัวแปรหลัก

วางค่าเงินกู้ไว้ในเซลล์แยก เพื่อแก้ทีเดียวแล้วทั้งตารางเปลี่ยนตาม

ใส่เงินต้นเป็นลบใน PMT (-B1) เพื่อให้ยอดผ่อนออกมาเป็นบวก ผ่อนเดือนละประมาณ 15,211 บาท

สร้างคอลัมน์งวดด้วย SEQUENCE

SEQUENCE(B3) สร้างเลข 1 ถึง 36 ล้นลงมาเองใน Microsoft 365 ถ้าใช้เวอร์ชันเก่าให้พิมพ์ 1 แล้วลากคัดลอกลง หรือใช้ =A7+1

เติมสูตรดอกเบี้ย เงินต้น และยอดคงเหลือ

• ดอกเบี้ย (B7): =IPMT($B$2/12, A7, $B$3, -$B$1)
• เงินต้น (C7): =PPMT($B$2/12, A7, $B$3, -$B$1)
• คงเหลือ (D7): งวดแรก =$B$1-C7 งวดถัดไป =D7-C8

สังเกตการล็อก $B$2, $B$3, $B$1 ให้ชี้ค่าคงที่ ส่วน A7 (เลขงวด) ปล่อยให้ขยับตามแถว คัดลอกทั้งแถวลงไปครบ 36 งวดได้เลย

ตรวจความถูกต้อง

• ดอกเบี้ย + เงินต้น ของทุกงวด = ยอดผ่อนต่องวด (2,500 + 12,711 = 15,211)
• ยอดคงเหลือของงวดสุดท้ายต้องเป็น 0 (หรือใกล้ 0 มากเพราะการปัดเศษ)
• ดอกเบี้ยรวมทั้งตาราง เช็คเร็วด้วย =CUMIPMT(B2/12, B3, B1, 1, B3, 0) ในที่นี้ได้ประมาณ −47,612 บาท

เพิ่มลูกเล่น

• ใส่วันที่ครบกำหนดแต่ละงวด — =EDATE($B$5, A7-1) เมื่อ B5 คือวันเริ่มผ่อน
• จำลองการโปะ — เพิ่มคอลัมน์ “จ่ายเพิ่ม” แล้วให้คอลัมน์คงเหลือหักออกด้วย จะเห็นว่าปิดหนี้เร็วขึ้นกี่งวด
• หาจำนวนงวดจากยอดผ่อนที่ไหวจ่าย — ใช้ NPER ก่อนแล้วค่อยมาสร้างตาราง
• สรุปยอดช่วงงวด — CUMIPMT / CUMPRINC รวมดอกเบี้ย/เงินต้นเป็นช่วงได้ในสูตรเดียว

หมายเหตุ: สูตรนี้คิดแบบลดต้นลดดอก ตรงกับสินเชื่อบ้านและสินเชื่อส่วนบุคคลส่วนใหญ่ แต่สินเชื่อเช่าซื้อรถบางแบบคิดดอกเบี้ยแบบคงที่ ผลจะต่างออกไป ควรเทียบกับตารางของสถาบันการเงินด้วย