PURPOSE
To provide the formulae and detail computation used by CS Lucas for computing bond yield to maturity and duration.
WHY IS THIS IMPORTANT?
Allows users to verify the formulae and methodology used by CS Lucas to compute the bond yield and duration.
FORMULA
The formulae used by CS Lucas for price and yield calculation are as follows:
(I) For semi-annual coupon with 6 months or less to maturity:
(II) For semi-annual coupon with more than 6 months to maturity:
Where
AI accrued interest per S$100 face value
DSM days between settlement date and maturity date
DCS days between beginning of current coupon period and settlement date
E number of days in coupon period where settlement occurs
DSC E – DCS = days from settlement date to next 6 month coupon date
N number of coupons payable between settlement date and maturity date
CPN annual coupon rate (as a percentage)
Y annual yield (as a percentage)
P price per S$100 face value
RV redemption value
FREQ The number of coupon payments per year.
FREQ = 1 for annual coupon payments
FREQ = 2 for semi-annual coupon payments
FREQ = 4 for quarterly coupon payments
Source:
http://www.sgs.gov.sg/~/media/SGS/SGSRulesMktPractices.pdf
https://support.office.com/en-us/article/PRICE-function-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a
BOND REFERENCE
The bond used to illustrate the computation in this guide is an actual issue by the Singapore Government. Details are as follows:
| Reference Code | NA12100N |
| ISIN | SG3254976487 |
| Issuer | Singapore Government |
| Issue Date | 02-Apr-12 |
| Maturity Date | 01-Apr-42 |
| Currency | SGD |
| Annual CouponFrequency | Semi Annually |
| Coupon Type | Fixed |
| Annual Coupon Rate (% p.a.) | 2.75 |
QUERY
1. Navigate to Report 8034: Bond YTM and Duration.
2. Enter the Ticker of bond and required As At Date for the computation.
3. You may enter a price for the purpose of the computation. If you would like the system to use the latest archived priced to As At Date, leave the price field blank.
4. Click on the required format.
5.The report has two sections: Bond Summary and Periodic Cash Flow.
BOND SUMMARY
| Ticker | NA12100N |
| Last Coupon Date | 1-Oct-14 |
| As At Date | 31-Dec-14 |
| Days Accrued | 92 |
| Period Days (E) | 182 |
| Days To Settlement (DSC) | 90 |
| Next Coupon Date | 1-Apr-15 |
| Accrued Interest | 0.695055 |
| Market Price | 97.09 |
| Yield | 2.9051897577% |
| Period Per Year | 2 |
| Total Period | 55 |
Last Coupon Date The date when before the As At Date of the query when the coupon was paid.
Days accrued Number of days from last coupon date up to and including the As At Date.
1-Oct-14 to 31-Dec-14 + 1 day.
Period Days Number of days in the current coupon period (1-Oct-14 to 1-Apr-15)
Days to Settlement Number of days to the next coupon date (1-April-15)
Accrued Interest
Accrued interest is deducted from the present value of future cash flow on the bond to determine the bond market value. See Periodic Cash Flow below.
Yield See explanation below on the derivation.Yield See explanation below on the derivation.
PERIODIC CASH FLOW
….rows omitted…
Each column in the Periodic Cash Flow section is an intermediate working in Formula II above.
Below are the detailed computations using the coupon cash flow on 1-Oct-2015 (Highlighted).
YIELD
Using Formula II above, the price of a bond is the sum of the Present Value column in the Periodic Cash Flow less the Accrued Interest. This total is dependent on the value of Y used in the determination of Column Q.
The Yield of the bond is the value for Y such that the sum of the Present Value column equals to the market price of the bond.
In the illustration above, for a bond value of 97.07 (with minor rounding), Y has to be 2.9051897577%.
Therefore, the yield of the bond is 2.9051897577%.
MACAULAY DURATION
III) Macaulay Duration is given by the formula
Where
Present value of bond cash flow (coupon and/or redemption) at period t.
I Time to each cash flow in years.
k Periods to maturity.
V The present value of all future cash payments from the bond.
Source:
https://en.wikipedia.org/wiki/Bond_duration
Below are detailed computations using cash flow on 1-Oct-2015 (Highlighted).
MODIFIED DURATION
IV) Modified Duration is given by the formula
FREQUENTLY ASKED QUESTIONS
FAQ01. How does the computation of the YTM in this guide compare with using Excel?
The YIELD function in Excel is used to determine the yield of a bond.
Information on the use of this function can be found at:
https://support.office.com/en-us/article/YIELD-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe
A comparison between the Yield Function and the CS Lucas methodology is as follows:
FAQ02. How does the computation of the Macaulay Duration in this guide compare with using Excel?
The DURATION function in Excel is used to determine the Macaulay duration of a bond.
Information on the use of this function can be found at:
https://support.office.com/en-us/article/DURATION-function-b254ea57-eadc-4602-a86a-c8e369334038
Comparison between the DURATION Function and the duration using CS Lucas methodology is as follows:
RELATED INFORMATION
CHANGE HISTORY
0 Comments