Skip to main content
Solved

Rent Allocation by Start date and end date

  • February 25, 2025
  • 3 replies
  • 132 views
J
Apprentice Author

Hello All,

I currently need a bit of help with allocations. I want to create a rent schedule where the yearly rent amount needs to be allocated based on the yearly contract (start and end date). Just to clarify, one store code can repeat multiple times due to different contract years. Rent type is just used to differentiate between office and stores.

 

Best answer by Tanya Krytsyna

Hi Juned,

Have you already started working on a formula to streamline this allocation? You might find the PRORATA() function helpful for distributing rent amounts based on a given period. 

3 replies

T
Employee
Forum|alt.badge.img+1
  • Tanya KrytsynaEmployee
  • Employee
  • Answer
  • February 28, 2025

Hi Juned,

Have you already started working on a formula to streamline this allocation? You might find the PRORATA() function helpful for distributing rent amounts based on a given period. 

J
Apprentice Author
  • Juned.husainApprentice Author
  • Author
  • Apprentice Author
  • March 4, 2025

Hello ​@Tanya Krytsyna ,


Thank you for the suggestion. Prorata function works. I am currently facing an issue where it will start prorating every 1st month of the first year lease contract. I want the formula to only prorate the 1st month of the 1st lease contract while it takes the full value for all the subsequent 1st of month of the other lease contract.
EG: Store 1 Yr 1 annual rent is 100k
        Store 1 Yr 2 annual rent is 120k
        Store 1 Yr 3 annual rent is 140k


in the lease contract- due to prorata formula- the total rent for the year will be around 99k due to the 1st month being prorated. The 2nd and 3rd year will also prorate the 1st month (118k,139K). 

Ideal scenario - I want only the 1st month of the 1st year lease contract to prorate EG- annual rent Y1 is 100k I get a total of 99k due to 1st month being prorated (99k). Whereas, for Y2 and Y3, I want the 1st month not to be prorated but the value to be taken (120k,140k)

Current formula 
 

 

T
Employee
Forum|alt.badge.img+1

Hi ​@Juned.husain 

To ensure only the first month of the first lease contract is prorated, while subsequent years take the full value, the following adjustments are needed:

  • IF Condition: Check if the lease start year is the earliest for that store. If true, apply prorata only to the first month; otherwise, take the full monthly value. 
    IF(
        YEAR('Rent Schedule'.'Start date') = MIN(YEAR('Rent Schedule'.'Start date') BY 'Rent Schedule'.Store_Name), 
        PRORATA(Month, 'Rent Schedule'.'Start date', 'Rent Schedule'.'End date') * 'Rent Schedule'.'Monthly Value',
        'Rent Schedule'.'Monthly Value'
    )

     

  • Grouping by Store: Ensure the calculation applies prorata only for the first lease period per store, preventing unintended proration in later years.
  • Filters & Aggregations: Existing filters and summation logic remain unchanged.

This ensures correct proration only for the first lease contract while keeping full values for subsequent years. Let me know if you need further tweaks! 🚀

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement