{"text_format":"3","workbook":"xlsx","n_sheets":3,"sheets":["main","graph","version notes"],"not_rendered":{"charts":1,"macros":1,"defined_names":9,"comments":1},"read":"whole","date_system":"1900"}

{"sheet":"main","index":0,"n_rows":46,"n_cols":19,"rows_kept":46,"merged":[[17,7,22,8]]}
[null,"Convertible Bond Valuation",{"$nulls":4},"This workbook was developed by SαTEEK."]
[]
[null,"Trade Data (inputs)",{"$nulls":4},"Results",{"$nulls":4},"Notes",{"$nulls":3},"Errors and Warnings:"]
[]
[null,null,"Start Date","2009-04-10",{"$nulls":3},"Convertibe Bond  Value",{"$f":"I6+I7","v":{"v":112.23448939573274,"d":"112.23"}},{"$nulls":3},"This sheet values a convertible bond with a single 'convert' date at maturity.",{"$nulls":3},"Has Problem?",{"$f":"OR(Q8:Q21)","v":false}," "]
[null,null,"Maturity","2014-04-10",{"$nulls":3},"Bond only dirty price",{"$f":"I12+I13","v":{"v":105.75991313264018,"d":"105.76"}},{"$nulls":9}," "]
[null,null,"Coupon",{"v":0.05,"d":"5.00%"},{"$nulls":3},"Option Val",{"$f":"D39","v":{"v":6.474576263092554,"d":"6.47"}},{"$nulls":3},"At maturity we have:",{"$nulls":3},"Show?","Message"," "]
[null,null,"Coupon Period","6m",{"$nulls":3},"(Option Val) / (Face Val)",{"$f":"I7/D9","v":{"v":0.06474576263092553,"d":"6.47%"}},{"$nulls":3},"Payoff = max( N , r * S[T] )",null,null,"E",{"$f":"ISERR(_xll.qlVersion())","v":false},{"$f":"IF(Q8,\"Looks like the quantlib(.xll) addin  wasn't loaded, May need to install it.\",\"Using quantlib.xll version: \"&_xll.qlVersion())","v":"Using quantlib.xll version: 0.9.7"}," "]
[null,null,"Face Value",100,{"$nulls":3},"Delta (%)",{"$f":"D40*D10*D19/D9","v":{"v":0.2875802379682145,"d":"28.76%"}},{"$nulls":3},"where r is the conversion ratio, N is the face-value, S[T] is the spot at maturity",null,null,"R",{"$f":"D18>=D6","v":false},{"$f":"IF(Q9,\"The bond has expired, i.e. current date is on or after the maturity date.\",\"The current date is before the maturity.\")","v":"The current date is before the maturity."}," "]
[null,null,"Conversion Ratio",2,{"$nulls":3},"Delta for 1%",{"$f":"D40*D10*D19*0.01","v":{"v":0.2875802379682146,"d":"0.29"}},{"$nulls":3},"So Payoff = N + r * max(0, S[T] - N/r)",null,null,"R",{"$f":"ISERR(D39)","v":false},{"$f":"\"In cell \"&ADDRESS(ROW(D39),COLUMN(D39))& \" got: \"&IF(Q10,_xll.ohRangeRetrieveError(D39),\"no error.\")","v":"In cell $D$39 got: no error."},"  "]
[null,null,"Currency","EUR",{"$nulls":3},"Strike ",{"$f":"D9/D10","v":{"v":50,"d":"50.00"}},{"$nulls":3},"i.e. r call options, each with strike N/r.",null,null,"O",{"$f":"ISERR(D44)","v":false},{"$f":"\"In cell \"&ADDRESS(ROW(D44),COLUMN(D44))& \" got: \"&IF(Q11,_xll.ohRangeRetrieveError(D44),\"no error.\")","v":"In cell $D$44 got: no error."}," "]
[{"$nulls":7},"Bond only clean Price",{"$f":"_xll.qlBondCleanPrice(D43,D46)*D9*0.01","v":{"v":105.334088956816,"d":"105.334"}},{"$nulls":6},"R",null,null," "]
[{"$nulls":7},"Accrued Interest",{"$f":"_xll.qlBondAccruedAmount(D43,D18,D46)*D9*0.01","v":{"v":0.42582417582417437,"d":"0.426"}},{"$nulls":3},"This workbook was developed by SαTEEK Private Limited.",null,null,"S",null,null,"  "]
[{"$nulls":12},"http://sateek.com",{"$nulls":5}," "]
[{"$nulls":15},"W",null,null," "]
[null,"Market Data (inputs)",{"$nulls":4},"Diagnostics",{"$nulls":5},"Email for support or feedback:",null,null,"A",null,null," "]
[{"$nulls":12},"philip.kinlen@sateek.com",null,null,"R",null,null," "]
[null,null,"Current Date",{"$f":"TODAY()","v":"2010-05-13"},{"$nulls":3},{"$f":"IF($R$5,VLOOKUP(TRUE,$Q$8:$R$21,2,FALSE),\"No errors nor warnings reported.\")","v":"No errors nor warnings reported."},{"$nulls":7},"N",null,null," "]
[null,null,"Current Equity Spot",{"v":42,"d":"42.00"},{"$nulls":8},"Sateek open source licence and disclaimer (link).",null,null,"I",null,null," "]
[null,null,"Volatility",{"v":0.2,"d":"20.00%"},{"$nulls":11},"N",null,null," "]
[null,null,"Risk Free (apr)",{"v":0.025,"d":"2.50%"},{"$nulls":8},"Quantlib open source licence and documentation (link).",null,null,"G",null,null,"  "]
[null,null,"Credit Spread",{"v":0.01,"d":"1.00%"},{"$nulls":11},"S",null,null," "]
[null,null,"Dividend Yield",{"v":0.03,"d":"3.00%"},{"$nulls":14}," "]
[]
[]
[null,"Calculations",{"$nulls":4},"Bond Schedule Dates",{"$nulls":4},"Installing QuantLib xll addin"]
[]
[null,null,"Set Evaluation date",{"$f":"_xll.qlSettingsSetEvaluationDate(D18)","v":true},{"$nulls":3},"Schedule Dates",{"$nulls":4},"To get this sheet to work, you'll need to load the QuantLib (open-source)"]
[null,null,"Strike",{"$f":"D9/D10","v":50},{"$nulls":3},{"$f":"_xll.qlScheduleDates(D42)","v":"2009-04-14"},{"$nulls":4},"addin (xll). To do that:"]
[null,null,"Years to Mat",{"$f":"(D6-D18)/365","v":{"v":3.9123287671232876,"d":"3.91"}},{"$nulls":3},"2009-10-12",{"$nulls":4},"1: download installation package:  by clicking on this link."]
[null,null,"Vol Object",{"$f":"_xll.qlBlackConstantVol(\"blackvol1\",D18,\"Target\",D20,\"Actual/365 (Fixed)\",,D28)","v":"blackvol1#0001"},{"$f":"_xll.ohRangeRetrieveError(D31)","v":""},null,null,"2010-04-12",{"$nulls":4},"    If there's a newer version of QuantLibXL-bin-<version>.exe "]
[null,null,"Vol Process",{"$f":"_xll.qlGeneralizedBlackScholesProcess(\"blackscholes1\",D31,D19,\"Actual/365 (Fixed)\",D18,D21,D23)","v":"blackscholes1#0001"},{"$f":"_xll.ohRangeRetrieveError(D32)","v":""},null,null,"2010-10-11",{"$nulls":4},"    available at this link, "]
[null,null,"Exercise",{"$f":"_xll.qlEuropeanExercise(\"eu_exercise2\",D6,,D28)","v":"eu_exercise2#0001"},{"$f":"_xll.ohRangeRetrieveError(D33)","v":""},null,null,"2011-04-11",{"$nulls":4},"    then do use that."]
[{"$nulls":4},{"$f":"_xll.ohRangeRetrieveError(D34)","v":""},null,null,"2011-10-10"]
[null,null,"Payoff",{"$f":"_xll.qlStrikedTypePayoff(\"eu_payoff\",\"Vanilla\",\"Call\",D29,,,D28)","v":"eu_payoff#0001"},{"$f":"_xll.ohRangeRetrieveError(D35)","v":""},null,null,"2012-04-10",{"$nulls":4},"2: run the installation program locally on your PC."]
[null,null,"pricing engine",{"$f":"_xll.qlPricingEngine(\"eur_example_engine\",\"AE\",D32)","v":"eur_example_engine#0001"},{"$f":"_xll.ohRangeRetrieveError(D36)","v":""},null,null,"2012-10-10"]
[null,null,"european option",{"$f":"_xll.qlVanillaOption(\"european_option\",D35,D33,,D28)","v":"european_option#0001"},{"$f":"_xll.ohRangeRetrieveError(D37)","v":""},null,null,"2013-04-10",{"$nulls":4},"3: open excel (though if you're reading this it may be open already!)"]
[null,null,"set engine",{"$f":"_xll.qlInstrumentSetPricingEngine(D37,D36)","v":true},{"$f":"_xll.ohRangeRetrieveError(D38)","v":""},null,null,"2013-10-10"]
[null,null,"Option Value",{"$f":"_xll.qlInstrumentNPV(D37,D38)*D10","v":{"v":6.474576263092554,"d":"6.47"}},{"$f":"_xll.ohRangeRetrieveError(D39)","v":""},null,null,"2014-04-10",{"$nulls":4},"4: load the QuantLib addin. In Excel 2007 you need to "]
[null,null,"Delta of one call option",{"$f":"_xll.qlDelta(D37,D38)","v":{"v":0.34235742615263637,"d":"34.24%"}},{"$f":"_xll.ohRangeRetrieveError(D40)","v":""},null,null,{"$error":"#N/A"},{"$nulls":4},"     click on the \"Office Button\" ( top left corner of excel)"]
[{"$nulls":4},{"$f":"_xll.ohRangeRetrieveError(D41)","v":""},null,null,{"$error":"#N/A"},{"$nulls":4},"     then 'Open' and navigate to: "]
[null,null,"schedule",{"$f":"_xll.qlSchedule(\"schedule\",D5,D6,D8,\"Target\",\"Modified Following\",\"Modified Following\",\"Backward\",0,,,,D28)","v":"schedule#0001"},{"$f":"_xll.ohRangeRetrieveError(D42)","v":""},null,null,{"$error":"#N/A"},{"$nulls":4},"    C:/Program Files/QuantLibXL/xll/QuantLibXL<version>.xll"]
[null,null,"bond",{"$f":"_xll.qlFixedRateBond(\"bond\",\"fixed rate\",D11,0,D9,D42,D7,\"Actual/Actual (ISMA)\",\"Following\")","v":"bond#0001"},{"$f":"_xll.ohRangeRetrieveError(D43)","v":""},null,null,{"$error":"#N/A"},{"$nulls":4},"    In older versions of excel you'll click on File -> Open"]
[null,null,"yield curve",{"$f":"_xll.qlFlatForward(\"yc\",0,\"NullCalendar\",D21+D22,,\"Continuous\",\"Annual\",0,D28)","v":"yc#0001"},{"$f":"_xll.ohRangeRetrieveError(D44)","v":""},null,null,{"$error":"#N/A"}]
[null,null,"Pricing Engine",{"$f":"_xll.qlBondEngine(\"b_engine\",D44)","v":"b_engine#0001"},{"$f":"_xll.ohRangeRetrieveError(D45)","v":""},null,null,{"$error":"#N/A"}]
[null,null,"Set pricing engine",{"$f":"_xll.qlInstrumentSetPricingEngine(D43,D45)","v":true},{"$f":"_xll.ohRangeRetrieveError(D46)","v":""},null,null,{"$error":"#N/A"}]

