{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Model"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":20,"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":"Model","index":0,"n_rows":27,"n_cols":3,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["mydate",{"$f":"TODAY()","v":"2019-06-20"}]
[]
["coupon",0.03]
["P",0.6,"leverage"]
["tau",0.3,"effective tax advantage of debt"]
["alpha",0.3,"bankruptcy costs"]
["r",0.04,"risk free"]
["d",0.02,"dividend/firm value"]
["t",5]
["assetv",0.1]
["recovery",0.6]
["Vb",{"$f":"Defaultboundary(c_,t,alpha,delta,assetv,tau,P,r_)","v":0.56815162669684549}]
["mu",{"$f":"r_","v":0.04},"set to r (mu = r + risk premium, which we assume here is zero)"]
["v",{"$f":"Equityvol(assetv,P)","v":0.25},"equity vol"]
["equity div",{"$f":"d/(1-P)","v":4.9999999999999996E-2},"actual dividend"]
["c",{"$f":"B3*P","v":1.7999999999999999E-2},"coupon/firm value"]
["V0",1]
["b",{"$f":"LN(V0/Vb)","v":0.56536694747543348},"moneyness"]
["delta",{"$f":"c_+d","v":3.7999999999999999E-2},"payout %"]
["pd",{"$f":"EDF(t,b,mu,delta,assetv)","v":1.3551742436418771E-2}]
["bond value",{"$f":"BondfromEDF(B20,recovery,r_,t,B3)","v":0.93172217925324463}]
["bond ytm",{"$f":"YIELD(mydate,mydate+365*t,B3,B21*100,100,4)","v":4.5354330093158687E-2},"Uses the Excel Yield function (flaky) to calculate bond yield from bond price, term and coupon"]
["spread",{"$f":"B22-r_","v":5.3543300931586865E-3},"spread"]
["bp",{"$f":"B23*10000","v":53.543300931586863},"spread in basis points"]
["Corr",0.15,"Set to 15% by Basel"]
["Conf",0.999,"Set by Basel"]
["Capital requirement",{"$f":"(NORMSDIST(SQRT(1/(1-Corr))*NORMSINV(B20)+SQRT(Corr/(1-Corr))*NORMSINV(Conf))-B20)*(1-recovery)","v":4.8937230242535629E-2},"This is the Basel IRB formula used to set capital requirement"]