สร้าง Loan amortization schedule แบบปกติด้วย Python และ SAS

การทำงานที่ต้องเกี่ยวข้องกับเรื่องเงินทองอยู่บ่อยครั้ง แม้ว่างานหลักคือการทำ Risk model แต่ยังมีคำว่า Financial นำหน้าอยู่ ดังนั้นทำให้ต้องมีการศึกษาและทำ Loan amortization เพื่อตอบโจทย์สุดท้าย จากผลลัพธ์ของ Model ที่ออกมา

What is the loan amortization?

ถ้าลองค้นหาด้วยคำว่า “Amortization” ใน Google คำแรกในภาษาไทยที่ค้นเจอคือคำว่าค่าตัดจำหน่าย (เพิ่งเคยได้ยินเหมือนกัน) ซึ่งเมื่ออ่านและพิจารณาความหมายแล้ว อาจไม่ใช่สิ่งที่เราต้องการ ลองค้นด้วยคำใหม่ว่า “Loan amortization” จะเจอคำว่า “การทยอยชำระคืน” ตามความหมายจาก ThaiBMA ให้ความหมายไว้ว่า

การทยอยชำระคืน (Amortizing Repayment) คือ การแบ่งชำระเงินต้นเป็นงวด ๆ ในช่วงอายุเงินกู้ ถ้าทยอยชำระคืนเป็นงวดๆ งวดละเท่า ๆ กันเรียกว่า Equal Installments เช่น ชำระทุกสามเดือน หรือทุกหกเดือน

นับว่าเป็นความหมายที่สั้นและครอบคลุมจริง ๆ เพราะสิ่งที่ต้องการสื่อสารมีเพียงเท่านี้ ลองยกตัวอย่างเพื่อให้เห็นภาพง่ายกว่าเดิม ตัวอย่างเช่น เงินกู้ (Loan) อายุ 5 ปีชำระเงินต้นทุก ๆ เดือน ดังนั้นจึงมีทั้งหมด 60 งวด (5 x 12) ซึ่งมักจะเรียกติดปากกว่า Term หรือ Tenor ให้เอา 60 หารจำนวนเงินต้นทั้งหมด ได้ออกมาเป็นจำนวนที่ต้องชำระต่องวด ถ้าไม่ลงตัวนิยมปัดเศษไปงวดสุดท้าย (Installment) ดังนั้นกราฟที่ควรเป็นหลังจากเริ่มทะยอยจ่ายเงินต้นคือ เส้นโค้งที่วิ่งเข้าหา 0 เมื่อวานผ่านไป

ตัวอย่างกราฟของ Loan amortization

ซึ่งวันนี้ขอยกตัวอย่างการทำ Loan amortization ด้วย Python และ SAS ในรูปแบบปกติกันก่อน ซึ่งหมายความว่า มีการผ่อนชำระทุก ๆ เดือน ดอกเบี้ยที่เกิดขึ้นมีค่าเท่ากันทุกเดือน และจำนวน Term ไม่มีการเพิ่มหรือลด ความจริงแล้วเคยเขียนวิธีการทำด้วย Excel เมื่อนานมาแล้ว ซึ่งสามารถย้อนหลังกลับไปอ่านได้ที่ Link ข้างล่าง

สร้าง Loan amortization schedule ด้วย Excel’s solver

Python

สิ่งที่ต้องการสำหรับการเขียนด้วย Python ด้วย Library ที่ชื่อว่า numpy-financial ซึ่งดูเหมือนจะแยกตัวออกมาจาก Numpy อย่างสมบูรณ์แบบ ดังนั้นอาจต้องทำการติดตั้งเพิ่มเติม สามารถทำได้ด้วย pip install numpy-financial

