Prepayment Formula Help Please - Tweak required!

[Suzanne1307]

Hi All,
This is my first post to this forum - I've always managed to find the answer on here before without having to post :0)
I have created a prepayment schedule (a cut of which is uploaded). My problem is if I receive an invoice 2 months after the pp payment has started I would like to ensure that my formular picks this point up and calculates in the first pp month the months charge but also what has not been charged to date. Ie PP starts 1 Jan 14 - received the invoice 1 Mar 14, my formula is only calculating for the period remaining (10 months), I will need to release Jan & Febs payments at the same time as Mar.
Fingers crossed that this is a relatively easy fix. Thank you for taking the time to read this.
Suzanne
PP Q.xlsx

[p24leclerc]

I don't see a reference to March 1st in your sheet. Do you base your calculation on data in column I (Pd Added) whatever it means?
Also, are your start and end dates always at the beginning and end of the month or could it be in the middle of the month?

[Suzanne1307]

Hi Pierre,

Thank you for responding!

Cols G & H are the start and end dates of the period that the prepayment is required for. PP could start and end on any other date other than the start and end of the period (I just didn't know how to write that formula!) The P1, P2 etc are my financial periods. So my formula is effectively taking that into account and looking at the start and end dates also - which is why, if I add a PP in say P2 (Feb), but the software licence period of cover is from the Jan, my form will just work out what is left to pp - effectively leaving a balance in my pp account at the end of the period of cover. Our prepayment invoices are posted in total to the pp account on the balance sheet so if there is a delay in posting()and there have been some substantial ones!) I manually adjust my formula to take account of the earlier periods.

I really appreciate your help.

Suzanne

[p24leclerc]

try this formula in cell K3 and copy it to the right. You'll have to put only numbers in column I (Pd Added). Hopes you don't mind.

Please Login or Register  to view this content.

[p24leclerc]

I realized that the payment was starting a month late because of the february month which only has 28 days. If you change the 30 by 29 it seems to work.

Please Login or Register  to view this content.

[Suzanne1307]

Thank you - I have added this revised form and it appears to be doing what I would like (You're a genius!) The only thing that I can see is the if I, for example use 3 in col I, should the 3 month release be in March and not April to pick up Jan, Feb and Mar? When I total the calculated months I seem to be one payment short - I have released £833 and not £1000.

Apologies for all the questions but it is late here now and I am probably not seeing the wood for the trees!!

Suzanne

[Suzanne1307]

Absolutely perfect!! Saw your response after my post

Many thanks Pierre - I can go to sleep now

Suzanne