{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Help","Pricing-Simple","Pricing-Continuous","Futures Specs","Holidays","Basket_hedge_work"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Help","index":0,"n_rows":14,"n_cols":2,"rows_kept":14,"cut":false,"hidden":false,"merged":[[13,1,15,15]]}
["Input","Output"]
[]
["Instructions for using Interest rate futures pricing calculator (Theoretical)"]
["Use \"PRICING-Simple/\"PRICING-Continous\" Sheet\" "]
[1,"Select the GoI Security as underlying for 10 year Interest Rate Futures"]
[{"$f":"+A5+1","v":2},"Enter Settlement Date and Final Settlement Date in \"dd-mm-yyyy\" format"]
[3,"Enter Clean Price for the selected Underlying"]
[4,"Theoretical Futures price is displayed in OUTPUT section"]
[]
["Pricing Formula"]
[null,"Futures price = Bond Clean Price + Financing Cost – Income"]
[]
[]
["Disclaimer:","Metropoliatan Stock Exchange of India Pvt Ltd., provides the calculator for the benefit of members to derive the price of IRF contracts on best-effort basis. However, the exchange gives no warranty, express or implied, as to the accuracy, reliability and completeness of any information, formulae or calculations provided through the use of the calculators and does not accept any liability for loss or damage of whatsoever nature, which may be attributable to the reliance on and use of the calculators."]

{"sheet":"Pricing-Simple","index":1,"n_rows":23,"n_cols":12,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,5],[3,1,3,3]]}
["INTEREST RATE FUTURES PRICING CALCULATOR (THEORETICAL)"]
[]
["INPUT ",null,null,null,null,null,null,null,null,"Gsec-AI"]
["Select futures contract","10-YEAR BenchMark Single Bond Futures",null,null,{"$f":"IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(B4,'Futures Specs'!$K$1:$K$15,0)+2,2),\"\")","v":""}]
[]
["Underlying Security","8.40% GOVT.STOCK 2024",null,"Settlement Date(Spot)",null,null,null,"Day Count",365,360,"Special case","Modified Business day"]
[null,null,null,"(dd-mm-yyyy)"]
["Coupon of Security",{"$f":"IFERROR(IF(LEFT(B6,2)*1=91,0,LEFT(B6,4)*1),\"\")","v":8.4},null,"Settlement Date(Fut)",null,null,"LIP",{"$f":"COUPPCD(E6,B10,2,4)","v":"00:00:00"},{"$f":"COUPDAYBS(E6,$B$10,2,4)","v":0},{"$f":"COUPDAYBS(E8,$B$10,2,4)","v":0},{"$f":"IF(DAYS360(H8,E6)=0,180,COUPDAYBS(E6,$B$10,2,4))","v":180},{"$f":"WORKDAY(H8-1,1,Holidays!$A$1:$A$220)","v":{"$error":"#NUM!"}}]
[null,null,null,"(dd-mm-yyyy)",null,null,"NIP",{"$f":"COUPNCD(E6,B10,2,4)","v":"1900-01-28"},{"$f":"COUPDAYBS(E8,$B$10,2,4)","v":0},{"$f":"E8-L9","v":-30},null,{"$f":"WORKDAY(H9-1,1,Holidays!$A$1:$A$220)","v":"1900-01-30"}]
["Maturity of Underlying",{"$f":"IFERROR(HLOOKUP(B6,'Futures Specs'!$A$1:$D$5,5,0),\"\")","v":"2024-07-28"},null,"Cost of Carry",null,null,"Settlement2Del",null,{"$f":"(E8-E6)","v":0}]
[]
[null,null,null,null,null,null,{"$f":"IF(E6=H8,(G15+(B8*K8/J6))*(1+E10%*I10/I6),(G15+(B8*I8/J6))*(1+E10%*I10/I6))","v":{"$error":"#VALUE!"}},{"$f":"IF(H8=E6,(B8/2)*(1+E10%*I10/I6),IF(J9<0,0,((B8/2)*(1+E10%*J9/I6))))","v":4.2},{"$f":"IF(E6=H8,(B8*J8/J6),(IF(H12=0,+(B8*J8/J6),(B8*I9/J6))))","v":0}]
["INPUT ","Spot Price of Underlying",null,null,null,null,"(Pbond+AI0)x(1+rt)","-({c/2}x(1+rtdel))","-AIdel"]
["OUTPUT",null,null,null,null,null,{"$f":"G12-H12-I12","v":{"$error":"#VALUE!"}}]
["Spot Yield",{"$f":"IFERROR(YIELD(E6,B10,B8%,C13,100,2,4)*100,\"\")","v":""},null,null,null,null,{"$f":"IF(ISBLANK(C13),B21,C13)","v":""}]
["Price of the Futures",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C13)),\"\",IFERROR(IF(LEFT(B6,2)*1=91,\"Not Available\",(MROUND(G14,0.0025))),\"Invalid Input\"))","v":""},null,"Yield of the Futures",{"$f":"IFERROR(YIELD(E8,B10,B8%,B16,100,2,4)*100,\"\")","v":""}]
["(Based on Simple Interest)"]
[]
["INPUT ","Spot Yield of Underlying"]
["OUTPUT"]
["Spot Price",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C19)),\"\",(MROUND(PRICE(E6,B10,B8%,C19%,100,2,4),0.0025)))","v":""}]
["Price of the Futures",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C19)),\"\",IFERROR(IF(LEFT(B6,2)*1=91,\"Not Available\",(MROUND(G14,0.0025))),\"Invalid Input\"))","v":""},null,"Yield of the Futures",{"$f":"IFERROR(YIELD(E8,B10,B8%,B22,100,2,4)*100,\"\")","v":""}]
["(Based on Simple Interest)"]

