Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Report of invested capital; XIRR over time; Risk weighted return calculations
Hi Vidas,
Wanted to share a few suggestions with you for PS.
Suggestion 1: Invested capital v/s profit
One of the things that I have been tracking is the net invested capital at any point in time and the portfolio value at that time. To my knowledge this report is not available in the current version of PS. The invested capital is different from the Cost Basis or Start Value as they would, very likely, include the profits made from sale of other stocks. The way to get this would be to sum up the srcTrans:CashImpact column until the date required.
Suggestion 2: XIRR report over time
Similar to the above suggestion, if we are able to pull out the XIRR at the end of each month to show how the portfolio is performing, that would be great.
Suggestion 3: Risk weighted portfolio return Can we look at implementing a stock Beta and portfolio Beta report and the a risk weighted portfolio return calculation (Sharpe's ratio & Treynor's measure)? This would need to have another column to add the Beta for each stock. Portfolio Beta is computed as a value weighted average of the individual stock Betas. The trend of portfolio beta over time shows how riskier is the portfolio getting to be.
I would be happy to work with you on this as it is would be a learning experience for me too.
Thanks...
D
Wanted to share a few suggestions with you for PS.
Suggestion 1: Invested capital v/s profit
One of the things that I have been tracking is the net invested capital at any point in time and the portfolio value at that time. To my knowledge this report is not available in the current version of PS. The invested capital is different from the Cost Basis or Start Value as they would, very likely, include the profits made from sale of other stocks. The way to get this would be to sum up the srcTrans:CashImpact column until the date required.
Suggestion 2: XIRR report over time
Similar to the above suggestion, if we are able to pull out the XIRR at the end of each month to show how the portfolio is performing, that would be great.
Suggestion 3: Risk weighted portfolio return Can we look at implementing a stock Beta and portfolio Beta report and the a risk weighted portfolio return calculation (Sharpe's ratio & Treynor's measure)? This would need to have another column to add the Beta for each stock. Portfolio Beta is computed as a value weighted average of the individual stock Betas. The trend of portfolio beta over time shows how riskier is the portfolio getting to be.
I would be happy to work with you on this as it is would be a learning experience for me too.
Thanks...
D
Comments
Thank you very much for suggestions!
Suggestion 1: Invested capital vs profit. I believe you what you are looking for - is "Deposits". If you would sum all deposits, you would now how much money you invested. This information is already available, just not included in any reports. So you could just simply add new Pivot table, connect it to "Report currency" and add just one measure - Deposits. And you would see how much money you invested. If you want me to give you step-by-step directions how to do this, let me know version of your Excel, but it is very simple. Please let me know if that is what you are looking for.
I added "Total Deposits" report to "Deposits" worksheet, so it is going to be available in the next release.
Suggestion2: XIRR report over time.
Again, I believe this functionality is already available, just not included in monthly reports. For example in Mthly worksheet you can remove existing "Exch Impact" column (to have space) and add "Annlzd RoR" measure - that is exactly XIRR. You can have this XIRR calculated over any period. In Excel 2010/2013 this measure is not performs very well, but in Excel 2016, as Microsoft introduced this functionality", in Portfolio Slicer this measure should work very fast.
Suggestion3: Rick weighted portfolio return.
That is good suggestion, I will consider this. But this will not be in the next release - maybe in the release after that.
Again, thanks for suggestion! And please let me know if you were able to customize your Portfolio Slicer for your Suggestion 1 & 2.
On 1, since I am not tracking cash, the only way I can get to the cash flows is by totaling the buy/sell amounts; i.e. the Cash Impact column. Again, just the total deposits will not give the right picture because we may not have purchased securities for all the deposited cash. To get to the invested cash, we'd need to subtract the cash balance on a certain date from the net of deposits as on that date. The simpler way to do it would be to just sum up the transaction amounts (not incl deposits though) from the MinDate to the date that you need the data for.
On 2 - I'll try what you suggested. Could you kindly provide a bit more detail on what needs to be done? I run Excel 2016.
On 3 - Awesome! Please let me know if you'd like me to support you in any way. Happy to beta test it on my portfolios.
Thank you.
On 1: I believe it would be much more complicated to calculate investment amounts, than just sum Cash Impact. I also believe that I would have to include all money in the account, not just invested, because when money is left in cash, they are not earning much interest, but they are still part of investments.
On 2:
Step by step guide:
1. Go to Mhtly worksheet. select any cell inside first top left report.
2. Make sure on the right side you see "PivotTable Fields" list. If no, right mouse click on any cell in the same pivot table and choose "Show field list"
3. From the field list "Values" area (bottom right) click on the arrow next to "Exch impact" and choose "Remove Field"
4. From the field list (top right) find field "Annlzd RoR" and click checkbox next to it - this field will be added to your report.