Once again I present my sincere congratulations to the PSM team for this new forum. I demand your attention for an old request regarding the annual gain, so that that the users can compare percentual gain to an index, as much as any other fund. This feature would make the PSM the best software for controlling stocks and also the performance.

Those metrics are very important to identify if you are loosing or gaining money passing the time and either if you are ahead of the market and if your extra effort is making any sense.

The problem with those metrics is always the cash in and cash out in the management of cash flow. That can be very easy taken, as PSM treat them separately and add the dividends, etc. One easy step should be accumulate the Total Value displayed daily by the software and accumulate it in a spreadsheet cell.
 Than, all you have to do is reference to a certain data that should be the beginning of a NAV (Net Asset Value)

All the description of the mathematics you can find in 3 interesting articles made by the Motley Fool:

http://www.fool.com/investing/small-cap/2005/07/11/comp...

http://www.fool.com/investing/small-cap/2005/07/12/keep...

http://www.fool.com/investing/small-cap/2005/08/01/comp...

Thank you very much
Sergio

To implement some kind of XIRR report has been on the todo list for ages.

I was looking at my own portfolio wondering the same things. So, as a small side project, I've been working on creating a script extension in VBScript that exports all the transaction data to an OpenOffice Calc spreadsheet and does the calculation there.

I decided to use openoffice because not everyone has Excel installed and OpenOffice is free.

Eventually we will add reports that do these calculations as a native feature of the software but I'm not sure when. It's alot of work. (There's always the option of funding the work.)

But the spreadsheet extension I'm working on should be a good stop-gap solution. I

If you kindly read the tree articles I reference in my previous post, you will see that, in fact, the calculations are, indeed very very, easy and basic.
 I can explain it to you or send a spreadsheet full of examples if you have any doubts about it.

The only difficult thing is to know what your balance is in a daily basis, and also what your balance is when you cash in and what your balance is when you cash out. The PSM make the balance calculation every 15 minutes from the Yahoo server (for example), but did not store it in any place at the end of the day.
The only need is that you can store it in a cell at the end of the day. Or, at least get the yahoo story data for the stocks for the end of the day and create a balance.

If you can create a spreadsheet like that described below, the rest of the calculation is a piece of cake:

1st JAN Balance = 100.000 Cash in = 0 Cash out =0
2nd JAN Balance = 101.000 Cash in =0 Cash out =0
3rd JAN Balance = 99.000 Cash in =0 Cash out =0
4th JAN Balance = 101.000 Cash in =100 Cash out =0

Where cash in money put in the account or taken from account, not dividends.
If you can do that I can help you with the rest.

These files could be comma-delimited-files that could be used in any spreadsheet. But I'm quite sure that after learning the simple mathematics process you will not loose the chance to build it inside the PSM.

Best Regards
Sergio

Agreed. The algorithm is not difficult. I merely said it represents a lot of work.
The difficulty stems from the fact that the transaction register and gain/loss calculations are based on the current value of securities, not on a historic value. In order to do this correctly, we will have to rework the transaction calculations to be able to optionally use the historical data to calculate the value of the portfolio on a given date in the past. Once that's done the rest is a piece of cake.

BTW, do you have a copy of the spreadsheets mentioned in the article? It looks like the linked domain is no long valid.

The gain and loss are not measured on the current/past value of the stocks, but in the current / pat value of the NAV that is calculated based on the value of the portfolio at the end of the day.

You can start the calculations of the IRR on 2010 and forget everything before that! These will surely save a lot of work! At least, you will have your annual gain from 2010 on :).

I have a copy of the file
The gain and loss are not measured on the current/past value of the stocks, but in the current / pat value of the NAV that is calculated based on the value of the portfolio at the end of the day.

You can start the calculations of the IRR on 2010 and forget everything before that! These will surely save a lot of work! At least, you will have your annual gain from 2010 on :).

I have a copy of the file, how can I upload? I also have a file where I used to calculate my gains, but I have to introduce the share’s price manually.
Sergio

But to calculate the NAV you need to know your current value hence you have to run the portfolio value calculations. Like I said, we need to be able to run them over historical data, which we can't do right now. But this is planned for a future version.

Could you email me the spreadsheet to yml@dtlink.com?

Here's an article that discusses XIRR pretty well, and IMHO, provides a more standard measure of portfolio performance:

http://mindyourdecisions.com/blog/2008/11/13/calculatin...

Well, IMHO, this is getting more complicated than it needed to be.

