Home     Forum     401k     401k Rollovers     Crypto Forum
    Register   Login   Members   Search   FAQs     Recent Posts    



Investment Comparison Spreadsheet

Reply to topic
Money Talk > Retirement Planning

Author Thread
Wino
Senior Member


Cash: $ 113.80

Posts: 560
Joined: 03 Aug 2012
Location: Dubai
 Reply with quote  

So, you two think I should show her what her money could have done under different scenarios. I know! I'll write a spreadsheet that allows me to show two different scenarios side by side... (hint, look up earlier in this thread)

By the way, said spreadsheet had some errors. If you downloaded it, I suggest you grab it again and overwrite it. I think it's 100% now.

Any other changes you want to suggest? I think I'll do this again, but monthly, so the truly anal can update it to their heart's content. I already have a spreadsheet that looks up real-time stock information off of Yahoo. I think I might combine that with this, and do an automatic look-up.

Is anyone interested in a stock tracker/mutual fund tracker that's somewhat automated? You'd put in up to about 20 symbols (they must be on yahoo's finance list), and then just run the spreadsheet like all the time in the background. It would look up the symbols and their prices like once a month or so, and then track your investments. I haven't worked out the details.

If anyone has any ideas they'd like to see, let me know.
Post Sun Aug 18, 2013 4:34 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

both of you guys are missing the point. TIPs do not have real economic risk as long as they don't default. real economic risk for someone trying to retire is a reduction in retirement income. if TIPs product $1k in real retirement income in today's dollars, if their market value drops they will still produce the same $1k. therefore, there is no real economic risk. I did say their is opportunity cost. that means if the market value declines, it would be possible to produce more than that $1k in retirement income. this is an opportunity cost, not a loss since the original retirement income is locked in.

wino, if you like writing spreadsheets to show here examples, why don't you try writing one that gives her the information she is actually looking for. the one you have shows her how much money she can expect to have in the bank the day she retires, for example. what good is that? she is going to spend all her days starting at her bank account when she retires? someone who is going to retire wants to know how much they can spend.

so try this...ask her how much she needs in retirement and when she plans to stop working. inflate the spending needs at a certain rate from not to retirement, and show the spending for each year continuing to increase with inflation. look at 2 alternative investments: TIPs & stocks. for TIPs, buy a portfolio with maturities when she needs the money. reinvest the cash flows from the portfolio at inflation plus a small premium. then take stocks - show the historical returns on stocks in high inflation (say 5-7%) periods such as the 1970's and compare it to the TIPs portfolio in its ability to fund the retirement needs. note the TIPs portfolio calculations will precisely match the swings in inflation. stocks will do very poorly and the inflation will increase expenses at the same time. then try a positive period of returns with low inflation and high stock prices such as the 1990's. again, the TIP will not enjoy any benefit whereas stocks will do very well.

if you don't want to write the spreadsheet, it's okay. someone else has already done the work for you. look at http://www.prospercuity.com/

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Sun Aug 18, 2013 5:52 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

something else you should note. people do not tend to follow the advice of models unless the models tell them what they want to know. if someone wants to be safe in retirement and you show them how much money you think they will have on the first day of retirement, they may not jump in and take the risks you want them to take and hold them through bad markets. if you show them exactly how it will make them safe in retirement, they are much more likely to listen to you, and very importantly hold to the strategy during bad times.

right now, your best approach is to show her that her attempts to be safe are anything but safe. if she understands this, it will get her off the mark. but to demonstrate the point, you need to show her what safety is...that is TIPs compared to cash. once she understands what safety is, she can determine the place for stocks or non-market risk strategies to improve retirement. so basically, you guys are trying the blunt approach to the subject and giving up your best asset in communicating why she should get out of cash...

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Sun Aug 18, 2013 6:10 pm
 View user's profile Send private message
Wino
Senior Member


Cash: $ 113.80

Posts: 560
Joined: 03 Aug 2012
Location: Dubai
 Reply with quote  

quote:
Originally posted by smk
if you don't want to write the spreadsheet, it's okay. someone else has already done the work for you. look at http://www.prospercuity.com/


That spreadsheet is nowhere near what I'm talking about. First off, I don't count social security. No, this is not some wild conspiracy theory. I'm sure some form of SS will still be there when I retire. My problem is that since it is broke and unsustainable, they're going to have to reduce the benefits to folks. As I'm one of the "Rich" as defined by current government whims, I figure I'll be one of those who is screwed out of my "investments" in the Ponzi scheme.

What's the downside if I'm wrong? I get more money than I planned.

My calculator will allow you to run it, and just leave it alone. It will update itself. Your worst problem will be to open it (click on it, as it will need to be open to gather its data), and save it to keep the data on your computer. I guess I could write a C program to save it every week or so, but now I'm getting even more complicated. No... you'll just have to open it, leave it open, and remember to save it once in a while. That way, it'll "track" your investments more closely.

How did I miss your point? Type slowly, because I can't read fast. I can run any scenario I want inside Excel, no matter how complicated. The only problem is putting in "good" data from some trusted source.
Post Sun Aug 18, 2013 8:04 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

quote:
Originally posted by Wino


That spreadsheet is nowhere near what I'm talking about.


the only spreadsheet I saw was https://dl.dropboxusercontent.com/u/104508282/InvestmentComparison.xlsx
so I don't know what spreadsheet you are talking about that can do anything.

but I will type slowly so hopefully you will understand - the output of your spreadsheet that determines success or failure needs to be the portion of the retirement income covered by the investment portfolio and the risk of having too little to live on. this is the objective of the person who retires.

if you determine success or failure by how much money you have at a particular point in time, that objective may not translate into being able to support yourself in retirement.

using the correct measurement will produce less self destructive behavior. dw is probably seeing lost dollars today as her risk measurement, which indicates cash is riskless. but for retirement, investing totally in cash is very risky - you may not be able to pay your bills. if you look at the ability to pay your bills, she will get out of cash and start with TIPs or something like that. then to the extent she is able to bear risk, she can increase it.

I am typing very slowly now - your analysis targets the wrong goals. it doesn't work.

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Sun Aug 18, 2013 10:21 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

quote:
the retirement income goals of the retired investor, which is exactly the risk you are (rightfully) concerned about


progress at last...

now TIPs are not a panacea - they are a MODELING TOOL. you understand your objective as retirement income. you need to model this stream of cash flow to figure out which assets will match your needs best. so you use a series of TIPs as a proxy for your retirement income needs. you also know it is physically possible to buy a series of TIPs which will accomplish your goals as you agreed above.

so question - which asset will most closely align with the performance of that strip of TIPs? you will go back historically and figure the tracking error (SD of the difference in returns between series of TIPs and alternative assets you can choose from), as 1 example of a risk measure. there are other alternatives that may do well - series I bonds, longevity annuity if inflation protected, TIPs mutual funds, nominal bonds, cash, high dividend paying stocks, general stocks, etc. (probably in that order)

based upon your preferences for risk and return, you construct a portfolio measuring the risk as deviation from the series of TIPs you are using as a proxy for your retirement income goals. with this approach, you have a much better idea whether your strategy will work or not. the standard methodology using cash as your risk measure is like playing darts in a very dark room.

now we take this to someone with very low risk tolerance. they CAN purchase the series of TIPs if they want. if rates are low, they will likely give up the opportunity to raise their retirement income by simply waiting to purchase them, so they MAY choose to wait. if they decide they want the TIPS to lock in their retirement income because they cannot handle the risk of stocks, they are not necessarily wrong. but if they choose cash as a long term solution because they don't want to take risk, that is a particularly bad idea for the objective of retirement income. the return is very low and this risk is high. TIPs have lower risk and higher return.

by doing this analysis, dw can see in very practical terms to flaw in using cash to reduce risk. then an evaluation of dw's genuine risk tolerance can take place with a more relevant measure of success/failure. this approach would be particularly helpful to someone with low risk tolerance...

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Mon Aug 19, 2013 10:48 am
 View user's profile Send private message
Wino
Senior Member


Cash: $ 113.80

Posts: 560
Joined: 03 Aug 2012
Location: Dubai
 Reply with quote  

quote:
Originally posted by smk
series I bonds, longevity annuity if inflation protected, TIPs mutual funds, nominal bonds, cash, high dividend paying stocks, general stocks, etc. (probably in that order)


I don't see real estate (not REIT's) on your list. That's actually my plan for inflation protection. I figure if inflation rears its head, housing and rental prices must also go up, so my rental income may lag inflation, but it should still track inflation.

