Prepayment Schedule

[pauldaddyadams]

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:

Please Login or Register  to view this content.

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?

[humdingaling]

I hope i have read the instructions right

Formula two just needed some tweaking

Please Login or Register  to view this content.

If true...then 0 instead of F34

[pauldaddyadams]

Hi,

Thanks for helping with two of the formulas - formula 1 and 2 were perfect!

Formula 3 isnt correct though - it should be £1000 a month. Is there any way in which the formula could work independently of row 13 asthis row will not be present in the actual model. I think what I am trying to say is can the formula in row 9 be modified to show the amount expensed in the month rather than the amount outstanding?

The formula will be in the yellow highlighted cells and the expected output in the green.

Paul

[humdingaling]

G13 put

Please Login or Register  to view this content.

then
H13 put

Please Login or Register  to view this content.

fill right

i actually think your formula 2 doesnt quite work.....
Decemeber has not payments even though end date is 31st dec ? unsure if that is intended

i just stole what you had in there without checking it
do you need to fix that?

[pauldaddyadams]

Hi,

Formula 3 works however is there any way I can get it to do the same independent of row 9?

Formula looks like it might not be working but it is correct. Its the balance left outstanding at the end of december so as the final payment was made in December at the end of it the bill would have been fully paid with no amount outstanding.

[pauldaddyadams]

Hi,

Can anyone help with this? Its just the final formula I need help with.

If someone can help me understand formula 2:
=IF($D$9>G8,0,IF($E9<=DATE(YEAR(G$8),MONTH(G$8)+1,DAY(G$8)-1),0,MAX(0,(MOD(MONTH($E9)-MONTH(G$8),12)-IF(DAY($D9)<>1,0.5,0))*$F9/(MOD(MONTH($E9)-MONTH($D9),12)+1))))

Then I am sure this can be adapted.

What I need is formula 3 to work independently of row 13 as this row will not be present in the actual model. This formula will need to show the amount expensed in the month e.g. £12k over 12 months is £1k per month

[Fotis1991]

Paul

I haven't follow your whole thread but as an idea in G28 and copy across.

Please Login or Register  to view this content.

[pauldaddyadams]

Hi Fotis - good to hear from you.

When I enter that formula it errors on this part: (DATEDIF($D$28;$E$28,"m")+1))

Any ideas why?

[Fotis1991]

Any ideas why?

Yes. It's because i forgot to replace semi colons with comma(we work with semi colons as separators in Greece!)

Please Login or Register  to view this content.

replace this semi colon with a comma.

[pauldaddyadams]

Hi,

That must be so frustrating having to remember to switch everytime.

I have tried the formula but it didnt work - I only get zero's

I have uploaded the spreadsheet

[Fotis1991]

As i see that you uploaded a <=2003 excel file and as this works great in my computer i suspect that in your computer there is not installed the analysis packet for 2003.!

See the video with my formula in your worksheet to works great.

http://screencast.com/t/OdcklLdp


edit: http://answers.microsoft.com/en-us/o...5-fefe2f0da63f

[pauldaddyadams]

Hi,

Now I am stumped - I have just installed the add in however I now get a circular ref - do you have that on yours?

Paul

[Fotis1991]

Ok. No i don't get it(as i show to you in my video) but let's try this.

Subtract the sum part of the formula ONLY in g28. Use my previous formula in all other cells.

So ONLY in g28, use this.

Please Login or Register  to view this content.

[pauldaddyadams]

Hi,

I have reuploaded but its still not good news!

January works however the other months do not work now?

[Fotis1991]

In H28 and copy across.

Please Login or Register  to view this content.

[pauldaddyadams]

Fotis - you have done it again!!

Thanks you, you have solved my problem!

[Fotis1991]

You are welcome and thanks for the feedback and your kind words.

[nilani]

Hi,

I tried to follow this thread as I have a similar problem with my prepayment schedule. I don't understand the Mod formula so not too sure how to adapt this to get what I need. I have attached the table I have. I want to be able to recognise some of the cost in the month based on how many days of the month has elapsed and then the rest should be spread evenly across the months.

The second table shows the numbers I am expecting. Is it possible to adapt this formula to get the results I'm after?

Would be grateful for any help.
Best wishesCopy of prepayments.xls