Prorated Rent Schedule Formula Help
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/1905/31/20 Total Rent Due: $175,000
06/01/2005/31/21 Total Rent Due: $200,000
Total Rent of 2019:
Total Rent of 2020:
Comments

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
Categories
Check out the Formula Handbook template!