To that end, I've found a nice neighborhood with moderately-sized homes (1600 sqft) that are going for about $110K or so, each. Now, I know that if he's reading this, oldguy's going to jump in and say "smaller house at $80K is better," but I'm in Texas, and 1200 sqft homes only do well if in close to the city, and they go for closer to $300K.

Anyway, I've been speaking to DW about it, and we're putting aside a fund to save up 50%, at which point we'll purchase and set up the house rent to pay the remaining mortgage and fees. Then we'll rinse and repeat.

The main difference between my method and oldguy's is I won't be pulling out equity to gamble on the market. I'm more than content to let rising house values gain equity, and to get an income from the rental. If I want more, I still have my mutual funds, and DW still has her MM, that I'm trying to talk her in to abandoning.

Along the lines of having her invest rather than hold, I'm putting quite a bit into "her" funds as well as "my" funds. Even if she "loses" on the investments, she more than makes up for it in the "free money" she gets when I purchase shares.

I still don't see how my sheet can't be used to model TIPs. What other adjustments besides period fees ($), period fees (%), etc., is there with TIPs? Don't say "inflation," because that's a percentage, even if it just says "annual rate." If I break it down monthly, you can do it more precisely, but I still don't see any scenario where one of my variables won't do the trick close enough to allow you to check the outcome.

