Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template for 40-year quarterly cash flow mixed with some monthly flows
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Template for 40-year quarterly cash flow mixed with some monthly flows
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IRR formula for monthly cash flows | Excel Worksheet Functions | |||
How do I calculate IRR using monthly cash flows, not annual? | Excel Worksheet Functions | |||
Is the IRR calculation based on cash flows at beginning of year? | Excel Worksheet Functions | |||
Using MIRR, if cash flows are monthly, should rate be / 12? | Excel Worksheet Functions | |||
How do you calculate rate of return on monthly cash flows | Excel Worksheet Functions |