Prorated Rent Schedule Formula Help

Ginny Hager

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

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).