prepaid schdule in excel/starting date not working

[dvsdasari]

Hi,


I am working on the same thing since two days, bur i cann't find a solution for this.


Here my requirement is to distributr the amount based on the START DATE AND END DATE. Following is the example.......


paid exp payable #of Mon START END 01/01/11 01/02/11 01/03/11 01/04/11 01/05/11

1000 100 900 5 01/01/2011 31/06/2011 180 180 180 180 180

1500 150 1350 3 01/03/2011 31/05/2011 450 450 450


The expenses should be distributing like this.....


Please can any one help me in this regard.


Thanks-Sekhar

 

[NARAYANK991]

Hi Sekhar ,


Can you clarify a few points ?


1. Let us say you have entered data in columns A and B i.e. the amounts paid , and the expenses will be entered.


2. The Amount Payable ( in column C ) is now = Amount Paid - Expenses


3. What about the # of Months ( in column D ) , is this entered ?


4. The Start Date is also entered , in column E.


5. The End Date ( in column F ) can be calculated , using data entered in (3) and (4).


6. Based on the above 1 through 5 , the Amount Payable can be distributed in the number of installments specified in (3).


Do you want only this much ?


Narayan

 

[fred]

Hi Sekhar,


I tried to decifer your data table but it was a mess. could you please arrange your data table again for insight? and answering Narayan's questions as well, cos I have the same questions, too. Thanks.

 

[dvsdasari]

Hi Narayan,


Thanks for your reply.


As you said abouve 1&2 points are OK.


Third point is #of Months (no of months is calculation from satart date - end date, which is entered manually)


Start date and end date should enter.


The criteria is-- I have paid an amount of Rs.1000 to MR. A in the month of Jan11. Out of this amount Rs.250/- taken as expenses and rest of the amount(1000-250=750)(which is taken as prepaid expenses) should be equally distributed ot Feb11,mar11,Apr11 as 250,250,250.


Please help me.


Thanks-Sekhar

 

[NARAYANK991]

Hi Sekhar ,


Let us assume your data starts from cell A6 as follows :


A6 = Amount Paid ( e.g. 1000 )

B6 = Expenses ( e.g. 250 )

C6 will be a formula =A6-B6 ( e.g. 750 )

D6 will be a formula =MONTH(F6)-MONTH(E6) ( e.g. 3 )

E6 = Start Date , entered ( 1/1/2011 )

F6 = End Date , entered ( 31/3/2011 )


Now , the distribution of the balance amount of 750 , will be put in cells G6 , H6 , I6 , and so on , depending on the number of months.


Copy the following formula in cells G6 through say R6 , or even beyond.


=IF((COLUMN()-COLUMN($G6))>OFFSET($G6,0,-3),0,OFFSET($G6,0,-4)/OFFSET($G6,0,-3))


Narayan