<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 | ||
| 1 | 02-Jan | -5,000 | H2 Interest (of pr year) | |
| 2 | 05-Jan | -1,000 | Envelope fee | |
| 3 | 02-Jul | -5,000 | H1 interest | |
| 4 | ||||
| 5 | 16-Jan | -10,000 | Capital payment | |
| 6 | 16-Mar | -10,000 | Capital payment | |
| 7 | 16-Jul | -10,000 | Capital payment | |
| 8 | 16-Oct | -10,000 | Capital 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.
