Help modeling investment against historical data
Groups | Search | About
User Password
Register Now! Lost Password?

Financial Planning - Financial planning in general. (Moderated) 

get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content  add this group's latest topics to your Google content  YahooMyWeb Yahoo!  Google Google  Windows Live Favorites Windows Live  del.icio.us del.icio.us  digg digg  Add to Netscape Netscape
Subject Author Date
Help modeling investment against historical data JW 03-05-2008
Posted by JW on March 5, 2008, 11:05 am
Fo the sake of discussion - I have one investment consisting of one asset
classs and I have the historical data for that asset class going back to
1926.
Here are the givens:

1. I have the investment account set up such that its total return is
comprised of 4% Dividend and 6% Unrealized Gain. -- the account has a zero
basis.
2. The historical data is yearly total returns with dividends reinvested.
3. My model will include both deposits and withdrawals to the account.
4. I want to include an effective tax rate in the model to show the tax
consequenses of the withdrawals as well as the dividends.

The only thing I want the model to show is how my present investment would
do over the course of the historical data.

I realize that there are programs out there that can do this. Fidelity will
take my portfolio and project it against hisorical data, but I would like to
do this myself so I can expand it to my entire portfolio and various types
of events affecting the portfolio.

My first cut was -- each year apply that years total return to the current
balance.

Then is was pointed out that some of the historical returns were negative -
and it went down hill from there.
1. How do I account for the dividend with a negative total return?
2. Since my model include withdraws and dividends - there are tax
consequenses. My account has a total return of 10% -- how do I model this
against a historical return of 25% or even worse -25%?

I searched the internet and could find any references to this particular
circumstance. If anyone knows of a reference I can use I would appreciate
it. If you can give me some pointers on how to accomplish this that would
be great also.

Or it may be the wrong group -- if so I apologize.

Thanks,

JW

--------------------------------------
Misc.invest.financial-plan is a moderated newsgroup where Moderators strive
to keep the conversations on-topic for financial planning. Other posting
guidelines include a request for brevity and another for trimming posts to
which we respond. For all of the other tips and suggestions, see "FROM THE
MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the
Newsgroup.


Posted by rick++ on March 5, 2008, 12:18 pm
> The only thing I want the model to show is how my present investment would
> do over the course of the historical data.

1) You design an "investment experiment", example 50-50 stock-bonds
invested for five years.
2) Then you run the experiment starting for each year of your data
(or smaller intervals if you have finer data).
3) You count up the results. You might take each result, sort them
by amount and graph this in Excel. For the example, the balanced
investment you may find 85% of the experiments have positive return-
the middle of the graph (median) is 40% (8% APY), the extremes are
-15% and +200%, etc.

You can make the experiment as complicated as you want, but might have
to
write a computer program, or Excel equation to calculate it.
You might decide to run for the whole 20th century, or last half
depending on
whether you believe the economy was different then, etc.

--------------------------------------
Misc.invest.financial-plan is a moderated newsgroup where Moderators strive
to keep the conversations on-topic for financial planning. Other posting
guidelines include a request for brevity and another for trimming posts to
which we respond. For all of the other tips and suggestions, see "FROM THE
MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the
Newsgroup.


Posted by Will Trice on March 5, 2008, 8:49 pm


JW wrote:

> 1. I have the investment account set up such that its total return is
> comprised of 4% Dividend and 6% Unrealized Gain. -- the account has a
> zero basis.
<snip>
> Then is was pointed out that some of the historical returns were
> negative - and it went down hill from there.
> 1. How do I account for the dividend with a negative total return?
> 2. Since my model include withdraws and dividends - there are tax
> consequenses. My account has a total return of 10% -- how do I model
> this against a historical return of 25% or even worse -25%?

I think your problem is that you're modeling dividends as a proportion
of total return rather than as a percentage of the account value at the
beginning of each period. The latter will result in the dividend always
having the right sign and will keep it from being a huge proportion of
your return in high return periods.

Good luck,
-Will

william dot trice at ngc dot com

--------------------------------------
Misc.invest.financial-plan is a moderated newsgroup where Moderators strive
to keep the conversations on-topic for financial planning. Other posting
guidelines include a request for brevity and another for trimming posts to
which we respond. For all of the other tips and suggestions, see "FROM THE
MODERATORS: Posting to misc.invest.financial-plan", a weekly post now on the
Newsgroup.


Similar ThreadsPosted
Historical data for interest rates given by money market funds? October 18, 2008, 8:25 am
Need Historical Stock Data going back 20 years and with dividends reinvested, for free March 8, 2008, 7:20 pm
Source of historical intraday stock prices? May 23, 2008, 7:13 pm
Anomaly found in S&P 500 data provided by Yahoo! May 20, 2008, 9:57 am
What is max investment? September 22, 2006, 7:06 pm
Serious investment May 15, 2007, 11:39 am
Bond Investment December 8, 2006, 9:38 pm
investment decision January 5, 2007, 4:58 am
Mortgage for investment outside US January 8, 2007, 3:51 pm
investment plan for 25K January 27, 2007, 4:58 am

other essential online resources:
United States Treasury
US Securities and Exchange Commission
New York Stock Exchange
Tokyo Stock Exchange
Accounting and Tax Software Forums
Contact Us | Privacy Policy   XML SitemapXML Sitemap