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

Geometric Return On Share Prices Over 20 Year Time Period

Rate this: (3.3/5 from 3 votes)

	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 KylieG on 06 Jan 2021

Replies - Displaying 1 to 3 of 3

Order Replies By: Most recent | Chronological | Highest Rated

	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 Excel Helper on 08 Jan 2021

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 KylieG on 08 Jan 2021

	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 Excel Helper on 06 Jan 2021

Displaying page 1 of 1