# Calculate vacancy loss over several months.

1. ## Calculate vacancy loss over several months.

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?

3. ## Re: Calculate vacancy loss over several months.

The version i opened on my computer appears to be unlocked, my apologies. I'm uploading a fresh copy. Hopefully this will work better.

4. ## Re: Calculate vacancy loss over several months.

thanks, can you add an example where the formula does not work ?

5. Originally Posted by belinda200
thanks, can you add an example where the formula does not work ?
Hi Belinda, the reason it won't work is that the per diem rent changes depending on the nun beer of days in the month. So you may have a vacancy from 2/12-3/5. The equation longhand would be (rent is \$500/mo) (500/28*16+500/31*5). I need excel to pull the number of days in a month accurately to determine the correct per diem, then calculate the total.

6. Originally Posted by belinda200
thanks, can you add an example where the formula does not work ?

I think my solution is going to be a nested if statement that checks the difference in the months, then calculated the charges... if they're equal, the current formula is my first result, if they are not next if seeing if today- vacated month=1, calculate the current formula on last month's qty of days + current formula, and so on... you think that would work? That's going to be so long... suggestions to shorten ( vlookup, table)?

7. ## Re: Calculate vacancy loss over several months.

Try pasting the following into cell L9:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)