Is there a robust way to model 'bond plus cash' simulated returns, say in Excel, for an asset allocation problem between stocks vs bond plus cash?

For equity, mean 5% st dev 15% I would use X=U~[0,1] and R=N(X)~[0.05,0.15^2].

For bond, mean 3% st dev 6%, and for cash mean 2% st dev 2%.

The question is then two-fold:

  • How do I easily truncate cash returns at 0
  • How do I combine the return-risk assumption for both risk-less instruments.
  • $\begingroup$ Are you saying that cash and bonds are riskless because, although there is a standard deviation, you are capping the loss at 0? This problem seems very unrealistic, so I would appreciate more details. Is there an original question? $\endgroup$ – RandyF Dec 1 '16 at 21:36
  • 1
    $\begingroup$ No original. I'm making no assumptions on risky or risklessness. This is a question about simulations (say 5000,10000) given a distribution and what the distribution should be. Consider a DC pension lifestyling strategy that starts 100% equity and ends 0% eq 100% bond and cash at NRD. $\endgroup$ – rrg Dec 2 '16 at 8:37
  • $\begingroup$ So, you want to have a varying percentage of equity and bonds through time? If so, how do you plan to shift from equity to bonds? I ask because this shift moves from requiring a 2d matrix of random numbers (2d if returns are dependent on each other; if independent, this can be done with 2 closed form equations) to a 3d matrix (a 2d matrix for each asset). $\endgroup$ – RandyF Dec 2 '16 at 15:23
  • $\begingroup$ @RandyF not bothered about modelling the equity interdependence/covariance, forget it exists - just a bond/cash sketch as per qn. $\endgroup$ – rrg Dec 2 '16 at 15:56

If you are not interested in correlations, etc. I'd just make a column for each rebalance date, let's say you want to rebalance at the end of each year for 50 years, you'd have 50 columns, and a row for each number of simulations. Let's say 10,000 (each row is going to be the same, but rand() will make the result different for each. The first cell would be

$$ weight_{eq} * (0.05 + 0.15 * rand()) + weight_{bnd} * (0.03 + 0.06 * rand()) + weight_{cash} * (0.02 + 0.02 * rand())$$

You could put a row at the top that has number of years and a desire to move 2% from equity to bonds and cash. For example, make the weights as follows:

$$ \frac{50-year}{50} (0.05 + 0.15 * rand()) + \frac {year}{50} (0.03 + 0.06 * rand()) + \frac {year}{50} (0.02 + 0.02 * rand())$$

The ending total return would be the product of (1 + each of these numbers), assuming these are annualized returns. If you rebalance more or less often than annually, you need to multiply the interest by the percentage of a year and the standard deviation by the square root of time. My answer would likely change if I'm not understanding you correctly, particularly with your comment about truncating cash returns. If I were to model this myself, I would not have any variation in the return for cash.

  • $\begingroup$ sum of normal distribs a good approx.? $\endgroup$ – rrg Dec 5 '16 at 9:16
  • $\begingroup$ It should be $(1+r_1)*(1+r_2)*...*(1+r_n) - 1$ with correct compounding. $r_1+r_2+...+r_n$ can be very far off as the total term increases. $\endgroup$ – RandyF Dec 9 '16 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.