YouTubersLoveExcel#31: Dates, Cash Flow & Logic

I have read a lot, but still can't figure out the best way to do this:

I have 60 cells aligned horizontally: (Jan-05 to Dec-09)
Above the dates I have a project cash flow.

First I need to report the date at the first occurence of cash flow (a
negative number).

Secondly, I need to report the date at the last occurence of cash flow
(a positive number).

This is so I could set up for the XIRR function. The problem is that
the cash flow appears in various dates and durations based on the
project parameters, so I can't lock the XIRR calc to specific cells.

Any help or guidance is greatly appreciated!

Question, I doing a 60 month cash flow for a small apartment. There are 10 units (A thru J) in column A. Some units have different rents (1 bed room vs. 2 bed rooms) and rents starts at different months. I need to set up the cash flow where every 12 month after rent started it increases 3% for inflation. Also, I need to be able to change the rent start month and still calculate the 3% increase. For example - Unit A rent starts in month 2, so 3% increases in month 14. But if I change rent start for Unit A to month 4, it should show the 3% increase in month 16, then another 3% in month 28, etc...
Thanks in advance for the help.

Hi,

I am trying to set-up a cashflow statement that will allow users to enter
data against various categories for each day of use: i.e.

01-Jan-05 02-Jan-05 03-Jan-05 04-Jan-05 etc
Revenue:
Sales
Rental Income
etc


Expenses:
Rent
Overheads
Miscellaneous
etc

So in effect users would have a grid to enter their data into.

What I want to be able to do is:

for each of these expenses / revenues, group them first of all weekly
(running Sun-Sat) on a separate sheet, then group them monthly on a separate
sheet, which would allow analysis of weekly / monthly trends.

What is the best way of doing this?

I've managed to do this for the weekly groups by using a combination of the
sum and offset functions (as each week is a constant number of days (7), the
offset function allows me to "jump" across by 7 columns each time). however,
getting something for the monthly groupings is more difficult as each month
obviously varies in the number of days within it.

Any suggestions greatly appreciated,

Regards,

Gary T.

I have a start date in column B (d/m/yy), a finish date in column C (d/m/yy), a dollar value in column D. I need a cash flow of the amounts in column D apportioned over the months shown in row 2 (mmm/yy).

The result would be something like $20,000; $25,000; $5,000. This would represent a task starting towards the begining of the first month, continuing through the second month and finishing one fifth of the way through the third month.

Does anyone have anything that does something like this?

This may be asking for a miracle, but at this point a miracle is the only hope I have left. I have to answer the following question using Excel, and I don't have the slightest clue. Any help would be appreciated beyond belief.


Question:
1) 100,000,000 portfolio of loans

2) 9% interest rate/annum

3) 0.5% cost to service the loans/annum

4) 10% voluntary prepayment/annum (10% of all people repay their loans each year)

5) 10% default/annum, but 50% of all defaulted monies is recovered

6) 360 month Amortization


The question is: What is the total cash flow from the loans?




I will give my life to anybody that can help. Thank you in advance.


Sincerely,
ErniePoe

Hello

Can anyone tell me the formulas needed for cash budget for example oct-mar etc and for an operating profit statement for a period and a balance sheet. Also are there any examples of these finished layouts?

many thanks
robert

Hi,

I would be very grateful for some help;

I need to create a 5 year spreadsheet which for the first 3 years has 950 a month added to the cash flow and after the three years has 950 subtracted. It also needs to have another column which details a property sold each month and the figure it sold for. In other words for the first three years a property will be bought which will increase the rent by 950 each property and after three years it will decrease by one property resulting in a 950 decrease in rental but a value for the amount of profit realized.

Thanks

Mark

I am attempting to set up a simple spreadsheet to help me predict cash flow problems, and need to do the following.

Format a row of cells eg B5 to IV5, such that any value eg 1000 input within the row eg into cell F5 results in each cell in the range B5 to E5 inc displaying a value equal to 1000 divided by the number of cells in the rang B5 to E5. Obviously this can be done simply by counting the number of cells within the range B5 to E5, but there must be a way of formatting the sheet to do this automatically, as I wish to perform the function across many rows.

Help
Ian L

How do I come up with an annual IRR on a 4 month cash flow?
Many thanks in advance
Luiz

Cash Flows nov/09 dez/09 jan/09 fev/09 Revenues 100% on month 8 85.000,00 Land Investment -15.000,00 Loan Fee Loan Interest Last Month Other Licenses & Fees -4.000,00 Other Costs -1.000,00 Salaries Divided Evenly -4.023,33 -4.023,33 -4.023,33 Site Manager Salary Monthly 0,00 0,00 0,00 Construction 50% 1st month/Evenly the last 2 -17.000,00 -8.500,00 -8.500,00 Brokers Last Month -500,00 Eng+Arq Last Month -1.000,00 Taxes Last Month -5.100,00 Project Net Cash -42.023,33 -12.523,33 -13.023,33 79.900,00