help with Sum in between dates

[via sarpi]

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

 

[Don Guillett]

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.

 

[via sarpi]

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

 

[Don Guillett]

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

 

[isabelle]

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 :

 

[isabelle]

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))

 

[via sarpi]

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

 

[isabelle]

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 :

 

[via sarpi]

Ron got it right!

thanks a lot!

 

[via sarpi]

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!