ตารางผ่อน บ้าน Excel ลดต้นลดดอก

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

การผ่อนเงินกู้โดยทั่วไปสำหรับคนทั่วไป จะแบ่งเป็น 2 ลักษณะ คือ

1. แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate เช่น การผ่อนรถยนต์)
2. แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate แบบผ่อนบ้าน หรือ สินเชื่อธุรกิจ)

ซึ่งผมจะสอนวิธีคิดทั้งคู่เลยครับ

เอาล่ะ สมมติเราจะกู้เงิน 1 ล้านบาท ดอกเบี้ย 6% ต่อปี แล้วอยากรู้ว่าจะต้องผ่อนเดือนละกี่บาท (เดือนละเท่าๆ กัน) จึงจะผ่อนหมดพอดี ในระยะเวลา 5 ปี เราก็กรอกข้อมูลดังรูปเลย

• คำนวณยอดผ่อนเงินกู้แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate)
• คำนวณยอดผ่อนเงินกู้แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate)
• ถ้าอยากรู้ว่าจะกู้ผ่านรึเปล่าล่ะ?
  • • ตัวอย่างการคำนวณทำดังนี้

คำนวณยอดผ่อนเงินกู้แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate)

ทีนี้จะเริ่มจากตัวที่เข้าใจง่ายก่อน ก็คือการผ่อนรถ ซึ่งโดยทั่วไปจะคิดดอกเบี้ยแบบ Flat Rate คือ แบบไม่ลดต้นลดดอก ซึ่งแปลว่า ไม่ว่าเราจะผ่อนเงินต้นไปเท่าไหร่ ดอกเบี้ยที่จ่ายก็จะไม่ลดลงเลย (ช่วงแรกหรือช่วงหลัง ก็ต้องจ่ายดอกเบี้ยเท่ากันหมด)

ดังนั้น ยอดผ่อนก็จะเกิดจาก การเอาเงินต้น (เงินกู้ตั้งต้น) บวกด้วยดอกเบี้ยตลอดระยะเวลาการกู้ เพื่อให้รวมเป็นเงินทั้งหมดที่เจ้าหนี้จะได้ แล้วค่อยหารด้วยจำนวนงวดผ่อน ดังนี้

= (เงินต้น+ดอกเบี้ยทุกปี) / (จำนวนงวดทั้งหมด)

=(B1+ B1*B2*B3) / (B3*12)

คำนวณยอดผ่อนเงินกู้แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate)

การคิดยอดผ่อน แบบลดต้นลดดอก ถ้าใช้วิชาคณิตศาสตร์ทั่วไปจะยากมากๆ แต่เราโชคดีที่ Excel มีฟังก์ชัน PMT ที่จะตอบโจทย์เรื่องนี้ได้ครับ

=PMT(rate,nper,pv,[fv],[type])

สูตรพวกการเงินใน Excel เช่น PV, FV, PMT, RATE มีหลักการเหมือนกัน คือ ต้องดูทิศทางของ Cash Flow ด้วย เช่น ถ้าเงินเข้าให้ใส่เลขเป็นบวก ถ้าเงินออกให้ใส่เลขเป็นลบ (หรือจะกลับกันก็ได้ ขึ้นอยู่กับมองในมุมมองคนกู้ หรือ มุมมองธนาคาร)

ดังนั้นในสูตรนี้ผมจึงใส่เลขเงินต้นให้ติดลบ (ธนาคารเสียเงิน) เพราะทิศทางมันคนละอันกับยอดผ่อนแต่ละเดือน (ธนาคารได้เงิน)

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

ซึ่งเรื่องพวกนี้เราสามารถนำมาต่อยอดทำเป็น Sensitivity Analysis ได้สบายๆ ครับ เช่น ผมสามารถหายอดผ่อนต่อเดือนที่จะต้องผ่อน ถ้าจำนวนปีเปลี่ยน หรือ อัตราดอกเบี้ยเปลี่ยนไปได้

วิธีทำคือเตรียมอัตราดอกเบี้ย ปีที่กู้ เอาไว้ก่อนดังนี้

จากนั้นไปช่อง B11 ที่เป็นจุดตัดหัวตาราง แล้วเขียนสูตร Link กับช่องยอดผ่อนที่เราคิดไว้แล้ว คือ =B5

