Prepayment Schedule

Hi,

I am trying to set up a prepayments schedule spreadsheet but I can not find what I need already online so need some help with the formulas.

There are three formulas.

Formula 1
This is the easiest of the three. I need a formula which will look up a date (Cell C5) and then place it in the correct month of the schedule.

Formula 2
I require a formula which shows the balance outstanding on the prepayments account. I need the formula to look at the period (start and end dates) and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.

e.g. In the example the payment is in March of £10k and is to be split out over 10 months.At the end of March the balance would be £9k, April £8k and so on.

I did find a similar post on the forum with the following formula:

=IF($D34>G$33,$F34,IF($E34<=DATE(YEAR(G$33),MONTH(G$33)+1,DAY(G$33)-1),0,MAX(0,(MOD(MONTH($E34)-MONTH(G$33),12)-IF(DAY($D34)<>1,0.5,0))*$F34/(MOD(MONTH($E34)-MONTH($D34),12)+1))))

However this didnt work out as the formula showed the total balance in Jan and Feb when this should have been zero.

This incorrect formula is shown in red.

Formula 3
This formula will need to show the payment that has been expensed. e.g. in the example £10k is paid in March and so there will be a £1k charge in March through to December.

I have uploaded an example workbook. The yellow cells are where the formula would go and the green cells are the example required output.

Can anyone help with any element?