Home > Forum Home > Managing Supply Chain and Inventory > Geometric Return on Share Prices over 20 Year Time Period Share

Geometric Return on Share Prices over 20 Year Time Period

Excel Help for Geometric Return On Share Prices Over 20 Year Time Period in Managing Supply Chain and Inventory


Forum TopicPost Reply Login

Geometric Return On Share Prices Over 20 Year Time Period

Rate this:
(3.3/5 from 3 votes)
Confused Hi I am trying to work out the geometric return on 20 years worth of share prices for IPO's.  However i can not work out which part of my formular is not working as I go from $100M to many gazillions $$$ at the end with an annualise geometric return of lots. 

So i have set this up with
Spreadsheet 1:with my share prices coming in and out as they list and delist over the time period.
Spreadsheet 2: calcuates the market cap on a daily basis of the active shares (so if a share is listed on a day then the market cap is included in that day total market cap).
Spreadsheet 3: works out the weighting/rebalancing of the portfolio as shares list and delist.  So Total amount invested x (Market cap of the share at IPO list date/Total market cap onthat day) / share price.
Spreadsheet 4: Share price x weighting on that day 

Geometric return is 252 (trading days) x log (end value of the investment/begining value of investment (i.e. $100M)/ 5040 days to get an annualised return

I can not see how a $100M increases to this large total value of investmetn at the end of 2019.  Can someone please check to see if this makes sence for them as well?  I am happy to send the spreadsheet but cant find how to attach it here?



 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most Recent | Chronological | Highest Rated
Confused
Create a daily return schedule
Rate this:
(4/5 from 3 votes)
I would create a daily return schedule table so you can see the progression of total investment return.
The first calculation should be the daily return then you can cumulate it in a separate column if ability to redeem and reinvest (delist/list), otherwise it would be a simple additive return from one day to the next.

 Excel Business Forums Administrator
 Posted by on
Confused
Attach to notification email reply
Rate this:
(4/5 from 3 votes)
You can send the file as a reply to the forum notification email and we can take a look.
 Excel Business Forums Administrator
 Posted by on
Confused
Geometric Return on Share Prices over 20 Year Time Period
Rate this:
(3/5 from 1 vote)
Yes thank you i did do a daily return schedule but the cumlative increase seems too much and so i was hoping someone might be able to check the logic or if it is a forumla error, but hoping someone might be able to? It increases so much it does not seem correct - if anyone is able to quickly check it for me???  would be much appreciated?
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Geometric Return on Share Prices over 20 Year Time Period in the

© Business Spreadsheets   Legal   Follow us: