Improved Net Worth by Age [and plot]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Improved Net Worth by Age [and plot]

Post by bobblehead » Fri Jan 13, 2012 10:40 pm

I created a Google net worth form similar to the 'Net Worth by Age' Google spreadsheet. This form will take longer to fill out, but the results should be very interesting!

Net worth is broken down into 5 categories of assets, and 5 categories of debt. The results can be viewed in a spreadsheet within Google, but only I can edit the cells. Please leave suggestions for plots and calculations in this thread. This is for Household Net Worth. I left a comments box at the end to mention your marital status, degree, school, etc.

Form: https://docs.google.com/spreadsheet/vie ... E6MQ#gid=0
Results: https://docs.google.com/spreadsheet/ccc ... 19PRXZDYVE


Thanks to MidwestEng for the Google form idea!
Thanks to MarketTimer and BigFoot48 for help with the spreadsheet calculations.
Last edited by bobblehead on Wed Jan 18, 2012 3:57 pm, edited 6 times in total.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Fri Jan 13, 2012 10:53 pm

Social security benefits is annual household SS income?
Greg, retired 8/10.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Fri Jan 13, 2012 11:11 pm

GregLee wrote:Social security benefits is annual household SS income?
I'm going to change the form to state monthly SS, and 25x NPV pension benefit. Please PM me if I need to edit a row.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Fri Jan 13, 2012 11:50 pm

I added a summary by age that shows mean/median for Net Worth, Assets, and Debt.

So far the numbers are looking very reliable! I think this is an improvement to the previous spreadsheet.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Fri Jan 13, 2012 11:52 pm

bobblehead wrote:
GregLee wrote:Social security benefits is annual household SS income?
I'm going to change the form to state monthly SS, and 25x NPV pension benefit. Please PM me if I need to edit a row.
Okay. Just to be extra clear, "monthly SS" means monthly SS from both spouses if married, and "25x NPV pension benefit" means 25 times annual income from pensions of both spouses if married and both have pensions?
Greg, retired 8/10.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Fri Jan 13, 2012 11:57 pm

GregLee wrote:
bobblehead wrote:
GregLee wrote:Social security benefits is annual household SS income?
I'm going to change the form to state monthly SS, and 25x NPV pension benefit. Please PM me if I need to edit a row.
Okay. Just to be extra clear, "monthly SS" means monthly SS from both spouses if married, and "25x NPV pension benefit" means 25 times annual income from pensions of both spouses if married and both have pensions?
Yes that's correct. Thanks for your help clarifying early on.

travellight
Posts: 2780
Joined: Tue Aug 12, 2008 5:52 pm
Location: San Diego

Re: Improved Net Worth by Age [and plot]

Post by travellight » Sat Jan 14, 2012 12:03 am

should we enter monthly SS expected if we are below age currently?

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 12:07 am

ncounty wrote:should we enter monthly SS expected if we are below age currently?
Yes put the monthly expected. Here is a link to the SSA calculators: http://www.ssa.gov/planners/benefitcalculators.htm

User avatar
zaboomafoozarg
Posts: 1952
Joined: Sun Jun 12, 2011 12:34 pm

Re: Improved Net Worth by Age [and plot]

Post by zaboomafoozarg » Sat Jan 14, 2012 12:08 am

Hmm, isn't calculating net worth for some entries.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 12:13 am

zaboomafoozarg wrote:Hmm, isn't calculating net worth for some entries.
I have to manually drag the formulas down :)

I tried doing it in advance but when new form results come in they overwrite those cells. I'll try adding them to the last column in advance and see if that works.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 12:27 am

I tried adding the columns before and after, but the new form data still resets those cells to blank. For now I'll have to pull down the formulas for the calculated columns unless someone knows a better method.

User avatar
zaboomafoozarg
Posts: 1952
Joined: Sun Jun 12, 2011 12:34 pm

Re: Improved Net Worth by Age [and plot]

Post by zaboomafoozarg » Sat Jan 14, 2012 12:29 am

Someone's 26 with a spouse of 41? Is Ashton Kutcher a Boglehead?!?

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 12:53 am

Maybe there should be data on married or not, and retired or not.
Greg, retired 8/10.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 1:11 am