เริ่มต้นเขียน Function สำหรับการทำ Loan amortization ภายใต้ amortization_schedule มีการรับตัวแปรทั้งหมด 3 ตัวคือ interest_rate, term และ balance เมื่อมีตัวแปรทั้ง 3 นี้ทำให้สามารถคำนวณ Installment ที่ต้องจ่ายในแต่ละเดือนได้

เริ่มจากสร้าง DataFrame ว่างขึ้นมา กำหนด Index ด้วย numpy.arange() โดยให้เริ่มที่ 1 และเพิ่มขึ้นทีละ 1 เพราะโดยปกติ Python เริ่มนับ Index แรกเป็นค่า 0 Term สุดท้ายต้อง +1 เพราะเป็น Exclusive เพื่อให้ได้ Term ที่ครบถ้วนสมบูรณ์สำหรับการคำนวณ สามารถกำหนดชื่อให้ Columns ตามค่าตัวแปรที่เกี่ยวข้องได้ โดยที่

• numpy_financial.pmt สำหรับหา Installment
• numpy_financial.ppmt สำหรับหา Principal ของแต่ละเดือน สังเกตตรง df.index เพราะว่าแต่ละเดือนจะไม่เท่ากัน ดังนั้นเลยต้องสร้าง index ให้เริ่มที่ 1
• numpy_financial.ipmt สำหรับหา Interest ของแต่ละเดือน เหตุผลเดียวกันกับ Principal เพราะว่าแต่ละเดือนจะไม่เท่ากัน ดังนั้นเลยต้องสร้าง index ให้เริ่มที่ 1

เมื่อได้ตัวแปรครบแล้ว ขั้นตอนสุดท้าย เอายอดหนี้ตั้งต้น ลบกับ Cumulative Principal แต่ใน Code ที่เขียนเป็น + เพราะว่าค่าจาก numpy_financial.ppmt Return ออกมาเป็นค่าลบ ดังนั้นจึงสามารถบวกเข้าไป เพื่อให้ได้ Ending Balance ของแต่ละเดือน

ทดลองใช้ฟังก์ชั่น โดยให้ตัวแปรแต่ละตัวมีค่าเท่ากับ

• interest_rate = 3.6% ต่อปี
• terms = 24 เดือน
• balance = 500,000

ตารางการ repayment schedule ที่ออกจาก function

ifrs9/amortization.ipynb at main · naenumtou/ifrs9

Full code สามารถดูได้ที่ Link ด้านบน

SAS

ลองเปลี่ยนภาษาที่ใช้ทำ Amortization เป็น SAS ที่ใช้เยอะกว่าในงานที่ทำอยู่ เนื่องจาก SAS เป็นภาษาที่ค่อนข้างเฉพาะเจาะจง เหมือนกับคิดมาให้หมดแล้วว่าต้องเขียนอะไรยังไง ทำให้ Logic ในการเขียน SAS ง่ายกว่า Python มาก ๆ

เนื่องจากการเขียน ไม่ได้เป็นการสร้าง Function เหมือน Python แต่เป็นการทำงานบน Table มากกว่า ดังนั้นเพื่อความง่ายในการเปลี่ยนแปลงค่า Parameters ที่ใช้ในการคำนวณต่าง ๆ ขอใช้คำสั่ง %let เพื่อ Define ตัวแปรที่ต้องการใช้ในการคำนวณขึ้นมาก่อน โดยมีค่าเท่ากับตัวอย่างที่ใช้ใน Python ด้านบน

ต่อมาเริ่มสร้าง Table สำหรับการทำ Amortization ใช้หลักการเดียวกันคือ ต้องคำนวณหา Payment (Installment) ที่ต้องจ่ายในแต่ละเดือนก่อนเป็นอันดับแรก ใน SAS มีฟังก์ชั่น FINANCE ที่รวบรวมการคำนวณเกี่ยวกับการเงินเอาไว้อยู่ในตัวเดียว ซึ่งถือเป็นข้อดีของโปรแกรมเสียเงิน เพราะเมื่อทุกอย่างไม่ใช่ Open-source ทำให้โปรแกรมใช้งานออกมาในรูปแบบเดียวกัน และสำคัญมากไปกว่านั้นคือหา Reference ได้ง่าย อย่างเช่น Link ด้านล่างที่บอกรายละเอียด รวมไปถึงตัวอย่างการใช้งานฟังก์ชั่นนี้