You keep saying "retirement income." We won't be touching principle at all. Our "income" will be whatever it earned last month, or less. That's the ground rules we set up, and that's what we'll live by. I'll use real estate rental income as a fixed income, and let the funds provide any cushion. If the funds go down, then I'll just take the hit until it reaches our comfort level again. I'm not looking for retirement "income." I'm looking for "reserve assets."
Post Mon Aug 19, 2013 1:17 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

Wino, I an not sure how much I can help you. you seem to think you know more than you really do and do not seem open to looking at what I am saying. that is fine by me, but I cannot communicate these points to you if you just keep sticking with your old ideas which bear no relationship with what I am saying.

for example, you are relating market LEVELS to changes in market levels needed for the TIPs analysis. your spreadsheet just compares apples to oranges. real estate may not be as directly related to inflation as you think. that said, it is a powerful hedge against your future cost of living, to the extent it is the same property you attempt to live in. also, financing it through fixed rate debt and not using oldguy's speculative strategies are also good ideas.

but I don't think I can tell you anything more...

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Mon Aug 19, 2013 2:18 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

quote:
Originally posted by coaster
I guess your typing speed was finally s l o w enough. Laughing


for you I think. what did you think of the next steps...as in any analysis, you start out developing simple approximations and continue to work towards making them realistic...

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Mon Aug 19, 2013 2:24 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

sorry, but you have to explain to me how it is I am not looking at the macro? perhaps this is part of the communication gap Wink

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Mon Aug 19, 2013 2:29 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

well, wino is building a model to evaluate the macro situation. apparently his model seems to focus on maximizing nominal dollars at a particular point in time. I am building a model to evaluate the macro situation. my model includes both investment and non-investment factors and targets the objective of being able to actually pay your bills in retirement. the communication gap comes from an apparent disinterest in whether you can pay your bills in retirement in favor of the not so relevant ability to wave the flag to show how much money you have on a particular day...

sorry, but what I am doing is definitely macro.

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Tue Aug 20, 2013 10:43 am
 View user's profile Send private message
Wino
Senior Member


Cash: $ 113.80

Posts: 560
Joined: 03 Aug 2012
Location: Dubai
 Reply with quote  

quote:
Originally posted by smk
wino is building a model to evaluate the macro situation. apparently his model seems to focus on maximizing nominal dollars at a particular point in time.

emphasis added

A few points: Wino has built a model. I can change it, but the model is there, and it functions.

The model doesn't maximize or minimize anything. The model allows you to enter variables for two different future value equations for the purpose of comparing the outcome of the two strategies. Some of the variables are:

Amount invested initially (balloon, $)
Amount invested monthly ($)
Amount of any front end load fees (%)
Amount of any back end load fees (%)
Annual return (%)
Percentage for admin fees (%)
Annual admin fees ($)
Share purchase transaction fee ($)
Share sale fee ($)

Some fees or variables can be adjusted each year, but some cannot. If a change is made, that change is used from that point forward, while previous calculations use the previous variable.

So, where does this try to maximize anything? It doesn't. It allows you to compare two different investments assuming the same dollar amount is invested periodically in each of them. What variables you use are up to you, and you can use 0 or zero per cent for variables that do not apply to a given investment.

You want another variable? Name it, and define it. If you want to discuss nebulous trivia about government bonds, fine... define its variables, but I bet the figure is a percentage, which is already allowed in my model, and therefore can be used to model the results.[/quote]
Post Tue Aug 20, 2013 1:40 pm
 View user's profile Send private message
Wino
Senior Member


Cash: $ 113.80

Posts: 560
Joined: 03 Aug 2012
Location: Dubai
 Reply with quote  

quote:
Wino, I an not sure how much I can help you. you seem to think you know more than you really do and do not seem open to looking at what I am saying.


Let me help you by explaining how to use this model:

You invest money starting whenever you like. That is "year 1."

Do you want to use "real" variables? Then start with assumptions in 1992, and use variables from then until 2012 for any different number of scenarios.

Do you want to actually TRACK what you do today, compared to what you COULD HAVE done? Then start with assumptions in 2013, and update the variables annually. Was it a good choice you made?

Want to figure out which was good for the last ten years, then start investing now? Start with some figures in 2003, and figure out which one did better historically, and start with a "negative fee" in 2013. That's just like a deposit. You can then zero your previous investment variables and go forward from there.

