การสร้างกำหนดการตัดจำหน่ายเงินกู้ใน Excel อาจเป็นทักษะที่มีค่า ซึ่งช่วยให้คุณเห็นภาพและจัดการการชำระคืนเงินกู้ได้อย่างมีประสิทธิภาพ ตารางการตัดจำหน่ายคือตารางที่แสดงรายละเอียดการชำระเงินเป็นงวดสำหรับสินเชื่อตัดจำหน่าย (โดยทั่วไปคือสินเชื่อจำนองหรือสินเชื่อรถยนต์) โดยแบ่งการชำระเงินแต่ละครั้งออกเป็นดอกเบี้ยและองค์ประกอบหลัก โดยแสดงยอดคงเหลือหลังจากการชำระเงินแต่ละครั้ง มาดูคำแนะนำทีละขั้นตอนเพื่อสร้างกำหนดการดังกล่าวใน Excel

กำหนดการตัดจำหน่ายคืออะไร?

สร้างกำหนดการตัดจำหน่ายใน Excel

สร้างกำหนดการตัดจำหน่ายสำหรับจำนวนงวดที่เปลี่ยนแปลงได้

สร้างตารางการตัดจำหน่ายพร้อมการชำระเงินพิเศษ

สร้างกำหนดการตัดจำหน่าย (พร้อมการชำระเงินพิเศษ) โดยใช้เทมเพลต Excel

กำหนดการตัดจำหน่ายคืออะไร?

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

หากต้องการสร้างกำหนดการตัดจำหน่ายเงินกู้ใน Excel ฟังก์ชัน PMT, PPMT และ IPMT ในตัวมีความสำคัญอย่างยิ่ง มาทำความเข้าใจว่าแต่ละฟังก์ชันทำหน้าที่อะไร:

• ฟังก์ชันพีเอ็มที: ฟังก์ชันนี้ใช้ในการคำนวณการชำระเงินทั้งหมดต่องวดสำหรับเงินกู้โดยพิจารณาจากการชำระเงินคงที่และอัตราดอกเบี้ยคงที่
• ฟังก์ชัน IPMT: ฟังก์ชันนี้จะคำนวณส่วนดอกเบี้ยของการชำระเงินในช่วงเวลาที่กำหนด
• ฟังก์ชัน PPMT: ฟังก์ชันนี้ใช้ในการคำนวณสัดส่วนเงินต้นของการชำระเงินในช่วงเวลาที่กำหนด

ด้วยการใช้ฟังก์ชันเหล่านี้ใน Excel คุณสามารถสร้างกำหนดการตัดจำหน่ายโดยละเอียดที่แสดงดอกเบี้ยและองค์ประกอบหลักในการชำระเงินแต่ละครั้ง พร้อมด้วยยอดคงเหลือของเงินกู้หลังการชำระเงินแต่ละครั้ง

สร้างกำหนดการตัดจำหน่ายใน Excel

ในส่วนนี้ เราจะแนะนำวิธีการที่แตกต่างกันสองวิธีในการสร้างกำหนดการตัดจำหน่ายใน Excel วิธีการเหล่านี้ตอบสนองความต้องการของผู้ใช้และระดับทักษะที่แตกต่างกัน ทำให้มั่นใจได้ว่าทุกคน โดยไม่คำนึงถึงความเชี่ยวชาญในการใช้ Excel จะสามารถสร้างกำหนดการตัดจำหน่ายเงินกู้โดยละเอียดและแม่นยำได้สำเร็จ

สูตรช่วยให้เข้าใจการคำนวณพื้นฐานได้ลึกซึ้งยิ่งขึ้น และให้ความยืดหยุ่นในการปรับแต่งกำหนดการตามความต้องการเฉพาะ แนวทางนี้เหมาะสำหรับผู้ที่ต้องการสัมผัสประสบการณ์ตรงและมีข้อมูลเชิงลึกที่ชัดเจนเกี่ยวกับวิธีการชำระเงินแต่ละครั้งโดยแบ่งออกเป็นองค์ประกอบเงินต้นและดอกเบี้ย ตอนนี้เรามาแจกแจงกระบวนการสร้างกำหนดการตัดจำหน่ายใน Excel ทีละขั้นตอน:

⭐️ ขั้นตอนที่ 1: ตั้งค่าตารางข้อมูลสินเชื่อและค่าตัดจำหน่าย

