XL 2019 Calcul au prorata

[farah.bekhtaoui]

Bonjour,

En éspérant que quelqu'un pourra me venir en aide et résoudre ma problématique ci-dessous.

Sur le fichier ci-joint, je souhaiterai que la somme des loyers en colonne Q de l'onglet "variable" soit lissée sur l'année dans l'onglet "Fixe" où il est indiqué loyer dans la colonne B et celà par propriété en prenant en compte la date de fin de bail en colonne S de l'onglet "variable".
Exemple: Dans l'onglet "variable" on peut voir que dans la propriété "Haussmann" il y a 5 locataires en colonne O avec leur loyer respectif en colonne Q, la date de début du bail en colonne R et la date de fin du bail en colonne S. Il faudrait que la somme de [Q4:Q8] soit lissée dans l'onglet "fixe" entre C4 et N4 en prenant en compte la date de fin du bail de chacun des locataires. Le locataire BNP ayant une date de fin de son bail au 30/11/2021, le loyer global dans l'onglet "fixe" devrait donc baissé en N4. Et ce calcul devrait être appliqué aux autres propriété également: Roosevelt, etc.

Dans l'onglet "feuille 1", j'ai effectuée séparémment un petit test et ai réussi à trouver une formule qui me permet de calculer les loyers au prorata par locataire mais rien de concret. Je n'arrive pas à faire en sorte que le le montant s'ajuste dans l'ongle "fixe" en fonction de la période.

J'espère avoir été claire. N'hésitez pas si vous avez des questions.
Merci et très belle journée!

 

[eriiic]

Bonjour,

pas trop clair non.
Tu parles de lisser sur l'année, pour moi c'est donc annuel/12.
Mais plus bas tu dis " BNP ayant une date de fin de son bail au 30/11/2021, le loyer global dans l'onglet "fixe" devrait donc baissé en N4", comme si c'était le seul mois affecté et non lissé sur l'année...
Choisi entre lissé et réel.

De plus tu fait un prorata au jour et non au mois. Janvier et février non pas le même nombre de jours et cela affecte le résultat. Tu veux vraiment au jour ?
eric

 

[farah.bekhtaoui]

Bonojur Eric,

Merci pour ta réponse.
Non, je veux le réel mais qui s'affiche sur 12 mois dans l'onglet "fixe".
Oui, je voudrais en nombre de jour car la date de fin d'un bail n'est pas obligatoirement en fin de mois. Il peut être le 1, 2, 3, etc. du mois.

N'hésites pas si tu souhaites que je clarifie d'autres points.

Merci,

 

[eriiic]

Ton Rent en Q c'est bien annuel ?

 

[farah.bekhtaoui]

Ton Rent en Q c'est bien annuel ?

Oui, c'est le montant annuel.

 

[eriiic]

Bonjour,

j'ai fais avec une fonction personnalisée en vba, par formule ça devenait un peu trop long.
Accepter les macros à l'ouverture, ensuite s'utilise comme une fonction native d'excel.
Sur feuille :
=rentMens(immeuble, mois, tableVariable)
eric

 

[farah.bekhtaoui]

Bonjour Eric,

Je pense qu'il y a une erreur sur ton calcul. Entre le mois de janvier et celui de février tu passes de 118 à 16 ce qui est impossible.
J'ai calculé manuellement les résultats que l'on devrait avoir dans l'onglet variable en ligne 21. Mon calcul prend comme base le nombre de jour.

Merci,

 

[eriiic]

Bonjour,

oui, un excès de zèle. Je mettais tout l'annuel réparti sur le temps restant cette année et non x/365
J'aurais été aux manettes d'Unibail il n'aurait pas fait -70% ;-)

Je trouve une petite différence avec toi mais tu n'as pas fait attention la date de début de BNP n'est pas au 1er
On ne devrait plus en être très loin maintenant.
Il reste un écart en juillet car je compte Resdida pour 1 jour. Les dates de fin doivent être exactes car toujours comptées. Difficile de faire la part des choses entre une vraie et une fausse date...
eric

 

[eriiic]

de rien...

 

[farah.bekhtaoui]

Bonjour Eric,

Merci pour ton aide. J'ai dû m'éclipser quelques jours pour raisons personnelles d'où mon absence.

