ตารางค่าตัดจำหน่ายแสดงดอกเบี้ยที่ใช้กับเงินกู้ที่มีอัตราดอกเบี้ยคงที่และการลดลงของเงินกู้ยืมเงินต้นโดยการชำระคืน กำหนดการยังแสดงกำหนดการโดยละเอียดของการชำระเงินทั้งหมด เพื่อให้คุณสามารถทราบจำนวนเงินที่จะเข้าเงินกู้หลักและสิ่งที่จ่ายเป็นดอกเบี้ยจ่าย กำหนดการตัดจำหน่ายเป็นเรื่องง่ายมากที่จะสร้างด้วย Microsoft Excel เริ่มต้นด้วยขั้นตอนที่ 1 เพื่อสร้างตารางค่าตัดจำหน่ายที่บ้านโดยไม่ต้องจ่ายเงินให้คนอื่นทำ!
ขั้นตอน
ขั้นตอนที่ 1 เปิด Microsoft Excel และเปิดสเปรดชีตใหม่
ขั้นตอนที่ 2 ติดป้ายกำกับเซลล์ A1 ถึง A4 ดังนี้:
จำนวนเงินกู้ ดอกเบี้ย เดือน และการชำระเงิน
ขั้นตอนที่ 3 ป้อนข้อมูลที่เกี่ยวข้องกับเงินกู้ในเซลล์ B1 ถึง B3
ขั้นตอนที่ 4 ป้อนอัตราดอกเบี้ยเงินกู้เป็นเปอร์เซ็นต์
ขั้นตอนที่ 5. คำนวณการชำระเงินในเซลล์ B4 โดยพิมพ์ " =ROUND(PMT($B$2/12, $B$3, -$B$1, 0), 2)" ลงในกล่องสูตรโดยไม่ใส่เครื่องหมายคำพูด จากนั้นกด Enter
- เครื่องหมายดอลลาร์ในสูตรใช้สำหรับการอ้างอิงแบบสัมบูรณ์ เพื่อให้สูตรที่ป้อนค้นหาเซลล์ที่ระบุเสมอ แม้ว่าสูตรจะถูกคัดลอกไปยังส่วนใดๆ ของเวิร์กชีตก็ตาม
- อัตราดอกเบี้ยต้องหารด้วย 12 เพราะเป็นดอกเบี้ยรายปีซึ่งคำนวณเป็นรายเดือน
- ตัวอย่างเช่น หากเงินกู้ของคุณคือ 150,000 ดอลลาร์ ดอกเบี้ย 6 เปอร์เซ็นต์เป็นเวลา 30 ปี (360 เดือน) การชำระคืนเงินกู้จะเท่ากับ 899.33 ดอลลาร์
ขั้นตอนที่ 6 ติดป้ายกำกับเซลล์ A7 ถึง H7 ดังนี้:
ระยะเวลา ยอดคงเหลือเริ่มต้น การชำระเงิน เงินกู้หลัก ดอกเบี้ย เงินต้นสะสม ดอกเบี้ยสะสม และยอดคงเหลือปลายงวด
ขั้นตอนที่ 7 ป้อนค่าลงในคอลัมน์รอบระยะเวลา
- ป้อนเดือนและปีที่ชำระเงินกู้ครั้งแรกลงในเซลล์ A8 ต้องจัดรูปแบบคอลัมน์เพื่อแสดงเดือนและปีอย่างถูกต้อง
- เลือกเซลล์ คลิกแล้วลากลงเพื่อเติมคอลัมน์จนถึงเซลล์ A367 ตรวจสอบให้แน่ใจว่าตั้งค่าตัวเลือกการเติมอัตโนมัติเป็น " เติมเดือน"
ขั้นตอนที่ 8 เสร็จสิ้นกระบวนการป้อนข้อมูลในเซลล์ B8 ถึง H8
- ป้อนยอดดุลยกมาในเซลล์ B8
- พิมพ์ "=$B$4" ในเซลล์ C8 จากนั้นกด " Enter"
- ในเซลล์ E8 ให้สร้างสูตรเพื่อคำนวณดอกเบี้ยสำหรับยอดดุลต้นงวด สูตรคือ "=ROUND($B8*($B$2/12), 2)" เครื่องหมายดอลลาร์เดียวใช้เพื่อสร้างการอ้างอิงแบบสัมพัทธ์ สูตรจะค้นหาเซลล์ที่เหมาะสมในคอลัมน์ B
- ในเซลล์ D8 ให้ลบยอดดอกเบี้ยเงินกู้ในเซลล์ E8 ออกจากยอดชำระทั้งหมดใน C8 ใช้การอ้างอิงแบบสัมพัทธ์เพื่อให้สามารถคัดลอกเซลล์เหล่านี้ได้อย่างถูกต้อง สูตรคือ " =$C8-$E8"
- ในเซลล์ H8 ให้สร้างสูตรเพื่อลบส่วนของการชำระเงินกู้เงินต้นจากยอดดุลต้นงวดสำหรับงวด สูตรคือ " =$B8-$D8"
ขั้นตอนที่ 9 ทำกำหนดการต่อโดยสร้างรายการต่อไปนี้ในเซลล์ B9 ถึง H9
- เซลล์ B9 ต้องมีการอ้างอิงที่สัมพันธ์กับยอดดุลสุดท้ายของงวดก่อนหน้า พิมพ์ "=$H8" ในเซลล์แล้วกดปุ่ม Enter คัดลอกเซลล์ C8, D8 และ E8 แล้ววางลงใน C9, D9 และ E9 คัดลอกเซลล์ H8 แล้ววางลงใน H9 ประโยชน์ของการอ้างอิงแบบสัมพัทธ์สามารถสัมผัสได้ในขั้นตอนนี้
- ในเซลล์ F9 ให้ป้อนสูตรสำหรับทำตารางเงินกู้เงินต้นสะสมที่ชำระแล้ว สูตรคือ: " =$D9+$F8" ทำเช่นเดียวกันกับเซลล์ดอกเบี้ยสะสมใน G9 กล่าวคือ: " =$E9+$G8"
ขั้นตอนที่ 10. กรอกตารางค่าตัดจำหน่าย
- ไฮไลต์เซลล์ B9 ถึง H9 เลื่อนเคอร์เซอร์ไปที่มุมล่างขวาของส่วนที่เลือกของเซลล์จนกระทั่งเครื่องหมายบวกสีดำปรากฏขึ้น คลิกและลากส่วนที่เลือกขึ้นไปถึงบรรทัดที่ 367 ปล่อยปุ่มเมาส์
- ตรวจสอบให้แน่ใจว่าตั้งค่าตัวเลือกการเติมอัตโนมัติเป็น "คัดลอกเซลล์" และยอดคงเหลือสุดท้ายคือ $0.00
เคล็ดลับ
- ตอนนี้คุณสามารถเลื่อนดูระยะเวลาชำระคืนเงินกู้เพื่อดูจำนวนเงินที่ชำระในเงินกู้ต้น จำนวนเงินที่เรียกเก็บเป็นดอกเบี้ยเงินกู้ และจำนวนเงินต้นพร้อมดอกเบี้ยที่ชำระจนถึงปัจจุบัน
- ถ้ายอดดุลสุดท้ายไม่ใช่ 0.00 ดอลลาร์ ตรวจสอบให้แน่ใจว่าสูตรที่ใช้กำลังใช้การอ้างอิงแบบสัมพัทธ์และแบบสัมบูรณ์ตามคำแนะนำ และเซลล์ได้รับการคัดลอกอย่างถูกต้อง