I really don’t like XIRR. I don’t like it because it uses the *TIME* instead of the cash flow to ponder the profits. And what an investor should be interested in should be the cash in cash returns (or the simple IRR). (Of course, you would like to estimate your annual gain, after, say, a 5 years period, something like 20% by year - to compare to an index, but I mean past periods, not future….)

The XIRR will *estimate* the return over one year, even if you only get one month return.
 That will looks great for making everything you want with numbers. (Or pretend you are a great investor counting on your annual return in a bull month and than comparing it to the index. – In fact, I have seen more people that don’t know how to measure their performance saying too much and proving too little….)

In my investing word I would prefer something like that:

2007 - 20%
2008 -25%
2009 -13%
2010 - Jan – 3%

Since beginning - 75%

Medium 19% per year

Let’s get the example in spreadsheet in the article you mention:

Date Return (Payment)

01/01/2006 $ (100.000,00)

21/09/2006 $ 120.000,00

XIRR = 28,8%

As you can see, the return the XIRR give to you is 28,8%, as an estimate of the annual return. But, in fact what you deserve to know is that your actual return that is: 20%. That is what is your real return is at that point! That is also your base for collecting taxes. What WILL BE your return at the end of the year no one knows.

When you finish the year take your time to make the proper comparisons

But, any way, after you organize the data, the dividends, the deposits and the withdrawn of the money the mathematics of the TIR or the XTIR is just a matter of a formula.

As far as I know, the article I mention in my previous post is the way people in the fund business use to calculate their NAV.


Best regards

Understood. We do intend to develop that feature. Both of us over here understand it's important and that the software needs to accommodate it.

Unfortunately, as I mentioned before, there's currently no way for the software to answer the question:
What was my portfolio worth at the end of January 2009?
This is because the entire transaction register was built to use only current prices.

Anatoly has to rework how the transaction register works so that we can ask the software 'give me the value of the portfolio on such and such a date'. Until that's done, we can't automatically generate the NAV numbers you're looking for.

At the moment, we're working to add support for the big options symbol change which the industry is moving toward in February. That's currently our top priority. I do hope that we'll be able to add these calculation features into an upcoming version of the software, but right now I don't know when that'll be because of the amount of work involved.

In the mean time, I am implementing a spreadsheet based IRR which may be of use to some investors and can act as a model for other OpenOffice and Excel based integration projects.

You know, it dawns on me that once we have the transaction register updated so it can do historical calculations (what was my portfolio worth yesterday, last week, last month, etc) all kinds of possibilities open up to do interesting calculations.

BTW, I do appreciate all the time and feedback. Please keep it coming. Being a small company with limited resources, it's sometimes difficult for us to carve the time out to do larger projects, but this one is definitely on the todo list.

Thank you for your work on this in-the-meantime spreadsheet Yermo (smile)!

AND, thank you both for looking into storing and enabling the display of historical data, as I suggested via e-mail a few months ago. I am growing quite discontent with Quicken's 'Investment Tracking' functions and am using Personal Stock Monitor much more. But, Quicken has the historical data feature which is very useful for gaining historical data when necessary.

Thank you!

PSM already stores historical data for any ticker that has had a chart displayed or a performance report run on it.

We just can't run unrealized gain/loss calculations against historical data.

I'm not familiar with Quickens historical features? What kind of things can you do with it?

Just an FYI for those interesting, I came across this forum post over at the Fool today:

http://boards.fool.com/Message.asp?mid=13376296

As you can see, there's a lot of demand for the software calculation

I think I have had a question in my mind that might be related to the above great discussion:

Every year, I would like to take a snapshot in time of all my various portfolios I am tracking in PSM -- "frozen" in time as of Dec 31. Then, I would like to be able to open this file at any future point in time and re-visit all these values -- individual securities as well as total portfolio values, etc. -- the whole thing. All that WITHOUT PSM updating the security prices. Is that already easily doable in the tool today? Or is this also the same problem with not storing the historical transaction register?

And related: At times, I want to know what my portfolio was worth on date x and what the book value was on that date ... with only the transaction that had occured up until that date. Almost like filtering the transaction register for transactions until that date, then applying the market prices of that date to each security, then showing the resulting "holdings" tab. Then I wouldn't have to diligently save a backup copy of the portfolio on key dates (like at the end of each quarter), but instead could just use the current porfolio and "filter" for these key dates. ... Same problem?

Thanks.