YTD Cost

Solved/Closed
sha9gy - Jul 22, 2010 at 12:26 PM
 sha9gy - Jul 22, 2010 at 01:49 PM
Hello,

I need your help developin this formula please.

I have a spredsheet listing the different contracts my department has and their values. Each contract has a seprate start and end date.

For example:
Contract 1 on Row #50 has a contract input date of Jan 12, 2010 and Start date of Jan 1, 2010 and end date of Dec. 31, 2010 with a value of $100,000.00

Contract 2 on Row # 55 has a contract input date of March 8, 2010, Start Date of April 1, 2010, End Date of March 31, 2013 and a value of $500,000.00

I would like to figure out the YTD cost of each contract in a seprate cell.

Using the above example,
contract #1 should return a YTD value of $58,333.33 OR ($100,000.00/ 12 months * 7 months)

Contract #2 should return a YTD value of $83,333.33 OR ($500,000/ 24 months (term of the contract, difference in start and end dates) * 4 months)


Would you be able to help me with this?

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 22, 2010 at 01:35 PM
For first one I can help

=((YEAR(TODAY())-YEAR(B2)) * 12 + MONTH(TODAY()) - MONTH(B2) + 1) * D2/((YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)+1)

B2 : Start Date
C2: end Date
D2: amount
One of the assumption is that, the system clock of PC is showing right dates :P (one of my pc does not)


For the 2nd one you need help of a very creative accountant who can make Start Date of April 1, 2010, End Date of March 31, 2013 to come out to be 24 months instead of 36 :P
0
Oops, my bad... I meant 36 months :( shame on me :( :)
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 22, 2010 at 01:42 PM
Well in that case the formula still hold.
0
Thank you Rizvisa1. It worked like a charm... :)
0
Oops, what a bad accountant I am :P Shame on me... I meant 36 months.... now can you help ?
0