From the course: Excel for Investment Professionals

Computing asset allocation - Microsoft Excel Tutorial

From the course: Excel for Investment Professionals

Start my 1-month free trial

Computing asset allocation

“

- [Instructor] Many investors want to analyze portfolios containing a mixture of stocks and bonds. This is easily done in Excel. I'm in the zero, four, zero, one begin Excel file. Now what we have here are returns for stocks and bonds over the last 90 or so years. We've got data on stock returns from 1928 through 2018 and data on treasury bond returns for that same period. If you'd invested $100 in stocks in 1928, January, and reinvested all of your dividends, by the time we got to 2018, assuming no taxes, of course, you'd have $382850. In treasury bonds, if you'd invested $100 in January 1928, by the time we got to 2018, you'd have $7308. So treasury bonds, obviously, result in a lot less return, but they're much more stable. Stocks seem good over the long run, but there's plenty of years like 1930, 1931 as an example, 1937 or more recently, 2008, 2001, and 2002 where you have hefty negative returns, often times losing 1/3 of your money or more in that single year. For that reason, it's probably a good idea to balance stocks and bonds together. So now, we want to determine what the expected portfolio returns looks like. To do that, I've put together this formula that you see here. The rate formula is going to tell us about the growth rate in stocks or bonds. And we're using the starting period value of $100 in stocks and bonds versus the ending period of 383000 and $7300. Now you can certainly copy and paste that formula all the way down, if you'd like, but I'm going to go ahead and recreate it here just so that you see how this is done. So in rate, we need to start with the number of years that we're going to have for our period. In this case, it's 91. If there's any payments along the way, we're reinvesting all dividends, so that's not relevant. The initial investment that we put in, and we've got to put this in as a negative amount because we have a cash outflow. We're investing that money. The future return that we have or the future amount of money that we get, in this case, $383000, and the fact that that money is received at the end of the year. Now I'm going to go through and multiply that by the amount that we have in stocks, and that will tell me what my contribution to my overall portfolio return is from stocks. I'll now do the same thing with bonds, but adjusting for the weight in bonds from column I and the bond returns from column E. And that'll let me determine that my portfolio return on a 20-80 mix rather than a 10-90 mix is 5.76%. Now rather than typing this formula in for all 10 of these, I'd certainly rather go down and drag and drop that formula. From there, I'll now be prepared to determine what's my value in five and 10 years? Well, five years in the future, my 90-10 portfolio is worth $12943. 10 years in the future, my 90-10 portfolio is going to be worth, adjusting for the 10-year time frame, $16753. Now what happens if we take these values and expand them all the way down? Well, let's take a look. Now we can see that if we invest the maximum in stocks, five and 10 years down the road, we'll have 15 to 25000 versus only 12 to 17000 five to 10 years down the road. Based on these particular outcomes, we can determine what's the right balance between stocks and bonds for each of us.

Contents