GregLee wrote:Maybe there should be data on married or not, and retired or not.
I considered adding options for both. The hard part with a married indicator is the number of possibilities. Single, married, divorced, married multiple times, significant other. Someone added a pivot table to our first spreadsheet looking at net worth by marital status. The fact that married individuals were 12 years older on average made a big difference. Retirement is a tough one too as some work part-time, have a retired spouse, retired and went back to work, or still working full time. It's also highly correlated with age. I think the current form gives us a good picture of net worth factors by age. I also didn't add in degree for similar reasons.

I think the text box at the end is nice because we can look at other members our own age and read a little bit about them and how they got to where they are at financially.

travellight
Posts: 2780
Joined: Tue Aug 12, 2008 5:52 pm
Location: San Diego

Re: Improved Net Worth by Age [and plot]

Post by travellight » Sat Jan 14, 2012 1:21 am

The pension estimate is almost 5X what I would have lump sum guessed it to be making my net worth more than double what I thought it was. Pension calculation is really 25 X the annual amount upon retirement?!!! That is huge.
Last edited by travellight on Sat Jan 14, 2012 1:51 pm, edited 1 time in total.

Carl53
Posts: 1595
Joined: Sun Mar 07, 2010 8:26 pm

Re: Improved Net Worth by Age [and plot]

Post by Carl53 » Sat Jan 14, 2012 5:49 am

bobblehead wrote:
GregLee wrote:
bobblehead wrote:
GregLee wrote:Social security benefits is annual household SS income?
I'm going to change the form to state monthly SS, and 25x NPV pension benefit. Please PM me if I need to edit a row.
Okay. Just to be extra clear, "monthly SS" means monthly SS from both spouses if married, and "25x NPV pension benefit" means 25 times annual income from pensions of both spouses if married and both have pensions?
Yes that's correct. Thanks for your help clarifying early on.
I don't think that you should sum monthly SS income with NPV of pension and other current assests. Perhaps use 25 X (or lower factor - see below) annual SS income at age 65 based on gov est of benefits.

BTW, I'm just an engineer, but I believe that when I received my pension estimate, it showed a NPV of about 15 times the annual value projected. The calcs I did for myself before I retired showed that I would receive a NPV of about 20X the pension in then current dollars if I retired at 55 but this reduced to about 12X if I waited to 65. I used age 92 as a final payout with a 5% discount factor. Yes my pension still climbed every additional year I would have worked and I assumed modest raises, but father time rules. This was a significant piece of my deciding to retire early as I found that the NPV in todays dollars of your pension peak the longer you work. For me it was going to peak at age 56, but it was very flat for the next several years. It seemed appropriate to retire from that job and move on to something else. Note that if you are going to die before 92 then the ratio will be less yet.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 9:13 am

Does anyone else have an opinion on the NW calculation?

We could do a lower factor such as 15x pension and 15x annual SS. I could just mulitply pension column by 15/25 and the SS column by 12*15.

I'll wait to see a few other opinions. Thanks.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 10:07 am

bobblehead wrote:Does anyone else have an opinion on the NW calculation?

We could do a lower factor such as 15x pension and 15x annual SS. I could just mulitply pension column by 15/25 and the SS column by 12*15.
I didn't quite follow what Carl was saying about NPV -- I think perhaps he's talking about the lump sum his own pension system would give him as an alternative to monthly annuity payments. Maybe that's the NPV and maybe not, and how do we know what discount rate was used to derive it? The logic of approximating NPV by multiplying annual pension income by 25 is this: due to some empirical studies of how long portfolios of securities would have lasted in the past if a given inflation adjusted amount had been withdrawn, the figure of 4% of the portfolio is often assumed as a "safe withdrawal rate", starting at 65, which gives a good chance of the portfolio not being exhausted before death. So the 25X amount is the size of the portfolio which one would need to fund the given pension, following the 4% rule. To find the actual NPV, we'd need to know details about life expectancy, survival benefit, and discount rate -- it would be complicated.
Greg, retired 8/10.

ausgenf
Posts: 81
Joined: Fri Aug 22, 2008 11:41 pm

Re: Improved Net Worth by Age [and plot]

Post by ausgenf » Sat Jan 14, 2012 10:09 am

Looking at the spreadsheet, the formula used to calculate assets seems wrong:

