Multiyear Lease Analysis

[Gregg Wolin]

I'm analyzing a series of leases and would like to generate a single row that sums up the multiple rent steps in each without having to build a big matrix as shown in the screenshot. Any help would be appreciated!

 

[vletm]

Gregg Wolin
Would two columns match?
Tested with Your given sample data.

 

[Gregg Wolin]

Gregg Wolin
Would two columns match?
Tested with Your given sample data.

This is very close (and awesome)! I'm not sure what you mean by "two columns match" but the only modifications I need would be:

1. No blanks between the rent steps and,
2. The option to have if output horizontally (dates on top, numbers below).

I really thought this would have been possible with a "sumproduct" formula but you are the "Ninja" so VBA it is ;-)

Thanks in advance for your continued assistance!

 

[GraH - Guido]

Office 365 alternative, if I understand correctly.

=LET(
    FstDte;MAX(L9;LOOKUP(L9;F11:F18));
    rDtes;EOMONTH(FstDte-1;SEQUENCE(;DATEDIF(FstDte;G18;"m")));
    Periods;XLOOKUP(rDtes;G11:G18;I11:I18;;1);
    CHOOSE({1;2};rDtes;Periods)
    )

 

[vletm]

Gregg Wolin
"two columns match"
... You asked to get "to generate a single row that sums up the multiple rent steps"
... ... I offered both dates and sums
1. Okay
2. Select "down/right" => Press [ Do It ]

 

[Gregg Wolin]

Office 365 alternative, if I understand correctly.

=LET(
    FstDte;MAX(L9;LOOKUP(L9;F11:F18));
    rDtes;EOMONTH(FstDte-1;SEQUENCE(;DATEDIF(FstDte;G18;"m")));
    Periods;XLOOKUP(rDtes;G11:G18;I11:I18;;1);
    CHOOSE({1;2};rDtes;Periods)
    )

Spectacular! Almost exactly what i was looking for.

As I will always have an array of dates as a header row (as shown in my original post), can the formula be tweaked to use the header row as a lookup, placing the monthly rent from the lease in line with the corresponding period in the header above? Note in my post how the header row actually starts almost a year into the lease schedule. The usual scenario is that the lease will already exist when i an doing the analysis so the rents need to lay out based on my chosen investment period (the dates in the header row). Hope this makes sense ;-)

 

[Gregg Wolin]

Now i see how i misunderstood the "2 columns" question. In reality, I need 1 column of rent numbers that line up with an unrelated row (or column) of dates in a header as i outline above in my response to @GraH - Guido.

I am trying to replicate what shown in my example but i wasn't clear that there will be a header row of dates (unrelated to the leases) that the rent numbers need to line up with. Assume that there are a total of 6 lease tables the analysis and the user can choose to fill in 1 or more depending on how many leases (or renewals) exist.

 

[vletm]

Gregg Wolin Ver 3
Could You give at once all expected details?
> You can add as many 'lease's as You have ... use same procedure.
> I added maybe Your one more missed feature above dates.
Ps. those can overlap too ( 'previous end can be later than next start' ).

 

[Gregg Wolin]

Gregg Wolin Ver 3
Could You give at once all expected details?
> You can add as many 'lease's as You have ... use same procedure.
> I added maybe Your one more missed feature above dates.
Ps. those can overlap too ( 'previous end can be later than next start' ).

This is a great solution (even if the code is over my head)!

The only things i would change / add at the moment are:
1. I'd like for the total to be a few lines below the bottom of the results (not over the date in row 9). I can see where the code is

.Cells(30, x) = .Cells(9, x) + i)

but I don't know how to modify it so it will deposit the numbers a few rows below the final lease line.

2. Rather than having the sub create the date list, have it refer to a date list previously entered in row 10. Basically like filling in a calendar that already exists with the lease numbers corresponding to each month. does that make sense?

Thank you for your help - it's greatly appreciated.

 

[vletm]

Gregg Wolin ver 4
1. I moved those Your missed sums below - if You're missing Your few lines, then add something before 'Sum'.
... why needs to scroll to see sum-line? why cannot see it at once in the top ... hmm?
2. You should able to show ... what do You would like to have?
... 10th row Month-Year list will always create while solving this.
... 'My calendar' would be like my the 1st version - vertical - then there could see 'more' with 'less' scrolling.
Of course, those 'right side' -values could see without Filtering ... but would it be smoother to show those in other sheet?