Hi everyone! Attached you will find a spreadsheet I'm working on to report occupancy and delinquency for property management. In column L you'll vacancy loss. The formula I have there works as long as the vacancy does not extend beyond the same month that the unit is vacated. For instance, the formula will gather the number of days in the month in column f, and calculate prorated rent for that month accurately.
long hand example: rent: $500 Vacated: 10/15/20 days vacant:16 (15th through 31st) so the equation would be: 500/31*16=vacancy loss Oct 16-31.
The problem is that we often have vacancy dates that carry over from one month to the next. Worst case scenario, a unit is down for several months due to catastrophic loss. Let's say the unit is vacated 10/15/20, and is not filled until 1/21/21.
Building on the above equation: 500/31*16 + 500/30*30 + 500/31*31 + 500/31*20 = $1489 (round to whole dollar.)
October 31 -- Nov 30 days -- Dec 31 days -- Jan 31 days.
How can I make this calculation based on any dates up to a fiscal year in length?
Bookmarks