{"sheet":"Pricing-Continuous","index":2,"n_rows":23,"n_cols":12,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,5],[3,1,3,3]]}
["INTEREST RATE FUTURES PRICING CALCULATOR (THEORETICAL)"]
[]
["INPUT ",null,null,null,null,null,null,null,null,"Gsec-AI"]
["Select futures contract","10-YEAR BenchMark Single Bond Futures",null,null,{"$f":"IFERROR(INDEX('[1]Futures Specs'!$K$1:$Q$18,MATCH(B4,'[1]Futures Specs'!$K$1:$K$15,0)+2,2),\"\")","v":""}]
[]
["Underlying Security","8.40% GOVT.STOCK 2024",null,"Settlement Date",null,null,null,"Day Count",365,360,"Special case","Modified Business Day"]
[null,null,null,"(dd-mm-yyyy)"]
["Coupon of Security",{"$f":"IFERROR(IF(LEFT(B6,2)*1=91,0,LEFT(B6,4)*1),\"\")","v":8.4},null,"Settlement Date(Fut)",null,null,"LIP",{"$f":"COUPPCD(E6,B10,2,4)","v":"00:00:00"},{"$f":"COUPDAYBS(E6,$B$10,2,4)","v":0},{"$f":"COUPDAYBS(E8,$B$10,2,4)","v":0},{"$f":"IF(DAYS360(H8,E6)=0,180,COUPDAYBS(E6,$B$10,2,4))","v":180},{"$f":"WORKDAY(H8-1,1,[1]Holidays!$A$1:$A$220)","v":{"$error":"#NUM!"}}]
[null,null,null,"(dd-mm-yyyy)",null,null,"NIP",{"$f":"COUPNCD(E6,B10,2,4)","v":"1900-01-28"},{"$f":"COUPDAYBS(E8,$B$10,2,4)","v":0},{"$f":"E8-L9","v":-30},null,{"$f":"WORKDAY(H9-1,1,[1]Holidays!$A$1:$A$220)","v":"1900-01-30"}]
["Maturity of Underlying",{"$f":"IFERROR(HLOOKUP(B6,'Futures Specs'!$A$1:$D$5,5,0),\"\")","v":"2024-07-28"},null,"Cost of Carry",null,null,"Settlement2Del",null,{"$f":"(E8-E6)","v":0}]
[]
[null,null,null,null,null,null,{"$f":"IF(E6=H8,(G15+(B8*K8/J6))*EXP(E10%*I10/I6),(G15+(B8*I8/J6))*EXP(E10%*I10/I6))","v":{"$error":"#VALUE!"}},{"$f":"IF(H8=E6,(B8/2)*EXP(E10%*I10/I6),IF(J9<0,0,((B8/2)*EXP(E10%*J9/I6))))","v":4.2},{"$f":"IF(E6=H8,(B8*J8/J6),(IF(H12=0,+(B8*J8/J6),(B8*I9/J6))))","v":0}]
["INPUT ","Spot Price of Underlying",null,null,null,null,"(Pbond+AI0)x(1+rt)","-({c/2}x(1+rtdel))","-AIdel"]
["OUTPUT",null,null,null,null,null,{"$f":"G12-H12-I12","v":{"$error":"#VALUE!"}}]
["Spot Yield",{"$f":"IFERROR(YIELD(E6,B10,B8%,C13,100,2,4)*100,\"\")","v":""},null,null,null,null,{"$f":"IF(ISBLANK(C13),B21,C13)","v":""}]
["Price of the Futures",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C13)),\"\",IFERROR(IF(LEFT(B6,2)*1=91,\"Not Available\",(MROUND(G14,0.0025))),\"Invalid Input\"))","v":""},null,"Yield of the Futures",{"$f":"IFERROR(YIELD(E8,B10,B8%,B16,100,2,4)*100,\"\")","v":""}]
["(Based on Continuous Compounding)"]
[]
["INPUT ","Spot Yield of Underlying"]
["OUTPUT"]
["Spot Price",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C19)),\"\",(MROUND(PRICE(E6,B10,B8%,C19%,100,2,4),0.0025)))","v":""}]
["Price of the Futures",{"$f":"IF(OR(ISBLANK(B6),ISBLANK(C19)),\"\",IFERROR(IF(LEFT(B6,2)*1=91,\"Not Available\",(MROUND(G14,0.0025))),\"Invalid Input\"))","v":""},null,"Yield of the Futures",{"$f":"IFERROR(YIELD(E8,B10,B8%,B22,100,2,4)*100,\"\")","v":""}]
["(Based on Continuous Compounding)"]

