MIN COUPNCD for portfolio cash flow calculation

[LH_Bonds]

Good morning, I have a list of debt securities that vary by maturity date (B2:B10), interest frequency (C2:C10), and day count convention (D2:D10). Based on that portfolio, I am trying to determine the next coupon date for the entire list of securities (the minimum COUPNCD) and then the date after that and so on. H3 is today's date, which would be the "settlement date" in the COUPNCD or equivalent formula that I want to write in H4 copy down. I've made some lame attempts to make this work, but alas here I am. Any help would be greatly appreciated!

[Glenn Kennedy]

Hi. I am FAIRLY certain that you can't use COUPNCD in the way that you want. I tried a couple of different ways to coerce it into calculating an array... but they all fell over. But... why not just use column F??

In H2, copied down:

(your expected answers are incorrect, as we're now into December...)

=IFERROR(AGGREGATE(15,6,$F$2:$F$11/(COUNTIF($H$1:H1,$F$2:$F$11)=0),ROWS(H$2:H2)),"")

[LH_Bonds]

Thanks very much for the quick response, Glenn. The goal is to create a complete list of payment dates through maturity of the longest bond. Using Column F only picks up the next three payment dates. I could extended the formulas in Column F into Column G and calculate the next COUPNCD for each bond using Column F dates as settlement for Column G, but this approach only achieves the full goal if I extend it enough to pick up every payment date for the longest bond (in this case, 84 columns). Because the bonds have different interest frequencies, anything less than final maturity also risks missing payment dates. Any other thoughts if there might be a more elegant way to come up with complete list?

[Glenn Kennedy]

My head is fried!! So, I'm going out for a five-mile walk.

Take a look at this. I changed some of the dates, to make manual checking feasible. Using this sheet, can you see if it is giving you your expected results on the column to the right?

I have my doubts, 'cos I don't understand the Day Count bit of COUPCD. Don't bother explaining. I won't understand... the key thing is.. does it give you your expected results. I can explain all the odd-looking +ROW(Int_Freq)/10^10 bits later.

[LH_Bonds]

Unfortunately that did not work, but I'm still going to dissect your sheet and try to learn a little. Attached is a revised file with a worksheet called Array that includes a list L2:CR11 of the complete payment dates for all bonds. Then I used MIN/MINIFS formulae to generate a list of the aggregate cash flow dates in Column F and a slightly revised version of the AGGREGATE formula you used in Column G. The cash flows are correct in both columns. As you can tell when you open the file and try to do anything, the calculation efficiency is low. It's not unusable, but the sheet is noticeably sluggish. Another problem I am trying to avoid is having to manually size the table of bond-specific dates. I could fill a sheet with 600 columns (50-year security paying monthly) to plan for any eventuality, but that would basically cripple the sheet.

[Glenn Kennedy]

That did not work is not very descriptive. How did it not work? What are your expected answers?

[LH_Bonds]

Fair enough. There are numerous cash flow dates excluded or erroneously included in the list in Column L of your spreadsheet. Based on the bonds in your sheet (I just noticed some of the dates were changed), there should be a total of 20 cash flow dates (excluding 2019-12-01 as the settlement date) beginning with 2020-01-01 and ending with 2025-03-01. I will post a more comprehensive reply when I have time to think on this a little more.

[Glenn Kennedy]

Sorted. It was your bl**dy Named Range!! Last thing before posting, I accidentally used it, instead of ROW() in G2, and did not notice the downstream changes it made.

So, I copied your nice idea for cross-checking and it all seems to tally - NOW.

[LH_Bonds]

Very impressive, but it makes my head hurt. I greatly appreciate your help. I will not keep longing for a way of forcing COUPNCD to work with arrays, but this is an excellent workaround. Thank you!

[Glenn Kennedy]

You're welcome. From some googling.. I haven't seen any arrray use of the function.

Shout if you hit a problem.

My head's still sore. Massaging it with du vin rouge de la belle France!!

[Glenn Kennedy]

One further improvement. It's never a good idea to use TODAY() too often. It's volatile and causes a recalculation every time anything changes. So hard-enter the date. It calculates instantaneously. I added in some more rows (down to row 300). If the two check cells don't tally, then it needs to be longer still. Play with it...