EXCEL - How to spread X amount over N number of months

Question

I am creating a revenue recognition model that tells the user the specific months X amount of revenue falls into based on specific start date. For example, I have an invoice dated 1/1/17 for $1200 total in monthly services that will be incurred over the next 3 months ($400 per month)

I want to create the model so the user enters the invoice date "1/1/17", the invoice amount "1200", and the number of months the invoice amount is spread over. In this case "3".

The model is setup with 24 columns which have the MM/YYYY header in each column. "Jan 2017, Feb 2017..Dec 2018". As the user enters the date, amount and # months, the values per month "400" will populate in Jan, Feb, and March 2017. The remaining months will have zero or be blank because no revenue will be allocated in those months.

I don't want anyone to give me the answer here, but I don't even know if this is possible in excel without using VBA. Some insight in how to get started would be helpful. Thanks!

Answer 1

This could be a start:

B1, B2 and B3 are the input cells. B1 must be a date not a string.

D1 to O1 are the months. The values must be dates, not strings, but could then be formatted to show only month and year. Format MMM YYYY for example.

You need only inputting D1 and E1 as dates 2017-01-01 and 2017-02-01, then select D1:E1 and fill to right. Then a series will be created having from step to step the difference of E1 - D1, which is 1 month in this example.

Formula in D2 is

=IF(AND(D$1>=DATE(YEAR($B$1),MONTH($B$1),1),D$1<=DATE(YEAR($B$1),MONTH($B$1)+$B$3-1,1)),$B$2/$B$3,"XX")

and can be filled to right as needed. In the example up to O2.

Now if you are changing any of the input cells, the values in D2 to O2 will also changing due to the formula.