Distribute Project Value over entire project life

[rodmic]

Good Day

I have to calculate the per day amount for a project and put it in a report showing it on an annual basis. My issue comes if the project starts mid of the month, i need a formula to determine the date from project start to year end and calculate per day rate.

Eg:
Project Start 18/12/2019
Project End 19/08/2022
Project Budget 100,000
Project rate per day will be (project end date) - (project start date) / project budget = 102.5 per day.

Now my report headers will be:
YEAR 2019| YEAR 2020| YEAR 2021|YEAR 2022|YEAR 2023 < - should be formula to show contract start year and end years based on Project Start & Project End years (optional)

In year 2019, formula should calculate 18/12/2019 to 31/12/2019 * 102.5 = 13days * 102.5
In year 2020, formula should calculate 01/01/2020 to 31/12/2020 * 102.5 = 365days * 102.5
In year 2021, formula should calculate 01/01/2021 to 31/12/2021 * 102.5 = 365days * 102.5
In year 2020, formula should calculate 01/01/2022 to 19/08/2022 * 102.5 = 230days * 102.5
In year 2023, formula should identify 01/01/2023 and Determine this date doesn't fall in contract period and return '0' value
total should tally with the Project Budge = 100,000

 

[Peter_SSs]

See if this is any use. D2 & D3 formulas copied across.
Note that my formula produces a slightly different value for 2020 and 2021. You said you wanted a 'per day' amount and 2020 has 366 days whereas 2021 only has 365, hence the difference.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Start	End	Budget	YEAR 2019	YEAR 2020	YEAR 2021	YEAR 2022	YEAR 2023	YEAR 2024	YEAR 2025	TOTAL
2				2019	2020	2021	2022	2023	2024	2025
3	18/12/2019	19/08/2022	100,000	1,333.33	37,538.46	37,435.90	23,692.31	0.00	0.00	0.00	100,000.00
Spread

 

[rodmic]

Thank you Mr. Excel!! Works perfectly :D

 

[Peter_SSs]

Thank you Mr. Excel!! Works perfectly :D

You're welcome.