Spread cost over months

teens300

New Member
Joined
Mar 20, 2018
Messages
2
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
AB
1Contract start dateApril 1, 2018
2Contract end dateMarch 31, 2020
3Early terminationOctober 2, 2018
4Total sum to spread over contract period$2,400.00
5Allocation$100.00
6Allocations6
7
8Cost allocated in FY2018
9January 1, 20180
10February 1, 20180
11March 1, 20180
12April 1, 2018100
13May 1, 2018100
14June 1, 2018100
15July 1, 2018100
16August 1, 2018100
17September 1, 2018100
18October 1, 20181800
19November 1, 20180
20December 1, 20180
Sheet47
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top