Monthly Income over Term

Can someone help me put together a formula to calculate and show the Monthly Income based on a few data points ( see example spreadsheet). I need it to calculate the remaining Monthly income for a lease and show it on a monthly grid (as Shown)

Start date is 2/1/12
Term Date is in column B
Remaining Term is a calc from 2/1/12 to Term date, which give the # of remaining months (already have this formula)
Remaining Obligation is the total Rent due which needs to be spread over the remaining months

Sublease Income - Here is another thing that I need to work in (If Possible!! A "Would be Great, but I could do manually. But Formulas should work for both the L & S lease rows). If the lease shows sublease income then I need ti ADD a row & relabel the Lease ID to reflect the same number from the main lease but then substitute the L for an S (See example: Lease S2)

Sublease start Date - When the Sublease Income should start.

So, I need a formula that will calculate the same $ amounts I have in Columns G thru BF based on the given information. See the Green cell in L2 the lease only goes out to the first half of the month so only gets half the income. Most dates are Last day OR 15th Day of month, but some go to specific days 3/5/12, 4/12/14 etc.

I may not have explained this well, so If you have questions let me know!

Thanks In Advance!

Rolling Income for Excell Help Forum.xlsx

Do you mean monthly rent due to you still? It would simply be remaining rent due divided by months remaining yes? YES, The L#s we OWE Rent, S#'s we are RECEIVING rent. But trying to base the formula to start & stop based on dates.

As for the sublease thing you would need vba to add extra rows dependant on a condition. Yeah, i figured that, I have added them manually so not a problem.

The last question I don't really know what your asking for. If in this you are talking about the Half month of income, I will explain better. The formula I have set in V2 calculates the total income divided over the number of months correctly, on a full month basis. I.E. the $55,000 over the 5.5 months is $10,000/mth. The issue that needs solved is the final month is only $5,000 in obligation. But as you can see in V2 the formula drags that $10,000 through all months, I need it to stop mid July and show only the $5,000. Giving 5 Mths @ $10,000 & 1 mth @ $5,000 for the total of $55,000.

Some of the dates do not always begin and end on the 1st, 15th or 30th (31st) Days of them month, so I need to correctly account for that. Just noticed also that in my formula the sublease income is stopping 2 months early, somehow based on the 2 months between the 2/1/12 & 4/1/12 start dates