Spread cost over months

[teens300]

Hi
i have an excel which requires allocating a sum across the months in a contract period. And in case of early termination of contract, the unallocated sum is required to put into the month when the contract is terminated.

how can i do the allocation using excel formula? hope someone can help? And example is shown below

Contract start date: 1 Apr 2018
Contract end date: 31 Mar 2020
Early termination: 2 Oct 2018
Total sum to spread over contract period: $2400; cost allocated per month $100

Cost allocated in FY2018
Jan:0
Feb:0
Mar:0
Apr: $100
May:$100
Jun:$100
Jul:$100
Aug:$100
Sept:$100
Oct:$1800
Nov:$0
Dec:$0

Thanks a lot

 

[DRSteele]

Welcome to the forum.

I think this is what you're after.
I was required to assume a few things. Data should always use dates as Excel Date Serial Numbers (formatted as Dates, of course). If an employee is hired any day other than the first of the month, then his Allocation is zero. There is no Allocation in a month he is terminated. No employee will be terminated the same month he was hired.

Book1
	A	B
1	Contract start date	April 1, 2018
2	Contract end date	March 31, 2020
3	Early termination	October 2, 2018
4	Total sum to spread over contract period	$2,400.00
5	Allocation	$100.00
6	Allocations	6
7
8	Cost allocated in FY2018
9	January 1, 2018	0
10	February 1, 2018	0
11	March 1, 2018	0
12	April 1, 2018	100
13	May 1, 2018	100
14	June 1, 2018	100
15	July 1, 2018	100
16	August 1, 2018	100
17	September 1, 2018	100
18	October 1, 2018	1800
19	November 1, 2018	0
20	December 1, 2018	0
Sheet47

Cell Formulas
Range	Formula
B6	=MONTH(EOMONTH(MIN($B$2,$B$3),-1)+1)-MONTH(IF(DAY(B1)=1,B1,EOMONTH(B1,0)+1))
B9	=IF((A9>=$B$1)*(EOMONTH(A9,0)<=MIN($B$2,$B$3)),$B$5,0)+IF(EOMONTH(A9,0)=EOMONTH(MIN($B$2,$B$3),0),$B$4-$B$5*$B$6,0)

 

[teens300]

Thanks DRSteele.

But what if the date of early termination is not pre-defined?

And i change the dates as follows, the result in B6 end as a -2
Start: 12 June 2017
End: 12 June 2042

Early Terminate: 28 May 2018

Thanks a lot for your help!

 

[DRSteele]

Do you mean, what if there is no early termination date? Well, that would mean that $100 is allocated to each month but then the remaining $1800 is not allocated. Just what is this all about?

Please provide more detail on the nature of the problem here.