Calculate between Dates

[whiteknight]

Hi All,
Need a quick help, I have the data for Lease contracts for specific periods "like 10 years"
I have annul fee and Annual increase rate , Contract period
I want to calculate my income during the year 2020 with an annual increase of 10% from the beginning of the contract
so like if one contract "the start date in 25-05-2019 and the End date in 24-05-2029" and I only need the income during the year 2020.

Lease.xlsx
	A	B	C	D	E	F
1	Contract Number	start date	end date	Annul Fee	Rate	the income between 1/1/2020 to 31/12/2020
2	1	01-01-18	31-12-28	$ 500.00	10%
3	2	25-05-19	24-05-29	$ 800.00	10%
4	3	19-03-17	18-03-27	$ 600.00	10%
5	4	21-06-16	20-06-26	$ 450.00	10%
Sheet2

 

[JamesCanale]

MrExcelPlayground.xlsm
	A	B	C	D	E	F
1	Contract Number	start date	end date	Annul Fee	Rate	the income between 1/1/2020 to 31/12/2020
2	1	1/1/2018	12/31/2028	$ 500.00	10%	$ 605.00
3	2	5/25/2019	5/24/2029	$ 800.00	10%	$ 880.00
4	3	3/19/2017	3/18/2027	$ 600.00	10%	$ 798.60
5	4	6/21/2016	6/20/2026	$ 450.00	10%	$ 658.85
Sheet22

Cell Formulas
Range	Formula
F2:F5	F2	=D2*(1+E2)^(2020-YEAR(B2))

This one is super simple - you get a lump sum payment essentially on the anniversary of the start of the contract. It disregards the finish date. If a contract starts or finishes during 2020, it won't prorate it or anything. It gets more and more interesting as you add more requirements.

 

[whiteknight]

MrExcelPlayground.xlsm
	A	B	C	D	E	F
1	Contract Number	start date	end date	Annul Fee	Rate	the income between 1/1/2020 to 31/12/2020
2	1	1/1/2018	12/31/2028	$ 500.00	10%	$ 605.00
3	2	5/25/2019	5/24/2029	$ 800.00	10%	$ 880.00
4	3	3/19/2017	3/18/2027	$ 600.00	10%	$ 798.60
5	4	6/21/2016	6/20/2026	$ 450.00	10%	$ 658.85
Sheet22

Cell Formulas
Range	Formula
F2:F5	F2	=D2*(1+E2)^(2020-YEAR(B2))

This one is super simple - you get a lump sum payment essentially on the anniversary of the start of the contract. It disregards the finish date. If a contract starts or finishes during 2020, it won't prorate it or anything. It gets more and more interesting as you add more requirements.

That would solve it but only if the start date of the contract on first day of January
but if we have for ex: "start date on 25-05-2019 "
from 25-05-2019 to 25-05-2020 with 800$ (144 day: from 01-01-2020 to 25-25-2020 )
and the Annual rate would increased by 10% from 25-05-2020 to 25-05-2021 for with 880$ (221 day from 25-05-2020 to 31-12-2020)