value of assets= the value of account assets + value of all residencies + value of other assets + value of pensions + monthly SS benefits

So you are simply adding the monthly SS benefits to the other assets. Either you should not count SS benefits as assets at all, or you should figure out the value of SS benefits (15x or 25x annual SS benefits) and add that number to the other assets.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Improved Net Worth by Age [and plot]

Post by sscritic » Sat Jan 14, 2012 10:21 am

ausgenf wrote:Looking at the spreadsheet, the formula used to calculate assets seems wrong:

value of assets= the value of account assets + value of all residencies + value of other assets + value of pensions + monthly SS benefits

So you are simply adding the monthly SS benefits to the other assets. Either you should not count SS benefits as assets at all, or you should figure out the value of SS benefits (15x or 25x annual SS benefits) and add that number to the other assets.
Agreed. A pension is a stream of payments; SS is a stream of payments. What is the difference? Either measure them both as monthly income or both as some dollar value representing some present value.

P.S. I like good data. Here is my favorite:
Monthly social security benefits: $100,000

travellight
Posts: 2780
Joined: Tue Aug 12, 2008 5:52 pm
Location: San Diego

Re: Improved Net Worth by Age [and plot]

Post by travellight » Sat Jan 14, 2012 10:30 am

seems we could just leave out SS. Just adding the monthly seems trivial anyway.

How do we see the average net worth and other stats? There are almost 100 respondents and it seems the %millionaires is even higher.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Improved Net Worth by Age [and plot]

Post by sscritic » Sat Jan 14, 2012 10:46 am

How is SS trivial? Isn't the value of $2,000 a month for life the same whether it comes from a pension or from SS or from an IRA withdrawal? (Admittedly, the IRA could have a residual value that SS and the pension don't have.)

There really is a bigger problem: it is valuing somethings as if you stopped working today and others as if you kept working to 65. Most SS and pension estimates are based on assuming you keep working. If so, why don't you value your 401(k) and your investment accounts assuming you keep working and saving until 65? I think the answer is to go back to Jane Austen and nisiprius: what matters is whether your income is 1000 pounds or 10,000 pounds.

Here is my proposal for the next grand survey:
Assume you stop working today: what income could you produce? Use 4% for all your investments; value your social security using a calculator that lets you plug in zero earnings for the future; do something similar for your pension, etc. This puts everything in a common unit. (not quite. there are still flaws in this proposal as well.)

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 10:57 am

sscritic wrote: Agreed. A pension is a stream of payments; SS is a stream of payments. What is the difference? Either measure them both as monthly income or both as some dollar value representing some present value.
Yes, and I gave in the other thread my idea of a reasonable approximation to the present value of a given monthly SS payment at retirement (without Medicare deduction): multiply by 12 for annual SS benefit, then by 25 for portfolio size (see above), then (for someone not yet retired) discount back from 66 (or whatever retirement age is assumed for SS) at 5% per year (or some other rate). Some think SS should not contribute to net worth, though, so it would also be plausible not to count SS in NW at all.
Greg, retired 8/10.

stan1
Posts: 5820
Joined: Mon Oct 08, 2007 4:35 pm

Re: Improved Net Worth by Age [and plot]

Post by stan1 » Sat Jan 14, 2012 11:02 am

SS (and pensions) are not a trivial amount, the problem is when people want to count it as an asset rather than future income. We don't count future income from our jobs as an asset, nor do we count future contributions to 401Ks or taxable accounts as assets. SS and pensions are income for doing nothing.

User avatar
ruralavalon
Posts: 13752
Joined: Sat Feb 02, 2008 10:29 am
Location: Illinois

Re: Improved Net Worth by Age [and plot]

Post by ruralavalon » Sat Jan 14, 2012 11:31 am

Re: SS or pension as asset?

I am not an accountant or economist, but it does not seem to me that future income of any kind is an "asset".
"Everything should be as simple as it is, but not simpler." - Albert Einstein | Wiki article link:Getting Started

Howie
Posts: 133
Joined: Sun Jan 02, 2011 7:46 pm

Re: Improved Net Worth by Age [and plot]

Post by Howie » Sat Jan 14, 2012 11:49 am

bobblehead wrote:Does anyone else have an opinion on the NW calculation?

