Develop formula for cash flow

[sgl]

Hi All,

I am developinga spreadsheet where the user will enter the following data
for each entry which will accumulate in a table.

Start Date 15-Jan-07
End Date 22-Oct-07
Total days 281 (calculation)
Hire Payment per day 10,000
Total Hire Received 2,810,000 (calculation)
Payable every (Days) 15
Hire Paid in Advance (this could also be in arrears)

How can I develop a formula that will automatically calculate, in a
tabulated format going across in months, what is payable and what should be
allocated to each specific month.

Thank you and a Happy, Peaceful and Productive New Year to all of you/sgl

 

[Bernard Liengme]

If the text "Start Date" is in A1 and the date is in B1, then in B3 use
=B2-B1
But this will work only if you have 'real' dates in B1 and B2. To test this
in C1 enter =B1+1 and you should get 16-Jan-07. If not you need to learn how
to enter dates and format them.
In B5 use =B3*B4 to get 2,810,000 - you may need to format the cells to get
the commas
Now you can copy the formulas across the worksheet by dragging the fill
handle - solid square in lower right corner of active cell

But wait! You do not want to see 'funny' stuff before dates are added to the
new columns.
In B3 =IF(B1>0,B2-B1),"") ... "" is a pair of double
quotes
In B5 =IF(B4>0,b3*4),"")
best wishes

 

[Bob Phillips]

Assuming that data is in A1:A4, try this

G1:
=IF(DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$1)-1,1)>$A$2,"",DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$1)-1,1))

copy G1 across to R1

G2: =IF(G1="","",DATE(YEAR($A$1),MONTH($A$1)+COLUMN(A$1),1)-$A$1)
H2:
=IF(H1="","",MIN((DATE(YEAR($A$1),MONTH($A$1)+COLUMN(B$1),1)-$A$1-SUM($G$2:G2)),$A$3-SUM($G$2:G2)))

copy H2 acroos to R2

G3: =IF(G2="","",G2*$A$4)

copy G3 across to R3

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[sgl]

Thank you for your quick response,

I think that there is a misunderstanding here. The Hire Payment is paid in
advance therefore the calculations fall as follows:

15-Jan-07 to 29-Jan-07 15 days 150,000
29-Jan-07 to 12-Feb-07 15 days 150,000 300,000 January
12-Feb-07 to 26-Feb-07 15 days 150,000
26-Feb-07 to 12-Mar-07 15 days 150,000 300,000 February

Payments or receipts for January are made on the 15 Jan and 29 Jan so the
receipts for that month are USD 300k. The formula you suggested works the
total allocation as 17 days for January which gives you a total cash
collection of USD 170k only.

Collections will be made from a number of clients with varying dates. Some
collections are paid in advance others are paid in arrears so each entry must
be tested as to the payments date. Finally when all entries are collected
into a table a SUMIF possibly an Array formula will be needed to collect all
of the information for the specific month/period.

My apologies if I did not explain the problem clearly from the start.
Many thanks indeed/sgl