I deal with long-term (up to forty year) quarterly cash flows.
Generally, there will be certain items that will increase at an assumed
rate of inflation, while other items will have an additional adjustment
such as inflation plus 1%. Mostly, I will want to show four equal
payments in any one financial year and apply an annual inflation
uplift. This I can manage.

However, I also need to integrate cost schedules that may have payments
occuring in periods that do not tally with my quarterly schedule.
Typically, these will arrive from an external source. So I have a few
fixed dates and payments in one sheet and I would like to interleave
these with my quarterly dates and payments.

e.g.
Sheet1 - external cost schedule
DateX
DateX + 1 month
DateX + 3 months
DateX + 4 months
DateX + 5 months
DateX + 5 months and 3 days (maybe I don't need this level of precision
but let's generalise)

Sheet2 - cash flow
[Earlier of DateX or Start Date of quarterly flows - if equal, that's
OK, use one of them]
[Earlier of (next unused date in DateX series or next unused date in
quarterly flows)]
etc.

I then need to ensure that any payments or uplifts that are due to
occur, say, only once every four quarters, will be correctly applied
only at the relevant dates. Ideally, I also need to be able to deal
with items such as tax payments that are calculated in relation to a
range of dates with an offset from the date that they fall due.

Finally, I need to ensure that, once set up, any model is readily
maintainable by intelligent non-programmers (after it has been
carefully explained to them).

So do I need to just use VBA to build my cash flow sheet or can I do
clever things with dynamic ranges? Can I build an array of quarterly
dates from within an Excel function or UDF? Can I take two arrays of
dates and produce one array of unique, sorted dates from within an
Excel function or UDF?

Any hints/tips/solutions gratefully received.