Je souhaite faire évoluer mon fichier de la façon suivante en prenant en considération 2 nouveaux éléments:
- le pays (colonne A);
- la fréquence de quittancement mensuelle ou trimestrielle (colonne U).

J'expose les différents cas:
- Si le locataire est situé en France et que son quittancement est trimestriel (4) alors le loyer doit être réparti dans la page fixe ligne 4 sur le premier mois de chaque trimestre (janvier/avril/juillet/octobre). Le loyer est donc divisé en 4 et non lissé sur 12 mois;
- Si le locataire est situé en France ou aux UK et que son quittancement est mensuel (12) alors le loyer doit être réparti dans la page fixe ligne 4 sur chaque mois. Le loyer est donc lissé sur 12 mois tel que tu l'as réalisé dans notre premier cas.
- Si le locataire est situé aux UK et que son quittancement est trimestriel (4) alors le loyer doit être réparti dans la page fixe ligne 4 sur le premier mois de chaque trimestre anglais (mars/juin/septembre/décembre). Le loyer est donc divisé en 4 et non lissé sur 12 mois;

J'espère avoir été claire mais n'hésites pas si tu asdes questions. En espérant que tu pourras à nouveau m'aider dans ce cas de figure.

Merci Eric,
Bonne journée et de très bonnes fêtes de fin d'année

 

[eriiic]

Bonjour,

je ne comprend pas bien.
Ce n'est pas mon code, je t'avais fait une fonction.
eric

 

[farah.bekhtaoui]

Bonjour Eric,

Joyeux Noël!

Oui, c'est juste que j'ai pris une version 1 du fichier sur lequel j'avais fait une fonction sur un autre sujet. J'aimerais bien que ta fonction soit sur le fichier daté du 02/12/2020 mais je t'inclus ton fichier en PJ.

Est-ce que tu as compris l'évolution que j'ai expliqué plus haut sur laquelle j'aurais besoin de ton aide?

Merci,
Farah

 

[eriiic]

Bonjour,

je pense avoir compris oui.
Décidément, ces anglais ne veulent jamais rien faire comme tout le monde quand il s'agit de mesurer quelque chose...
Même principe ? C'est à dire selon nombre de jours réels dans le trimestre concerné ?
Parce que là tu dis loyer / 4 ?!? Bon, moi je préfère, c'est plus simple. Mais ton besoin exact c'est ?
Bonnes fêtes de fin d'année à toi aussi
eric

Edit : tu pourrais préciser pour les 2 cas le 1er mois de l'année où tu veux voir le montant, et surtout quels mois il recouvre ? J'ai l'impression que FR c'est début de trimestre (mois à venir) et UK fin de trimestre (mois échus)

 

[farah.bekhtaoui]

Bonjour,

je pense avoir compris oui.
Décidément, ces anglais ne veulent jamais rien faire comme tout le monde quand il s'agit de mesurer quelque chose...
Même principe ? C'est à dire selon nombre de jours réels dans le trimestre concerné ?
Parce que là tu dis loyer / 4 ?!? Bon, moi je préfère, c'est plus simple. Mais ton besoin exact c'est ?
Bonnes fêtes de fin d'année à toi aussi
eric

Edit : tu pourrais préciser pour les 2 cas le 1er mois de l'année où tu veux voir le montant, et surtout quels mois il recouvre ? J'ai l'impression que FR c'est début de trimestre (mois à venir) et UK fin de trimestre (mois échus)

Haha Oui en effet, les anglais ne font rien comme nous!
On peut le faire par mois.
- Quand le quittancement est mensuel, que ça soit aux UK ou en France alors le loyer annuel est lissé sur 12 mois.
- Quand le quittancement est trimestriel en France, le loyer annuel tombe sur les mois de janvier/avril/juillet/octobre (exemple: si loyer annuel de 100 alors 25 en janvier, 25 en avril, 25 en juillet, 25 en octobre et 0 sur les autres mois).
- Quand le quittancement est trimestriel aux UK, le loyer annuel tombe sur les mois de mars/juin/septembre/décembre (exemple: si loyer annuel de 100 alors 25 en mars, 25 en juin, 25 en septembre, 25 en décembre et 0 sur les autres mois).

Merci encore pour ton aide Eric!

 

[eriiic]

Bonjour,

Ca je l'avais compris.
Je ne vois pas de réponses précises à mes questions en fait
eric