Extract data on 1st occurrence date comparison

alexorfa

New Member
Joined
Feb 10, 2017
Messages
3
A B
13-Jan-176,500
23-Feb-178,000
316-Feb-179,000
43-Mar-1710,000
53-Apr-179,500
63-May-179,000
73-Jun-178,500
83-Jul-178,000
93-Aug-177,500
103-Sep-177,000

<tbody>
</tbody>

<tbody>
</tbody>

I have a series of irregular payments that are listed on column A (date) and B (amount).

I also have a series of regular payments (E) that become effective on specific dates (D) EVERY year.


D E F
102-Jan-5,000H2 Interest (of pr year)
205-Jan-1,000Envelope fee
302-Jul-5,000H1 interest
4
516-Jan-10,000Capital payment
616-Mar-10,000Capital payment
716-Jul-10,000Capital payment
816-Oct-10,000Capital payment

<tbody>
</tbody>

I am looking for a formula (G) that will ‘sum’ the two tables (payments & Expenses) based on the closest payment date following the expense date. So an expense charge on 2-Jan will be subtracted from the next payment of 3-Jan-17 and Col G1 will show a value of 1,500 (6,500-5,000).

Also, when the expense is larger than the next payment then 0 will show in col G and the negative balance will be subtracted from the following payment. For example, payment B2 of 8,000 on 3-Feb-17 is not enough to cover the expenses that became due on 5-Jan (-1,000) and 16-Jan (-10,000). So G2=0 and G3=-3,000+9,000=6,000.

Note that the series runs for payments and expenses over a period of 30+ years.

I have tried hard to come up with a formula that will single out the first payment date after the relevant expense date, store the difference if negative and carry it to the next payment amount (also taking into consideration that since payment dates are irregular u may end up with 1 payment date having to cover for 2 or more expense dates). No luck. I am a good excel user and understand complex functions if any of you can come up with the formula that will make this work.

Thanks a lot
Alexander


PS. I have deepened my knowledge of excel by viewing posts in this forum and I thank u all very much.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,369
Members
416,098
Latest member
jpmiralrio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top