[XL-2010] Calcul de la valeur actuelle avec vba

[ETOUKE]

Bonjour à tous,

Je suis nouveau dans le forum et debutant sur VBA. Pour des besoins de contrôle, j'aimerais avoir les codes VBA pour le calcul du taux de rendement et la valeur actuelle au 31/12/2010 d'une obligation à taux fixe ayant les caracteristiques suivantes:

1-cours à l'achat: 104.83
2- Taux coupon: 4.65%
3- Nominal : 1000
4- Qté: 120
5- prix de remboursement 100
6- Date d'achat 28/05/2003
7- Date de maturité : 15/01/2018

Je compte sur votre aide

[B.AF]

Il faut la périodicité des flux aussi.
Ca te permettra de déterminer l'échéancier des flux futurs de la date d'achat à la date de maturité.

Le seul moyen connu aujourd'hui pour déterminer le taux actuariel est l'itération de newton raphson.

Tout dépend de quelle version d'Excel tu parles, mais la fonction associée est IRR sous excel.

[ETOUKE]

Je suis sur excel 2010, pour le coupon, il est annuel (un coupon par an).

D'avance merci

[B.AF]

Voilà un exemple en Java; ca reste assez similaire en VBA ou autre langage :
- On crée le tableau de flux
- On actualise les flux avec un taux incrémenté selon un nombre d'itérations max déterminées et une précision requise
- La valeur initiale est valable pour un taux fixe; tu peux partir de 0 ou utiliser un générateur aléatoire.

IRR.java

Code java :

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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
import java.util.*;
import java.lang.*;
import java.lang.Object;
 
public class IRR {
 
    private Collection<Flow> _flows=new ArrayList<Flow>();
    private int _nbflows=0;
    private double precision=0.000001;
    private double step=0.0001;
    private int _iterationcount=0;
    private int _maxiteration=50000;
    private double _initialEstimatedReturn=-1;
    private double _result=0;
 
 
    private void assignFlow(Collection<Flow> flows)throws Exception
    {
      _flows=flows;
       for (Flow f:flows ) _nbflows++;
       if (_nbflows<2) 
       {
           throw new java.lang.Exception("At least 2 cash flows required !");
       }
    }
 
    private double SumOfIRRPolynomial(double estimatedReturnRate)
        {
            double sumOfPolynomial = 0;
            if (IsValidIterationBounds(estimatedReturnRate))
                for (int j = 0; j < _flows.size(); j++)
                {
                    Flow f=(Flow)_flows.toArray()[j];
                    sumOfPolynomial += f.Flow()/(Math.pow((1 + estimatedReturnRate),f.Period()));
                }
            return sumOfPolynomial;
        }
 
    private boolean IsValidIterationBounds(double estimatedReturnRate)
    {
            return _initialEstimatedReturn != -1 && (_initialEstimatedReturn < Integer.MAX_VALUE) &&
                   (estimatedReturnRate > Integer.MIN_VALUE);
    }
 
    private double IRRDerivativeSum(double estimatedReturnRate)
    {
            double sumOfDerivative = 0;
            if (IsValidIterationBounds(estimatedReturnRate))
                for (int i = 1; i < _flows.size(); i++)
                    {
                    Flow f=(Flow)_flows.toArray()[i];
                    sumOfDerivative += (f.Flow()* f.Period())/Math.pow((1 + estimatedReturnRate), f.Period());
                }
            return sumOfDerivative*-1;
   } 
 
   private void DoNewtonRapshonCalculation(double estimatedReturn)
   {
           _iterationcount++;
            _result = estimatedReturn - SumOfIRRPolynomial(estimatedReturn)/IRRDerivativeSum(estimatedReturn);
            while (!HasConverged(_result)&& ConfigurationHelper.MaxIterations != _iterationcount)
            {
                DoNewtonRapshonCalculation(_result);
            }
    }
 
  private boolean HasConverged(double estimatedReturnRate)
        {
            //Check that the calculated value makes the IRR polynomial zero.
            boolean isWithinTolerance = Math.abs(SumOfIRRPolynomial(estimatedReturnRate)) <= ConfigurationHelper.Tolerance;
            return (isWithinTolerance) ? true : false;
        }
 
    public IRR(Collection<Flow> flows) throws Exception
    {
      assignFlow(flows);
      _initialEstimatedReturn=InitialGuess();
 
    }
 
