สอนทำ Nelson-Siegel Fitting ใน Excel

Tutorial for Nelson-Siegel Fitting using Excel

Sasiwut Chaiyadecha
3 min readDec 28, 2022
Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

เคยเขียนเกี่ยวกับการใช้ Nelson-Siegel สำหรับการ Fitting lifetime PD Curve เมื่อนานมาแล้ว ในตอนนั้นใช้ Python เป็นเครื่องมือในการสร้าง Curve ขึ้นมา ทั้งเขียน Optimization เอง รวมไปถึงการใช้งาน Library

แต่เนื้อหาของ Blog ตอนนี้เป็นวิธีการทำ Nelson-Siegel ด้วย Excel พร้อมทั้งบอกปัญหาที่อาจจะเกิดขึ้น เมื่อใช้ข้อมูลที่เป็น Lifetime PD มาเป็น Input ตั้งต้น

Marginal lifetime PD

ใช้เป็นข้อมูลเดิมจากตอนก่อนหน้านี้เลย แต่ค่าที่ Estimate ออกมาคงไม่เท่ากัน เพราะเนื่องจากข้อจำกัดของ Nelson-Siegel model เอง ขอ Recap สั้น ๆ เกี่ยวกับข้อมูลที่นำมาเป็น Input ใช้งาน ก่อนเริ่มเข้าวิธีการทำใน Excel

ข้อมูลที่นำมาใช้งานเป็น Marginal PD โดยที่เกิดจากผลต่างของ Cumulative PD ระหว่าง Period ไม่ว่าจะเป็นเดือนหรือปี ถ้านำค่า Cumulative PD มาลบกันจะได้สิ่งที่เรียกว่า Marginal PD จากตัวอย่างตารางด้านล่างเป็นข้อมูล Marginal PD ทั้งหมด 10 ปี ที่นำมาเป็นตัวอย่างการทำ Nelson-Siegel ใน Blog ตอนนี้

Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver
Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

จุดสังเกตของ Marginal PD คือกราฟจะมีลักษณะเป็น Hump curve คือมีลักษณะชันขึ้นในช่วงปีแรกและค่อย ๆ ลดลงในปีถัด ๆ ไป ซึ่งลักษณะนี้เองคล้ายกับ Yield curve ที่ใช้ Nelson-Siegel formula ในการ Fitting curve ออกมา

แต่ต้อง Noted ไว้ว่าลักษณะของ Marginal PD ที่เป็น Hump curve อาจไม่ได้เป็นจริงเสมอไป โดยเฉพาะกับ Credit risk ที่มีความเสี่ยงสูง ๆ เพราะเมื่อความเสี่ยงสูงที่ปีแรก ๆ แล้ว Room to default ในปีหลัง ๆ ย่อมมีโอกาสเกิดขึ้นน้อยลง

Excel

Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

เริ่มต้นที่สูตรของ Nelson-Siegel ตามด้านบน โดยที่เขียนสูตรใน Excel และแทนค่าตามตัวแปรให้ถูกต้อง โดยที่ตัวแปรทั้งหมดมีความหมายตามด้านล่าง

  • β0 คือค่าเฉลี่ยของ Marginal PD ทั้งเส้น
  • β1 คือ Short-term หรือจุดที่กราฟขึ้นสู่จุด Peak
  • β2 คือ Medium-term หรือจุดที่ค่อย ๆ Decay เข้าสู่ 0
  • λ หรือ Lambda คือตัวแปรที่กำหนด Decay function ว่าช้าหรือเร็ว
  • τ จริง ๆ แล้วคือ Time คือตัวเลขแทนจำนวนปีของ Marginal PD

โดยตัวอย่างสูตรเขียนไว้ให้ดูด้านล่าง