แล้วลากครอบพื้นที่ แล้วกด Data -> What if Analysis -> Data Table

• แล้วเลือก Row Input Cell ไปที่ช่องอัตราดอกเบี้ย คือ B2
• แล้วเลือก Column Input Cell ไปที่ช่องจำนวนปี คือ B3

แล้วกด ok ก็จะได้ดังรูปครับ

ที่นี้ถ้าไม่อยากให้ผ่อนเป็นเศษ ก็ใช้พวก ROUND, ROUNDUP, ROUNDDOWN มาช่วยในช่องที่เราคำนวณยอดผ่อนได้ครับ (ในที่นี้ผมใช้ ROUNDUP เพราะถ้าผ่อนน้อยไปเดี๋ยวผ่อนไม่หมด)

โดยเทคนิคคือ การใส่ Digit ให้ติดลบ จะช่วยให้เป็นเลขกลมๆ ได้ เช่น -2 คือ ทำให้เลขหาร 100 ลงตัว (มี 0 อยู่ 2 ตัว), -3 คือ ทำให้เลขหาร 1000 ลงตัว (มี 0 อยู่ 3 ตัว)

จากนั้นค่าในตารางของ Data Table ก็จะเปลี่ยนไปเองโดยอัตโนมัติเลย

ถ้าเพื่อนๆ อยากรู้เรื่องอะไรอีกก็ Comment ทิ้งไว้ได้เลยครับ จะได้เอามาทำเป็นบทความให้ครับ

ถ้าอยากรู้ว่าจะกู้ผ่านรึเปล่าล่ะ?

มีคนถามผมอีกว่า จะคำนวณความสามารถในการชำระหนี้ยังไง?

จริงๆ แล้ววิธีคำนวณมันง่ายมากเลยครับ มันจะมีคำศัพท์อยู่ 2 แบบ คือ

1. Debt Burden นั่นคือ เอาภาระผ่อนหนี้ / รายได้ (กรณีเงินเดือน) หรือกำไรธุรกิจ(กรณีทำธุรกิจ) แล้วคำนวณว่าเป็นกี่%
2. DSCR นั่นคือ รายได้หรือ กำไรธุรกิจ / ภาระผ่อนหนี้

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

เอาเป็นว่า เผื่อๆ ไว้หน่อยละกัน ว่าตัวเองมีสินเชื่ออะไรบ้าง ให้เอามารวมๆ ให้หมด อย่างน้อยพวกวงเงินหมุนเวียนควรจะคิดดอกเบี้ยว่าต้องจ่ายเท่าไหร่มาเป็นยอดผ่อนด้วยน่ะครับ

ในรูปข้างล่าง ผมสมมติว่ามีหนี้เดิมทั้งหมดที่ต้องจ่ายอยู่ 40,000 บาท (ในช่อง G4)

ตัวอย่างการคำนวณทำดังนี้

จากรูปจะเห็นว่า Debt Burden ได้ 74.3% แปลว่า
ต้องผ่อนหนี้ด้วยจำนวน 74.3% ของเงินที่สามารถมาผ่อนหนี้ได้

ส่วน DSCR 1.35 เท่า ก็คือ มี เงินที่สามารถมาผ่อนหนี้ได้ เป็น 1.35 เท่าของหนี้ที่ต้องผ่อน

แต่เกณฑ์ระดับไหนที่เรียกว่าผ่าน หรือ ไม่ผ่าน แต่ละธนาคารคิดไม่เหมือนกันครับ ส่วนตัวคิดว่าเลขประมาณนี้ก็ปริ่มๆ แล้วล่ะ (เป็นคุณจะเอามั้๊ย? ต้องผ่อนหนี้ 75% ของรายได้ ซึ่งก็เยอะอยู่นะ ยกเว้นรายได้คุณสูงมากจนทำให้เงินที่เหลืออีก 25% ยังพอดำรงชัวิตได้)

คิดว่าน่าจะพอเห็นภาพบ้างนะครับ ว่าธนาคารมีวิธีเรื่องคำนวณยอดผ่อนเงินกู้ยังไง เอาเป็นว่าถ้าสงสัยอะไรก็ comment ถามมาได้เลยครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ

Can I use Excel online?

How do I create a simple Excel spreadsheet?

How can I learn Excel quickly?