Spreading costs over months or as one off payments

[panesai1]

Hi,

I have an XL sheet which tracks my regular monthly and annual (one-off payment) expenses. (see below).

For monthly costs (eg. Electricity) i need a formula to enter the £42.00 in each of the corresponding months between the start/end dates (F2 to I2)
However, if the expense is a one-off annual payment, (i.e. the Monthly cost column is blank, e.g. Car insurance), I want the formula to place the corresponding cost only once in the corresponding month that the annual payment occured/will occur. In the case of car insurance, the £500 should appear once only in the Jan 2016 column (H4).

I have seen may forum posts that show the correct formula for spreading an annual cost over the period of a start /stop date (for instance if a cost of £12,000 was to be evenly spread out as 12*£1000 costs from Jan - Dec), but I have not seen any posts regarding my issue?

Hope someone can help?

Many thanks
Indy

	A	B	C	D	E	F	G	H	I
1		per month	per annum	start date	end date	Nov 2015	Dec 2015	Jan 2016	Feb 2016
2	Electricity	£42.00		4/11/13	7/7/16
3	Council tax	£103.00		8/5/14	12/7/16
4	Car insurance		£500.00	3/1/16	2/1/17
5	Car road tax		£180.00	1/6/15	31/5/16
6	Internet bill	£10.00		1/9/15	31/3/17

<tbody>
</tbody>

 

[DRSteele]

If I understand correctly, this should meet your needs. Just copy the formulas in row two down as far as necessary.

	A	B	C	D	E	F	G	H	I
1		per month	per annum	start date	end date	Nov-15	Dec-15	Jan-16	Feb-16
2	Electricity	$42.00		November 4, 2013	July 7, 2016	42	42	42	42
3	Council tax	$103.00		May 8, 2014	July 12, 2016	103	103	103	103
4	Car insurance		$500.00	January 3, 2016	January 2, 2017			500
5	Car road tax		$180.00	June 1, 2015	May 31, 2016
6	Internet bill	$10.00		September 1, 2015	March 31, 2017	10	10	10	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula F2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(F$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,"")) G2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(G$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=G$1,$E2>=G$1),$B2,"")) H2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(H$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=H$1,$E2>=H$1),$B2,"")) I2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(I$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=I$1,$E2>=I$1),$B2,"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[panesai1]

@DRSteele: Thank you so much !!! This is absolutely fantastic. Just tried it and it works a treat. Now I'm gonna go away, lock myself in a room and try to figure out how you've pieced it all together. Thanks again!

Indy

If I understand correctly, this should meet your needs. Just copy the formulas in row two down as far as necessary.

	A	B	C	D	E	F	G	H	I
1		per month	per annum	start date	end date	Nov-15	Dec-15	Jan-16	Feb-16
2	Electricity	$42.00		November 4, 2013	July 7, 2016	42	42	42	42
3	Council tax	$103.00		May 8, 2014	July 12, 2016	103	103	103	103
4	Car insurance		$500.00	January 3, 2016	January 2, 2017			500
5	Car road tax		$180.00	June 1, 2015	May 31, 2016
6	Internet bill	$10.00		September 1, 2015	March 31, 2017	10	10	10	10

<tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula F2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(F$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,"")) G2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(G$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=G$1,$E2>=G$1),$B2,"")) H2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(H$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=H$1,$E2>=H$1),$B2,"")) I2 =IF(AND(ISNUMBER($C2),EOMONTH($D2,-1)=EOMONTH(I$1,-1)),$C2,IF(AND(ISNUMBER($B2),$D2<=I$1,$E2>=I$1),$B2,"")) <tbody> </tbody>

<tbody>
</tbody>

 

[DRSteele]

Oh good show!

The formula looks to see whether or not there is a number in Per Annum as well as whether or not the month of Start Date matches the month of the current column; if so it reports the per Annum number, otherwise putting something else because of the FALSE. The second IF (which is triggered when the first IF reports a FALSE) looks to see whether or not there is a number in the Per Month column as well as whether or not the months of Start Date and End Date bookend the month of the current column; if so, it reports the Per Month Number, otherwise reporting a blank (or "" in Excel).

 

[panesai1]

Oh good show!

The formula looks to see whether or not there is a number in Per Annum as well as whether or not the month of Start Date matches the month of the current column; if so it reports the per Annum number, otherwise putting something else because of the FALSE. The second IF (which is triggered when the first IF reports a FALSE) looks to see whether or not there is a number in the Per Month column as well as whether or not the months of Start Date and End Date bookend the month of the current column; if so, it reports the Per Month Number, otherwise reporting a blank (or "" in Excel).

Hi DRSteele: This is very very helpful for a novice/learner such as myself. Thanks again for everything !

Indy

 

[panesai1]

Hi again...

Sorry, to re-bump my own thread when I had thought it was all resolved.... but I just noticed two nuances....

