Sum-of-digitsを表示するテーブルを作成する
表には、年々の債務返済(桁の合計)が示されており、契約😎💚の寿命にわたって利息が徐々に減少することで、早期段階で支払いを加速する利息分配の方法として機能しています(年々の減価償却の計算に似ています)。#エクセルソージー #エクセル #エクセルトリック #エクセルティップス #レモン8 Howtoo
เวลาคนค้นหา “ตารางคำนวณดอกเบี้ยเงินกู้ 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 จะช่วยลดเวลาพิมพ์และลดความผิดพลาดได้มากค่ะ
