Rent Roll Help!

Thanks:  0
Likes:  0

1. ## Rent Roll Help!

I feel like an idiot b/c I think this shouldn't be too hard:

I have a table for an office building showing tenants and lease expirations.
I want to show, as a percentage, the amount of tenants leaving in any given year.

Layout:
Column A = Tenant Name
Column B = Lease Start*
Column C = LEASE END*
Column D = SQUARE FEET
Column E = BASE RENT

*Dates are entered as follows: 1/15/2005

I can do this with a funky SUMIF feature (for Sq.Ft and Base Rent calculations), but isn't there something easier?

I want to know:
1. TOTAL Sq.Ft expiring for years 2008 - 2015 and,
2. TOTAL Base Rent expiring for years 2008 - 2015.

Everything else will be easy to calculate. Is there a SUMIF command that will display all leases expiring ONLY in 2009, 2010, 2011, etc...???

Thanks for any help!

-JRG

2. ## Re: Rent Roll Help!

Try using SUMPRODUCT, e.g. for total base rent expiring for 2008-2015

=SUMPRODUCT((YEAR(C2:C100)>=2008)*(YEAR(C2:C100)<=2015),E2:E100)

3. ## Clarification on post

Sorry I wasn't very clear.

I need to know ONLY the amount of SF expiring in 2008, and in the next cell, ONLY amount of SF expiring in 2009, then ONLY the amount of SF expiring in 2010, etc...

I don't need to know ALL the leases expiring from 2008 - 2015 as a lump sum, I would just sort the data by leases expiring and add the appropriate cells.

Thanks for the help!!!

-JRG

Is there a CSE formual I need to use in order to have the formula return the total
SF expiring >2008 but <2009?

Also, do I need to convert the format of dates expiring to just a YEAR format? (e.g. 1/15/2008 needs to be converted to 2008)???

4. ## Re: Rent Roll Help!

If you use SUMPRODUCT it's just a regular formula, you don't need CSE....and you don't need to convert the dates.

List the years in, say, G2 down then use this formula in H2 copied down for a sum of rent for leases expiring each year

=SUMPRODUCT(--(YEAR(C\$2:C\$100)=G2),E\$2:E\$100)

change ranges as necessary

5. ## Re: Rent Roll Help!

Hi elmsley4:

Following is a solution using EXCEL's one-variable DataTable ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2H5I5H6I6H7I7H8I8H9I9H10I10H11I11H12I12H13I13 =

A
B
C
D
E
F
G
H
I
1
TenantLeaseStartLeaseEndSFTRent database
2
T_36/15/20076/14/200812001500 FALSE criterion
3
T_43/1/20082/28/201120002500 DataTable
4
T_57/9/20087/8/200912001500 DataTable Solution
5
T_611/16/20082/28/201020002500 LeaseExpireYearSFTRent
6
T_73/26/20097/8/201212001500 200812001500
7
T_88/3/20092/28/201320002500 200912001500
8
T_912/11/20097/8/201212001500 201020002500
9
T_104/20/20102/28/201520002500 201120002500
10
T_118/28/20107/8/201412001500 201224003000
11
T_121/5/20112/28/201520002500 201320002500
12
201412001500
13
201540005000
 Sheet3

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

computed criterion formula in cell G2 is ... =YEAR(C2)=\$G\$3

formula in cell H5 is ... =DSUM(\$A:\$E,4,\$G\$1:\$G\$2)

formula in cell I5 is ... =DSUM(\$A:\$E,5,\$G\$1:\$G\$2)

DataTable Range is ... G5:I13; column_input cell is \$G\$3

6. ## Re: Rent Roll Help!

This is awesome. I hate to say it, but JUST when I feel I'm getting pretty good w/ Excel, somoene like you makes me feel like a kid! (which is a good thing).

Thanks for the help. I don't fully understand how/why the above formulas work together, but I can appreciate the efficiency of the model.

Thank you again!

Best,

Joel

7. ## Re: Rent Roll Help!

Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....

8. ## Re: Rent Roll Help!

Originally Posted by elmsley4
Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....
Hi elmsley4:

H5 and I5 indeed house the DSUM formulas that are needed for the one-variable DataTable. And no there are no TEXT Boxes, cells H5, and I5 are CustomNumber Formatted to display "SFT", and "Rent" (to optionally delineate more meaningfully the headers for the respective columns of the subsequent rows of the DataTable.)

Please look into the use of EXCEL DataTable to fully understand the entire setup for this proposed solution.

9. ## Re: Rent Roll Help!

I am going to ask sort of the same question, for the same type of spreadsheet: I have a sheet containing the same basic data (square footages in Column C, Expiration Dates in Column E). But, I want to be able to see how much square footage is expiring within 6 months of TODAY, 12 months of TODAY, etc., with the total SF for each date range summing into one cell. Bonus points for conditional formatting that will highlight the names of tenants (in Column B) whose lease are expiring in those ranges.

Thanks!

C.

10. ## Re: Rent Roll Help!

Originally Posted by elmsley4
Quickly,

How do you have H5 & I5 contain text (SFT & Rent) AND an equation (DSUM)????

This isn't a text box....
Hi elmsley4:

Today I was prompted for solution to a followup question after a long time since 2008 vintage of this thread for which I Used Google spreadsheet's QUERY function ... and I thought I will revisit this question using the QUERY function ... and I have presented that solution in my following blog post

yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year
Cloud Computing -- Google Docs Way: yogi_Compute Year By Year SquareFootage And Rent For LeaseEnd Year

so please check it out to see how this works for you.

Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com

## User Tag List

#### Posting Permissions

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