+ Reply to Thread
Results 1 to 7 of 7

Calculate vacancy loss over several months.

  1. #1
    Registered User
    Join Date
    10-26-2020
    Location
    Greenville, SC USA
    MS-Off Ver
    Microsoft 365
    Posts
    4

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Calculate vacancy loss over several months.

    Why is your sheet protected and what is the password?

  3. #3
    Registered User
    Join Date
    10-26-2020
    Location
    Greenville, SC USA
    MS-Off Ver
    Microsoft 365
    Posts
    4

    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. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Calculate vacancy loss over several months.

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

  5. #5
    Registered User
    Join Date
    10-26-2020
    Location
    Greenville, SC USA
    MS-Off Ver
    Microsoft 365
    Posts
    4
    Quote Originally Posted by belinda200 View Post
    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. #6
    Registered User
    Join Date
    10-26-2020
    Location
    Greenville, SC USA
    MS-Off Ver
    Microsoft 365
    Posts
    4
    Quote Originally Posted by belinda200 View Post
    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)?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Calculate vacancy loss over several months.

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

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to calculate savings/loss
    By jsmith1529 in forum Excel General
    Replies: 5
    Last Post: 08-10-2019, 10:37 PM
  2. [SOLVED] Change background color based on a gain, loss or void of sales over two months
    By janemongthu in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-31-2019, 07:43 AM
  3. [SOLVED] Calculate win/loss streaks
    By Jim.water in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2018, 09:04 AM
  4. Calculate win or loss
    By ChaosTrader63 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-18-2015, 12:58 PM
  5. Calculate profit/loss
    By Boldsen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2014, 08:34 AM
  6. Replies: 8
    Last Post: 02-20-2014, 05:46 PM
  7. Replies: 1
    Last Post: 06-14-2013, 01:14 AM

Tags for this Thread

Bookmarks

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