=$B0$+$B1$*((1-EXP(-$lambda$*time))/($lambda$*time)+$B2$*((1-EXP(-$lambda$*time))/($lambda$*time)-EXP(-$lambda$*time))

ตอนนี้ยังไม่ต้องสนใจว่าตัวเลขที่จะแทนเข้ามาใน β0, β1 หรือ Parameters ต่าง ๆ ควรเป็นเลขเท่าไหร่ สามารถใส่ตัวเลขมั่ว ๆ เริ่มต้นเข้าไปได้ก่อน เมื่อผูกสูตรได้แล้วจะเห็นได้ว่าตัวเลขยังไม่มีความใกล้เคียงกับ Marginal PD เลย

จากจุดนี้ต้องใช้ Excel solver เข้ามาช่วยในการหาค่าที่เหมาะสม แต่ก่อนการใช้งาน Excel solver ต้องกำหนด Cost function เพื่อให้โปรแกรมใช้ในการ Minimise error ที่เกิดขึ้นทั้งหมดก่อน

ถ้ามีจำนวนข้อมูลเยอะ อาจใช้เป็นสูตร =SUMSQ() หรือการ Minimise ของผลรวมของค่ากำลังสองของ 2 Array แต่ในกรณีที่ไม่ได้มีข้อมูลเยอะ แนะนำให้ใช้เป็น Sum Absolute Error จะได้ผลดีมากกว่า วิธีการคือให้หาผลต่างระหว่างค่า Marginal PD และ Nelson-Siegel Estimated จากนำให้ใส่ฟังก์ชั่น =ABS() และทำการ =SUM() Error ทั้งหมดให้อยู่ใน Cell เดียว

สุดท้ายใช้เป็น Excel solver ด้วยการเปลี่ยนแปลงค่า Nelson-Siegel Parameters โดยให้ Minimise cell ผลรวมของผลต่างที่ได้ผูกสูตรไว้ก่อนหน้านี้

Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

คำตอบที่ออกมาจะเป็นค่า Nelson-Siegel Estimated ที่มีความใกล้เคียงกับ Marginal PD (แต่อาจไม่ได้เท่ากัน 100%)

Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

เมื่อได้ Parameters ทั้งหมดออกมาแล้ว จากเดิมที่มี Marginal PD ที่ 10 ปี สามารถใช้ Parameters ที่ Estimate ออกมาได้ ทำการ Plot marginal PD เพิ่มอีก 10 ปี รวมทั้งหมดเป็น 20 ปี เพื่อหาค่าในช่วงเวลาที่ไกลออกไป

Nelson-Siegel fitting curve for PD Lifetime model มาตรฐานบัญชี TFRS 9 แบบจำลอง TFRS 9 แบงค์ชาติ Bank of Thailand BOT forward-looking information lifetime PD Observed Default Rate ODR Rating model unbias PD Model Default rate NPL Ratio Point in Time PD (PiT PD) Through The Cycle (TTC PD) nelson-siegel svensson Optimisation process scipy Humped curve Bond yield curve Decay function long-run average excel tutorial excel solver

จากรูปทั้ง In-time และ Out-of-time จะเห็นได้ว่า Nelson-Siegel Estimator พยายามเลียนแบบ Curve และ Slope ของ Marginal PD โดยมีเพียง Time ที่เป็น Input ก็สามารถสร้าง Curve ออกไปให้ไกลได้มากขึ้น

Conclusion

จบแล้วสำหรับการใช้งาน Excel เพื่อหา Parameters สำหรับ Estimate ค่าด้วย Nelson-Siegel ถือว่าเป็นวิธีที่ง่าย อาจจะง่ายกว่าการใช้ Code เพื่อสร้าง Fitting curve ออกมา และ Blog ตอนนี้ก็เป็นตอนสุดท้ายของปีนี้ 2022 นี้แล้ว อ่านมาถึงตรงนี้ขอ Happy new year ล่วงหน้าไว้เลย

--

--

Sasiwut Chaiyadecha
Sasiwut Chaiyadecha

No responses yet