Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Develop formula for cash flow
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Develop formula for cash flow
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(B10,B2-B1),"") ... "" is a pair of double quotes In B5 =IF(B40,b3*4),"") best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "sgl" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Develop formula for cash flow
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)+COLU MN(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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Develop formula for cash flow
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 "Bob Phillips" wrote: 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)+COLU MN(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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NPV of Cash Flow Payments | Excel Worksheet Functions | |||
Cash Flow | Charts and Charting in Excel | |||
Cash Flow | Excel Discussion (Misc queries) | |||
Cash Flow by Week | Excel Worksheet Functions | |||
Cash Flow Set-Up | Excel Worksheet Functions |