建構收益率曲線 Yield Curve (2) 工作日調整 Business Day Convention - 用 Excel

上一篇我們詳細介紹了什麼是 Business Day Convention 以及用 Python 和 QuantLib 來實現工作日調整. 這一篇我們用 Excel 來做.

範例下載

範例檔案可以在此下載.

配置參數

首先把重要的參數排好位置. 三個參數分別是:

1. 開始日期
2. 每期的月數: 在 B2 格輸入數字直接表示每一期是幾個月
3. 調整方式: 我們實作 Unadjusted, Preceding, ModifiedPreceding, Following, 和 ModifiedFollowing 五種方式.

表格

把表格空間預留下來, 包括期數, 未調整日期, 星期幾, 和調整後日期. 星期幾是是供參考.

前進月份和 EDATE()

把每一期的未調整日期指定為開始日期加上期數*每期月數. 使用 Excel 函數 EDATE() 可以指定日期並加上特定月數. B6儲存格的公式為:

=EDATE(B$1, B$2*A6)

複製到其他期就變成下圖

WEEKDAY() 函數

在C欄我們加上了一個星期的參考值, 看看我們未調整日期是星期幾. 使用 Excel 的 WEEKDAY() 函數, 配上參數可以有不同的回復值. 在此我們選 數字11, 星期一到六分別是1到6, 而星期天是7.

自建假日列表

我們需要維護一個假日列表, 在另一個工作表或是任何地方輸入假日的日期, 把這個範圍指定一個名稱, 圖中左上方我把它命名為 假日列表

調整函數 DayRoll()

剩下最後一步了, 我們要來做調整的步驟. 我們需要用到 VBA. 我不是很想用 VBA 在教程上, 除非儲存格公式和表格沒辦法做.

Excel 的財務函數都太簡單了, 像是大學投資學課程用的, 對於我們需要的假日和調整功能完全沒有, 我們需要自己來做.

這個調整步驟很難用表格來實現, 因為牽涉到兩個不同的部份: 周末和假日, 我們必須要來回檢查調整後的日期是否為假日或周末.

有一個變通的方式是把所有周末都輸入成為假日, 這樣用單一儲存格就可以做到, 但這樣子假日列表會很長. 我們還是用 VBA 來做.

流程圖