1. ป้อนข้อมูลสินเชื่อที่เกี่ยวข้อง เช่น อัตราดอกเบี้ยรายปี ระยะเวลาเงินกู้เป็นปี จำนวนการชำระเงินต่อปี และจำนวนเงินกู้ลงในเซลล์ดังภาพต่อไปนี้:
   
2. จากนั้น สร้างตารางค่าตัดจำหน่ายใน Excel โดยมีป้ายกำกับที่ระบุ เช่น ระยะเวลา การชำระเงิน ดอกเบี้ย เงินต้น ยอดคงเหลือในเซลล์ A7:E7
3. ในคอลัมน์งวด ให้ป้อนหมายเลขงวด สำหรับตัวอย่างนี้ จำนวนการชำระเงินทั้งหมดคือ 24 เดือน (2 ปี) ดังนั้น คุณจะต้องป้อนตัวเลข 1 ถึง 24 ลงในคอลัมน์งวด ดูภาพหน้าจอ:
   
4. เมื่อคุณสร้างตารางด้วยป้ายกำกับและหมายเลขงวดแล้ว คุณสามารถป้อนสูตรและค่าสำหรับคอลัมน์การชำระเงิน ดอกเบี้ย เงินต้น และยอดคงเหลือตามข้อมูลเฉพาะของสินเชื่อของคุณได้

⭐️ ขั้นตอนที่ 2: คำนวณจำนวนเงินที่ชำระทั้งหมดโดยใช้ฟังก์ชัน PMT

ไวยากรณ์ของ PMT คือ:

ป้อนสูตรต่อไปนี้ลงในเซลล์ B7 จากนั้นลากจุดจับเติมลงเพื่อเติมสูตรนี้ลงในเซลล์อื่น และคุณจะเห็นจำนวนเงินที่ชำระคงที่สำหรับทุกงวด ดูภาพหน้าจอ:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ ขั้นตอนที่ 3: คำนวณดอกเบี้ยโดยใช้ฟังก์ชัน IPMT

ในขั้นตอนนี้ คุณจะคำนวณดอกเบี้ยสำหรับงวดการชำระเงินแต่ละงวดโดยใช้ฟังก์ชัน IPMT ของ Excel

ใส่สูตรต่อไปนี้ลงในเซลล์ C7 จากนั้นลากจุดจับเติมลงในคอลัมน์เพื่อเติมสูตรนี้และรับดอกเบี้ยสำหรับแต่ละงวด

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ ขั้นตอนที่ 4: คำนวณเงินต้นโดยใช้ฟังก์ชัน PPMT

หลังจากคำนวณดอกเบี้ยสำหรับแต่ละงวดแล้ว ขั้นตอนถัดไปในการสร้างกำหนดการตัดจำหน่ายคือการคำนวณสัดส่วนเงินต้นของการชำระเงินแต่ละครั้ง ซึ่งทำได้โดยใช้ฟังก์ชัน PPMT ซึ่งออกแบบมาเพื่อกำหนดส่วนหลักของการชำระเงินในช่วงเวลาหนึ่งๆ โดยพิจารณาจากการชำระเงินคงที่และอัตราดอกเบี้ยคงที่

ไวยากรณ์ของ IPMT คือ:

ไวยากรณ์และพารามิเตอร์สำหรับสูตร PPMT เหมือนกับที่ใช้ในสูตร IPMT ที่กล่าวถึงก่อนหน้านี้

ใส่สูตรต่อไปนี้ลงในเซลล์ D7 จากนั้นลากจุดจับเติมลงในคอลัมน์เพื่อเติมหลักสำหรับแต่ละงวด ดูภาพหน้าจอ:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ ขั้นตอนที่ 5: คำนวณยอดคงเหลือ

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

1. ในเซลล์แรกของคอลัมน์ยอดดุลของคุณ – E7 ให้ป้อนสูตรต่อไปนี้ ซึ่งหมายความว่ายอดคงเหลือจะเป็นจำนวนเงินกู้เดิมลบด้วยส่วนของเงินต้นของการชำระเงินครั้งแรก:

   =B4-D7

   
2. สำหรับงวดที่สองและงวดต่อๆ ไป ให้คำนวณยอดคงเหลือโดยการลบการชำระเงินต้นของงวดปัจจุบันออกจากยอดคงเหลือของงวดก่อนหน้า โปรดใช้สูตรต่อไปนี้ในเซลล์ E8:

   =E7-D8

    หมายเหตุ: การอ้างอิงไปยังเซลล์สมดุลควรสัมพันธ์กัน ดังนั้นจึงอัปเดตเมื่อคุณลากสูตรลง
   
