# 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?  Register To Reply

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

Why is your sheet protected and what is the password?  Register To Reply

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.  Register To Reply

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

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

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.  Register To Reply

6. Originally Posted by belinda200 thanks, can you add an example where the formula does not work ?
Did the new file download open for you?

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)?  Register To Reply

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.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1