XL 2013 Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

[GuillaumeR]

Bonjour à tous,

Je suis nouveau sur le forum et j'ai cherché en vain un réponse à ma question. Mon problème est le suivant :

Je dois calculer le prorata d'un montant sur l'année en cours en fonction d'une date de début et d'une date de fin.

Par exemple :
Date de début : 01/01/2016
Date de fin : 31/21/2016
Montant : 10000€
Prorata sur l'année en cours : 10000€

La c'est facile me direz vous, mais ça se complique quand les dates de début et de fin ne sont pas celle de l'année en cours

Par exemple :

Date de début : 01/09/2015
Date de fin : 01/07/2016
Montant : 10000€
Prorata sur l'année en cours : 6000€

Et encore plus dur :

Date de début : 01/09/2015
Date de fin : 01/07/2017
Montant : 30000€
Prorata sur l'année en cours : 16363,64€

Voici le fichier que j'ai créé, ça marche à peu près sauf pour le dernier cas, mais mon souci c'est que l'année prochaine ça ne fonctionnera plus car j'ai mis l'année en cours.

Avez-vous des pistes?

Merci pour votre aide

Guillaume

 

[JHA]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonjour à tous,

A essayer

JHA

 

[GuillaumeR]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Merci pour cette réponse rapide JHA, ça fonctionne très bien sauf dans un cas. Si la date de début est en 2015 et celle de fin en 2017, le prorata devrait correspondre aux 12 mois de l'année 2016.

Prenons l'exemple suivant :
Date de début : 01/10/2015
Date de fin : 01/02/2017
Montant : 30000€
Prorata de l'année en cours :
Le calcul doit être :
3 mois en 2015
12 mois en 2016 et 1 mois en 2017

Donc si 16 mois font 30000€ les 12 mois de 2016 font au prorata : X=(30000*12)/16=22500€

Comment faire pour prendre en compte ce cas?

Merci encore pour votre aide.

 

[JHA]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonjour à tous,

Ajout de la condition supplémentaire.

JHA

 

[GuillaumeR]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Merci et bravo! C'est exactement le fonctionnement attendu! J'aime beaucoup l'utilisation du DROITE(). Je suis bête de ne pas y avoir pensé.

Bonne journée et encore merci JHA.

 

[GerardCalc]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonjour,

Une autre formule, un peu plus courte :

=MAX(0;D3*(MIN(C3;DATEVAL("31/12/"&DROITE($E$2;4)))-MAX(B3;DATEVAL("1/1/"&DROITE($E$2;4))))/(C3-B3))

A+

 

[excfl]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonsoir le forum,

Date de début : 01/10/2015
Date de fin : 01/02/2017
Prorata de l'année en cours :
Le calcul doit être :
3 mois en 2015
12 mois en 2016 et 1 mois en 2017

Autrement dit : date de fin : 31/01/2017.

A partir de l'excellente formule de GerardCalc qui donne les bonnes réponses pour les 3 années concernées :

E3 :

=MAX(0;$D3*(MIN($C3;DATEVAL("31/12/"&E$2))-MAX($B3;DATEVAL("1/1/"&E$2))))/($C3-$B3)

Le deuxième tableau est calculé "manuellement".

Merci d'avance à celui qui me donnera la formule à insérer en E11.

 

[JHA]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonjour à tous,

Il manquait 1 journée.

JHA

 

[excfl]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Merci pour la réponse.

Mais, comme le demandeur, je souhaite un prorata par mois :
30 000 x 3/16 pour 2015
30 000 x 12/16 pour 2016
30 000 x 1/16 pour 2017.

 

[JHA]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Bonjour à tous,

Peut être avec datedif

JHA

 

[excfl]

Re : Calcul de Prorata sur l'année en cours en fonction d'un montant et de 2 dates

Merci beaucoup.

Cela me semble parfait.