Class 1

Derivatives – Prof Ramesh Laxman

YTM calculation from 1st Principal (Bonds)

Day Count Conversion

Basis	In Excel
30/360, American	Default / 0
Actual/Actual	1
Actual/360	2
Actual/365	3
30/360, European; Indian Govt Bond Market	4

Calculation – All marked in yellow is given in the question. All marked in purple / colorless are calculated.

Face Value	100
Coupon Rate	7.99%
Settlement Date	24-Oct-11
Maturity Date	9-Jul-17
Frequency (per year)	2
Previous Coupon	9-Jul-11
Price of Bond (Current)	99.99
Accrued Interest	2.33
Dirty Price	102.32

Next Coupon Date

9-Jan-12

Formula used for the above calculation is as follows -

· Settlement Date = Today()

· Previous Coupon = COUPPCD(Settlement Date, Maturity Date, Frequency, basis = 4)

· Accrued Interest = YEARFRAC(Previous Coupon Date, Settlement Date, Basis = 4)

· Next Coupon Date = EDATE(Previous Coupan Date, Number of Months after previous coupon which is 6 months here). Similarly, EOMONTH = Gives month end date, used by bankers to calculate FDs.

Coupan Date	Outflow	PV
9-Jan-12	3.995	3.9303
9-Jul-12	3.995	3.7793
9-Jan-13	3.995	3.6342
9-Jul-13	3.995	3.4946
9-Jan-14	3.995	3.3603
9-Jul-14	3.995	3.2312
9-Jan-15	3.995	3.1071
9-Jul-15	3.995	2.9878
9-Jan-16	3.995	2.8730
9-Jul-16	3.995	2.7626
9-Jan-17	3.995	2.6565
9-Jul-17	3.995	2.5545
9-Jan-18	3.995	2.4563
9-Jul-18	103.995	61.4851
Sum	155.9300	102.3128

In the above calculation, formula used is as follows -

· 1^st Coupon Date = EDATE(Previous Coupan Date, Number of Months after previous coupon) = EDATE(9-Jul-11, 6) = 9-Jan-12

· Outflow = Face Value * Coupon rate / Frequency (per annum)

· PV = Outflow / (1+Yield%/Frequency per annum)^(Frequency per annum*YEARFRAC(Settlement Date, Current Coupon Date, Basis=4))

Duration Calculation

Duration(Settlement Date, Maturity Date, Coupon, Yield, Frequency per annum, Basis=4 here)

= 4.589762

Yield = 7.99%

Sum of PVs = Dirty Price

Hence, use Goal seek to make Sum of PVs = Dirty Price by changing / finding the value of yield.