Excel - Calculate the YTD Cost

December 2016




Issue


I have a spreadsheet listing the different contracts my department has and their values. Each contract has a separate 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 separate 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?

Solution


=((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 

Note that


Solved by rizvisa1

Related :

This document entitled « Excel - Calculate the YTD Cost » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.