{"sheet":"graph","index":1,"n_rows":60,"n_cols":7,"rows_kept":60}
[null,"This workbook was developed by SαTEEK.",{"$nulls":4},"For this graph generation page to work, the VBA macros must be enabled."]
[]
[null,"Graph Configuration:"]
[]
[null,null,"X-axis Label","equity spot"]
[null,null,"Ref to cell to change",{"$f":"main!D19","v":{"v":42,"d":"42.00"}}]
[null,null,"Min Val",10]
[null,null,"Max Val",170]
[null,null,"Y-axis Label","convert val"]
[null,null,"Ref to cell with result",{"$f":"main!I5","v":{"v":112.23448939573274,"d":"112.23"}}]
[null,null,"Num steps",40]
[]
[]
[]
[]
[]
[null,"Table of Results (generated by VBA)"]
[]
[null,null,"Input","Result"]
[null,null,10,105.62089338424325]
[null,null,14,105.62329109321927]
[null,null,18,105.64846403602326]
[null,null,22,105.76746578920255]
[null,null,26,106.11600579346909]
[null,null,30,106.86725226174859]
[null,null,34,108.18485607360003]
[null,null,38,110.18630917338518]
[null,null,42,112.92863411943861]
[null,null,46,116.4126231706183]
[null,null,50,120.59669690448152]
[null,null,54,125.41290058526185]
[null,null,58,130.78079785181177]
[null,null,62,136.61769819943348]
[null,null,66,142.84522847230582]
[null,null,70,149.39294369779645]
[null,null,74,156.1998271702258]
[null,null,78,163.21443389658018]
[null,null,82,170.39425348979248]
[null,null,86,177.70469141507334]
[null,null,90,185.11792319984784]
[null,null,94,192.6117708985301]
[null,null,98,200.1686799120297]
[null,null,102,207.77482918703072]
[null,null,106,215.41938115207455]
[null,null,110,223.09386328458197]
[null,null,114,230.79166641117965]
[null,null,118,238.50764261210165]
[null,null,122,246.23778589730165]
[null,null,126,253.9789803781227]
[null,null,130,261.728802717344]
[null,null,134,269.48536777632876]
[null,null,138,277.2472083705859]
[null,null,142,285.01318179687536]
[null,null,146,292.7823972784631]
[null,null,150,300.55415969976855]
[null,null,154,308.3279259944996]
[null,null,158,316.1032713456856]
[null,null,162,323.87986298526954]
[null,null,166,331.6574398756826]
[null,null,170,339.4357969426725]

{"sheet":"version notes","index":2,"n_rows":5,"n_cols":6,"rows_kept":5}
[null,"This workbook was developed by SαTEEK."]
[]
[null,"Version","Date","Author","Company","Changes"]
[]
[null,"1.00","2010-05-05","Philip Kinlen","Sateek Pte Ltd","First Version, prices convert with single convert date."]