Thread: Distribute Project Value over entire project life Thanks: 0 Likes: 0

1. Distribute Project Value over entire project life

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

2. Re: Distribute Project Value over entire project life

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.

 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

 Cell Formula D2 =RIGHT(D1,4)+0 D3 =MAX(MIN(DATE(D\$2,12,31)-\$A3,DATE(D\$2,12,31)-DATE(D\$2,1,1)+1,\$B3-DATE(D\$2,1,1)+1,\$B3-\$A3+1),0)*\$C3/(\$B3-\$A3) K3 =SUM(D3:J3)

Excel tables to the web >> Excel Jeanie HTML 4

3. Re: Distribute Project Value over entire project life

Thank you Mr. Excel!! Works perfectly :D

4. Re: Distribute Project Value over entire project life

Originally Posted by rodmic
Thank you Mr. Excel!! Works perfectly :D
You're welcome.