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>

 

[dee101]

DRSteele, I think this is very useful, I also have bills that are due every 6 months could this be modified to add a category for every 6 months between per month and per annum? So if you put a number in the semi annual column and a start date of 2-15-16 it would show up under Feb-2016, Aug-2016 and Feb-2017…………

Thanks

 

[DRSteele]

DEE101, I'm glad we could help! yes, it could be modified so.

 

[DRSteele]

You're welcome, Panesai1. I'm glad we got it sussed.

 

[dee101]

DRSteele, so after I added a column in between per month and per annum what would be the formula to use in G2 to account for the new information added for the 6 month info??
Thanks