Want to do two completely different investments? Fine. Change the percentages annually; change the amounts annually using the "fees" dollar which allows you to buy (negative fee) or sell (positive fee).

The good thing about custom models is I can make them do anything I want. I'll ask one more time: Do you want a different variable? Should you be able to change it annually? Should it be a dollar amount or a percentage of something; if a percentage, a percentage of what?

You seem to think that random variables I put in are some analysis I've done. I just commented on some of the results using some numbers pulled from a single prospectus against the SP500 on Vanguard, and then I said I was somewhat surprised by the results.

It sure is good to know what the results are when you're making decisions, and if you can vary some of your assumptions, then you can get a clearer picture, still. Good thing I have a model that allows me to do it, now that I've written it.
Post Tue Aug 20, 2013 2:02 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

wino...I stopped doing stuff like your model (which I did in my sleep) in the early 1980's because it was too antiquated.

you need to enter a lot more variables. what about tax structure for roth, trad ira and maximizing the benefits based upon changing tax rates throughout your life. what about deferring pension income (social security based upon an assumed reduction in the amount or private pensions), what about the impact of longevity through lifetime annuities, what about inflation spikes, what about the costs of home ownership/downsizing and moving to a no tax state. these are all basic ideas on how to improve retirement income without taking market risk. if you don't consider these first, you are doing dw a disservice.

but before you get to these, you need to know how to evaluate whether your alternative investments are good or bad. your model output shows total money at the end of the period with both alternatives, but you can't decide which is better based upon just that. the return assumptions you use are not guaranteed. what about dispersion of returns. then, what are you comparing it to. for you it is nominal dollars at the end of the period. what about inflation adjusted dollars at the point in time when you actually need each dollar. then have you considered you can purchase that stream of cash flows in the open market and monitor the price every day? how does the price of your alternative investments match up to the price of the cash flows you will need when you actually retire on a daily basis? take the changes in returns and compare them to each other - what is the standard deviation (dispersion) of those returns. what is the dispersion of actual dollars you will have to spend every year in your retirement?

again, this is all fairly basic stuff. but you can't compare one asset to another without knowing how to evaluate which is better. your model produces dollars at a point in time as the output, but that point in time is not the point in time when you need it. also it does not consider the inflation of those dollars needed or the dispersion of returns verses those inflated dollars...

you wanted some variables...you got em Wink

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Tue Aug 20, 2013 2:36 pm
 View user's profile Send private message
smk
Preferred Member


Cash: $ 37.05

Posts: 183
Joined: 12 Dec 2012

 Reply with quote  

quote:
Originally posted by coaster
Steve, I decided to not relate my story. It's too long. And in any case, we're just going to rehash ground you and I have covered before about the limits of models. I didn't say you weren't covering the macro. I did say you were looking at the micro. You want a model with a lot of micro inputs. And therein lies the problem. Any one of the micro input assumptions that in real life deviates from the assumption changes the result of the macro. And the more the inputs, the more they can gang up on skewing the resulting macro. The approach I've decided to take, after my own bout with models, is to postulate the macro based on logic, common sense, and real-life observations. Then, while implementing that macro, to observe and test the outcomes on an ongoing basis to determine if the macro holds. If the macro holds over time, it means my postulate was correct. If the macro fails over time, then my postulate was wrong, and I need to ditch it and develop another. It's more like real life. Things change and you adapt to the change.


we agree on the limits of models. I do the same was monitoring model performance against reality. the question is when you start with a model, are you targeting your objective or something else?

as for your approach on high dividend paying stocks, if you look at very long history, dividends produce the majority of economic benefit. however, in periods of high inflation, the underlying businesses cannot produce dividend improvements that match inflationary increases. common sense says your approach has a hole in it. an emphasis on reits as dividend payors may help, but again the deviations can be substantial. if you take out a long term fixed rate mortgage where the notional amount matches the amount of d paying stocks, the inflationary risk may be covered. if you are buying any bonds with limited credit risk and have not considered inflation protected assets instead, that is a very inefficient way to play the fixed income markets given a retirement objective. these are the common sense results that come from studying the models I was discussing above. but in order to get to the common sense, you need to start with a model that targets your objective and study how things work...wino is definitely not doing that...

Steve Kanney, CFA
http://www.integratedfinancialny.com/index.html
Any comments made are designed to help you make your own decisions and do not consititute investment advice.
Post Tue Aug 20, 2013 3:39 pm
 View user's profile Send private message

Goto page Previous  1, 2, 3, 4  Next
Reply to topic
Forum Jump:
Jump to:  
  Display posts from previous:      


Money Talk © 2003-2022

Crypto Prices