Excel 2007 : Formula Query: How To Apportion An Invoice Over A Period

[The_Snook]

Afternoon all,

I'm trying to create an automated prepayment schedule but i'm struggling with the apportionment forumla (to be honest I don't know where to start).
Attached is an example of the schedule in use at the moment. What i'm looking to create is a document where I input the relevant info in columns A to F and then the formula will apportion the invoice value appropriately across the relevant period. For example, in row 2 the document value is £3,600 and relates to a 12 month period (Apr 10 - Mar 11) therefore in April (col G) a month's worth is effectively incurred and 11/12's is carried forward to future periods hence the figure of £3,300 in cell G2 (£3,600 x 11/12).

Thanks in advance,

Snook

[shg]

Is a month a unit, or must there be consideration for the number of days in a months?

Does it break in even dollars, or dollars & cents?

[The_Snook]

A month could be classified as a unit if it makes it easier to calculate? At the moment I either classify the month as a whole unit if the prepayment period starts early in the month or as a half if it starts roughly half way. If it starts towards the end of a month I generally class that as the start of the following month.

The amount allocated to each period is usually rounded to two decimal places although it can be a whole number if it is divisible evenly.

Hope this helps?

Snook

[shg]

Here's one way:

Please Login or Register  to view this content.

The formula in G3 and down is =F3 - E3 + 1

The formula in H3 and across and down is

=IF(EOMONTH($F3, 0) = EOMONTH(I$1, 0), $D3 - SUM($H3:H3), ROUND(MAX(0, MIN($F3, EOMONTH(I$1, 0)) - MAX($E3, I$1) + 1) * $D3 / $G3,0) )