création d'une formule d'actualisation de Coût

StefD

XLDnaute Nouveau
Bonjour Forum

Après moult recherches infructueuses j'ai l'impression qu'il n'existe pas de formule Excel prédéfinie pour mon besoin, pouvez vous m'aider à programmer une formule adaptée ?

J'ai un coût fixe mensuel de 100 € que j'actualise à 3% chaque année le 1er janvier. Je voudrais trouver la formule Excel qui me donne le coefficient d'actualisation moyen pour n'importe quel mois de départ et pour n'importe quelle période (en mois).

Cela donne :

An1 Jan 100
fev 100
mar 100
...
Déc 100

An2
jan 103
fév 103
mar 103
....
Déc 103

An3
jan 106.9
fév 106.9
.....

fx(Cm) (fev(An1) ; 15 mois) = ((11mois x 1) + (4mois x 1,03)) /15 = 1,008
fx(Cm) (jan(An2) ; 25 mois) = ((12 mois x1,03) + (12 mois x 1,069) + (1 mois x 1,10107)/25 = 1,0515628

Avec comme variables :

Mois_début : date du début de la suite (ex fév 2007)
N : nombre de mois de la période
Pct : Taux d'actualisation annuel en % appliqué le 1er janvier pour l'année entamée

L'idéal étant de créer une formule excel dont on approvisionne les 3 variables en adressant les cellules qui comportent les variables et qui restitue le Coût moyen.

Merci à vous chers membres du forum pour votre intelligence et votre disponibilité.

StefD
 

Dugenou

XLDnaute Barbatruc
Re : création d'une formule d'actualisation de Coût

Bonjour,

voici un début de réponse avec un tableau intermédiaire pour le calcul des mois par année.

Je ne vois pas bien comment faire une formule sur une seule cellule mais il doit y avoir moyen (peut etre en vb)

Cordialement
 

Fichiers joints

Dugenou

XLDnaute Barbatruc
Re : création d'une formule d'actualisation de Coût

Re,

et ben avec une méthode de bourrin on arrive à tout faire en une seule fois.
avec le mois de départ en a2, le nb de mois en b2 et le taux en c2

voir PJ

Prenez votre souffle :
=100*(((SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)+SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)>12;12;B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2))*1*(1+$C$2/100)+SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)>24;12;SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-12<0;0;B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-12))*1*(1+$C$2/100)^2+SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)>36;12;SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-24<0;0;B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-24))*1*(1+$C$2/100)^3+SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)>48;12;SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-36<0;0;B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-36))*1*(1+$C$2/100)^4+SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)>60;12;SI(B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-48<0;0;B2-SI(12-MOIS(A2)<B2;12-MOIS(A2);B2)-48))*1*(1+$C$2/100)^5)/B2)-1)

Cordialement


Edit : eeuuuuu les $ peuvent être supprimés ou alors il faut en mettre partout si on veut coller la formule ailleur ! de même on peut supprimer les *1
 

Fichiers joints

Dernière édition:

StefD

XLDnaute Nouveau
Re : création d'une formule d'actualisation de Coût

Merci Dugenou

cette fonction sommeprod est bien séduisante même si un peu perturbante parce qu'on n'en visualise mal le fonctionnement.

Pour ma part, elle me pose problème pour des feuille de calcul oû j'ai des listes de calclul à faire et je reste persuadé qu'une fonction VBA est possible.

Merci beaucoup pour cette idée.

StefD
 

StefD

XLDnaute Nouveau
Re : création d'une formule d'actualisation de Coût

J'adore la méthode bourin (je suis spécialiste) et je me rend compte que 60 mois de période, finalement, c'est largement suffisant. J'avais tenté une formule universelle à base de SI mais elle ne fanctionne pas dans tous les cas.

Merci Dugenou
 

Discussions similaires


Haut Bas