Hi, I have the following numbers, about 4 real estate projects (named
A, B, C, D). For each of them, there is a lease contract, and the rent
changes according to a "start date" and an "end date".
But since I need to sum the rent paid in each year, I need for each
project the sum of rent paid in that specific year, considering the
rent amount can change during the year.
In the example below, project A would pay 2 months of first due rent
in year 2011, then in 2012 it will pay for 10 months the same rent,
and then the new rent for the next 2 months, and so on.
How to do these total?
thanks

Projects START END ANNUAl RENT
A 01/11/2011 31/10/2012 1.800.000
A 01/11/2012 31/10/2013 2.200.000
A 01/11/2013 31/10/2014 2.300.000
A 01/11/2014 30/12/2021 2.800.000
B 01/01/2012 30/09/2013 1.800.000
B 01/10/2013 30/12/2021 3.600.000
C 01/11/2011 30/12/2021 2.600.000
D 01/01/2012 31/12/2013 2.000.000
D 01/01/2014 31/12/2015 2.400.000
D 01/01/2016 30/12/2021 2.800.000

On Nov 16, 10:23*am, via sarpi wrote:
Hi, I have the following numbers, about 4 real estate projects (named
A, B, C, D). For each of them, there is a lease contract, and the rent
changes according to a "start date" and an "end date".
But since I need to sum the rent paid in each year, I need for each
project the sum of rent paid in that specific year, considering the
rent amount can change during the year.
*In the example below, project A would pay 2 months of first due rent
in year 2011, then in 2012 it will pay for 10 months the same rent,
and then the new rent for the next 2 months, and so on.
How to do these total?
thanks

Projects START * * * * *END * * * * * * * * * * ANNUAl RENT
A * * * 01/11/2011 * * *31/10/2012 * * * 1.800.000
A * * * 01/11/2012 * * *31/10/2013 * * * 2.200.000
A * * * 01/11/2013 * * *31/10/2014 * * * 2.300.000
A * * * 01/11/2014 * * *30/12/2021 * * * 2.800.000
B * * * 01/01/2012 * * *30/09/2013 * * * 1.800.000
B * * * 01/10/2013 * * *30/12/2021 * * * 3.600.000
C * * * 01/11/2011 * * *30/12/2021 * * * 2.600.000
D * * * 01/01/2012 * * *31/12/2013 * * * 2.000.000
D * * * 01/01/2014 * * *31/12/2015 * * * 2.400.000
D * * * 01/01/2016 * * *30/12/2021 * * * 2.800.000

Your question needs better clarification.

Your question needs better clarification.

sorry for this.

So I have 4 real estate projects, for each of them there is a lease in
place, where a tenant pays a rent.
The problem is that this rent might change at any date, within the
year. In the example above, for the project A, tenant pays 1800m from
1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the
first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent
steps up to (2200/12) per month (second row in the example above).
And so on.
Rent might change/step up without any fixed rule. I need a formula
which gives me the total paid for each project in a specific year. So
the formula would check how many months for the year in question
tenant pays a specific bracket (in the example above, for 2012 tenant
pays first bracket for 10 months, and second bracket for 2 months) and
sum the result. I believe is a sort of Sumproduct IF the bracket falls
within the year I am summing up, or something like this.

Hope this is clearer now
thanks a lot in advance
cheers

On Nov 16, 5:11*pm, via sarpi wrote:
Your question needs better clarification.

sorry for this.

So I have 4 real estate projects, for each of them there is a lease in
place, where a tenant pays a rent.
The problem is that this rent might change at any date, within the
year. In the example above, for the project A, tenant pays 1800m from
1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the
first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent
steps up to (2200/12) per month (second row in the example above).
And so on.
Rent might change/step up without any fixed rule. I need a formula
which gives me the total paid for each project in a specific year. *So
the formula would check how many months for the year in question
tenant pays a specific bracket (in the example above, for 2012 tenant
pays first bracket for 10 months, and second bracket for 2 months) and
sum the result. I believe is a sort of Sumproduct IF the bracket falls
within the year I am summing up, or something like this.

Hope this is clearer now
thanks a lot in advance
cheers

Instead of recreating your project, just send this and your file with
examples to dguillett1 @gmail.com

hi,

create a new table
F2: F5 put the values €‹€‹(A, B, C, D)
G1: Q1 put the values €‹€‹(2011, 2012, 2013, etc ...)
in cell G2 put the following formula:

=SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11))
+SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(13-MONTH($C$2:$C$11))*($D$2:$D$11))

and copy this formula in range G2:Q5


--
isabelle



Le 2011-11-16 18:11, via sarpi a écrit :

Your question needs better clarification.

sorry for this.

So I have 4 real estate projects, for each of them there is a lease in
place, where a tenant pays a rent.
The problem is that this rent might change at any date, within the
year. In the example above, for the project A, tenant pays 1800m from
1/11/2011 to 31/10/2012. This means that in 2012 tenant pays for the
first 10 months= (1800/12)*10. Then for the last 2 months of 2012 rent
steps up to (2200/12) per month (second row in the example above).
And so on.
Rent might change/step up without any fixed rule. I need a formula
which gives me the total paid for each project in a specific year. So
the formula would check how many months for the year in question
tenant pays a specific bracket (in the example above, for 2012 tenant
pays first bracket for 10 months, and second bracket for 2 months) and
sum the result. I believe is a sort of Sumproduct IF the bracket falls
within the year I am summing up, or something like this.