{"sheet":"Futures Specs","index":3,"n_rows":12,"n_cols":16,"rows_kept":12,"cut":false,"hidden":true,"merged":[]}
["DESC","91-DTB","8.40% GOVT.STOCK 2024","8.40% GOVT.STOCK 2024","Remarks",null,"Contracts",null,"Available Expiry Dates",null,"10-YEAR 8.97% Notional Bond Futures","Notional Coupon",8.9700000000000006]
["DSP Futures",0,0,0,"Not used",null,"91-DTB FUTURES",null,"2015-02-26",null,"ISIN","DESC","Mat","Coupon","Weights","CF"]
["ModDuration",0.25,6.59,6.89,"Not used",null,"10-YEAR BenchMark Single Bond Futures",null,"2015-03-26",null,"IN0020130012","7.16% GOVT.STOCK 2023","2023-05-20",7.16,0.4]
["Contract Size",2000,2000,2000,"Not used",null,"10-YEAR 8.97% Notional Bond Futures",null,"2015-04-30",null,"IN0020130061","8.83% GOVT.STOCK 2023","2023-11-25",8.83,0.3]
["Maturity","NA","2024-07-28","2024-07-28","Used in Bond Portfolio Sheet",null,null,null,"2015-05-28",null,"IN0020110048","9.15% GOVT.STOCK 2024","2024-11-14",9.15,0.3]
["Spot Price Underlying",0,90.793735993482841,90.793735993482841,"Not Used",null,null,null,"2015-06-25"]
["Conversion factor(Optional)",null,null,null,"Not Used",null,null,null,"2015-07-30"]
["ISIN",null,"IN0020130012","IN0020130061","Not used",null,null,null,null,null,"10-YEAR 9.00% Notional Bond Futures","Notional Coupon",9]
[null,null,null,null,null,null,null,null,null,null,"ISIN","DESC","Mat","Coupon","Weights","CF"]
[null,null,null,null,null,null,null,null,null,null,"IN0020130012","7.16% GOVT.STOCK 2023","2032-05-20",7.16,0.4]
[null,null,null,null,null,null,null,null,null,null,"IN0020130061","8.83% GOVT.STOCK 2023","2032-05-20",8.83,0.3]
[null,null,null,null,null,null,null,null,null,null,"IN0020110048","9.15% GOVT.STOCK 2024","2032-05-20",9.15,0.3]

