Annual Rent Schedule/Forecast

Kelly Meck

Hi all!

I want to create a yearly calendar for the 106 leases I manage showing the monthly rent for each property.

My Master Lease sheet has columns for "Monthly Rent", "Rent Increase Date", and "Monthly Rent After Increase" which will be effective once the rent increase date is reached. If there are no renewal options left for a property, the rent increase date populates "N/A".

My Rent Schedule Calendar has a column for each month, and each of the rows are titled by a property ID.

If there are renewal options remaining on a property lease, the following formula returns a correct value, if there are no renewal options left, I get "#INVALIDOPERATION".

=IF(VLOOKUP([Property ID]@row, {Master Lease Sheet 1}, 48) > DATE(2023, 4, 30), VLOOKUP([Property ID]@row, {Master Lease Sheet - Rent VLOOKUP}, 40), VLOOKUP([Property ID]@row, {Master Lease Sheet Range 2}, 50))

I've tried every iteration I can think of to get the formula to return "Monthly Rent" if there is an "N/A" but I can't figure it out for the life of me. If there is even a better way than what I'm trying to create this rent forecast, I'm all ears. I super hope this question makes sense. Thanks for your time!

Genevieve P.

Hi @Kelly Meck

If I'm understanding your issue correctly, you could use the IFERROR function to return text instead of receiving an error.

=IF(IFERROR(VLOOKUP([Property ID]@row, {Master Lease Sheet 1}, 48), 0) > DATE(2023, 4, 30), IFERROR(VLOOKUP([Property ID]@row, {Master Lease Sheet - Rent VLOOKUP}, 40), "Monthly Rent"), IFERROR(VLOOKUP([Property ID]@row, {Master Lease Sheet Range 2}, 50), "Monthly Rent"))

See: IFERROR Function

If this hasn't helped, it would be useful to know what each of your {ranges} are looking at and returning.

Cheers!

Genevieve

Kelly Meck

Hi @Genevieve P.

Thank you so much for sending a response! I ended up abandoning my original plan and made a new sheet which included columns for the VLOOKUP data I was using so I could pare down on the formulas I was using. This method was much more successful.

In the first rent schedule attempt, I couldn't figure out the correct formula to return "Monthly Rent" if the cell in the "Rent Increase Date" of my master sheet read "N/A". My ultimate goal was to get the following:

• if the "Rent Increase Date" of my master sheet is greater than the Column Year and Month of my Schedule sheet, populate "Current Monthly Rent" from the master sheet.
• if the "Rent Increase Date" of my master sheet is less than the Column Year and Month of my Schedule sheet, populate "Monthly Rent After Increase" from the master sheet.
• if the "Rent Increase Date" of my master sheet is "N/A", populate "Current Monthly Rent" from the master sheet.

In my successful second attempt, I decided I was not in love with the thought of having so many VLOOKUPs in my work, so I added the columns for the rent change date, current monthly rent, next monthly rent, and options remaining to the sheet as helper columns. I also made header rows across the top with the year in the top row, the month number in the second and the last day of the month (number form) in the third. I then came up with this formula:

=IF(ISTEXT($[Rent Change Date]@row), $[Current Monthly Rent]@row, IF(DATE(YEAR($[Rent Change Date]@row), MONTH($[Rent Change Date]@row), DAY($[Rent Change Date]@row)) > DATE(January$1, January$2, January$3), $[Current Monthly Rent]@row, IF(DATE(YEAR($[Rent Change Date]@row), MONTH($[Rent Change Date]@row), DAY($[Rent Change Date]@row)) < DATE(January$1, January$2, January$3), $[Next Monthly Rent]@row)))

I feel pretty confident that I'll be able to modify the years and term dates as they progress or are extended, so the last thing I'll want to figure out one day is how to make payment amounts populate "$0.00" after the Last Rent Payment Date has passed.

I'm open to feedback from a pro! If there is a less complicated method to return the same or better results, I'm all ears! Thank you again for your response!

--Kelly