Permutation
Is there a function / macro in excel that generates various combination of numbers across different cells? Those set of numbers adding up to a certain sum. This has to do with a Monte Carlo Model I’m doing that seeks to generate the optimal Portfolio Mix for a Fixed Income Portfolio based on key ... Is there a function / macro in excel that generates various combination of numbers across different cells? Those set of numbers adding up to a certain sum.

This has to do with a Monte Carlo Model I’m doing that seeks to generate the optimal Portfolio Mix for a Fixed Income Portfolio based on key rate durations and view on yields.

The basic input to the model is a portfolio mix, defined by allocation(%) per bond tenor.

Allocation per Bond Tenor
Run No. 6Month 1Year 3Year 5Year 7Year 10Year 15Year 20Year 25Year 30Year Total
1 100% 0% 0% 0% 0% 0% 0% 0% 0% 0% 100%

Each portfolio mix would constitute one run, and the model needs to do several for it find the optimum mix. Is there a quick way for excel to do this? Thanks.

Allocation per Bond Tenor
Run No. 6Month 1Year 3Year 5Year 7Year 10Year 15Year 20Year 25Year 30Year Total
1 100% 0% 0% 0% 0% 0% 0% 0% 0% 0% 100%
2 95% 5% 0% 0% 0% 0% 0% 0% 0% 0% 100%
3 95% 0% 5% 0% 0% 0% 0% 0% 0% 0% 100%
4 95% 0% 0% 5% 0% 0% 0% 0% 0% 0% 100%
…. …. …. …. …. …. …. …. …. …. …. …
…. …. …. …. …. …. …. …. …. …. …. …
…. …. …. …. …. …. …. …. …. …. …. …
xx 0% 0% 0% 0% 0% 0% 0% 0% 0% 100% 100% Read More

Accounting Bootcamp- Short term debt CFO or CFF
In the the Accounting Bootcamp video module, as part of our discussion of the Cash Flow Statement, we've outlined the following formula: CFO = change in CL - Change in other CA + R - Exp. (w. non-cash items added back). In the JPMC case, where we're applying the said formula, the instructor class... In the the Accounting Bootcamp video module, as part of our discussion of the Cash Flow Statement, we've outlined the following formula:
CFO = change in CL - Change in other CA + R - Exp. (w. non-cash items added back).

In the JPMC case, where we're applying the said formula, the instructor classified the change in short-term debt under CFF instead of CFO (being that Short term debt is a CL). While I understand that debt is a financing option, it eludes me as to why short term obligations, part of the core operating activity of a firm is not classified under CFO. Especially, considering that on the balance sheet, short term debt is included in the CL total. Moreover, doesn't GAAP state that obligation that mature w/i a year are CL? Am I missing something here? While the said classification of Short term Debt under CFF did not prevent me from finding the correct answer, it definitely negatively impacted my understanding of management's decision. So, Thank you for clarifying. Read More

Re: Insurance financial modeling
Hello, we have a solution for you. The original logic that we used to construct the insurance sweep was that if you generated cash that year, i.e. Cash Available/Required in row 8 of Sweep tab was positive, that the minimum cash balance was essentially ignored because you would be building cash b... Hello, we have a solution for you.

The original logic that we used to construct the insurance sweep was that if you generated cash that year, i.e. Cash Available/Required in row 8 of Sweep tab was positive, that the minimum cash balance was essentially ignored because you would be building cash balance in that scenario. Then, if you lost money that year (row 8 is negative), then the min cash balance kicks in because you want to ensure enough for working capital purposes. Any required cash comes from portfolio sweep.

However, you are correct in pointing out that in either case, to build a purely dynamic model, as we always advocate, the minimum cash balance requirement should always be in play and affecting the model regardless of the other figures. As such, here is the fix required, from your completed model, Sweep tab:

1) In cell C16, add this label: Cash Required to hit Min Cash Bal / (Excess above Min)

2) In cell G16, add this formula: =G15-G10 and copy it to column K. We then made this row 16 white cell so it won't show up on printout.
The logic is that you are calculating the amount of money required to "replenish" cash to hit MIN or if negative, it is the excess above MIN. This is an interim subtotal we need to facilitate the next calculation.

3) In cell G17 (Cash Required from Portfolio), put in this formula and copy to column K: =-MAX(0,G16-G8)
The logic is that you are calc'ing either:
a) Cash Req. less Generated => get ? from Port
b) Excess Cash less Shortfall => get ? from Port
c) Max(0) to force positive number & correct calc

OPTIONAL NOTE: You can actually remove the negative sign in front of the MAX function here, but if you do so, in all of the Discretionary Sweep calculations below (rows 22, 29, 35, 41 and 47), remove the negative sign in front of G17 in the MIN(MAX()) function. In addition, in Debt Borrowing in row 80, you need to switch the signs, so it would be: =G17+G22+G29+G35+G41+G47. If you leave the - in front of the MAX in Step 3 above, you do not need to change the signs in the Sweeps or Debt Borrowing.

4) Finally, in cell G11, (Change in Cash), update the formula to: =G8+G17 and copy to column K
The logic is that there are two sources of cash changes: (i) cash generated that year (or shortfall required if row 8 is negative); and (ii) portfolio drawdowns.

Thus, the end result is that we reverse the flow and the logic to calculate the Cash Required from Portfolio first based on any changes from cash beginning balance vs minimum cash balance and cash generated (or shortfall) that year. Then the Change in Cash is easier to calculate.

This is cleaner, more dynamic and more important, gets rid of the ugly IF statement that we initially had used because we got a bit lazy.

To prove this works in all scenarios, we tested the following relevant scenarios, and the model changes we outlined are sufficient to capture all possible outcomes:
Min Cash > Cash Beg: Made Money (more than Required) => Build Cash Balance
Min Cash > Cash Beg: Made Money (less than Required) => get from Portfolio
Min Cash > Cash Beg: Lost Money => Portfolio: Required + Loss
Min Cash > Cash Beg: Lost Money (lost less than Excess) => Cash down, no Portfolio required
Min Cash < Cash Beg: Lost Money (lost more than Excess) => Cash down to Min, get from Portfolio
Min Cash < Cash Beg: Lost Money (equal to Excess) => Cash down to Min, no Portfolio required
Min Cash < Cash Beg: Made Money => Cash Up
Min Cash = Cash Beg: Lost Money => Cash Same, get from Portfolio
Min Cash = Cash Beg: Made Money => Cash Up

Seek and yee shall find. Read More

What is the purpose of management participation in the LBO?
What is the purpose of management participation in the LBO? Should the financial sponsors consider adding all company employees to participate in the LBO? If so, how would one structure the labor participation in the transaction?

Distressed Debt Model
In the distressed debt model Hamilton went over, can this model be used to evaluate High Yield bonds? Or High Yield Bonds are a complete separate animal where the distressed model cannot be applied? As I understand, In distressed you are more concerned about figuring out what security to invest an... In the distressed debt model Hamilton went over, can this model be used to evaluate High Yield bonds? Or High Yield Bonds are a complete separate animal where the distressed model cannot be applied? As I understand, In distressed you are more concerned about figuring out what security to invest and to identify the fulcrum security. Where as if you were presented w/ a HY bond, the analysis would be to determine whether this bond is attractive given its risk/reward relationship? In any case, if I were to analyze a HY bond, how would I go about modeling it out? Thank you! Read More