3. จากนั้นลากจุดจับเติมลงไปที่คอลัมน์ ดังที่คุณเห็น แต่ละเซลล์จะปรับโดยอัตโนมัติเพื่อคำนวณยอดคงเหลือตามการชำระเงินต้นที่อัปเดต
   

⭐️ ขั้นตอนที่ 6 : ทำสรุปสินเชื่อ

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

● วิธีคำนวณการชำระเงินทั้งหมด:

=SUM(B7:B30)

● วิธีคำนวณดอกเบี้ยรวม:

=SUM(C7:C30)

⭐️ ผลลัพธ์:

ขณะนี้ ตารางการตัดจำหน่ายเงินกู้ที่เรียบง่ายแต่ครอบคลุมได้ถูกสร้างขึ้นเรียบร้อยแล้ว ดูภาพหน้าจอ:

สร้างกำหนดการตัดจำหน่ายสำหรับจำนวนงวดที่เปลี่ยนแปลงได้

ในตัวอย่างก่อนหน้านี้ เราสร้างกำหนดการชำระคืนเงินกู้สำหรับจำนวนการชำระเงินคงที่ แนวทางนี้เหมาะสำหรับการจัดการสินเชื่อหรือการจำนองเฉพาะเจาะจงโดยที่เงื่อนไขไม่เปลี่ยนแปลง

แต่หากคุณต้องการสร้างกำหนดการตัดจำหน่ายที่ยืดหยุ่น ซึ่งสามารถนำมาใช้ซ้ำกับเงินกู้ที่มีระยะเวลาต่างกันได้ ซึ่งช่วยให้คุณสามารถแก้ไขจำนวนการชำระเงินตามที่จำเป็นสำหรับสถานการณ์เงินกู้ที่แตกต่างกัน คุณจะต้องปฏิบัติตามวิธีการที่มีรายละเอียดมากขึ้น

⭐️ ขั้นตอนที่ 1: ตั้งค่าตารางข้อมูลสินเชื่อและค่าตัดจำหน่าย

1. ป้อนข้อมูลสินเชื่อที่เกี่ยวข้อง เช่น อัตราดอกเบี้ยรายปี ระยะเวลาเงินกู้เป็นปี จำนวนการชำระเงินต่อปี และจำนวนเงินกู้ลงในเซลล์ดังภาพต่อไปนี้:
   
2. จากนั้น สร้างตารางค่าตัดจำหน่ายใน Excel โดยมีป้ายกำกับที่ระบุ เช่น ระยะเวลา การชำระเงิน ดอกเบี้ย เงินต้น ยอดคงเหลือในเซลล์ A7:E7
3. ในคอลัมน์งวด ให้ป้อนจำนวนการชำระเงินสูงสุดที่คุณอาจพิจารณาสำหรับเงินกู้ใดๆ เช่น กรอกตัวเลขตั้งแต่ 1 ถึง 360 ซึ่งอาจครอบคลุมเงินกู้มาตรฐาน 30 ปีหากคุณชำระเงินรายเดือน
   

⭐️ ขั้นตอนที่ 2: แก้ไขสูตรการชำระเงิน ดอกเบี้ย และเงินต้นด้วยฟังก์ชัน IF

ป้อนสูตรต่อไปนี้ในเซลล์ที่เกี่ยวข้อง จากนั้นลากจุดจับเติมเพื่อขยายสูตรเหล่านี้ลงไปตามจำนวนงวดการชำระเงินสูงสุดที่คุณตั้งไว้

● สูตรการชำระเงิน:

โดยปกติ คุณจะใช้ฟังก์ชัน PMT เพื่อคำนวณการชำระเงิน หากต้องการรวมคำสั่ง IF สูตรไวยากรณ์คือ:

ดังนั้นสูตรจึงเป็นดังนี้:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● สูตรดอกเบี้ย:

สูตรไวยากรณ์คือ:

ดังนั้นสูตรจึงเป็นดังนี้:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● สูตรหลักการ:

สูตรไวยากรณ์คือ:

ดังนั้นสูตรจึงเป็นดังนี้:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ ขั้นตอนที่ 3: ปรับสูตรยอดคงเหลือ