   public double Execute()
    {
          DoNewtonRapshonCalculation(InitialGuess());
          return _result;
    }
 
    public IRR(Collection<Flow> flows, double initial_guess) throws Exception
    {
       assignFlow(flows);
       _initialEstimatedReturn=initial_guess;        
    }
 
   private double InitialGuess()
    {
      Flow f1=(Flow)(_flows.toArray())[0];
      Flow f2=(Flow)(_flows.toArray())[1];
      double x =(1+ (f2.Flow()/f1.Flow()))*-1;     
      return x;
    }
 
}

La classe Flow.java
Un flux est
- une période de temps (on utilise par convention une fraction annuelle)
- une valeur signée représentant les flux concrets.

Code java :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class Flow {
    private double _period=0;
    private double _flow=0;
 
    public Flow(double period,double flow)
    {
        this._period=period;
        this._flow=flow;
    }
 
    /**
     * @return the period
     */
    public double Period() {
        return _period;
    }
 
    /**
     * @return the flow
     */
    public double Flow() {
        return _flow;
    }
}

La classe de config pour l'itération

Code java :

1
2
3
4
public class ConfigurationHelper {
    public static int MaxIterations =100000; 
    public static double Tolerance= 0.0001;   
}

A l'usage; voilà comment calculer le taux actuariel d'une oblig achetée -107.8175 (+ intérêts); payant 5.125% d'intérêts et remboursée in fine à 100. (Qui fait 105.25 + le dernier flux d'intérêt)

Code java :

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
public void testIRR() throws Exception {
 
Collection<Flow> flows=new ArrayList<Flow>();
flows.add(new Flow(0,-107.8175));
flows.add(new Flow(0.293150684931507,5.125));
flows.add(new Flow(1.29315068493151,5.125));
flows.add(new Flow(2.29315068493151,5.125));
flows.add(new Flow(3.29315068493151,5.125));
flows.add(new Flow(4.29315068493151,5.125));
flows.add(new Flow(5.29315068493151,5.125));
flows.add(new Flow(6.29315068493151,5.125));
flows.add(new Flow(7.29315068493151,5.125));
flows.add(new Flow(8.29315068493151,5.125));
flows.add(new Flow(9.29315068493151,5.125));
flows.add(new Flow(10.2931506849315,5.125));
flows.add(new Flow(11.2931506849315,5.125));
flows.add(new Flow(12.2931506849315,5.125));
flows.add(new Flow(13.2931506849315,5.125));
flows.add(new Flow(14.2931506849315,5.125));
flows.add(new Flow(15.2931506849315,5.125));
flows.add(new Flow(16.2931506849315,5.125));
flows.add(new Flow(17.2931506849315,5.125));
flows.add(new Flow(18.2931506849315,5.125));
flows.add(new Flow(19.2931506849315,5.125));
flows.add(new Flow(20.2931506849315,5.125));
flows.add(new Flow(21.2931506849315,5.125));
flows.add(new Flow(22.2931506849315,5.125));
flows.add(new Flow(23.2931506849315,5.125));
flows.add(new Flow(24.2931506849315,5.125));
flows.add(new Flow(25.2931506849315,5.125));
flows.add(new Flow(26.2931506849315,5.125));
flows.add(new Flow(27.2931506849315,5.125));
flows.add(new Flow(28.2931506849315,5.125));
flows.add(new Flow(29.2931506849315,5.125));
flows.add(new Flow(30.2931506849315,5.125));
flows.add(new Flow(31.2931506849315,5.125));
flows.add(new Flow(32.2931506849315,5.125));
flows.add(new Flow(33.2931506849315,5.125));
flows.add(new Flow(34.2931506849315,5.125));
flows.add(new Flow(35.2931506849315,5.125));
flows.add(new Flow(36.2931506849315,105.125));
 
        IRR irr=new IRR(flows);
 
        double yield=irr.Execute();
        System.out.print(yield);
    }

Dans tous les cas ce qui peut induire des erreurs ou des écarts sont les calculs de période qui correspondent à des normes / convention. On parle de norme de décompte des jours.
Ici

Il faut aussi vérifier que ton prix à l'achat soit coupon inclue (=dirty) sinon il te manquera une partie des flux.

En VBA, il s'agit aussi de la fonction RENDEMENT.TITRE dans Excel.