Hope this is clearer now
thanks a lot in advance
cheers

sorry,

you have to remove the second "13-"

=SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11))
+SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(MONTH($C$ 2:$C$11))*($D$2:$D$11))


--
isabelle


Le 2011-11-17 09:39, isabelle a écrit :
hi,

create a new table
F2: F5 put the values €‹€‹(A, B, C, D)
G1: Q1 put the values €‹€‹(2011, 2012, 2013, etc ...)
in cell G2 put the following formula:

=SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($B$2:$B$11)=G$1)*(13-MONTH($B$2:$B$11))*($D$2:$D$11))
+SUMPRODUCT(--($A$2:$A$11=$F2)*(YEAR($C$2:$C$11)=G$1)*(13-MONTH($C$2:$C$11))*($D$2:$D$11))

and copy this formula in range G2:Q5

thanks really a lot....BUT it doesnt work.

take for example the Project "C".
your formula dont get it, since its start-year is before our
calculation date, and its end-date is after. This means the rent is
always the same in the years between START and END, but your formula
(Year = ...) don't consider it

On Wed, 16 Nov 2011 08:23:07 -0800 (PST), via sarpi wrote:

Hi, I have the following numbers, about 4 real estate projects (named
A, B, C, D). For each of them, there is a lease contract, and the rent
changes according to a "start date" and an "end date".
But since I need to sum the rent paid in each year, I need for each
project the sum of rent paid in that specific year, considering the
rent amount can change during the year.
In the example below, project A would pay 2 months of first due rent
in year 2011, then in 2012 it will pay for 10 months the same rent,
and then the new rent for the next 2 months, and so on.
How to do these total?
thanks

Projects START END ANNUAl RENT
A 01/11/2011 31/10/2012 1.800.000
A 01/11/2012 31/10/2013 2.200.000
A 01/11/2013 31/10/2014 2.300.000
A 01/11/2014 30/12/2021 2.800.000
B 01/01/2012 30/09/2013 1.800.000
B 01/10/2013 30/12/2021 3.600.000
C 01/11/2011 30/12/2021 2.600.000
D 01/01/2012 31/12/2013 2.000.000
D 01/01/2014 31/12/2015 2.400.000
D 01/01/2016 30/12/2021 2.800.000

Assuming your table above starts in A1, NAME the ranges
Select the table
Defined Names: Create from selection / top row

You will then have four names like:
ANNUAL_RENT =Sheet1!$D$2:$D$11
END =Sheet1!$C$2:$C$11
Projects =Sheet1!$A$2:$A$11
START =Sheet1!$B$2:$B$11

Then, set up a new table:

I1: A
J1: B
K1: C
L1: D

H2: 2011
.... ...
H12: 2012

This formula must be **array-entered**:

I2:
=SUMPRODUCT((Projects=I$1)*(TRANSPOSE(
DATE($H2,ROW(INDIRECT("1:12")),1))=START)*
(TRANSPOSE(DATE($H2,ROW(
INDIRECT("1:12")),1))<=END)*ANNUAL_RENT/12)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Finally, select the cell and fill right to L2
Then select I2:L2 and fill down to I12:L12

This is what I get:

A B C D
2011 $300.00 $- $433.33 $-
2012 $1,866.67 $1,800.00 $2,600.00 $2,000.00
2013 $2,216.67 $2,250.00 $2,600.00 $2,000.00
2014 $2,383.33 $3,600.00 $2,600.00 $2,400.00
2015 $2,800.00 $3,600.00 $2,600.00 $2,400.00
2016 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2017 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2018 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2019 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2020 $2,800.00 $3,600.00 $2,600.00 $2,800.00
2021 $2,800.00 $3,600.00 $2,600.00 $2,800.00

Note that time periods before the first date in START for a given project are NOT included.

hi,

ok i see what you want to say
but i see no other solution than use a VBA macro,
is what you want to do ?

--
isabelle



Le 2011-11-18 09:33, via sarpi a écrit :
thanks really a lot....BUT it doesnt work.

take for example the Project "C".
your formula dont get it, since its start-year is before our
calculation date, and its end-date is after. This means the rent is
always the same in the years between START and END, but your formula
(Year = ...) don't consider it

Ron got it right!

thanks a lot!

Ron,

what's the point of using Transpose? doesn't the array have the same
dimension even without transposing?

if I get it properly, the formula Ron suggests basically creates a
monthly schedule within the formula itself. Pretty smart!

On Sat, 19 Nov 2011 07:15:32 -0800 (PST), via sarpi wrote:

Ron,

what's the point of using Transpose? doesn't the array have the same
dimension even without transposing?


Yes, but you need to multiply each element in each array by the other; if you don't transpose then you will only do a one-to-one, rather than a one-to-all. Like multiplying two arrays of columns, vs an array of row by an array of columns.

On Sat, 19 Nov 2011 07:01:09 -0800 (PST), via sarpi wrote:

Ron got it right!

thanks a lot!

Glad to help. Thanks for the feedback.