Template for 40-year quarterly cash flow mixed with some monthly flows

[janderson]

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.

 

[janderson]

OK, well I've done a little more thinking so I hope I can provoke a
response to some more specific ideas.

Outline process:

1. I have a primitive UDF that I call "DateGlobber" that generates a
series of regular-period dates based on a start date, number of
periods, and an interval in months. It just returns these dates as a
variant containing an array to a single cell. I can then use
SMALL(DateGlobber,row()) to return each date in rows on a worksheet.

For the moment I am content for it to rely on the EOMONTH add-in
function. I have not implemented the "convention" part of the function:
I intend that this will allow one to use built-in or add-in Excel
functions other than EOMONTH to do the actual work.

Option Base 1
Public Function DateGlobber(startdate As Date, periods As Long,
interval As Integer, _
Optional convention As Integer) As Variant

Dim count As Integer
Dim arr() As Date


ReDim Preserve arr(1 To periods)

For count = 1 To periods
arr(count) = eomonth(startdate, count * interval)
Next count

DateGlobber = arr

End Function

2. I intend to use or write another function to join the array of
regular period dates to a range-entered set of specific dates. Once I
have a single variant containing all of the dates, including
duplicates, from both sets of dates, I intend to use SMALL to provide a
list of unique dates down however many rows I need on my worksheet.

3. I can then base decisions on whether a value needs to be inflated,
say, by RPI, by testing whether the date is part of the regular-period
dates from my dateglobber function (with some extra to make it
only-every-fourth-period), or not.

Is this reasonable? Particularly re performance. For example, I am
wondering about calculation and recalculation overhead. Can I define a
name "regdates" as =DateGlobber(x,y,z), enter SMALL(regdates,row()) in
my rows and ensure thereby that no recalculation of these cells need
takeplace unless regdates is itself redefined?