สำหรับยอดคงเหลือ คุณอาจลบเงินต้นออกจากยอดคงเหลือก่อนหน้า ด้วยคำสั่ง IF ให้แก้ไขเป็น:

● เซลล์สมดุลแรก:(E7)

=B4-D7

● เซลล์สมดุลที่สอง:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ ขั้นตอนที่ 4 : ทำสรุปสินเชื่อ

หลังจากที่คุณได้ตั้งค่ากำหนดการตัดจำหน่ายด้วยสูตรที่ปรับเปลี่ยนแล้ว ขั้นตอนต่อไปคือการสร้างสรุปสินเชื่อ

● วิธีคำนวณการชำระเงินทั้งหมด:

=SUM(B7:B366)

● วิธีคำนวณดอกเบี้ยรวม:

=SUM(C7:C366)

⭐️ ผลลัพธ์:

ขณะนี้ คุณมีกำหนดการตัดจำหน่ายที่ครอบคลุมและเปลี่ยนแปลงตลอดเวลาใน Excel พร้อมด้วยสรุปสินเชื่อโดยละเอียด เมื่อใดก็ตามที่คุณปรับระยะเวลาการชำระเงิน กำหนดการตัดจำหน่ายทั้งหมดจะอัปเดตโดยอัตโนมัติเพื่อแสดงการเปลี่ยนแปลงเหล่านี้ ดูการสาธิตด้านล่าง:

สร้างตารางการตัดจำหน่ายพร้อมการชำระเงินพิเศษ

ด้วยการชำระเงินเพิ่มเติมนอกเหนือจากที่กำหนดไว้ จึงสามารถชำระคืนเงินกู้ได้รวดเร็วยิ่งขึ้น ตารางการตัดจำหน่ายที่รวมการชำระเงินพิเศษเมื่อสร้างขึ้นใน Excel แสดงให้เห็นว่าการชำระเงินเพิ่มเติมเหล่านี้สามารถเร่งการชำระคืนเงินกู้และลดดอกเบี้ยที่จ่ายทั้งหมดได้อย่างไร ต่อไปนี้เป็นวิธีการตั้งค่า:

⭐️ ขั้นตอนที่ 1: ตั้งค่าตารางข้อมูลสินเชื่อและค่าตัดจำหน่าย

1. ป้อนข้อมูลสินเชื่อที่เกี่ยวข้อง เช่น อัตราดอกเบี้ยรายปี ระยะเวลาเงินกู้เป็นปี จำนวนการชำระเงินต่อปี จำนวนเงินกู้ และการชำระเงินพิเศษลงในเซลล์ตามภาพต่อไปนี้:
   
2. จากนั้น คำนวณการชำระเงินที่กำหนดเวลาไว้
   นอกจากเซลล์อินพุตแล้ว ยังจำเป็นต้องมีเซลล์ที่กำหนดไว้ล่วงหน้าอีกเซลล์หนึ่งสำหรับการคำนวณครั้งต่อไปของเรา - จำนวนเงินที่ต้องชำระ นี่คือจำนวนเงินที่ชำระตามปกติของเงินกู้ โดยสมมติว่าไม่มีการชำระเงินเพิ่มเติม โปรดใช้สูตรต่อไปนี้ในเซลล์ B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. จากนั้น สร้างตารางค่าตัดจำหน่ายใน Excel:
   • ตั้งค่าป้ายกำกับที่ระบุ เช่น ระยะเวลา กำหนดการชำระเงิน การชำระเงินพิเศษ การชำระเงินทั้งหมด ดอกเบี้ย เงินต้น ยอดคงเหลือในเซลล์ A8:G8
   • ในคอลัมน์งวด ให้ป้อนจำนวนการชำระเงินสูงสุดที่คุณอาจพิจารณาสำหรับเงินกู้ใดๆ ตัวอย่างเช่น กรอกตัวเลขตั้งแต่ 0 ถึง 360 ซึ่งครอบคลุมเงินกู้มาตรฐาน 30 ปีหากคุณชำระเงินรายเดือน
   • สำหรับงวด 0 (แถวที่ 9 ในกรณีของเรา) ให้ดึงค่ายอดคงเหลือด้วยสูตรนี้ = B4ซึ่งสอดคล้องกับจำนวนเงินกู้เริ่มต้น เซลล์อื่นๆ ทั้งหมดในแถวนี้ควรเว้นว่างไว้
   • 

