Calculating average portfolio yield

[redhead50]

I'm in the first year of early retirement and using the (java-based) flexible retirement planner (www.flexibleretirementplanner.com/wp/) to help me model my retirement.

I'd like to enter portfolio return estimates for that model. I'm trying to estimate the average historical annual yield of my portfolio based on the last 8 years of my 403(b) end-of-year portfolio value. (Yes, future yields do not necessarily reflect past yields....) The end-of-year portfolio values are net of fees.

I contributed a total of $30k in each of those 8 years (my contribution + employer match) and so I want to take that into account in my calculations.

I haven't started to withdraw anything so far from this portfolio and all dividends were automatically reinvested, and so the only changes in each year end value should be due to the $30k annual contribution and the portfolio's performance right?

For any year after 2009, I calculated year-over-year return like this:
[(year 2 - year 1)/year 1] X 100 = year-over-year % yield

Here's that historical data:
Year |value| value-$30000 | calculated % yield
2009 $424000 $394000
2010 $527000 $497000 26.14
2011 $547000 $517000 4.02
2012 $655000 $625000 20.89
2013 $844000 $814000 30.24
2014 $928000 $898000 10.31
2015 $941000 $911000 1.45
2016 $1087000 $1057000 16.02
2017 $1270000 $1240000 17.31
Average calculated yield 15.80%

My annual $30k contributions were spread out, twice monthly, and while that annual contribution is subtracted, the effect of gradual yearly contributions isn't taken into account, but is, I think, small.

Is it logical that I enter 15.8 % as a reasonable estimate of the annual return of this portfolio?

Thanks for anyone's time,
Red Head

 

[marko]

My annual $30k contributions were spread out, twice monthly, and while that annual contribution is subtracted, the effect of gradual yearly contributions isn't taken into account, but is, I think, small.

I can't do the math, but I'd enter your holdings and values into M* Portfolio Tracker. It would then calculate your 3,5,10 and 15 year average annual return. Discounting any small variables it should be close enough.

 

[Dtail]

I have used FRP. I would not enter a customized 15% return for future returns, as that was based on a full bull market run.
Why not try the standard returns they offer based on style of portfolio?

 

[mountainsoft]

I have used FRP. I would not enter a customized 15% return for future returns, as that was based on a full bull market run.
Why not try the standard returns they offer based on style of portfolio?

There is a simple return on investment calculator at:
https://www.calculator.net/roi-calculator.html

That said, I agree with Dtail, I would not assume 15% for future returns. That's being very optimistic and could come back to bite you.

I also use Flexible Retirement Planner but use a custom 6% average return with 8% standard deviation. With any luck my portfolio should do better than this but I prefer to err on the side of caution. I also assume a 3.2% inflation rate, a "stable" spending plan, and am only accounting for 75% of what social security says we will receive.

Hopefully my estimates will be wrong and we'll end up with more money than planned. That's better than thinking we're gonna have a certain amount of money and ending up short because I estimated too high.

 

[foxfirev5]

Amount your portfolio generates divided by portfolio value. The percentage doesn't matter as long as it's a damn site better than your expenses.

 

[clobber]

This calculation is actually fairly simple to do given the infrequent and regular your purchases you made. Get to know the XIRR function in Excel or Google Sheets. There are plenty of examples out there, one of which is here

You'll just set you two columns, one with dates and one with purchase amounts. It will calculate the % for you.

 

[redhead50]

How to use Flexible Retirement Planner to model portfolio yields

Dtail,

Thanks for the feedback.

I tried FRP's canned investing styles initially but even FRP's Aggressive portfolio style didn't yield anything approaching the final eight year portfolio value I have from my portfolio's historical data. Aggressive portfolio has 10%(15.8%) return and yields an eighth year portfolio value of ~$795k (see my detailed response to mountainsoft). Way low.

This worried me; this is what sent me down this initial year-over-year calculation.

Red Head

********************************

mountainsoft,

Thanks for the feedback and the tool:
CALCULATOR.NET'S return on investment calculator yields an annualized ROI of 15.41 over an 8 year investment length (see below).

As you described, I also use what I consider more reasonable yields in my retirement planning, similar to yours: if you can realistically be happy with the outcome of (historically) low estimates, and then history provides higher yields over a retirement horizon, all the better.

I was just trying to get a handle on the yields I had experienced in the last eight years before I retired. Your suggested ROI calculator says I'm not crazy in my initially posted calculation (again, see below).

With that said:

I was also able to model my posted historical portfolio return using the Flexible Retirement Planner itself:

I entered the following 6 variables in FRP. No other FRP variables affected the calculation.

1) Current age = 60 y
2) Life expectancy = 68 y
These used to generate the 8 years of model output to match my available historical portfolio data.
Any 8 year range will do.

3) Tax deferred portfolio value = $394,000 (the initial portfolio value with that years $30k subtracted)

4) Average annual inflation = 1.7%(sample standard deviation = 0.86)
(based on latest available 9 years of available data from inflationdata.com)
And yes this is an historically low inflation rate [1914-2017 = 3.2%(SD 4.8) as mountainsoft suggested]

5) Annual retirement spending = $0
Since all I'm trying to do is calculate the historical yield of a portfolio growing untouched.

6) I set the investing style to custom, entered average return of 15.8%(SD 10.1).
The sample standard deviation I calculated from the historical data using STDEV function in (Apple's) Numbers; this would be THE STDEV.S FUNCTION in Excel as I recall.

Here are the results.
My portfolio's historical data (with annual $30k subtracted) are on the left and FRP's median portfolio data from it Monte Carlo simulation on the right:

Historical | Model
$394000 | $450539
$497000 | $512742
$517000 | $582266
$625000 | $662219
$814000 | $751867
$898000 | $855038
$911000 | $969561
$1057000 | $1106274
$1240000 | $1254141 (ninth year)

Note the model's calculated final year value is 1.1% different from the actual historical value in the ninth year.


The close historical | model portfolio values tell me that my calculation showing a calculated average annual return of 15.8% in my initial post was reasonable.

The analogous calculation for the portfolio's historical data WITHOUT the $30k subtracted using its own initial value $424,000 and average year-over-year return, 15.0%(SD 9.6), yield similar results ($1,264,000 in the eight year; compare to my initial post ninth year value of $1,270,000).

Finally note that this is a Monte Carlo simulation which provides the median portfolio values from 10,000 runs. On my laptop this runs instantaneously each time I click the run button. This means that I'm seeing the average of 10,000 simulations with each run applying a set of random values initial values contained by the entered standard deviations.

Red Head