การสร้างตารางแสดงการชำระหนี้แบบ Sum-of-digits

เวลาคนค้นหา “ตารางคำนวณดอกเบี้ยเงินกู้ excel รายเดือน” ส่วนใหญ่ต้องการไฟล์/วิธีทำตารางที่เห็นชัดๆ ว่าแต่ละเดือนจ่ายเท่าไหร่ แยกเป็นดอกเบี้ยเท่าไหร่ ตัดเงินต้นเท่าไหร่ และยอดหนี้คงค้างเหลือเท่าไหร่ ฉันแนะนำให้ตั้งโครง Amortization Table ให้ครบก่อน แล้วค่อยเลือกวิธีกระจายดอกเบี้ย (ในโพสต์นี้คือแบบ Sum-of-digits ที่ดอกเบี้ยหนักช่วงแรกและลดลงเรื่อยๆ) 1) เตรียมช่อง “ข้อมูลตั้งต้น” ให้ถูกกับรายเดือน - เงินกู้ (PV): ยอดเงินต้นที่กู้ - อัตราดอกเบี้ยต่องวด (rate): ถ้าดอกเบี้ยต่อปีและคิดรายเดือน ให้ใช้ =อัตราดอกเบี้ยต่อปี/12 ตัวอย่าง: 16% ต่อปี รายเดือน => 16%/12 - จำนวนงวด (nper): จำนวนเดือนทั้งหมด เช่น 3 ปี = 36 งวด - ค่างวด (PMT): ใช้สูตร =PMT(rate,nper,-PV,0,0) ทริคส่วนตัว: ใส่ PV เป็นค่าติดลบเพื่อให้ PMT ออกมาเป็นบวก อ่านง่ายเวลาทำตาราง 2) สร้างตารางรายเดือน (คอลัมน์แนะนำ) - งวดที่ - ค่างวด (PMT) - ดอกเบี้ย - เงินต้น - หนี้คงค้าง เริ่มที่ “งวดที่ 0” ไว้ 1 แถวก็ได้ โดยตั้งหนี้คงค้าง = PV 3) ทำให้เป็น “ตารางคำนวณดอกเบี้ยเงินกู้รายเดือน” แบบ Sum-of-digits แนวคิดของ Sum-of-digits คือเอา “ดอกเบี้ยรวมตลอดสัญญา” มากระจายตามสัดส่วนของลำดับงวด (งวดต้นๆ ได้สัดส่วนสูงกว่า) - สร้างลำดับงวด 1 ถึง n ด้วย =SEQUENCE(nper) - หาผลรวมของลำดับ (Sum-of-digits) ด้วย =SUM(SEQUENCE(nper)) - คิด “ดอกเบี้ยรวมโดยประมาณ” (สำหรับกรณีใช้ค่างวดคงที่) ฉันมักเทียบจาก ดอกเบี้ยรวม = (PMT*nper) - PV จากนั้นค่อยกระจายดอกเบี้ยรายเดือนแบบ Sum-of-digits - สัดส่วนงวดที่ k (ให้หนักช่วงแรก) มักใช้ (nper-k+1)/SumDigits - ดอกเบี้ยงวดที่ k = ดอกเบี้ยรวม * สัดส่วนงวดที่ k - เงินต้นงวดที่ k = PMT - ดอกเบี้ยงวดที่ k - หนี้คงค้างงวดถัดไป = หนี้คงค้างงวดก่อน - เงินต้นงวดที่ k 4) จุดที่ควรเช็กให้ชัวร์ (กันตารางเพี้ยน) - ผลรวม “เงินต้น” ทั้งหมดควรเท่ากับ PV (หรือใกล้เคียงมากๆ ถ้ามีการปัดเศษ) - หนี้คงค้างงวดสุดท้ายควรเป็น 0 (ถ้าไม่พอดี ให้ปรับงวดสุดท้ายเล็กน้อยจากการปัดทศนิยม) - ถ้าดอกเบี้ย “ทบต้น” และงวดชำระไม่ใช่รายเดือน (เช่น ทุก 6 เดือน) ให้แปลง rate ต่อ “งวดที่จ่ายจริง” เสมอ เช่น 16% ทบต้นทุก 6 เดือน => rate = 16%/2 และ nper = จำนวนครั้งที่จ่าย ถ้าคุณตั้งเป้าทำไฟล์ใช้เอง ฉันแนะนำให้ทำเป็นเทมเพลตที่กรอกแค่ PV, ดอกเบี้ย/ปี, จำนวนปี แล้วให้ Excel สร้างตารางรายเดือนอัตโนมัติด้วย SEQUENCE จะช่วยลดเวลาพิมพ์และลดความผิดพลาดได้มากค่ะ