⭐️ ขั้นตอนที่ 2: สร้างสูตรสำหรับกำหนดการตัดจำหน่ายพร้อมการชำระเงินพิเศษ

ป้อนสูตรต่อไปนี้ในเซลล์ที่เกี่ยวข้องทีละเซลล์ เพื่อปรับปรุงการจัดการข้อผิดพลาด เราได้รวมสูตรนี้และสูตรในอนาคตทั้งหมดไว้ภายในฟังก์ชัน IFERROR วิธีการนี้ช่วยหลีกเลี่ยงข้อผิดพลาดที่อาจเกิดขึ้นหลายประการหากเซลล์อินพุตใดๆ เว้นว่างไว้หรือเก็บค่าที่ไม่ถูกต้อง

● คำนวณการชำระเงินตามกำหนดเวลา:

ป้อนสูตรต่อไปนี้ลงในเซลล์ B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● คำนวณการชำระเงินเพิ่มเติม:

ป้อนสูตรต่อไปนี้ลงในเซลล์ C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● คำนวณการชำระเงินทั้งหมด:

ป้อนสูตรต่อไปนี้ลงในเซลล์ D10:

=IFERROR(B10+C10, "")

● คำนวณเงินต้น:

ป้อนสูตรต่อไปนี้ลงในเซลล์ E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● คำนวณดอกเบี้ย:

ป้อนสูตรต่อไปนี้ลงในเซลล์ F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● คำนวณยอดคงเหลือ

ป้อนสูตรต่อไปนี้ลงในเซลล์ G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

เมื่อคุณกรอกแต่ละสูตรเสร็จแล้ว ให้เลือกช่วงเซลล์ B10:G10 และใช้จุดจับเติมเพื่อลากและขยายสูตรเหล่านี้ลงไปตลอดระยะเวลาการชำระเงินทั้งชุด สำหรับช่วงเวลาใดๆ ที่ไม่ได้ใช้ เซลล์จะแสดง 0 ดูภาพหน้าจอ:

⭐️ ขั้นตอนที่ 3 : ทำสรุปสินเชื่อ

● รับจำนวนการชำระเงินตามกำหนด:

=B2:B3

● รับจำนวนการชำระเงินตามจริง:

=COUNTIF(D10:D369,">"&0)

● รับการชำระเงินพิเศษทั้งหมด:

=SUM(C10:C369)

● รับดอกเบี้ยทั้งหมด:

=SUM(F10:F369)

⭐️ ผลลัพธ์:

ด้วยการทำตามขั้นตอนเหล่านี้ คุณจะสร้างกำหนดการตัดจำหน่ายแบบไดนามิกใน Excel ที่บัญชีสำหรับการชำระเงินเพิ่มเติม

สร้างกำหนดการตัดจำหน่ายโดยใช้เทมเพลต Excel

การสร้างกำหนดการตัดจำหน่ายใน Excel โดยใช้เทมเพลตเป็นวิธีที่ตรงไปตรงมาและประหยัดเวลา Excel มีเทมเพลตในตัวที่จะคำนวณดอกเบี้ย เงินต้น และยอดคงเหลือของการชำระเงินแต่ละรายการโดยอัตโนมัติ ต่อไปนี้เป็นวิธีสร้างกำหนดการตัดจำหน่ายโดยใช้เท็มเพลต Excel:

1. คลิก เนื้อไม่มีมัน > ใหม่ในกล่องค้นหา ให้พิมพ์ กำหนดการตัดจำหน่ายและกด เข้าสู่ สำคัญ. จากนั้นเลือกเทมเพลตที่ตรงกับความต้องการของคุณมากที่สุดโดยคลิกที่เทมเพลตนั้น ตัวอย่างเช่น ที่นี่ ฉันจะเลือกเทมเพลตเครื่องคำนวณสินเชื่อแบบง่าย ดูภาพหน้าจอ:
   
2. เมื่อคุณเลือกเทมเพลตแล้ว ให้คลิกที่ สร้างบัญชีตัวแทน ปุ่มเพื่อเปิดเป็นเวิร์กบุ๊กใหม่
3. จากนั้น ป้อนรายละเอียดสินเชื่อของคุณเอง เทมเพลตควรคำนวณและเติมกำหนดการโดยอัตโนมัติตามข้อมูลที่คุณป้อน
4. สุดท้าย ให้บันทึกสมุดงานกำหนดการตัดจำหน่ายใหม่ของคุณ