1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
|
Sub MAIN()
Dim feuil As Variant
Dim nomonglet As String
' Message box
'Dim annee As String
'annee_s = InputBox(vbCrLf & vbCrLf & "Année de référence?")
For Each feuil In Worksheets
If feuil.Name = "TEG" Then
nomonglet = feuil.Name
Dim n, x, i, j, duree_i, nbre_echeances As Integer
Dim TEG, CRD_i, capital_i, interets_i, echeance_i, facteur_actualisation_i, echeance_actualisee_i, taux_i, taux_amortissement, flux_debit, flux_credit, flux_nul, frais As Double
'nom de la feuille recup correspondante à nomonglet
Sheets(nomonglet).Select
' initialisation
n = Range("C65536").End(xlUp).Row
x = Range("IV19").End(xlToLeft).Column
j = 2
nbre_echeances = 17
'initialisation de la premiere ligne
TEG = Sheets(nomonglet).Cells(2, 7).Value
frais = Sheets(nomonglet).Cells(2, 5).Value
taux_amortissement = Sheets(nomonglet).Cells(3, 5).Value
duree_i = 0
CRD_i = Sheets(nomonglet).Cells(6, 4).Value
capital_i = 0
interets_i = 0
taux_i = Sheets(nomonglet).Cells(6, 3).Value
echeance_i = echeance(capital_i, interets_i)
facteur_actualisation_i = facteur_actualisation(TEG, duree_i)
echeance_actualisee_i = echeance_actualisee(echeance_i, facteur_actualisation_i)
flux_credit = 0
flux_credit = CRD_i + frais
For i = 7 To 23
duree_i = duree_i + 1
taux_i = Sheets(nomonglet).Cells(i, 3).Value
interets_i = interets(taux_i, duree_i, CRD_i)
If i = 7 Then
capital_i = capital_initial(taux_amortissement, CRD_i, nbre_echeances)
Else
capital_i = capital(taux_amortissement, capital_i)
End If
CRD_i = CRD_i - capital_i
echeance_i = echeance(capital_i, interets_i)
facteur_actualisation_i = facteur_actualisation(TEG, duree_i)
echeance_actualisee_i = echeance_actualisee(echeance_i, facteur_actualisation_i)
flux_debit = flux_debit + echeance_actualisee_i
Sheets(nomonglet).Cells(i, 4) = CRD_i
Sheets(nomonglet).Cells(i, 5) = capital_i
Sheets(nomonglet).Cells(i, 6) = interets_i
Sheets(nomonglet).Cells(i, 7) = echeance_i
Sheets(nomonglet).Cells(i, 8) = facteur_actualisation_i
Sheets(nomonglet).Cells(i, 9) = echeance_actualisee_i
Next
flux_nul = flux_debit - flux_credit
Sheets(nomonglet).Cells(25, 9) = flux_debit
Sheets(nomonglet).Cells(26, 9) = flux_credit
Sheets(nomonglet).Cells(27, 9) = flux_nul
End If
Next
End Sub |
Partager