Somewhat Complex: Allocation of Payments

[ryguy7272]

Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2.
Using the day function, I have the number of days in that month, which is 31,
in cell E4. I have a start date (1/29/2008) in A5 and an End Date
(1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000)
in D5. I am trying to allocate that $120,000 over the entire time interval,
from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because
there are 353 days between the start date and the end date and there are two
days between 1/29/2008 and 1/31/2008. Based on the same logic, I would
expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I
would expect to see $5,439 in Q5. Then, when I sum all the data elements on
this row, I would like to see a total of $120,000, which indicates that all
payments have been made and accounted for. After all this, I would go to E5,
and fill-down a few rows, and all cells should populate with the correct
payments/amounts.

I am using this function
=IF(AND($A5>E$2,$B5>E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5))))

It is close, but not quite correct. I’d appreciate any and all assistance.
This is somewhat complicated for me, only because I can’t seem to get my mind
into it. In all honesty, I don’t think it is all that difficult though.

Thanks!!

 

[Niek Otten]

<Based on the same logic, I would expect to see $9,858 in F5.>

That bit I don't understand. What is the logic?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Ugh! This is turning into quite a mess. I have a date (1/31/2008) in E2.
| Using the day function, I have the number of days in that month, which is 31,
| in cell E4. I have a start date (1/29/2008) in A5 and an End Date
| (1/15/2009) in B5. I have the number of days in C5 and an amount ($120,000)
| in D5. I am trying to allocate that $120,000 over the entire time interval,
| from 1/29/2008 to 1/15/2009. For instance, E5 should show $680, because
| there are 353 days between the start date and the end date and there are two
| days between 1/29/2008 and 1/31/2008. Based on the same logic, I would
| expect to see $9,858 in F5. Fill-right; so on and so forth. Finally, I
| would expect to see $5,439 in Q5. Then, when I sum all the data elements on
| this row, I would like to see a total of $120,000, which indicates that all
| payments have been made and accounted for. After all this, I would go to E5,
| and fill-down a few rows, and all cells should populate with the correct
| payments/amounts.
|
| I am using this function:
| =IF(AND($A5>E$2,$B5>E$2),0,IF(E$2-E$4<$A5,(E$4-DAY($A5))*$D5/$C5,IF(E$2-E$4<$B5,(E$4-DAY($B5))*($D5/$C5),E$4*($D5/$C5))))
|
| It is close, but not quite correct. I'd appreciate any and all assistance.
| This is somewhat complicated for me, only because I can't seem to get my mind
| into it. In all honesty, I don't think it is all that difficult though.
|
| Thanks!!
|
|
| --
| RyGuy

 

[ryguy7272]

E2:AB2 contains dates, with the end of the month in each column.
For instance, E2 = 31-Jan-08, F2 = 2/29/2008...AB2 = 12/31/2009

S4:AB4 contains the number of days in each month.
E4 = 31, F4 = 29...AB4 = 31

A5 = Start Date and B5 = End date and C5 = No. of Days and D5 = $120,000
A5 = 1/29/08
B5 = 1/15/09
C5 = 353 (1/15/09 - 1/29/08) + 1
D5 = $120,000
E5 = $680
The $680 is basically 2 * (120,000/353). The number of days from 1/29/08 to
1/31/08 is 2. This is multiplied by the total amount of revenue collected
divided by the total number of days over which the revenue is collected
(120,000/353).

I think I am close to a solution, but I can't quite resolve this thing.

 

[David Biddulph]

You might try
=($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5>=E$2-E$4)

Note that you've added 1 in your C5 formula (so effectively counted start
and end dates) but in your E5 formula you haven't added an extra day, so you
need to add an extra day at the end.

 

[David Biddulph]

Sorry. Ihadn't trapped for a start date beyond the first column.
Change my suuggestion to
=($D5/$C5)*(MIN($B5+1,E$2)-MAX($A5,E$2-E$4))*($B5>=E$2-E$4)*($A5<=E$2)

 

[ryguy7272]

No problem can be solved from the same level of consciousness that created it.
Albert Einstein-

Thank you so much David!! I made a small modification, and went with this:
=IF(AND($A5>E$2,$B5>E$2),0,($D5/$C5)*(MIN($B5,E$2)-MAX($A5,E$2-E$4))*($B5>=E$2-E$4))

Regards,
Ryan---

 

[David Biddulph]

I assume that you've deleted the +1 from your formula in C5?

 

[ryguy7272]

Indeed, I did delete it! Excel is quite remarkable. I am constantly amazed
at the types of things one can do with a little creative thinking.

Thanks again Dave!!!