Prorated Rent Schedule Formula Help

Ginny Hager
Ginny Hager ✭✭
edited 12/09/19 in Formulas and Functions

Need a formula to calculate what is due per calendar year when the rent amount changes year to year (midyear)   Any help appreciated!

06/01/19-05/31/20 Total Rent Due: $175,000

06/01/20-05/31/21 Total Rent Due: $200,000

Total Rent of 2019:

Total Rent of 2020:



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The easiest way would be to break it down into a few more sections.


    6/1/19 - 12/31/19

    1/1/20 - 5/31/20

    6/1/20 - 12/31/20

    1/1/21 - 5/31/21


    Have these dates in two separate date columns ([Range Start] and [Range End] for this example.


    In the next column, you would have the rent amount for that period ([Amount Per Year] for this example).


    Since you are splitting the year in half, you can use another column ([Amount Per Period]) to take the yearly amount and divide it by two which will give you the amount due per period.


    =[Amount Per Year]@row / 2


    We will then use a Year column to manually enter the year you are looking to sum and an [Amount Due] column with the following:


    =SUMIFS([Amount Per Period]:[Amount Per Period], [Range End]:[Range End], YEAR(@cell) = Year@row)


    The [Amount Due] column will display next to the corresponding year, how much is owed for that calendar year. (See screenshot).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!