Home > Forum Home > Excel Portfolio Performance Tracking Template > Portfolio Performance and Graph problems Share

Portfolio Performance and Graph problems

Excel Help for Portfolio Performance And Graph Problems in Excel Portfolio Performance Tracking Template


Forum TopicPost Reply Login

Portfolio Performance And Graph Problems

Rate this:
(3/5 from 1 vote)
ConfusedExcel Spreadsheet:
  Value at   Capital Income    Annual
  Start of  Current  Invested Distributed  Net Return Return Since
Product Name Period Valuation Over Period Over Period Over Period Inception
             
500 Bank Insured Deposit - IRA $6,789.56 $9,969.59 $3,180.03 $2.25 36.30% 189.27%


 Posted by on
 
Replies - Displaying 1 to 8 of 8Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Rate this:
(3/5 from 1 vote)
Okay, I don't see my original post, so I guess I'll have to post it again.
Performance tab:  Net return seems to be counting contributions as investment return.  I have put contributions in transactions as investments.  See spreadsheet.
Graph tab: "total value" cell displays as ######.  Apparently the total value is too big to display in the cell.
Graph tab: there are two predefined categories which have a value of zero.  They are still showing up on the graph, and they are crammed together (which looks very messy).  How can I fix this?

 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
The return calculations take into account of investments and divestment over the reporting period by adjusting the start value  for amount and timing accordingly under the 'money-weighted' return.

Contributions should be added as positive investments and increase the start value thereby reducing the net return over the period.

The graph can be unprotected using the password released on purchase in order to modify cell sizes to accommodate large numbers, remove categories from the chart data source and other customizations such as styles.

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
In the original post you can see in the spreadsheet that start value + capital invested = current valuation.  Of the capital invested, only 2.25 is return (reinvested mmkt dividends).  The rest is new principal.  Therefore, the net return over period should be microscopic, not 36%.
 Posted by on
Surprised
Rate this:
(3/5 from 1 vote)
The difference is due to the timing of the investment transactions. We do not know the actual start and end dates of the reporting period; however it seems like the bulk on investment was made near the end. The start value is adjusted by the weighted average of investment amount and time between the start date and investment dates. In this case, under the money weighted return, the principle value was lower for the majority of the reporting period (until the capital injection) resulting in a higher return calculation for the entire period.

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
This is a money market yielding 0.15%.  Bulk of the money was invested up front ($6789 out of $9969 total).  $2318 came in right at the end of the reporting period (1/20/2010 thru 3/31/10), but that represents less than a third of the total.  Regardless, the actual interest earned on the mmkt was only $2.25.
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
The calculation is based on the money weighted return which is commonly used to account for fluctuations of the principle over the reporting period.

To understand the theory behind the formula you can refer to the user guides here. You can also unprotect the template to view the components of the formula or modify it to create a straight return and ignoring the timing. If you are still using the trial version and do not yet have the unprotection password, you can reply to the notification email of this post and we will send it through to you.

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Any advice on how to еxplain in layman's terms that a money market yielding 15 bps "returned" 36% over a 2 1/2 month period?
 Posted by on
Happy
Rate this:
(3/5 from 1 vote)
In layman's terms: the money-weighted rate of return incorporates the size and timing of cash flows, so as to effectively measure for returns on a portfolio.

 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Portfolio Performance and Graph problems in the