We could do a lower factor such as 15x pension and 15x annual SS. I could just mulitply pension column by 15/25 and the SS column by 12*15.

I'll wait to see a few other opinions. Thanks.
On the pension piece one really needs to factor in current age and when benefits will begin. The NPV will be different for one who is currently 45 years of age vs. one who is 55 assuming benefits commence at the same age. Also, one's expected lifespan post-benefit commencement should be considered-- genetics/lifestyle play a huge part in creating this estimation and similar to the pre-commencement impact, the number of years benefits will be paid will also influence NPV.

One suggestion would be to use the following online calculators. The first can help determine the PV of a future pension (viewed as an annuity and with a finite number of years selectable for the payment stream) and then the second can be used to reduce this for those who have some time remaining before actually being able to collect and, therefore, the actual value is less.

http://www.money-zine.com/Calculators/R ... alculator/
http://www.money-zine.com/Calculators/I ... alculator/

hicabob
Posts: 2696
Joined: Fri May 27, 2011 5:35 pm
Location: cruz

Re: Improved Net Worth by Age [and plot]

Post by hicabob » Sat Jan 14, 2012 11:54 am

The inquisitiveness (I was going to say nosiness) that people have into others financial positions - even though it makes zero difference to their own personal condition, is interesting.

User avatar
SimpleGift
Posts: 3121
Joined: Tue Feb 08, 2011 3:45 pm
Location: Central Oregon

Re: Improved Net Worth by Age [and plot]

Post by SimpleGift » Sat Jan 14, 2012 1:12 pm

Looking at the Boglehead's scatter plot, then comparing it to the median net worth, by age, of most Americans (below), it appears that Bogleheads in general are a very wealthy subset of the U.S. population. But I guess we already knew that. :wink:

Image
Source: Pew Research Center
Cordially, Todd

stan1
Posts: 5820
Joined: Mon Oct 08, 2007 4:35 pm

Re: Improved Net Worth by Age [and plot]

Post by stan1 » Sat Jan 14, 2012 1:22 pm

