ตารางตัดชำระหนี้ (amortization schedule) คือตารางที่แสดงว่าแต่ละงวดผ่อนเท่าไร แยกเป็นดอกเบี้ยเท่าไร เงินต้นเท่าไร และเหลือหนี้อีกเท่าไร สร้างได้เองด้วยสูตร PMT, IPMT, PPMT และ SEQUENCE บทความนี้จะพาสร้างทีละขั้นจนได้ตารางครบทุกงวด ใช้กับสินเชื่อบ้าน รถ หรือเงินกู้ทั่วไป
ตั้งค่าตัวแปรหลัก
วางค่าเงินกู้ไว้ในเซลล์แยก เพื่อแก้ทีเดียวแล้วทั้งตารางเปลี่ยนตาม
ภาพจริงจาก Excel (Microsoft 365): ตัวอย่าง Amortization SCHEDULE จากคลิปสอน ข้อมูลในภาพเป็นตัวอย่างสมมติ อาจต่างจากตัวเลขในข้อความเล็กน้อย แต่หลักการเหมือนกัน
ตัวอย่างสูตร =PMT(B2/12, B3, -B1) ได้ผลลัพธ์ 15,211
ใส่เงินต้นเป็นลบใน PMT (-B1) เพื่อให้ยอดผ่อนออกมาเป็นบวก ผ่อนเดือนละประมาณ 15,211 บาท
สร้างคอลัมน์งวดด้วย SEQUENCE
ตัวอย่างสูตร =SEQUENCE(B3) ได้ผลลัพธ์ 1 / 2 / 3 …
SEQUENCE(B3) สร้างเลข 1 ถึง 36 ล้นลงมาเองใน Microsoft 365 ถ้าใช้เวอร์ชันเก่าให้พิมพ์ 1 แล้วลากคัดลอกลง หรือใช้ =A7+1
เติมสูตรดอกเบี้ย เงินต้น และยอดคงเหลือ
ตัวอย่างสูตร =IPMT($B$2/12, A7, $B$3, -$B$1) ได้ผลลัพธ์ 2,500 / 12,711 / 487,289 / 2,436 / 12,775 / 474,514 / 2,373 / 12,839 / 461,675
- ดอกเบี้ย (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รวมดอกเบี้ย/เงินต้นเป็นช่วงได้ในสูตรเดียว
หมายเหตุ: สูตรนี้คิดแบบลดต้นลดดอก ตรงกับสินเชื่อบ้านและสินเชื่อส่วนบุคคลส่วนใหญ่ แต่สินเชื่อเช่าซื้อรถบางแบบคิดดอกเบี้ยแบบคงที่ ผลจะต่างออกไป ควรเทียบกับตารางของสถาบันการเงินด้วย