1. The following seems to happen for whichever month is used as start date:
   • When there is a cost in the "per month" column, and start date is the first day of the month (eg. 01-Jan-2016), the cost is correctly displayed in the Jan 2016 column.
   • But, when there is a cost in the "per month" column, and start date is NOT the first day of the month (eg. 02-31 Jan 2016), the cost is NOT displayed in the Jan 2016 column. (but does display in correctly subsequent months, provided that the end date has been set sufficiently in to the future. So in this case, the costs are displayed from Feb 2016 onwards)
2. When there is a cost in the annual column (eg. £50), and a Start and End date are entered (04-Jul-2016 start , end 04-Jul-2025), the cost is correctly displayed only in the Jul-2016 column, but does not repeat in subsequent years (as per end date, it should repeat every July until year 2025).

Any ideas?

Many thanks in advance

Indy

 

[DRSteele]

It's not a "re-bump" --- the problem has not been solved, so post away until we're done. I've invented some more data to help us to get to the bottom of this.

I fixed my oversight in point 2; I stupidly thought it was a one-time payment. Now the formula will report a figure when a per annum payment month matches the month in the column and the year in the column is bookended by the payment start and end years. See below.

I don't quite follow what you mean in point 1; are you suggesting that my 37 payment on Dec 17/15 should show up in the Dec 1/15 column? Note that the dates in Row1 are all the first day of the month. Could you please clarify point 1?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		per month	per annum	start date	end date	Nov 1, 2015	Dec 1, 2015	Jan 1, 2016	Feb 1, 2016	Jan 1, 2017	Jul 1, 2019	Jul 1, 2025	Jul 1, 2026
2	Electricity	$42.00		Nov 4, 2013	Jul 7, 2016	42	42	42	42
3	Council tax	$103.00		May 8, 2014	Jul 12, 2016	103	103	103	103
4	Car insurance		$500.00	Jan 3, 2016	Jan 2, 2017			500		500
5	Car road tax		$180.00	Jun 1, 2015	May 31, 2016
6	Internet bill	$10.00		Sep 1, 2015	Mar 31, 2017	10	10	10	10	10
7	Beer exam		$50.00	Jul 4, 2016	Jul 4, 2025						50	50
8	Bacon ration	$37.00		Dec 17, 2015	Jun 30, 2026			37	37	37	37	37

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula F2 =IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[panesai1]

It's not a "re-bump" --- the problem has not been solved, so post away until we're done. I've invented some more data to help us to get to the bottom of this.

I fixed my oversight in point 2; I stupidly thought it was a one-time payment. Now the formula will report a figure when a per annum payment month matches the month in the column and the year in the column is bookended by the payment start and end years. See below.

I don't quite follow what you mean in point 1; are you suggesting that my 37 payment on Dec 17/15 should show up in the Dec 1/15 column? Note that the dates in Row1 are all the first day of the month. Could you please clarify point 1?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		per month	per annum	start date	end date	Nov 1, 2015	Dec 1, 2015	Jan 1, 2016	Feb 1, 2016	Jan 1, 2017	Jul 1, 2019	Jul 1, 2025	Jul 1, 2026
2	Electricity	$42.00		Nov 4, 2013	Jul 7, 2016	42	42	42	42
3	Council tax	$103.00		May 8, 2014	Jul 12, 2016	103	103	103	103
4	Car insurance		$500.00	Jan 3, 2016	Jan 2, 2017			500		500
5	Car road tax		$180.00	Jun 1, 2015	May 31, 2016
6	Internet bill	$10.00		Sep 1, 2015	Mar 31, 2017	10	10	10	10	10
7	Beer exam		$50.00	Jul 4, 2016	Jul 4, 2025						50	50
8	Bacon ration	$37.00		Dec 17, 2015	Jun 30, 2026			37	37	37	37	37

<tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula F2 =IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),$D2<=F$1,$E2>=F$1),$B2,"")) <tbody> </tbody>

<tbody>
</tbody>

Thanks again DRSteele!

So for point 1, what i mean.... using your example in Row 8 (Bacon ration) above....

• As you can see in your table, the first instance of Bacon Ration occurs in Dec 2015 (17th day). Therefore there should be an entry of $37 in cell G8. However, there is no entry, and the entries only begin from H8, I8, J8 onwards.
• However, you will find, if you change the start date of Bacon Ration to Dec 01, 2015, suddenly $37 will appear in cell G8.
• Dec 01, 2015 is the only start date that correctly enters $37 in cell G8. If you change the date to Dec 02, 2015; Dec 03, 2015, etc.... it will fail to show an entry in cell G8.

Hope it makes sense?

Indy

 

[DRSteele]

I see I had mistakenly inferred that you did not want any payments that occurr mid-month to be included in that month's columns. I think finally knocked the sticky wicket:

Put this in F2 and copy AcrossAndDown

=IF(AND(ISNUMBER($C2),MONTH($D2)=MONTH(F$1),YEAR($D2)<=YEAR(F$1),YEAR($E2)>=YEAR(F$1)),$C2,IF(AND(ISNUMBER($B2),(EOMONTH($D2,-1)+1)<=F$1,$E2>=F$1),$B2,""))

P.S. No need to reply with quotes...the post will become monstrous!

 

[panesai1]

You da man !!
It worked. Thanks a million.
Indy