SAS Help Center: FINANCE Function

ตัวอย่างการใช้งานฟังก์ชั่น finance() บน sas

เมื่อต้องการหา Installment รายเดือน สิ่งที่ควรสนใจคือฟังก์ชั่น pmt ที่รับตัวแปรทั้งหมด 4 ตัวด้วยกันตามตัวอย่างซึ่งได้อธิบายไปแล้ว สิ่งที่ต้องระวังคือ &interest_rate. ที่หน่วยเป็นต่อปี ดังนั้นก่อนเริ่มการคำนวณควรหาร 12 เพื่อทำให้กลับมาเป็นรายเดือนก่อน ต่อมาสร้าง Column balance เพิ่มขึ้นมาเพื่อเอาไว้ใช้ในการคำนวณยอดหนี้ของเดือนต่อ ๆ ไป

เริ่มวนลูปในโปรแกรม SAS ประกาศคำสั่ง do ที่ i = 1 ไปจนถึง &term. สุดท้ายที่มีค่าเท่ากับ 24 (ตรงนี้ไม่ต้อง +1 เหมือน Python เพราะว่า SAS เริ่มการคำนวณที่ 1 และไม่มี Exclusive)

สร้าง Column แรกขึ้นมาคือดอกเบี้ยรายเดือนที่ต้องตัดจ่ายก่อนยอดเงินต้น โดยเอา Column balance คูณกับ &interest_rate. ตรงนี้สังเกตว่า ในแต่ละเดือนยอดหนี้ต้องเปลี่ยนไปแต่อัตราดอกเบี้ยมีค่าเท่าเดิม เพื่อตัดจ่ายส่วนที่เป็นดอกเบี้ยก่อนเสมอ ดังนั้น principal จึงมีค่าเท่ากับ Installment รายเดือนลบด้วยดอกเบี้ยที่ต้องจ่ายในเดือนนั้น ๆ ใส่ abs() ที่ installment เพราะว่าค่า Return จากฟังก์ชั่น finance(‘pmt’) มีค่าเป็นลบ

เพื่อได้ principal ในแต่ละเดือนแล้วก็สามารถนำไปลบกับ balance ที่ยกยอดมาจากเดือนก่อนหน้าเพื่อเป็นยอดปลายงวดของเดือนนั้น ๆ ได้ เพื่อให้ลูปนี้มันจำค่าที่เกิดขึ้นก่อนหน้านั้น ให้ใช้คำสั่ง output; เพื่อเก็บค่าที่คำนวณไปก่อนหน้านั้นเอาไว้ ซึ่งในที่นี้คือเดือนก่อนหน้า เมื่อลูปทำงานเรียบร้อยให้ปิดด้วย end; ก่อนที่จะ run; เพื่อให้ได้ Table เหมือนด้านล่างออกมา

ifrs9/amoritzation.sas at main · naenumtou/ifrs9

Full code สามารถดูได้ที่ Link ด้านบน

Conclusion

การทำ Amortization ในทั้ง 2 ภาษาใช้ Logic เหมือนกันแต่ส่วนตัวแล้วชอบเขียนแบบ Python มากกว่าเพราะเข้าใจได้ง่ายกว่า แต่ในขณะที่เขียนด้วยก็ได้เปรียบในเรื่องของการที่เขียนได้ง่ายกว่า เพราะ Syntax ของ SAS ไม่ยาก วันนี้จบเรื่องการทำ Amortization แบบปกติ โอกาสต่อไปมาลองทำแบบไม่ปกติกันดูบ้าง