Bobblehead,
Thanks for the survey. The data looks much more reasonable.
As soon as I saw all those multi-millionaires at $4-7M in the first attempt I figured someone was playing games with us (or didn't understand the question).

travellight
Posts: 2780
Joined: Tue Aug 12, 2008 5:52 pm
Location: San Diego

Re: Improved Net Worth by Age [and plot]

Post by travellight » Sat Jan 14, 2012 1:50 pm

sscritic- I meant just adding the number $2000 which is a monthly payment to the entire net worth which could be a million, is a trivial addition if that was how the math was being done. SS definitely is not trivial, especially if you get it. It is how to quantify it that is perplexing. Perhaps leave it out altogether or find an accurate way to determine PV of it.

I still don't see what the average mean or median net worth is here. Is the data more "reasonable" now? It still looks like high numbers to me which may and likely represents this group.

MoneyOCD
Posts: 281
Joined: Sun Nov 08, 2009 8:56 pm

Re: Improved Net Worth by Age [and plot]

Post by MoneyOCD » Sat Jan 14, 2012 2:02 pm

bobblehead wrote:
ncounty wrote:should we enter monthly SS expected if we are below age currently?
Yes put the monthly expected. Here is a link to the SSA calculators: http://www.ssa.gov/planners/benefitcalculators.htm
I do not think formula for SS -> NW is right or i do not understand it right.
I did enter monthly expected value but looks like you multiplied it by 25. That can not be right because that would be equall of 4% MONTHLY WR. I was under impression that 4% SWR is annual value then means that you need to multiply monthly SS by 12 and then by 25 to come with lump sum that will deliver monthly amount estimated.
Am I missing something?

ETA: looked at spreadsheet again and confused even more.
Why SS added as one month pay only? I would think it should be rathe excluded all together or pu odditional column as total month retirement pay and add there pensions and annuity where you do not have access to principal.

User avatar
Peter Foley
Posts: 4501
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Re: Improved Net Worth by Age [and plot]

Post by Peter Foley » Sat Jan 14, 2012 2:13 pm

Interesting survey. I probably would have left SS out because, unlike some pensions, it stops upon death. A pension is also difficult to value because of the permutations (survivor benefits, etc) and its relationship to age. A pension of $20,000/year is not worth as much to a 90 year old as a 60 year old when life expentancy is considered. Perhaps the pension calculation (and SS or that matter) could be calculated as an annual benefit times average life expectancy.

I hope you are able to do some aggregation of the data to provide insight into how the BH group compares to the population as a whole. (A statistically valid sample of the BH group is probably not possible, however.)

User avatar
Sbashore
Posts: 948
Joined: Wed Feb 20, 2008 10:38 pm
Location: Goodyear, AZ

Re: Improved Net Worth by Age [and plot]

Post by Sbashore » Sat Jan 14, 2012 2:18 pm

Thanks so much for your effort. A couple of observations. I too, think that SS is not represented correctly. Should probably be treated like NPV of pensions. Also it looks like in the Social Security fields that future dollars and present dollars are represented. For instance, I'm on SS now. Some posters are reporting future benefits. Unless we have a lot of disabled young Bogleheads. Shouldn't they be adjusted to real dollars as of a certain date? Sorry if I overly complicated things. :D
Steve | Semper Fi

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:24 pm

sscritic wrote:
ausgenf wrote:Looking at the spreadsheet, the formula used to calculate assets seems wrong:

value of assets= the value of account assets + value of all residencies + value of other assets + value of pensions + monthly SS benefits

So you are simply adding the monthly SS benefits to the other assets. Either you should not count SS benefits as assets at all, or you should figure out the value of SS benefits (15x or 25x annual SS benefits) and add that number to the other assets.
Agreed. A pension is a stream of payments; SS is a stream of payments. What is the difference? Either measure them both as monthly income or both as some dollar value representing some present value.

P.S. I like good data. Here is my favorite:
Monthly social security benefits: $100,000
Ok for now I am NOT including social security in the net worth calculation. If we can have some agreement on how to add it in I'll put it back in the formula. I think the $100k is yearly, we could probably divide by 12 but I'll wait to hear if that user wants his/her row changed.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:25 pm

ncounty wrote:seems we could just leave out SS. Just adding the monthly seems trivial anyway.

How do we see the average net worth and other stats? There are almost 100 respondents and it seems the %millionaires is even higher.
There are 3 tabs at the bottom of the spreadsheet. Take a look at tab 3 for summaries by age, and overall (total row).

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:26 pm

stan1 wrote:Bobblehead,
Thanks for the survey. The data looks much more reasonable.
As soon as I saw all those multi-millionaires at $4-7M in the first attempt I figured someone was playing games with us (or didn't understand the question).
Thank you Stan, I'm glad you like it.

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:30 pm

MoneyOCD wrote:
bobblehead wrote:
ncounty wrote:should we enter monthly SS expected if we are below age currently?
Yes put the monthly expected. Here is a link to the SSA calculators: http://www.ssa.gov/planners/benefitcalculators.htm
I do not think formula for SS -> NW is right or i do not understand it right.
I did enter monthly expected value but looks like you multiplied it by 25. That can not be right because that would be equall of 4% MONTHLY WR. I was under impression that 4% SWR is annual value then means that you need to multiply monthly SS by 12 and then by 25 to come with lump sum that will deliver monthly amount estimated.
Am I missing something?

ETA: looked at spreadsheet again and confused even more.
Why SS added as one month pay only? I would think it should be rathe excluded all together or pu odditional column as total month retirement pay and add there pensions and annuity where you do not have access to principal.
I was originally just adding in the monthly SS. Right now I removed it from the total assets calculation since that didn't make sense.

We do have separate columns for each, so maybe we need 3 net worth calculations.

NW with pension and SS (both 25x annual)
NW with pension (25x annual)
NW without either

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:34 pm

Peter Foley wrote:Interesting survey. I probably would have left SS out because, unlike some pensions, it stops upon death. A pension is also difficult to value because of the permutations (survivor benefits, etc) and its relationship to age. A pension of $20,000/year is not worth as much to a 90 year old as a 60 year old when life expentancy is considered. Perhaps the pension calculation (and SS or that matter) could be calculated as an annual benefit times average life expectancy.

I hope you are able to do some aggregation of the data to provide insight into how the BH group compares to the population as a whole. (A statistically valid sample of the BH group is probably not possible, however.)
The data is available in the spreadsheet if anyone would like to make a copy. I can also try to add new statistics to the Summary tab if there is anything in particular people want to look at.

User avatar
market timer
Posts: 5940
Joined: Tue Aug 21, 2007 1:42 am

Re: Improved Net Worth by Age [and plot]

Post by market timer » Sat Jan 14, 2012 2:37 pm

If you want to make the pension value realistic, it has to include life expectancy. Why not just copy-paste the table below into Excel and do a vlookup? You can multiply annual payment by life expectancy as a first approximation.

http://www.ssa.gov/oact/STATS/table4c6.html

User avatar
BigFoot48
Posts: 2578
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Improved Net Worth by Age [and plot]

Post by BigFoot48 » Sat Jan 14, 2012 2:53 pm

market timer wrote:If you want to make the pension value realistic, it has to include life expectancy. Why not just copy-paste the table below into Excel and do a vlookup? You can multiply annual payment by life expectancy as a first approximation.

http://www.ssa.gov/oact/STATS/table4c6.html
And do the same for SS, arriving at a total expected benefit amount, then discount that back to a PV based on the life expectancy and a suitable discount rate.
Last edited by BigFoot48 on Sat Jan 14, 2012 6:54 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

bobblehead
Posts: 185
Joined: Tue Feb 19, 2008 12:19 pm

Re: Improved Net Worth by Age [and plot]

Post by bobblehead » Sat Jan 14, 2012 2:57 pm

BigFoot48 wrote:
market timer wrote:If you want to make the pension value realistic, it has to include life expectancy. Why not just copy-paste the table below into Excel and do a vlookup? You can multiply annual payment by life expectancy as a first approximation.

http://www.ssa.gov/oact/STATS/table4c6.html
And do the same for SS, arriving at a total expected benefit amount, then discount that back to a NPV based on the life expectancy and a suitable discount rate.
I really like this idea, keeps things consistent. We will still have people enter SS as monthly and Pension as NPV*25, and then we can do the calculation in the spreadsheet.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 3:58 pm

bobblehead wrote:I really like this idea, keeps things consistent. We will still have people enter SS as monthly and Pension as NPV*25, and then we can do the calculation in the spreadsheet.
No, the value of a pension/SS is not simply the pension payment multiplied by the number of payments expected through life expectancy, any more than you can decide the appropriate size of your retirement portfolio by multiplying your yearly income needs by your life expectancy. Money has interest value. An annuity payment 10 years from now is worth far less to you than an immediate payment. This is why the 25X rule, though it is very crude, is an appropriate measure of value. It is derived by measuring portfolios of securities which increase in value over time.
Greg, retired 8/10.

stan1
Posts: 5820
Joined: Mon Oct 08, 2007 4:35 pm

Re: Improved Net Worth by Age [and plot]

Post by stan1 » Sat Jan 14, 2012 4:01 pm

Just looking over the pension data:

It's possible some people may have entered monthly, others annual, and still others lifetime.

NPV * 25 may not be clear to everyone.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Improved Net Worth by Age [and plot]

Post by sscritic » Sat Jan 14, 2012 4:08 pm

GregLee wrote: No, the value of a pension/SS is not simply the pension payment multiplied by the number of payments expected through life expectancy, any more than you can decide the appropriate size of your retirement portfolio by multiplying your yearly income needs by your life expectancy. Money has interest value. An annuity payment 10 years from now is worth far less to you than an immediate payment. This is why the 25X rule, though it is very crude, is an appropriate measure of value. It is derived by measuring portfolios of securities which increase in value over time.
The data includes your age. If you are 30, the spreadsheet knows you are 35 years from 65. The suggestion was to use NPV to calculate the value as of today. I don't see the problem if the calculation is done behind the scenes (not by the person entering the data).
then discount that back to a NPV based on the life expectancy and a suitable discount rate
The NPV of a stream of income starting 35 years from now is the NPV of the stream valued at that time (35 years from now) discounted back to today. It can be thought of as a two stage process: go to 2047 and compute the NPV of the stream as of that (starting) year, then discount your answer back to today.

Or were you thinking of something else? I am not understanding your objection to using NPV to value the future stream.

User avatar
market timer
Posts: 5940
Joined: Tue Aug 21, 2007 1:42 am

Re: Improved Net Worth by Age [and plot]

Post by market timer » Sat Jan 14, 2012 4:15 pm

GregLee wrote:
bobblehead wrote:I really like this idea, keeps things consistent. We will still have people enter SS as monthly and Pension as NPV*25, and then we can do the calculation in the spreadsheet.
No, the value of a pension/SS is not simply the pension payment multiplied by the number of payments expected through life expectancy, any more than you can decide the appropriate size of your retirement portfolio by multiplying your yearly income needs by your life expectancy. Money has interest value. An annuity payment 10 years from now is worth far less to you than an immediate payment. This is why the 25X rule, though it is very crude, is an appropriate measure of value. It is derived by measuring portfolios of securities which increase in value over time.
Greg, it works very well in this case because real interest rates are near zero over 10-15 years (typical SS payout) and SS is a constant, inflation-adjusted stream of income. If real interest rates change substantially, the formula would need to be modified.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 4:17 pm

stan1 wrote:NPV * 25 may not be clear to everyone.
"NPV * 25" doesn't even make sense. The appropriate formula is "annual pension income * 25", which is arguably not an appropriate part of net worth, but at least it does make some sense as an approximation to the NPV of a pension (at retirement age). You don't multiply NPV by 25, you multiply by 25 to get NPV (or at least some sort of approximation to NPV). I think submitting annual pension income (or annual SS income) would be better for the next such table someone does, simply because it is closer to being a fact and less like a theoretical conclusion.
Greg, retired 8/10.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Improved Net Worth by Age [and plot]

Post by sscritic » Sat Jan 14, 2012 4:19 pm

Also, SS estimates are in something approximating today's dollars. If your social security estimate says you will get $2000 a month in 2047, that is $2000 of 2012 dollars, not $2000 of 2047 dollars. You don't need to discount them back to today; they already are discounted.

Howie
Posts: 133
Joined: Sun Jan 02, 2011 7:46 pm

Re: Improved Net Worth by Age [and plot]

Post by Howie » Sat Jan 14, 2012 4:22 pm

sscritic wrote: The NPV of a stream of income starting 35 years from now is the NPV of the stream valued at that time (35 years from now) discounted back to today. It can be thought of as a two stage process: go to 2047 and compute the NPV of the stream as of that (starting) year, then discount your answer back to today.
You are exactly right and my earlier post (a dozen or so above this one) speaks to this and provides (I believe) links to two online calculators for quick computation.

sscritic
Posts: 21858
Joined: Thu Sep 06, 2007 8:36 am

Re: Improved Net Worth by Age [and plot]

Post by sscritic » Sat Jan 14, 2012 4:24 pm

Howie wrote:
sscritic wrote: The NPV of a stream of income starting 35 years from now is the NPV of the stream valued at that time (35 years from now) discounted back to today. It can be thought of as a two stage process: go to 2047 and compute the NPV of the stream as of that (starting) year, then discount your answer back to today.
You are exactly right and my earlier post (a dozen or so above this one) speaks to this and provides (I believe) links to two online calculators for quick computation.
But my suggestion is to let the data/spreadsheet master do it instead of relying on the individual entering the data. That way you get a consistent methodology, right or wrong.

User avatar
GregLee
Posts: 1748
Joined: Wed Oct 27, 2010 3:54 pm
Location: Waimanalo, HI

Re: Improved Net Worth by Age [and plot]

Post by GregLee » Sat Jan 14, 2012 4:35 pm

sscritic wrote:The NPV of a stream of income starting 35 years from now is the NPV of the stream valued at that time (35 years from now) discounted back to today.
I don't think you're considering both of the two discountings involved. To value today a pension which starts 35 years from now, you have to discount the value of the pension as of 35 years from now back to today. If the table has the value of the pension at retirement age, and if we can agree on an appropriate discount rate (5% perhaps?), then since the table includes current age, we can do that. However, my previous comment concerned the other discounting problem: what are all of the monthly pension payments worth at the time of retirement, given that those payments will be paid at varying times after the retirement date? The 25X rule approximates the value of the pension at retirement, and discounting back to the present age of a prospective retiree is a separate calculation.
Greg, retired 8/10.

Post Reply