ตารางผ่อนเงินกู้ลดต้นลดดอกใน Excel: สูตร PMT, IPMT + โปะเพิ่ม
วิธีทำตารางผ่อนเงินกู้แบบลดต้นลดดอกใน Excel ทีละขั้น คิดค่างวดด้วย PMT แยกดอกเบี้ยกับเงินต้นทุกงวด คำนวณเงินโปะเพิ่ม และบัญชีลูกหนี้สำหรับคนปล่อยกู้ พร้อมไฟล์ฟรี
เงินกู้แบบ ลดต้นลดดอก (Effective Rate) คิดดอกเบี้ยจากเงินต้นที่ยังเหลือในแต่ละงวด งวดแรก ๆ ค่างวดส่วนใหญ่จึงเป็นดอกเบี้ย พอเงินต้นลดลง ดอกเบี้ยก็ลดตาม และเงินที่ไปตัดต้นก็มากขึ้นเรื่อย ๆ บทความนี้จะทำตารางผ่อนแบบนี้ใน Excel ทีละขั้น ใช้ได้ทั้งคนที่กู้และคนที่ปล่อยกู้
ตัวอย่างที่ใช้ในบทความ
- ยอดกู้ ฿500,000 (ช่อง B1)
- ดอกเบี้ย 6% ต่อปี (ช่อง B2)
- ผ่อน 60 งวด หรือ 5 ปี (ช่อง B3)
ขั้นที่ 1: คิดค่างวดด้วย PMT
=ROUND(PMT(B2/12, B3, -B1), 2)ขั้นที่ 2: ทำตารางทีละงวด
ทำหัวตาราง งวด (A) / ยอดยกมา (B) / ดอกเบี้ย (C) / ค่างวด (D) / ตัดเงินต้น (E) / คงเหลือ (F) โดยแถว 6 เป็นงวดแรกและยอดยกมาเท่ากับยอดกู้:
=ROUND(B6*$B$2/12, 2)=D6-C6=B6-E6งวดถัดไปให้ยอดยกมาเท่ากับคงเหลือของงวดก่อน (=F6) แล้วลากสูตรลงไปจนครบ 60 งวด คงเหลือในงวดสุดท้ายจะเป็น 0 พอดี (อาจต่างกันไม่กี่สตางค์จากการปัดเศษ ให้ปรับค่างวดสุดท้ายเป็นยอดยกมา + ดอกเบี้ย)
| งวด | ดอกเบี้ย | ตัดเงินต้น | คงเหลือ |
|---|---|---|---|
| 1 | ฿2,500.00 | ฿7,166.40 | ฿492,833.60 |
| 2 | ฿2,464.17 | ฿7,202.23 | ฿485,631.37 |
| 3 | ฿2,428.16 | ฿7,238.24 | ฿478,393.13 |
ไม่ต้องทำตารางก็หาดอกเบี้ยได้: IPMT, PPMT, CUMIPMT
- =IPMT(B2/12, 1, B3, -B1) ดอกเบี้ยของงวดที่ 1
- =PPMT(B2/12, 1, B3, -B1) เงินต้นที่ตัดในงวดที่ 1
- =CUMIPMT(B2/12, B3, B1, 1, B3, 0) ดอกเบี้ยรวมทั้งสัญญา (ได้ค่าติดลบ ใส่เครื่องหมายลบด้านหน้าให้เป็นบวก) ตัวอย่างนี้ประมาณ ฿79,984
โปะเพิ่มแล้วประหยัดเท่าไร
เพิ่มคอลัมน์ โปะเพิ่ม ระหว่างค่างวดกับตัดเงินต้น แล้วให้เงินโปะไปตัดต้นทั้งหมด:
=MIN(B6, D6-C6+N(G6))ตัวอย่างเดิม ถ้าโปะ ฿50,000 ตอนงวดที่ 12 ดอกเบี้ยรวมลดจากราว ฿79,984 เหลือราว ฿67,331 ประหยัดดอกเบี้ย ฿12,653 และปิดหนี้ได้ใน 54 งวดแทน 60 งวด ยิ่งโปะเร็ว ยิ่งประหยัดมาก เพราะดอกเบี้ยคิดจากเงินต้นที่เหลือ
ระวังดอกเบี้ยคงที่ (Flat Rate)
ข้อเสนอที่เขียนว่าดอกเบี้ยต่ำมาก เช่น 2.5% ต่อปี มักเป็นแบบ คงที่ ที่คิดจากยอดกู้เต็มตลอดสัญญา ถ้าแปลงเป็นลดต้นลดดอกจะสูงเกือบเท่าตัว:
=RATE(B3, -ค่างวด, B1) * 12อ่านเพิ่มเรื่องนี้ใน ตารางผ่อนรถ Excel: ดอกเบี้ยคงที่ vs ลดต้นลดดอก
สำหรับคนปล่อยกู้: ติดตามว่าใครจ่ายแล้ว ใครค้าง
ถ้าให้ญาติ เพื่อน หรือพนักงานยืมเงินหลายราย ตารางผ่อนอย่างเดียวไม่พอ ต้องรู้ว่าแต่ละรายจ่ายมาแล้วกี่งวด ค้างกี่งวด ค้างมากี่วัน และดอกเบี้ยที่ได้รับจริงเท่าไร หลักการคือ:
- ทำ ทะเบียนสัญญา หนึ่งแถวต่อหนึ่งราย: ยอดกู้ ดอกเบี้ย จำนวนงวด วันครบกำหนดงวดแรก
- ทำ บันทึกรับชำระ หนึ่งแถวต่อการจ่ายหนึ่งครั้ง พร้อมรหัสสัญญา
- รวมยอดที่รับแล้วของแต่ละสัญญาด้วย SUMIFS แล้วหารค่างวดเพื่อหาจำนวนงวดที่จ่ายครบ
- นับงวดที่ควรจ่ายถึงวันนี้ด้วย =DATEDIF(วันครบกำหนดงวดแรก, TODAY(), "m") + 1 ถ้าจ่ายน้อยกว่านี้ = ค้างชำระ
ไฟล์สำเร็จรูป
ตารางผ่อนเงินกู้ ลดต้นลดดอก เวอร์ชันฟรี มีทุกอย่างในบทความนี้: ใส่ยอดกู้ ดอกเบี้ย จำนวนงวด แล้วได้ตารางผ่อนครบทุกงวด ใส่เงินโปะเพิ่มได้ทุกงวด และมีหน้าแปลงดอกเบี้ยคงที่เป็นดอกเบี้ยที่แท้จริง
ตารางผ่อนเงินกู้ ลดต้นลดดอก (ฟรี)ดาวน์โหลดฟรีเวอร์ชัน Premium สำหรับคนปล่อยกู้หลายราย: ทะเบียนสัญญา 200 ราย บันทึกรับชำระที่แยกเงินต้น ดอกเบี้ย และค่าปรับให้เอง ตารางผ่อนรายสัญญาบอกงวดที่ค้าง ใบแจ้งยอดส่งให้ผู้กู้ เทียบข้อเสนอเงินกู้ และแดชบอร์ดลูกหนี้ที่ค้างนานที่สุด
คำถามที่พบบ่อย
สูตร Excel คิดค่างวดเงินกู้ลดต้นลดดอกคืออะไร?
=PMT(ดอกเบี้ยต่อปี/12, จำนวนงวด, -ยอดกู้) เช่น กู้ 500,000 บาท ดอกเบี้ย 6% ต่อปี 60 งวด ได้ค่างวดประมาณ 9,666.40 บาท
ลดต้นลดดอกคิดดอกเบี้ยยังไง?
แต่ละงวดคิดดอกเบี้ยจากเงินต้นที่ยังเหลือ × ดอกเบี้ยต่อปี ÷ 12 ส่วนที่เหลือของค่างวดไปตัดเงินต้น ดอกเบี้ยจึงลดลงทุกงวด
โปะเงินกู้ลดต้นลดดอกช่วยได้แค่ไหน?
เงินที่โปะไปตัดเงินต้นทั้งหมด ดอกเบี้ยงวดถัดไปจึงลดลงทันที ตัวอย่างกู้ 500,000 บาท 6% 60 งวด โปะ 50,000 บาทที่งวด 12 ประหยัดดอกเบี้ยราว 12,653 บาท และปิดเร็วขึ้น 6 งวด
ปล่อยกู้ระหว่างบุคคลคิดดอกเบี้ยได้เท่าไร?
กฎหมายไทยกำหนดดอกเบี้ยเงินกู้ระหว่างบุคคลไม่เกิน 15% ต่อปี และการปล่อยกู้เป็นธุรกิจต้องได้รับอนุญาตจากหน่วยงานที่เกี่ยวข้อง
ใช้ใน Google Sheets ได้ไหม?
ได้ PMT, IPMT, PPMT, CUMIPMT, RATE และ EDATE ใช้ใน Google Sheets ได้เหมือน Excel