{"sheet":"Holidays","index":4,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":true,"merged":[]}
["2015-01-26",null,null,"Last Updated","2015-02-06"]
["2015-02-17"]
["2015-02-19"]
["2015-03-06"]
["2015-04-01"]
["2015-04-02"]
["2015-04-03"]
["2015-04-14"]
["2015-05-01"]
["2015-05-04"]
["2015-08-18"]
["2015-09-17"]
["2015-09-25"]
["2015-10-02"]
["2015-10-22"]
["2015-11-11"]
["2015-11-12"]
["2015-11-25"]
["2015-12-24"]
["2015-12-25"]

{"sheet":"Basket_hedge_work","index":5,"n_rows":32,"n_cols":12,"rows_kept":32,"cut":false,"hidden":true,"merged":[]}
["Settlement Date(Spot)",null,{"$f":"#REF!","v":{"$error":"#REF!"}}]
["Settlement Date(Fut)",null,{"$f":"#REF!","v":{"$error":"#REF!"}}]
["Notional Basket Fut",null,{"$f":"#REF!","v":{"$error":"#REF!"}}]
["Notional Coupon",null,{"$f":"VLOOKUP(C3,'Futures Specs'!$K$1:$M$100,3,0)","v":{"$error":"#REF!"}}]
["Yield Change",null,1]
["Underlying","ISIN","Maturity","Coupon","SPOT PRICE","Yield of the Underlying","Mduration of Underlyin","Conversion Factor of Underlyin","Weights","Price(fut)@1%Up","Price(fut)@1%Down","1/CF"]
[{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+2,2),\"\"))","v":""},{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+2,1),\"\"))","v":""},{"$f":"VLOOKUP(A7,'Futures Specs'!$L$3:$Q$5,2,0)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(A7,'Futures Specs'!$L$3:$Q$5,3,0)","v":{"$error":"#N/A"}},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"YIELD($C$1,C7,D7%,E7,100,4)*100","v":{"$error":"#REF!"}},{"$f":"MDURATION($C$1,C7,D7%,F7%,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C7,D7%,$C$4%,100,2,4)/100","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(A7,'Futures Specs'!$L$3:$Q$5,4,0)","v":{"$error":"#N/A"}},{"$f":"PRICE($C$2,C7,D7%,(F7+$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C7,D7%,(F7-$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$f":"1/H7","v":{"$error":"#REF!"}}]
[{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+3,2),\"\"))","v":""},{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+3,1),\"\"))","v":""},{"$f":"VLOOKUP(A8,'Futures Specs'!$L$3:$Q$5,2,0)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(A8,'Futures Specs'!$L$3:$Q$5,3,0)","v":{"$error":"#N/A"}},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"YIELD($C$1,C8,D8%,E8,100,4)*100","v":{"$error":"#REF!"}},{"$f":"MDURATION($C$1,C8,D8%,F8%,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C8,D8%,$C$4%,100,2,4)/100","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(A8,'Futures Specs'!$L$3:$Q$5,4,0)","v":{"$error":"#N/A"}},{"$f":"PRICE($C$2,C8,D8%,(F8+$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C8,D8%,(F8-$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$f":"1/H8","v":{"$error":"#REF!"}}]
[{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+4,2),\"\"))","v":""},{"$f":"(IFERROR(INDEX('Futures Specs'!$K$1:$Q$18,MATCH(C3,'Futures Specs'!$K$1:$K$15,0)+4,1),\"\"))","v":""},{"$f":"VLOOKUP(A9,'Futures Specs'!$L$3:$Q$5,2,0)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(A9,'Futures Specs'!$L$3:$Q$5,3,0)","v":{"$error":"#N/A"}},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"YIELD($C$1,C9,D9%,E9,100,4)*100","v":{"$error":"#REF!"}},{"$f":"MDURATION($C$1,C9,D9%,F9%,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C9,D9%,$C$4%,100,2,4)/100","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(A9,'Futures Specs'!$L$3:$Q$5,4,0)","v":{"$error":"#N/A"}},{"$f":"PRICE($C$2,C9,D9%,(F9+$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$f":"PRICE($C$2,C9,D9%,(F9-$C$5)%,100,2,4)","v":{"$error":"#REF!"}},{"$error":"#REF!"}]
[]
[]
[]
[]
["Futures Value",null,null,null,"Portfolio Value"]
["Modified Duration (Futures)",null,{"$f":"SUMPRODUCT($G$7:$G$9,$I$7:$I$9)","v":{"$error":"#REF!"}},null,"Modified Duration Portfolio",null,{"$f":"IFERROR(SUMPRODUCT(OFFSET(#REF!,#REF!,0,-#REF!,1),OFFSET(#REF!,#REF!,0,-#REF!,1)),\"\")","v":""}]
[]
["Initial Futures Price",null,{"$f":"#REF!","v":{"$error":"#REF!"}},null,"Initial Value@Vo wid CF",null,{"$f":"IFERROR(SUMPRODUCT(OFFSET(#REF!,#REF!,0,-#REF!,1),OFFSET(#REF!,#REF!,0,-#REF!,1),OFFSET(#REF!,#REF!,0,-#REF!,1)),\"\")","v":""}]
[null,null,null,null,"portfolio Value @V-",null,{"$f":"SUMPRODUCT(#REF!,#REF!)","v":{"$error":"#REF!"}}]
["Futures Price @V-",null,{"$f":"SUMPRODUCT($I$7:$I$9,$K$7:$K$9,$L$7:$L$9)","v":{"$error":"#N/A"}}]
[null,null,null,null,"portfolio Value @V+",null,{"$f":"SUMPRODUCT(#REF!,#REF!)","v":{"$error":"#REF!"}}]
["Futures Price @V+",null,{"$f":"SUMPRODUCT($I$7:$I$9,$J$7:$J$9,$L$7:$L$9)","v":{"$error":"#N/A"}}]
[null,null,null,null,"Initial Value@Vo",null,{"$f":"IFERROR(SUMPRODUCT(OFFSET(#REF!,#REF!,0,-#REF!,1),OFFSET(#REF!,#REF!,0,-#REF!,1)),\"\")","v":""}]
["# of contracts",null,{"$f":"(G22*G15)/(C17*2000*C15)","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Profit/Loss(Bond)@1% Up",null,{"$f":"IFERROR((G20-G22),\"\")","v":""}]
["Initial Fut Value@Vo",null,{"$f":"C23*C17*2000","v":{"$error":"#VALUE!"}},null,"Profit/Loss(Bond)@1% Down",null,{"$f":"IFERROR(G18-G22,0)","v":0}]
[]
["Futures Portfolio Value @V+",null,{"$f":"C23*C21*2000","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Effectiveness @ 1% Up",null,{"$f":"-(C31/G24)*100","v":{"$error":"#VALUE!"}}]
["Futures Portfolio Value @V-",null,{"$f":"C23*C19*2000","v":{"$error":"#VALUE!"}}]
[null,null,null,null,"Effectiveness @ 1% Down",null,{"$f":"-(C32/G25)*100","v":{"$error":"#VALUE!"}}]
["Profit/Loss(Fut)@1% Up",null,{"$f":"C25-C27","v":{"$error":"#VALUE!"}}]
["Profit/Loss(Fut)@1% Down",null,{"$f":"C25-C29","v":{"$error":"#VALUE!"}}]