{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Enoncé","Calcul Rendement","Age_Volat","Calcul"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"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":"Enoncé","index":0,"n_rows":21,"n_cols":15,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,0,3],[22,0,22,1]]}
["Evaluation au 01/9/21"]
[]
[null,"Taux coupon","Échéance","RBT","Prix","Rdt",null,null,"QUESTIONS :"]
[1,2.9000000000000001E-2,"2037-09-01","IN FINE",1.0038,"?",null,null,null,null,"Calculer le rendement actuariel des titres "]
[2,null,"2065-09-01","ZC","?",4.0099999999999997E-2,null,null,null,null,"Automatiser la procédure avec un pricer (affichage des flux de paiement)"]
[3,"?","2041-09-01","AC",1.1356999999999999,3.6400000000000002E-2,null,null,null,null,"Automatiser en écrivant des macro fonctions  ( x inputs --> 1 output scalaire)"]
[4,3.5999999999999997E-2,"2025-09-01","SEA","?",1.89E-2,null,null,null,null,"Calculer les mesures d'âge et de volatilité (en macro fonction ou en VBA)"]
[5,5.2999999999999999E-2,"2033-09-01","IN FINE","?",2.53E-2,null,null,null,null,null,"AGE",null,null,"VOLATILITE"]
[6,3.2000000000000001E-2,"2032-09-01","AC",1.0403,"?",null,null,null,null,null,"Maturité",null,null,"Variabilité"]
[7,4.8000000000000001E-2,"2026-09-01","AC","?",1.9800000000000002E-2,null,null,null,null,null,"Vie Probable",null,null,"Sensibilié"]
[8,0.05,"?","IN FINE",1.2055,2.4299999999999999E-2,null,null,null,null,null,"Délai de récupération",null,null,"Elasticité"]
[9,3.3000000000000002E-2,null,"RP",0.75690000000000002,"?",null,null,null,null,null,"Vie Moyenne",null,null,"Convexité"]
[10,0.06,"2057-09-01","IN FINE",1.4100999999999999,"?",null,null,null,null,null,"Vie Moyenne Pondérée",null,null,"Duration 2"]
[11,null,"2029-09-01","ZC","?",2.2499999999999999E-2,null,null,null,null,null,"Échéance Moyenne"]
[12,"?","2043-09-01","IN FINE",1.0373000000000001,3.2599999999999997E-2,null,null,null,null,null,"Duration Macaulay"]
[13,4.2999999999999997E-2,"2036-09-01","SEA",1.1121000000000001,"?"]
[14,null,"2050-09-01","ZC",0.36359999999999998,"?"]
[15,null,"?","ZC",0.71630000000000005,2.5999999999999999E-2]
[16,"?",null,"RP",0.50229999999999997,4.3799999999999999E-2]
[17,null,"2025-09-01","ZC",0.92569999999999997,"?"]
[18,4.8500000000000001E-2,"2024-09-01","IN FINE","?",1.8200000000000001E-2]

{"sheet":"Calcul Rendement","index":1,"n_rows":146,"n_cols":18,"rows_kept":146,"cut":false,"hidden":false,"merged":[[0,10,0,14],[0,16,0,18],[0,0,0,3]]}
["Evaluation au 01/09/21",null,null,null,null,null,null,null,"méthode 1",null,"méthode 2                                    Génération automatique des flux",null,null,null,null,null,"Méthode 3                                            sans les flux"]
[]
["2021-09-01","Taux coupon","Échéance","RBT","Prix","N","Rdt",null,"Calcul Rdt classique IF",null,null,null,null,0,{"$f":"L7","v":"IN FINE"}]
[1,2.9000000000000001E-2,"2037-09-01","IN FINE",1.0038,16,2.87E-2,null,{"$f":"E4","v":1.0038},null,null,null,null,1,{"$f":"IF($O$3=\"RP\",-$L$9,IF(N4>$L$10,\"\",IF($O$3=\"IN FINE\",-$L$9-IF(N4=$L$10,1,0),IF($O$3=\"AC\",-$L$9/(1-(1+$L$9)^(-$L$10)),IF($O$3=\"SEA\",-1/$L$10-$L$9*(1-N3/$L$10),IF($O$3=\"ZC\",IF(N4=$L$10,1,\"\"),\"erreur\"))))))","v":-2.9000000000000001E-2}]
[2,null,"2065-09-01","ZC",{"$f":"C69","v":0.17729470258849495},44,4.0099999999999997E-2,null,{"$f":"-B4","v":-2.9000000000000001E-2},1,"Numéro",1,null,2,{"$f":"IF($O$3=\"RP\",-$L$9,IF(N5>$L$10,\"\",IF($O$3=\"IN FINE\",-$L$9-IF(N5=$L$10,1,0),IF($O$3=\"AC\",-$L$9/(1-(1+$L$9)^(-$L$10)),IF($O$3=\"SEA\",-1/$L$10-$L$9*(1-N4/$L$10),IF($O$3=\"ZC\",IF(N5=$L$10,1,\"\"),\"erreur\"))))))","v":-2.9000000000000001E-2}]
[3,{"$f":"C86","v":5.0982579452875543E-2},"2041-09-01","AC",1.1356999999999999,20,3.6400000000000002E-2,null,{"$f":"I5","v":-2.9000000000000001E-2},2,null,null,null,3,-2.9000000000000001E-2,null,"=R_IF( prix ; coupon ; durée )"]
[4,3.5999999999999997E-2,"2025-09-01","SEA",{"$f":"C95","v":1.0411859259074485},{"$f":"F91","v":4},1.89E-2,null,{"$f":"I6","v":-2.9000000000000001E-2},3,"Type",{"$f":"INDEX(D4:D21,L5)","v":"IN FINE"},null,4,-2.9000000000000001E-2,null,null,{"$f":"rdt(E4,B4,F4,D4)","v":2.8699739074707027E-2}]
[5,5.2999999999999999E-2,"2033-09-01","IN FINE",{"$f":"C103","v":1.2836246431416711},12,2.53E-2,null,-2.9000000000000001E-2,4,"Prix",{"$f":"INDEX(E4:E21,L5)","v":1.0038},null,5,-2.9000000000000001E-2]
[6,3.2000000000000001E-2,"2032-09-01","AC",1.0403,11,2.4899999999999999E-2,null,-2.9000000000000001E-2,5,"coupon",{"$f":"IF(L7=\"ZC\",0,INDEX(B4:B21,L5))","v":2.9000000000000001E-2},null,6,-2.9000000000000001E-2,null,"Si la macro n'est pas activée,"]
[7,4.8000000000000001E-2,"2026-09-01","AC",{"$f":"D134","v":1.0833071390675482},5,1.9800000000000002E-2,null,-2.9000000000000001E-2,6,"Durée",{"$f":"IF(L7=\"RP\",0,YEAR(INDEX(C4:C21,L5))-YEAR(A3))","v":16},null,7,-2.9000000000000001E-2,null,"modifier les paramètres de sécurité"]
[8,0.05,{"$f":"D144","v":"2029-09-01"},"IN FINE",1.2055,9,2.4299999999999999E-2,null,-2.9000000000000001E-2,7,"Taux",{"$f":"IF(L7=\"ZC\",L8^(-1/L10)-1,IF(L7=\"RP\",L9/L8,IRR((L8,O4:INDEX($O$4:$O$63,L10)),L9)))","v":2.8700476242893069E-2},null,8,-2.9000000000000001E-2]
[9,3.3000000000000002E-2,null,"RP",0.75690000000000002,null,4.3598890210067381E-2,null,-2.9000000000000001E-2,8,null,null,null,9,-2.9000000000000001E-2]
[10,0.06,"2057-09-01","IN FINE",1.4100999999999999,36,3.8699999999999998E-2,null,-2.9000000000000001E-2,9,null,null,null,10,-2.9000000000000001E-2]
[11,null,"2029-09-01","ZC","?",8,2.2499999999999999E-2,null,-2.9000000000000001E-2,10,null,null,null,11,-2.9000000000000001E-2,null,"L'utilisation du VBA est aussi possible"]
[12,"?","2043-09-01","IN FINE",1.0373000000000001,22,3.2599999999999997E-2,null,-2.9000000000000001E-2,11,null,null,null,12,-2.9000000000000001E-2]
[13,4.2999999999999997E-2,"2036-09-01","SEA",1.1121000000000001," ",2.6800000000000001E-2,null,-2.9000000000000001E-2,12,null,null,null,13,-2.9000000000000001E-2,null,"Pour lire la macro Rdt taper Alt F11"]
[14,null,"2050-09-01","ZC",0.36359999999999998,29,3.5499999999999997E-2,null,-2.9000000000000001E-2,13,null,null,null,14,-2.9000000000000001E-2,null,"Ouvrir le dossier Module1"]
[15,null,"?","ZC",0.71630000000000005,13,2.5999999999999999E-2,null,-2.9000000000000001E-2,14,null,null,null,15,-2.9000000000000001E-2]
[16,"?",null,"RP",0.50229999999999997,null,4.37985267768266E-2,null,-2.9000000000000001E-2,15,null,null,null,16,-1.0289999999999999]
[17,null,"2025-09-01","ZC",0.92569999999999997,4,1.95E-2,null,{"$f":"I19-1","v":-1.0289999999999999},16,null,null,null,17,""]
[18,4.8500000000000001E-2,"2024-09-01","IN FINE","?",3,1.8200000000000001E-2,null,null,null,null,null,null,18,""]
[null,null,null,null,null,null,null,null,{"$f":"IRR((I4:I20),B4)","v":2.8700476242893069E-2},null,null,null,null,19,"",null,null,{"$f":"1.0401^-44","v":0.17729470258849495}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,20,""]
["Le rendement actuariel peut aussi s'obtenir par ",null,null,null,null,null,null,null,null,null,null,null,null,21,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,22,""]
[null,"interpolation",null,null,null,null,null,null,null,null,null,null,null,23,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,24,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,25,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,26,""]
[null,"approximation",null,null,null,null,null,null,null,null,null,null,null,27,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,28,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,29,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,30,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,31,""]
["Emprunt N° 1",null,"Calcul du rendement actuariel d'un emprunt In Fine",null,null,null,null,null,null,null,null,null,null,32,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,33,""]
[null,"a)  calcul de la durée entre 1/9/37 et 1/9/21",null,null,null,null,null,null,null,null,null,null,null,34,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,35,""]
[null,null,{"$f":"YEAR(C4)-YEAR(A3)","v":16},null,null,null,null,null,null,null,null,null,null,36,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,37,""]
[null,"b) calcul du rendement actuariel avec la fonction TRI ",null,null,null,null,null,null,null,null,null,null,null,38,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,39,""]
[null,0,{"$f":"-E4","v":-1.0038},"Vo",null,null,null,null,null,null,null,null,null,40,""]
[null,1,{"$f":"$B$4","v":2.9000000000000001E-2},"i",null,null,null,null,null,null,null,null,null,41,""]
[null,2,{"$f":"$B$4","v":2.9000000000000001E-2},"i",null,null,null,null,null,null,null,null,null,42,""]
[null,3,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,43,""]
[null,4,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,44,""]
[null,5,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,45,""]
[null,6,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,46,""]
[null,7,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,47,""]
[null,8,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,48,""]
[null,9,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,49,""]
[null,10,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,50,""]
[null,11,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,51,""]
[null,12,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,52,""]
[null,13,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,53,""]
[null,14,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,54,""]
[null,15,2.9000000000000001E-2,"i",null,null,null,null,null,null,null,null,null,55,""]
[null,16,{"$f":"1+$B$4","v":1.0289999999999999},"1 + i",null,null,null,null,null,null,null,null,null,56,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,57,""]
[null,"TRI",{"$f":"IRR(C43:C59)","v":2.8700476242971673E-2},null,null,null,null,null,null,null,null,null,null,58,""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,59,""]
["Emprunt N° 2",null,"Trouver le prix (Vo) d'un emprunt Zéro Coupon",null,null,null,null,null,null,null,null,null,null,60,""]
[]
[null,"a) calcul de la durée entre 1/9/65 et le 1/9/21",null,null,null,{"$f":"YEAR(C5)-YEAR(A3)","v":44}]
[]
[null,"b) le prix -->","Vo = 1 / ( 1 + r ) n"]
[]
[null,"Vo =",{"$f":"1/(1+G5)^F65","v":0.17729470258849495}]
[]
[]
["Emprunt N° 3",null,"Trouver le taux de coupon (i) d'un emprunt remboursé par Annuités Constantes"]
[]
[null,"a) calcul de la durée entre 1/9/41 et le 1/9/21",null,null,null,{"$f":"YEAR(C6)-YEAR(A3)","v":20}]
[]
[null,"b) calcul de l'AC         AC = Vo [  ( 1 - (1 + r)-n ]/ r",null,null,null,{"$f":"E6*G6/(1-(1+G6)^-F74)","v":8.0924690878127675E-2}]
[]
[null,"c) recherche de i par estimation"]
[null,null,"comme Vo est > 100% alors  i > r"]
[null,null,"AC = i / [ 1 -(1 + i)-n ]"]
[]
[null,null,"test sur i","AC","Vo"]
[null,null,0.05,{"$f":"C83/(1-(1+C83)^-F74)","v":8.0242587190691314E-2},{"$f":"(C83/G6)*(1-(1+G6)^-F74)/(1-(1+C83)^-F74)","v":1.1261273325061181}]
[null,null,0.06,{"$f":"C84/(1-(1+C84)^-F74)","v":8.7184556976851402E-2},{"$f":"(C84/G6)*(1-(1+G6)^-F74)/(1-(1+C84)^-F74)","v":1.2235511842452036}]
[]
[null,"interpolation",{"$f":"C83+(C84-C83)*(E83-E6)/(E83-E84)","v":5.0982579452875543E-2}]
[]
[]
["Emprunt N° 4",null,"Recherche du prix pour un emprunt SEA"]
[]
[null,"a) calcul de la durée entre 1/9/25 et le 1/9/21",null,null,null,{"$f":"YEAR(C7)-YEAR(A3)","v":4}]
[]
[null,"b) Vo = (i/r) + [ (r-i)/rn ] * [ (1 - (1+r)-n ] / r"]
[]
[null,"Vo =",{"$f":"(B7/G7)+((G7-B7)/(G7*F91))*(1-(1+G7)^-F91)/G7","v":1.0411859259074485}]
[]
["Emprunt N° 5",null,"Trouver le prix d'un In Fine"]
[]
[null,"a) calcul de la durée entre 1/9/33 et le 1/9/21",null,null,null,{"$f":"YEAR(C8)-YEAR(A3)","v":12}]
[]
[null,"b) Vo = (i/r) + [ (r-i)/r ] *  (1+r)-n "]
[]
[null,"Vo =",{"$f":"(B8/G8)+((G8-B8)/G8)*(1+G8)^-F99","v":1.2836246431416711}]
[]
["Emprunt N° 6",null,"Recherche du rendement (r ) pour un AC"]
[]
[null,"a) calcul de la durée entre 1/9/32 et le 1/9/21",null,null,null,{"$f":"YEAR(C9)-YEAR(A3)","v":11}]
[]
[null,"b) calcul de l'AC",null,"AC = i / [ 1 -(1 + i)-n ]",null,{"$f":"B9/(1-(1+B9)^-F107)","v":0.10927811789442125}]
[]
[null,"b) calcul du rendement actuariel avec la fonction TRI "]
[]
[null,null,0,{"$f":"-E9","v":-1.0403}]
[null,null,1,{"$f":"$F$109","v":0.10927811789442125}]
[null,null,2,{"$f":"$F$109","v":0.10927811789442125}]
[null,null,3,0.10927811789442125]
[null,null,4,0.10927811789442125]
[null,null,5,0.10927811789442125]
[null,null,6,0.10927811789442125]
[null,null,7,0.10927811789442125]
[null,null,8,0.10927811789442125]
[null,null,9,0.10927811789442125]
[null,null,10,0.10927811789442125]
[null,null,11,0.10927811789442125]
[]
[null,null,"TRI",{"$f":"IRR(D113:D124)","v":2.489634454658729E-2}]
[]
["Emprunt N° 7",null,"Recherche du prix (Vo) pour un AC"]
[]
[null,"a) calcul de la durée entre 1/9/26 et le 1/9/21",null,null,null,{"$f":"YEAR(C10)-YEAR(A3)","v":5}]
[]
[null,"b) calcul  de Vo  ",null,"Vo = (i/r) * [ (1 - (1+r)-n ] /  [ (1 - (1+i)-n ]"]
[]
[null,null,"Vo =",{"$f":"(B10/G10)*(1-(1+G10)^-F130)/(1-(1+B10)^-F130)","v":1.0833071390675482}]
[]
["Emprunt N° 8",null,"Recherche de la date d'un IF"]
[]
[null,"a) recherche de n"]
[]
[null,null,"Vo = i/r + ((r-i)/r)* (1+r)-n"]
[]
[null,null,"n = [ ln [ (r-i)/(rVo-i) ] / ln(1+r)",null,null,{"$f":"LN((G11-B11)/(G11*E11-B11))/LN(1+G11)","v":8.9985632010492118}]
[]
[null,"b) calcul de la date",null,{"$f":"DATE(YEAR(A3)+F142,9,1)","v":"2029-09-01"}]
[]
["Emprunt N° 9 & +"]

{"sheet":"Age_Volat","index":2,"n_rows":33,"n_cols":25,"rows_kept":33,"cut":false,"hidden":false,"merged":[[0,7,0,13],[0,15,0,20],[0,22,0,24],[22,5,22,7]]}
[null,null,null,null,null,"∞",null,"DIFFERENTES MESURES DE L'AGE D'UN TITRE",null,null,null,null,null,null,null,"MESURES de VOLATILITE",null,null,null,null,null,null,"+/- value (si r +1%)"]
[null,"i","Typ","Vo","n","r",null,"Maturité","Age_Vpr","Age_Dre","Age_Vmo","Age_Vmp","Age_Ech","Age_Dur",null,"Ris_Var","Ris_Sen","Ris_Ela","CVX","D²","M²",null,"Est Dur","Est Cvx","Réel"]
[1,2.9000000000000001E-2,"IN FINE",1.0038,16,2.87E-2,null,{"$f":"IF(ISNUMBER(E3),E3,0)","v":16},{"$f":"Age_Vpr(B3,E3,C3)","v":16},{"$f":"Age_Dre(B3,E3,C3)","v":16},{"$f":"Age_Vmo(B3,E3,C3)","v":16},{"$f":"Age_Vmp(B3,E3,C3)","v":13.622950819672131},{"$f":"Age_Ech(B3,E3,F3,C3)","v":13.336702338244129},{"$f":"Age_Du1(B3,E3,F3,C3)","v":13.031438133732427},null,{"$f":"Ris_Var(B3,E3,F3,C3)","v":-12.716084879982946},{"$f":"Ris_Sen(B3,E3,F3,C3)","v":-12.667870257346582},{"$f":"Ris_Ela(B3,E3,F3,C3)","v":-13.031438133732427},{"$f":"Ris_Cvx(B3,E3,F3,C3)","v":205.78968257271603},{"$f":"Ris_Du2(B3,E3,F3,C3)","v":192.75824443898358},{"$f":"Ris_M2(B3,E3,F3,C3)","v":22.939864605687863},null,{"$f":"-N3*(1%)/(1+F3)","v":-0.12667870257346583},{"$f":"W3+(S3/2)*((1%/(1+F3))^2)","v":-0.11695534802577488},{"$f":"Var_Rel(B3,E3,F3,C3)","v":-0.11747788687316119}]
[2,null,"ZC",0.17730000000000001,44,4.0099999999999997E-2,null,{"$f":"IF(ISNUMBER(E4),E4,0)","v":44},{"$f":"Age_Vpr(B4,E4,C4)","v":44},{"$f":"Age_Dre(B4,E4,C4)","v":44},{"$f":"Age_Vmo(B4,E4,C4)","v":44},{"$f":"Age_Vmp(B4,E4,C4)","v":44},{"$f":"Age_Ech(B4,E4,F4,C4)","v":44},{"$f":"Age_Du1(B4,E4,F4,C4)","v":44},null,{"$f":"Ris_Var(B4,E4,F4,C4)","v":-7.5002085509987229},{"$f":"Ris_Sen(B4,E4,F4,C4)","v":-42.303624651475822},{"$f":"Ris_Ela(B4,E4,F4,C4)","v":-44},{"$f":"Ris_Cvx(B4,E4,F4,C4)","v":1980},{"$f":"Ris_Du2(B4,E4,F4,C4)","v":1936},{"$f":"Ris_M2(B4,E4,F4,C4)","v":0},null,{"$f":"-N4*(1%)/(1+F4)","v":-0.42303624651475819},{"$f":"W4+(S4/2)*((1%/(1+F4))^2)","v":-0.33152278101545951},{"$f":"Var_Rel(B4,E4,F4,C4)","v":-0.34362010900460649}]
[3,5.0999999999999997E-2,"AC",1.1356999999999999,20,3.6400000000000002E-2,null,20,12.390838070783685,12.357217148893163,12.127203517664775,10.5,9.9081221754569135,{"$f":"Age_Du1(B5,E5,F5,C5)","v":9.321241158269185},null,-10.21429127089854,-8.9938644908039205,-9.321241158269185,128.62157731432214,119.30033615605296,32.414799425441522,null,-8.9938644908039225E-2,-8.3951372379043987E-2,-8.425764256545154E-2]
[4,3.5999999999999997E-2,"SEA",1.0411999999999999,4,1.89E-2,null,4,2,3.3474903474903468,2.5,2.4587155963302751,2.4470339638132055,2.435357578020759,null,-2.4886250218738497,-2.3901831171074286,-2.435357578020759,9.6129384658941479,7.1775808878733907,1.2466143550502531,null,-2.3901831171074289E-2,-2.3438850305878826E-2,-2.3446617241301038E-2]
[5,5.2999999999999999E-2,"IN FINE",1.2836000000000001,12,2.53E-2,null,12,12,12,12,9.8618581907090466,9.7083832860994548,9.5490906688108428,null,-11.9549869326829,-9.3134601275829922,-9.5490906688108428,113.9511694336487,104.40207876483787,13.216946163667556,null,-9.3134601275829926E-2,-8.77147561558798E-2,-8.7942543118790129E-2]
[6,3.2000000000000001E-2,"AC",1.0403,11,2.4899999999999999E-2,null,11,5.9740531197052116,9.1509628758993475,6.3143530262072645,6,5.8771003075256605,5.7543515233890394,null,-5.8406957071579857,-5.6145492471353702,-5.7543515233890394,48.830119993231371,43.07576846984233,9.9632070151125696,null,-5.6145492471353697E-2,-5.3821178409913101E-2,-5.3897335514205613E-2]
[7,4.8000000000000001E-2,"AC",1.0832999999999999,5,1.9800000000000002E-2,null,5,2.64617696938011,4.3535176759562599,3.0936779756964938,3,2.9803951031420421,2.9607934713474924,null,-3.1451742545747576,-2.9033079734727325,-2.9607934713474924,13.726092351335208,10.765298879987716,1.9990009000137816,null,-2.903307973472732E-2,-2.8373166397730278E-2,-2.8385833883750711E-2]
[8,0.05,"IN FINE",1.2055,9,2.4299999999999999E-2,null,9,9,9,9,7.7586206896551726,7.6905025063320043,7.6201884401588869,null,-8.9684283483893488,-7.4394107587219436,-7.6201884401588869,71.729562952975584,64.109374512816714,6.0421026492855896,null,-7.4394107587219441E-2,-7.0975778711927423E-2,-7.1091068021292614E-2]
[9,3.3000000000000002E-2,"RP",0.75690000000000002,null,4.3598890210067381E-2,null,0,0,30.303030303030301,0,0,0,23.936363636363637,null,-17.360533636363634,-22.936363636363637,-23.936363636363637,1145.899008264463,1121.962644628099,549.01314049586779,null,-0.22936363636363638,-0.17675595867768598,0.81342897286105154]
[10,0.06,"IN FINE",1.4100999999999999,36,3.8699999999999998E-2,null,36,36,16.666666666666668,36,24.037974683544299,21.251354333404908,18.406594509493811,null,-24.988072998389804,-17.720799566278821,-18.406594509493811,506.22403330060843,487.8174387911148,149.0147173541871,null,-0.17720799566278822,-0.1537477529542971,-0.15598557235496358]
[11,null,"ZC",0.83689999999999998,8,2.2499999999999999E-2,null,8,8,8,8,8,8,8,null,-6.5481728809907391,-7.8239608801955995,-8,72,64,0,null,-7.823960880195599E-2,-7.4796300835121746E-2,-7.4905602519059311E-2]
[12,3.5000000000000003E-2,"IN FINE",1.0373000000000001,22,3.2599999999999997E-2,null,22,22,22,22,17.432203389830509,16.657980298680066,15.826889504424326,null,-15.898484348539094,-15.327222065102001,-15.826889504424326,321.50810309734584,305.68121359292149,55.190782207664675,null,-0.15327222065102003,-0.13819581945056236,-0.13923076177984217]
[13,4.2999999999999997E-2,"SEA",1.1121000000000001,15,2.6800000000000001E-2,null,15,7.5,10.600823045267489,8,7.4027777777777786,7.162472537491241,6.9242736883308558,null,-7.4992956828689685,-6.7435466384211686,-6.9242736883308558,72.702523793358125,65.778250105027269,17.83268399411628,null,-6.743546638421169E-2,-6.3987621083092833E-2,-6.4126777254721268E-2]
[14,null,"ZC",0.36359999999999998,29,3.5499999999999997E-2,null,29,29,29,29,29,29,29,null,-10.183449194174178,-28.005794302269432,-29,870,841,0,null,-0.28005794302269432,-0.23948943365195155,-0.24324392469438283]
[15,null,"ZC",0.71630000000000005,13,2.5999999999999999E-2,null,13,13,13,13,13,13,13,null,-9.0757035469572092,-12.670565302144249,-13,182,169,0,null,-0.12670565302144249,-0.11806101782504777,-0.11846640099518058]
[16,2.1999999999999999E-2,"RP",0.50229999999999997,null,4.37985267768266E-2,null,0,0,45.45454545454546,0,0,0,23.831818181818182,null,-11.46842227272727,-22.831818181818182,-23.831818181818182,1135.9111157024793,1112.0792975206609,544.12373966942141,null,-0.22831818181818181,-0.17618898966942148,0.81412130407430705]
[17,null,"ZC",0.92569999999999997,4,1.95E-2,null,4,4,4,4,4,4,4,null,-3.6318160264949406,-3.9234919077979398,-4,20,16,0,null,-3.9234919077979401E-2,-3.8272807281069653E-2,-3.8291362500396553E-2]
[18,4.8500000000000001E-2,"IN FINE",1.0876999999999999,3,1.8200000000000001E-2,null,3,3,3,3,2.8729812309035356,2.8712009831740808,{"$f":"Age_Du1(B20,E20,F20,C20)","v":2.869404014832917},null,-3.0652317754943095,-2.8181143339549375,-2.869404014832917,11.304009935458524,8.4346059206256072,0.2011265202863444,null,-2.818114333954937E-2,-2.7635967815782068E-2,-2.7644695161794242E-2]
[]
[]
[null,null,null,null,null,"Macro fonctions"]
[]
[null,"Rdt ( prix ; taux coupon ; maturité ; type )",null,null,null,null,null,null,null,"Ris_Var (  taux  coupon ; maturité ; rendement ; type )"]
[null,null,null,null,null,null,null,null,null,"Ris_Sen (  taux  coupon ; maturité ; rendement ; type )"]
[null,"Age_Vpr ( taux  coupon ; maturité ; type )",null,null,null,null,null,null,null,"Ris_Ela (  taux  coupon ; maturité ; rendement ; type )"]
[null,"Age_Dre (  taux  coupon ; maturité ; type )",null,null,null,null,null,null,null,"Ris_Cvx (  taux  coupon ; maturité ; rendement ; type )"]
[null,"Age_Vmo (  taux  coupon ; maturité ; type )",null,null,null,null,null,null,null,"Ris_Du2 (  taux  coupon ; maturité ; rendement ; type )"]
[null,"Age_Vmp (  taux  coupon ; maturité ; type )",null,null,null,null,null,null,null,"Ris_M2 (  taux  coupon ; maturité ; rendement ; type )"]
[]
[null,"Age_Ech (  taux  coupon ; maturité ; rendement ; type )",null,null,null,null,null,null,null,"Var_Rel (  taux  coupon ; maturité ; rendement ; type )"]
[null,"Age_Du1 (  taux  coupon ; maturité ; rendement ; type )"]

{"sheet":"Calcul","index":3,"n_rows":27,"n_cols":16,"rows_kept":27,"cut":false,"hidden":false,"merged":[[22,6,22,7]]}
[]
[null,null,"Calcul du prix, de la duration et de la convexité"]
[]
[null,null,null,null,"avant",null,null,null,"après"]
[null,null,null,"r =",0.06,null,null,"r* =",{"$f":"E5+E23","v":6.2E-2},null,null,null,null,"ZC(4 ans)"]
[null,null,null,"Fj","Fj/(1+r)j","j * Fj/(1+r)j","j² * Fj/(1+r)j",null,"Fj/(1+r*)j",null,null,null,null,"Du = n",4]
[null,null,1,5.8000000000000003E-2,{"$f":"D7/(1+$E$5)^C7","v":5.4716981132075473E-2},{"$f":"C7*E7","v":5.4716981132075473E-2},{"$f":"C7*F7","v":5.4716981132075473E-2},null,{"$f":"D7/(1+$I$5)^C7","v":5.4613935969868174E-2},null,null,null,null,"Cvx n(n+1)",20]
[null,null,2,5.8000000000000003E-2,{"$f":"D8/(1+$E$5)^C8","v":5.1619793520825914E-2},{"$f":"C8*E8","v":0.10323958704165183},{"$f":"C8*F8","v":0.20647917408330366},null,{"$f":"D8/(1+$I$5)^C8","v":5.1425551760704492E-2},null,null,null,null,"Vo(6%)",{"$f":"1/(1.06^4)","v":0.79209366323802044}]
[null,null,3,5.8000000000000003E-2,4.8697918415873495E-2,0.14609375524762047,0.43828126574286141,null,4.84233067426596E-2,null,null,null,null,"choc",0.02]
[null,null,4,5.8000000000000003E-2,4.5941432467805188E-2,0.18376572987122075,0.735062919484883,null,4.5596334032636153E-2,null,null,null,null,"Vo(8%)",{"$f":"1/1.08^4","v":0.73502985279645328}]
[null,null,5,5.8000000000000003E-2,4.3340974026231299E-2,0.2167048701311565,1.0835243506557826,null,4.2934401160674346E-2]
[null,null,6,5.8000000000000003E-2,4.0887711345501225E-2,0.24532626807300734,1.4719576084380441,null,4.0427873032650039E-2,null,null,null,null,"dVo / Vo",null,{"$f":"(O10-O8)/O8","v":-7.2041745932285989E-2}]
[null,null,7,5.8000000000000003E-2,3.8573312590095492E-2,0.27001318813066844,1.890092316914679,null,3.8067677055226026E-2]
[null,null,8,5.8000000000000003E-2,3.6389917537825937E-2,0.2911193403026075,2.32895472242086,null,3.5845270296823002E-2,null,null,null,null,"dV/V = -Du d(1+r)/(1+r)",null,{"$f":"-4*2%/1.06","v":-7.5471698113207544E-2}]
[null,null,9,5.8000000000000003E-2,3.4330110884741451E-2,0.30897099796267308,2.7807389816640575,null,3.3752608565746703E-2]
[null,null,10,1.0580000000000001,0.59078167397619474,5.9078167397619472,59.078167397619474,null,0.57974965683745716,null,null,null,null,"dV/V  [avec Du et Cvx]",null,{"$f":"P14+20/2*((2%)/1.06)^2","v":-7.1911712353150581E-2}]
[]
[]
[null,null,null,"Vo",{"$f":"SUM(E7:E16)","v":0.98527982589717022},{"$f":"SUM(F7:F16)/E19","v":7.843221036843949},{"$f":"SUM(G7:G16)/E19","v":71.114797924898085},null,{"$f":"SUM(I7:I16)","v":0.97083661545444566}]
[null,null,null,"DU",{"$f":"F19","v":7.843221036843949}]
[null,null,null,"CVX",{"$f":"E20+G19","v":78.958018961742027}]
[]
[null,null,null,"d(1+r)",2E-3,null,"avec calcul matriciel"]
[null,null,null,"dVo/Vo",null,null,"Vo",{"$f":"SUM(D7:D16/(1+E5)^(C7:C16))","v":0.98527982589717022}]
[null,null,null,"exact",{"$f":"(I19-E19)/E19","v":-1.4658993377412301E-2},null,"DU",{"$f":"SUM((D7:D16*C7:C16)/(1+E5)^(C7:C16))/H24","v":7.843221036843949}]
[null,null,null,"proxy D",{"$f":"-E20*(E23/(1+E5))","v":-1.479853025819613E-2},null,"CVX",{"$f":"SUM((D7:D16*C7:C16*C7:C16)/(1+E5)^(C7:C16))/H24+H25","v":78.958018961742027}]
[null,null,null,"Proxy D + Cvx",{"$f":"E26+(E21/2)*(E23/(1+E5))^2","v":-1.4657985546623075E-2}]