สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 1
วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง)
ใช้ Excel คำนวณมูลค่าเงินในอนาคตจากการลงทุน

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

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

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

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 2

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

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

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

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 3

ยอดผ่อนแบบลดต้นลดดอก (ดอกเบี้ย Effective Rate)

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

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

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 4

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 5

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 6

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 7
  • แล้วเลือก Row Input Cell ไปที่ช่องอัตราดอกเบี้ย คือ B2
  • แล้วเลือก Column Input Cell ไปที่ช่องจำนวนปี คือ B3

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 8

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 9

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 10

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

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

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

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

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

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

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

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

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

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 11

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

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

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

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

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง)
ใช้ Excel คำนวณมูลค่าเงินในอนาคตจากการลงทุน
  • 1.1K
  •  
  •  
  •  
  •  

Posted on: May 30, 2019
Tags: , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *