คิดดอกเบี้ยเงินกู้รายวันใน Excel: สูตร + กระดาษทำการแบบผู้สอบบัญชี
วิธีคิดดอกเบี้ยเงินกู้ตามจำนวนวันจริงใน Excel สูตรนับวัน แยกดอกเบี้ยกับเงินต้นเมื่อจ่ายคืน และทำกระดาษทำการเงินกู้ยืมบุคคลที่เกี่ยวข้องกันพร้อมกระทบยอดดอกเบี้ยทั้งปี
เงินกู้ระหว่างบริษัทกับกรรมการ ผู้ถือหุ้น หรือบริษัทในเครือ มักจ่ายคืนไม่ตรงวันเป๊ะทุกเดือน ยอดก็ไม่เท่ากันทุกครั้ง การคิดดอกเบี้ยแบบค่างวดคงที่จึงใช้ไม่ได้ วิธีที่ผู้สอบบัญชีนิยมใช้คือ คิดดอกเบี้ยตามจำนวนวันจริง จากเงินต้นที่คงเหลือในแต่ละช่วง บทความนี้จะทำกระดาษทำการแบบนี้ใน Excel ทีละขั้น
สูตรดอกเบี้ยรายวัน
ดอกเบี้ย = เงินต้นคงเหลือ × อัตราดอกเบี้ยต่อปี × จำนวนวัน ÷ 365เพราะคิดจากเงินต้นคงเหลือ ทุกครั้งที่จ่ายคืนเงินต้นลดลง ดอกเบี้ยช่วงถัดไปก็ลดลงตาม คือเป็นแบบ ลดต้นลดดอก แต่ละเอียดถึงระดับวัน
หน้าตากระดาษทำการ
| คอลัมน์ | หัวข้อ | กรอก / สูตร |
|---|---|---|
| A–D | ว.ด.ป. · เลขที่ใบสำคัญ · ใบเสร็จรับเงิน · รายการ | กรอก |
| E | จำนวนเงินที่จ่าย | กรอก |
| F | เดบิต (กู้เพิ่ม) | กรอก |
| G | เครดิต (ตัดเงินต้น) | =E9-M8 |
| H | คงเหลือ | =H8+F9-G9 |
| I–J | ระยะเวลาเริ่มต้น – สิ้นสุด | กรอก หรือ =J8+1 และ =EOMONTH(I9,0) |
| K | จำนวนวัน | =J9-I9+1 |
| L | อัตราดอกเบี้ย | กรอก |
| M | ดอกเบี้ย | =H9*L9*K9/365 |
หลักสำคัญคือ ดอกเบี้ยของช่วงก่อนจะถูกหักจากเงินที่จ่ายคืนครั้งถัดไปก่อน ที่เหลือจึงไปตัดเงินต้น เครดิตในแถว 9 จึงเป็น =E9-M8 (เงินที่จ่าย − ดอกเบี้ยของแถวก่อน)
ตัวอย่าง: กู้ 1,000,000 บาท จ่ายคืนเดือนละ 80,000
| จ่ายคืน | เครดิต | คงเหลือ | ช่วงดอกเบี้ย | วัน | ดอกเบี้ย |
|---|---|---|---|---|---|
| ยอดยกมา | - | 1,000,000.00 | 01/01/66 – 31/01/66 | 31 | 5,520.55 |
| 31/01/66 | 74,479.45 | 925,520.55 | 01/02/66 – 28/02/66 | 28 | 4,614.92 |
| 28/02/66 | 75,385.08 | 850,135.47 | 01/03/66 – 31/03/66 | 31 | 4,693.21 |
| 31/03/66 | 75,306.79 | 774,828.69 | 01/04/66 – 30/04/66 | 30 | 4,139.50 |
| … | … | … | … | … | … |
| 31/12/66 | 79,134.14 | 77,709.51 |
งวดแรก: จ่าย 80,000 หักดอกเบี้ยเดือน ม.ค. 5,520.55 ก่อน เหลือ 74,479.45 ไปตัดเงินต้น คงเหลือ 925,520.55 แล้วเดือน ก.พ. มี 28 วัน ดอกเบี้ยจึงเหลือ 4,614.92
จ่ายไม่ตรงสิ้นเดือน
ข้อดีของการคิดรายวันคือรองรับการจ่ายวันไหนก็ได้ ถ้าผู้กู้จ่ายวันที่ 15 มี.ค. ให้แก้วันสิ้นสุดของช่วงก่อนหน้าเป็น 15/03/66 แล้วช่วงถัดไปเริ่ม 16/03/66:
- ช่วง 01/03/66 – 15/03/66 = 15 วัน → 850,135.47 × 6.5% × 15 ÷ 365 = 2,270.91
- ช่วงถัดไปเริ่ม 16/03/66 คิดจากเงินต้นใหม่หลังจ่ายคืน
นับวันแบบไหนดี
| วิธี | สูตรจำนวนวัน | ใช้เมื่อ |
|---|---|---|
| นับรวมวันแรกและวันสุดท้าย | =สิ้นสุด-เริ่มต้น+1 | ช่วงต่อกันแบบ 1–31, 1–28 (แบบกระดาษทำการ) |
| นับจากวันที่จ่ายถึงวันที่จ่าย | =วันที่จ่ายครั้งนี้-วันที่จ่ายครั้งก่อน | บันทึกเป็นรายการตามวันที่จริง |
ทั้งสองแบบได้ผลเท่ากันถ้าใช้ให้สม่ำเสมอ สิ่งที่ต้องระวังคืออย่านับวันซ้ำ (เช่น ช่วง 1–31 ม.ค. แล้วต่อด้วย 31 ม.ค.–28 ก.พ.) เพราะจะคิดดอกเบี้ยวันที่ 31 สองครั้ง
กระทบยอดดอกเบี้ยกับบัญชีตอนปิดปี
- รวมคอลัมน์ดอกเบี้ยทั้งปี =SUM(M8:M43)
- ใส่ดอกเบี้ยที่บันทึกบัญชีไว้ ณ วันสิ้นรอบบัญชี
- ผลต่าง = ดอกเบี้ยตามการคำนวณ − ดอกเบี้ยตามบัญชี ถ้าไม่เป็นศูนย์ ให้ตรวจว่าตั้งดอกเบี้ยค้างรับ/ค้างจ่ายครบหรือยัง
ถ้าการจ่ายคืนครั้งสุดท้ายของปีไม่ตรงวันสิ้นรอบบัญชี ดอกเบี้ยตั้งแต่วันถัดจากการจ่ายครั้งสุดท้ายถึงวันสิ้นปีคือ ดอกเบี้ยค้างรับ (หรือค้างจ่าย) ที่ต้องบันทึกในปีนั้น
วันที่แบบ พ.ศ. ใน Excel
ถ้าพิมพ์ 31/1/66 Excel จะเข้าใจเป็นปี ค.ศ. 1966 ไม่ใช่ พ.ศ. 2566 ให้พิมพ์วันที่เป็น ค.ศ. (31/1/2023) แล้วตั้งรูปแบบเซลล์ให้แสดงเป็น พ.ศ.:
[$-th-TH,107]dd/mm/yyไฟล์สำเร็จรูป
กระดาษทำการเงินกู้ ดอกเบี้ยรายวัน มีทุกสูตรในบทความนี้ หัวกระดาษและคอลัมน์ครบแบบกระดาษทำการ ระยะเวลาเติมให้อัตโนมัติเป็นรายเดือน แก้วันได้เมื่อจ่ายไม่ตรงสิ้นเดือน รองรับกู้เพิ่มและเปลี่ยนอัตรากลางปี พร้อมบรรทัดดอกเบี้ยตามบัญชีและผลต่าง วันที่แสดงเป็น พ.ศ. พิมพ์ส่งได้ทันที
กระดาษทำการเงินกู้ ดอกเบี้ยรายวัน฿199.00ถ้าเป็นเงินกู้ที่ผ่อนค่างวดเท่ากันทุกเดือน ใช้ ตารางผ่อนเงินกู้ ลดต้นลดดอก เวอร์ชันฟรี หรืออ่าน วิธีทำตารางผ่อนด้วยสูตร PMT
คำถามที่พบบ่อย
สูตรคิดดอกเบี้ยรายวันใน Excel คืออะไร?
=เงินต้นคงเหลือ*อัตราดอกเบี้ยต่อปี*จำนวนวัน/365 เช่น 1,000,000 × 6.5% × 31 ÷ 365 = 5,520.55 บาท
นับจำนวนวันระหว่างสองวันที่ใน Excel ยังไง?
ลบวันที่ได้ตรง ๆ =วันสิ้นสุด-วันเริ่มต้น ถ้าต้องการนับรวมทั้งวันแรกและวันสุดท้ายให้ +1
จ่ายคืนเงินกู้แล้วแยกดอกเบี้ยกับเงินต้นยังไง?
หักดอกเบี้ยที่ค้างถึงวันจ่ายก่อน ส่วนที่เหลือจึงตัดเงินต้น เครดิตเงินต้น = จำนวนเงินที่จ่าย − ดอกเบี้ยของช่วงก่อน
คิดดอกเบี้ยใช้ 365 หรือ 366 วัน?
แล้วแต่สัญญา หลายสัญญาใช้ 365 วันตลอด บางสัญญาใช้ 366 วันในปีอธิกสุรทิน ควรระบุไว้ในสัญญาให้ชัด
Excel แสดงวันที่เป็น พ.ศ. ได้ไหม?
ได้ ใช้ Custom format [$-th-TH,107]dd/mm/yy แต่ต้องพิมพ์วันที่เป็นปี ค.ศ. ไม่อย่างนั้น Excel จะเข้าใจผิดเป็นปี ค.ศ. อื่น