{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Accueil","Bordereaux","Coupons","Journal","Rendement"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":6,"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":"Accueil","index":0,"n_rows":34,"n_cols":3,"rows_kept":34,"cut":false,"hidden":false,"merged":[[5,2,5,3],[3,2,3,3],[4,2,4,3],[12,2,12,3],[10,2,10,3],[7,2,7,3],[8,2,8,3]]}
[]
[]
[]
[null,null,"Achat et vente de titres"]
[null,null,"Encaissement de coupons"]
[null,null,"Calcul de rendement"]
[]
[null,null,"Bordereaux automatiques"]
[null,null,"à compléter"]
[]
[null,null,"Accès par les onglets ci-dessous"]
[]
[null,null,"Compléter les zones claires"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"© Yvan Péguiron - HEP-Lausanne - 2006"]

{"sheet":"Bordereaux","index":1,"n_rows":47,"n_cols":18,"rows_kept":47,"cut":false,"hidden":false,"merged":[[20,6,20,7],[44,6,44,7],[30,6,30,7]]}
[]
[null,{"$f":"IF(N6=1,\"Bordereau d'achat d'actions\",\"Bordereau de vente d'actions\")","v":"Bordereau de vente d'actions"}]
[]
[null,null,null,null,null,null,null,"Lausanne",null,"2003-01-20",null,null,null,null,null,"Titre","Choix"]
[]
[null,50,{"$f":"IF(B6>1,\"actions\",\"action\")","v":"actions"},null,"porteur",null,null,null,null,null,null,{"$f":"IF(LEFT(E6,3)=\"nom\",\"nom\",\"por\")","v":"por"},"achat",2,{"$f":"IF(N6=1,\"Achat de \",\"Vente de \")","v":"Vente de "},null,1]
[null,null,"Nestlé",null,null,null,null,null,null,null,null,null,"vente",1,{"$f":"IF(N7=1,\"Achat de \",\"Vente de \")","v":"Achat de "},{"$f":"IF(N7=1,J34+J43,J34-J43)","v":25857.65},0]
[null,null,"de","CHF",10," de valeur nominale",null,null,{"$f":"B6*E8","v":500}]
[]
[null,null,"au cours de",null,null,384.75,null,null,null,{"$f":"F10*B6","v":19237.5}]
[null,null,"Non-libéré",null,null,0,"%",null,null,{"$f":"(E8/100*F11)*B6","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Valeur effective : ",null,null,null,null,null,null,{"$f":"J10-J11","v":19237.5}]
[]
[null,{"$f":"IF(N6=1,\"+\",\"-\")","v":"-"},"Frais:"]
[null,null,"Courtage",null,null,{"$f":"O16","v":1.1000000000000001},"o/o  sur",{"$f":"P16","v":19237.5},211.6,null,null,{"$f":"P16/100*O16","v":211.61250000000001},{"$f":"IF(AND((J13<100),(L16<5)),5,IF(L16<10,10,L16))","v":211.61250000000001},null,{"$f":"VLOOKUP(J10,Tarif,$M$18)","v":1.1000000000000001},{"$f":"J10","v":19237.5},{"$f":"IF(R16>80,R16,80)","v":211.6},{"$f":"ROUND(P16*O16/100*2,1)/2","v":211.6}]
[null,null,"Timbre fédéral",null,null,{"$f":"O17","v":0.75},"o/oo sur",19237.5,14.45,null,null,{"$f":"P17/1000*O17","v":14.428125000000001},null,null,{"$f":"IF(D8<>\"CHF\",1.5,0.75)","v":0.75},{"$f":"J13","v":19237.5},{"$f":"INT(((L17-0.0001)*20)+1)/20","v":14.45}]
[null,null,"Taxe de bourse",null,null,{"$f":"O18","v":0.1},"o/oo sur",20000,2,null,null,"Action",2,null,0.1,{"$f":"INT(((J13-0.0001)/1000)+1)*1000","v":20000},{"$f":"ROUND(P18*O18/1000,1)","v":2}]
[null,null,{"$f":"IF(L6=\"nom\",\"Transfert\",\"\")","v":""},null,null,{"$f":"O19","v":0},{"$f":"IF(L6=\"nom\",\"fr x\",0)","v":0},0,0,{"$f":"SUM(I16:I19)","v":228.04999999999998},null,0,1.1000000000000001,0.8,{"$f":"IF(L6=\"nom\",LOOKUP(F10,L19:L23,M19:M23),0)","v":0},{"$f":"IF(L6=\"nom\",B6,0)","v":0},{"$f":"O19*P19","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,50001,1,0.7]
[null,null,"Valeur",null,{"$f":"J4","v":"2003-01-20"},null,{"$f":"IF(N6=1,\"A votre débit\",\"A votre crédit\")","v":"A votre crédit"},null,null,{"$f":"IF(N6=1,SUM(J13:J19),J13-J19)","v":19009.45},null,100001,0.9,0.6]
[null,null,null,null,null,null,null,null,null,null,null,150001,0.8,0.5,true,false]
[]
[]
[]
[null,{"$f":"IF(N7=1,\"Bordereau d'achat d'obligations\",\"Bordereau de vente d'obligations\")","v":"Bordereau d'achat d'obligations"},null,null,null,null,null,null,null,null,null,200001,0.7,0.4]
[null,null,null,null,null,null,null,null,null,null,null,250001,0.5,0.35]
[null,null,null,null,null,null,null,"Zurich",null,"2006-03-13",null,1000001,0.2,0.2]
[]
[null,5,{"$f":"IF(B30>1,\"obligations\",\"obligation\")","v":"obligations"},null,null,3.5,"% ",null,null,null,null,"Oblig.",3]
[null,null,"Ville de Lausanne",null,null,"Echéance :","2008-06-15"]
[null,null,"de","CHF",5000," de valeur nominale",null,null,{"$f":"B30*E32","v":25000}]
[]
[null,null,"au cours de",null,null,102.5,"%",null,null,{"$f":"I32*F34/100","v":25625},null,{"$f":"DAYS360(G31,J28)","v":-812}]
[null,null,"Intérêt couru",null,null,"pdt",{"$f":"MOD(L34,360)","v":268},"jours",null,{"$f":"ROUND(I32*F30*G35/36000*2,1)/2","v":651.4}]
[]
[null,null,"Valeur effective : ",null,null,null,null,null,null,{"$f":"SUM(J34:J35)","v":26276.400000000001}]
[]
[null,{"$f":"IF(N7=1,\"+\",\"-\")","v":"+"},"Frais:"]
[null,null,"Courtage",null,null,{"$f":"O40","v":0.8},"o/o  sur",{"$f":"P40","v":26276.400000000001},210.2,null,null,{"$f":"P40/100*O40","v":210.21120000000002},{"$f":"IF(AND((J37<100),(L40<5)),5,IF(L40<10,10,L40))","v":210.21120000000002},null,{"$f":"VLOOKUP(J37,Tarif,$M$30)","v":0.8},{"$f":"J37","v":26276.400000000001},{"$f":"IF(R40>80,R40,80)","v":210.2},{"$f":"ROUND(P40*O40/100*2,1)/2","v":210.2}]
[null,null,"Timbre fédéral",null,null,{"$f":"O41","v":0.75},"o/oo sur",26276.400000000001,19.75,null,null,{"$f":"P41/1000*O41","v":19.707300000000004},null,null,{"$f":"IF(D32<>\"CHF\",1.5,0.75)","v":0.75},{"$f":"J37","v":26276.400000000001},{"$f":"INT(((L41-0.0001)*20)+1)/20","v":19.75}]
[null,null,"Taxe de bourse",null,null,{"$f":"O42","v":0.1},"o/oo sur",27000,2.7,null,null,null,null,null,0.1,{"$f":"INT(((J37-0.0001)/1000)+1)*1000","v":27000},{"$f":"P42*O42/1000","v":2.7}]
[null,null,{"$f":"IF(L30=\"nom\",\"Transfert\",\"\")","v":""},null,null,{"$f":"O43","v":0},null,0,0,{"$f":"SUM(I40:I43)","v":232.64999999999998},null,0,0.05]
[null,null,null,null,null,null,null,null,null,null,null,50,0.1]
[null,null,"Valeur",null,{"$f":"J28","v":"2006-03-13"},null,{"$f":"IF(N7=1,\"A votre débit\",\"A votre crédit\")","v":"A votre débit"},null,null,{"$f":"IF(N7=1,SUM(J37:J43),J37-J43)","v":26509.050000000003},null,100,0.5]
[null,null,null,null,null,null,null,null,null,null,null,500,1,null,true,false]
[null,null,null,null,null,null,null,null,null,null,null,1000000000,1]

{"sheet":"Coupons","index":2,"n_rows":12,"n_cols":11,"rows_kept":12,"cut":false,"hidden":false,"merged":[[11,5,11,6],[4,4,5,4],[4,9,5,9],[4,1,5,1],[4,10,5,10],[4,6,5,6],[4,5,5,5],[4,2,5,2],[4,3,5,3],[4,7,5,7],[4,8,5,8]]}
[]
[]
[null,"Encaissement de coupons"]
[]
[null,"Nb. de coupons","Echéance","Désignation des valeurs","Capital ou dividende","Capital ou dividende total","Taux en % pour oblig.","Montant brut","Impôt fédéral","Frais et commission","NET CHF"]
[]
[null,2,"2006-04-18","Obligation",2000,{"$f":"B7*E7","v":4000},5.5,{"$f":"IF(G7=0,F7,ROUND(F7*G7*2/100,1)/2)","v":220},{"$f":"ROUND(H7*0.35*2,1)/2","v":77},null,{"$f":"H7-I7-J7","v":143}]
[null,10,"2006-04-20","Actions",46,{"$f":"B8*E8","v":460},null,{"$f":"IF(G8=0,F8,ROUND(F8*G8*2/100,1)/2)","v":460},{"$f":"ROUND(H8*0.35*2,1)/2","v":161},null,{"$f":"H8-I8-J8","v":299}]
[null,null,null,null,null,{"$f":"B9*E9","v":0},null,{"$f":"ROUND(F9*G9*2/100,1)/2","v":0},{"$f":"ROUND(H9*0.35*2,1)/2","v":0},null,{"$f":"H9-I9-J9","v":0}]
[null,null,null,null,null,{"$f":"B10*E10","v":0},null,{"$f":"ROUND(F10*G10*2/100,1)/2","v":0},{"$f":"ROUND(H10*0.35*2,1)/2","v":0},null,{"$f":"H10-I10-J10","v":0}]
[null,null,null,null,null,{"$f":"B11*E11","v":0},null,{"$f":"ROUND(F11*G11*2/100,1)/2","v":0},{"$f":"ROUND(H11*0.35*2,1)/2","v":0},null,{"$f":"H11-I11-J11","v":0}]
[null,null,null,null,null,"Totaux ",null,{"$f":"SUM(H7:H11)","v":680},{"$f":"SUM(I7:I11)","v":238},{"$f":"SUM(J7:J11)","v":0},{"$f":"SUM(K7:K11)","v":442}]

{"sheet":"Journal","index":3,"n_rows":13,"n_cols":11,"rows_kept":13,"cut":false,"hidden":false,"merged":[[8,2,8,3],[8,4,8,5],[6,2,6,3],[9,4,9,5],[9,2,9,3],[6,4,6,5],[7,2,7,3],[7,4,7,5],[4,2,4,3],[4,4,4,5],[13,2,13,3],[13,4,13,5],[10,2,10,3],[10,4,10,5],[11,2,11,3],[11,4,11,5],[12,2,12,3],[12,4,12,5],[5,2,5,3],[5,4,5,5],[0,1,0,8],[1,1,1,8],[2,1,3,1],[2,2,2,5],[2,6,3,6],[2,7,2,8],[3,2,3,3],[3,4,3,5]]}
[null,"Journal des bordereaux actifs de ce classeur"]
[null,"(Ne rien entrer dans ce journal)"]
[null,"Date","Compte à",null,null,null,"Libellé","Sommes à"]
[null,null,"débiter",null,"créditer",null,null,"débiter","créditer"]
[null,{"$f":"Bordereaux!J4","v":"2003-01-20"},{"$f":"IF(Bordereaux!N6=1,\" 1060 Titres\",\" 1020 Banque\")","v":" 1020 Banque"},null,{"$f":"IF(Bordereaux!N6=1,\" 1020 Banque\",\" 1060 Titres\")","v":" 1060 Titres"},null,{"$f":"Bordereaux!O6&Bordereaux!B6&\" \"&Bordereaux!C6&\" \"&Bordereaux!C7&\" à \"&Bordereaux!F10","v":"Vente de 50 actions Nestlé à 384.75"},{"$f":"Bordereaux!J21","v":19009.45},{"$f":"H5","v":19009.45},null,{"$f":"Bordereaux!Q6","v":1}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"Bordereaux!Q7","v":0}]
[null,{"$f":"Bordereaux!J28","v":"2006-03-13"},{"$f":"IF(Bordereaux!N7=1,\"\",\" 1020 Banque\")","v":""},null,{"$f":"IF(Bordereaux!N7=1,\" 1020 Banque\",\"\")","v":" 1020 Banque"},null,{"$f":"Bordereaux!O7&\" \"&Bordereaux!B30&\" \"&Bordereaux!C30&\" \"&Bordereaux!C31&\" \"&Bordereaux!F30&\"% - nom. \"&Bordereaux!E32&\" à \"&Bordereaux!F34","v":"Achat de  5 obligations Ville de Lausanne 3.5% - nom. 5000 à 102.5"},{"$f":"IF(C8=\"\",SUM(I8:I9),\"\")","v":""},{"$f":"IF(C8=\"\",\"\",SUM(H8:H9))","v":26509.050000000003},null,{"$f":"IF(Coupons!B7=0,0,1)","v":1}]
[null,null,{"$f":"IF(Bordereaux!N7=1,\" 1060 Titres\",\"\")","v":" 1060 Titres"},null,{"$f":"IF(Bordereaux!N7=1,\"\",\" 1060 Titres\")","v":""},null,{"$f":"\"      \"&Bordereaux!B30&\" x \"&Bordereaux!C31&\" à \"&Bordereaux!F34","v":"      5 x Ville de Lausanne à 102.5"},{"$f":"IF(C8=\"\",\"\",Bordereaux!P7)","v":25857.65},{"$f":"IF(C8=\"\",Bordereaux!P7,\"\")","v":""}]
[null,null,{"$f":"IF(Bordereaux!N7=1,\" 7400 Prod. des titres\",\"\")","v":" 7400 Prod. des titres"},null,{"$f":"IF(Bordereaux!N7=1,\"\",\" 7400 Prod. des titres\")","v":""},null,{"$f":"\"      Intérêt couru pendant \"&Bordereaux!G35&\" jours\"","v":"      Intérêt couru pendant 268 jours"},{"$f":"IF(C8=\"\",\"\",Bordereaux!J35)","v":651.4},{"$f":"IF(C9=\"\",Bordereaux!J35,\"\")","v":""}]
[]
[null,{"$f":"Coupons!C7","v":"2006-04-18"}," 1020 Banque",null,null,null,{"$f":"\"Coupons \"&Coupons!D7&\" - \"&Coupons!D8&\", net\"","v":"Coupons Obligation - Actions, net"},{"$f":"Coupons!K12","v":442}]
[null,null," 1176 Créance AFC",null,null,null,"Impôt anticipé 35%",{"$f":"Coupons!I12","v":238}]
[null,null,null,null," 7400 Prod. des titres",null,{"$f":"\"Coupons \"&Coupons!D7&\" - \"&Coupons!D8&\", brut\"","v":"Coupons Obligation - Actions, brut"},null,{"$f":"Coupons!H12","v":680}]

{"sheet":"Rendement","index":4,"n_rows":87,"n_cols":12,"rows_kept":87,"cut":false,"hidden":false,"merged":[[84,5,84,6],[86,5,86,8],[80,1,80,4],[80,6,80,7],[81,1,81,4],[81,6,81,7],[6,6,6,7],[40,6,40,7],[41,6,41,7],[43,6,43,7],[15,6,15,7],[35,6,35,7],[1,1,1,3],[39,6,39,7],[49,5,49,6],[51,5,51,8],[41,1,41,4],[39,1,39,4],[40,1,40,4],[43,1,43,4],[44,1,44,4],[47,1,47,4],[62,1,62,4],[62,6,62,7],[63,1,63,4],[63,6,63,7],[45,1,45,3],[42,6,42,7],[61,1,61,4],[61,6,61,7],[47,6,47,7],[44,6,44,7],[73,5,73,8],[67,1,67,3],[69,1,69,4],[69,6,69,7],[71,5,71,6],[64,6,64,7],[65,1,65,4],[65,6,65,7],[66,1,66,4],[66,6,66,7]]}
[]
[null,"Bordereau d'achat"]
[]
[null,null,null,null,null,null,null,"Zurich",null,"1999-02-01",{"$f":"DAYS360($G$7,J4)","v":-4456}]
[]
[null,25,null,null,null,5,"% "]
[null,null,"Confédération",null,null,"Echéance :","2011-06-17"]
[null,null,"de","CHF",500," de valeur nominale",null,null,{"$f":"B6*E8","v":12500},null,1,"action "]
[null,null,null,null,null,null,null,null,null,null,2,"actions"]
[null,null,"au prix unitaire de",null,null,325,".-",null,null,{"$f":"F10*B6","v":8125},3,"obligation"]
[null,null,"Intérêt couru pour les obligations",null,null,"pdt",{"$f":"IF(F6=0,0,MOD(K4,360))","v":224},"jours",null,{"$f":"ROUND(I8*F6*G11/36000*2,1)/2","v":388.9},4,"obligations"]
[]
[null,null,"Valeur effective : ",null,null,null,null,null,null,{"$f":"SUM(J10:J11)","v":8513.9},null,4]
[]
[null,"+","Frais:",null,null,null,null,null,164.45,{"$f":"I15","v":164.45}]
[null,null,"Valeur",null,{"$f":"J4","v":"1999-02-01"},null,"A votre débit",null,null,{"$f":"J13+J15","v":8678.35}]
[]
[]
[]
[]
[]
[null,"Bordereau de vente"]
[]
[null,null,null,null,null,null,null,"Zurich",null,"2002-09-18",{"$f":"DAYS360($G$7,J24)","v":-3149}]
[]
[null,{"$f":"B6","v":25},{"$f":"VLOOKUP(L13,K8:L11,2)","v":"obligations"},null,null,{"$f":"F6","v":5},"% "]
[null,null,{"$f":"C7","v":"Confédération"}]
[null,null,"de",{"$f":"D8","v":"CHF"},{"$f":"E8","v":500}," de valeur nominale",null,null,{"$f":"B26*E28","v":12500}]
[]
[null,null,"au prix unitaire de",null,null,435,".-",null,null,{"$f":"F30*B26","v":10875}]
[null,null,"Intérêt couru pour les obligations",null,null,"pdt",{"$f":"IF(F26=0,0,MOD(K24,360))","v":91},"jours",null,{"$f":"ROUND(I28*F26*G31/36000*2,1)/2","v":158}]
[]
[null,null,"Valeur effective : ",null,null,null,null,null,null,{"$f":"SUM(J30:J31)","v":11033}]
[]
[null,{"$f":"IF(N7=1,\"+\",\"-\")","v":"-"},"Frais:",null,null,null,null,null,203.9,{"$f":"I35","v":203.9}]
[null,null,"Valeur",null,{"$f":"J24","v":"2002-09-18"},null,{"$f":"IF(N7=1,\"A votre débit\",\"A votre crédit\")","v":"A votre crédit"},null,null,{"$f":"J33-J35","v":10829.1}]
[]
[]
[]
[null,"Capital engagé sans IC",null,null,null,null,{"$f":"J10+J15","v":8289.4500000000007}]
[null,"Capital récupéré",null,null,null,null,{"$f":"J30-J35","v":10671.1}]
[null,{"$f":"IF(G42=0,\"\",IF(G42>0,\"Gain\",\"Perte\"))","v":"Gain"},null,null,null,null,{"$f":"G41-G40","v":2381.6499999999996}]
[null,"Dividendes encaissés ou"]
[null,"Intérêts",null,null,null,null,{"$f":"I8*F6*E46/36000","v":2269.0972222222222}]
[null,"Revenu total",null,null,null,null,{"$f":"G42+G43+G44","v":4650.7472222222223}]
[null,"Durée",null,null,{"$f":"DAYS360(J4,J24)","v":1307},"jours"]
[]
[null,"Revenu annuel",null,null,null,null,{"$f":"G45/E46*360","v":1281.0015302218821}]
[]
[null,null,null,null,null,{"$f":"G48","v":1281.0015302218821},null," x",100]
[null,"Taux de rendement annuel  :",null,null,null,null,null,null,null,"         =",{"$f":"F50*I50/F52","v":15.453395945712707}," %"]
[null,null,null,null,null,{"$f":"G40","v":8289.4500000000007}]
[]
[]
[]
[]
[]
[]
[]
[null,"Calcul sans bordereau"]
[]
[null,"Capital engagé sans IC",null,null,null,null,100]
[null,"Capital récupéré",null,null,null,null,90]
[null,{"$f":"IF(G64=0,\"\",IF(G64>0,\"Gain\",\"Perte\"))","v":"Perte"},null,null,null,null,{"$f":"G63-G62","v":-10}]
[null,"Dividendes encaissés ou"]
[null,"Intérêts",null,null,null,null,20]
[null,"Revenu total",null,null,null,null,{"$f":"G64+G65+G66","v":10}]
[null,"Durée",null,null,360,"jours"]
[]
[null,"Revenu annuel",null,null,null,null,{"$f":"G67/E68*360","v":10}]
[]
[null,null,null,null,null,{"$f":"G70","v":10},null," x",100]
[null,"Taux de rendement annuel  :",null,null,null,null,null,null,null,"         =",{"$f":"F72*I72/F74","v":10}," %"]
[null,null,null,null,null,{"$f":"G62","v":100}]
[]
[]
[]
[]
[null,"Taux de rendement direct"]
[]
[null,"Capital engagé sans IC",null,null,null,null,120]
[null,"Intérêt annuel ou dividende",null,null,null,null,5]
[]
[]
[null,null,null,null,null,{"$f":"G82","v":5},null," x",100]
[null,"Taux de rendement direct  :",null,null,null,null,null,null,null,"         =",{"$f":"F85*I85/F87","v":4.166666666666667}," %"]
[null,null,null,null,null,{"$f":"G81","v":120}]