Calculating Portfolio Rate of Return from Purchases/Sales

pcaster · 2015-04-27T17:45:45.000Z

I need help from our math, finance, and investing experts! In the database (MySQL) that I've built to run my LEGO business through I have the ability to track actual purchase cost and final net after fees for every individual item in my inventory. I'd like to create a report (Java and PHP are my languages of choice) that will tell me what my performance is in terms of both average rate of return as well as margin. The margin part is really easy, but I'm struggling with rate of return. It is pretty straightforward to calculate rate of return for each inventory item, but then what??? Somehow it seems like I need to do some weighting based upon purchase price, and also need to account for purchase/sale dates. Here's where I've gotten - please help me take it from here: I was trying to start simple and add more complexity to figure out a model. Say I have $100 that nets $150 after a year. Here are a few scenarios: One purchase: Purch DatePurch AmtSale DateSale NetDurationRate of Return1/1/20141001/1/2015150365 0.50 Two purchases, weighted for purchase amount: Purch DatePurch AmtSale DateSale NetDurationIndv Rate of ReturnCost Weighted Rate of Return1/1/2014901/1/20151303650.444440.41/1/2014101/1/2015203651.00.1 0.50 Here's where I start to get lost. What if that second purchase/sale actually occurs as two that are serial? I can't figure out how that could be accounted for: Purch DatePurch AmtSale DateSale NetDurationIndv Rate of ReturnCost Weighted Rate of Return1/1/2014901/1/20151303650.4440.34781/1/2014108/2/2014152131.0030.08728/2/2014151/1/2015201520.9950.1298 0.5649 I've tried coming at it from a few different angles with weighting based upon duration, but still can't get things to work out. I've also seen plenty of simple models that use portfolio values at start and end dates, but I'm really trying to measure my actual performance in terms of my decisions of when to buy and sell, so it doesn't make sense for me to include the values of items that are sitting in inventory. I also tried rolling up everything into total cost, total sales, and average duration and then running it through the CAGR formula but that is very inaccurate - maybe there are suggestions for weighting there??? I know we have some math experts here so hoping someone can help this novice. If something as simple as VIP rewards can generate as much discussion as it did I can only imagine where this might lead!

Bypcaster·11yinInvesting

pcaster

Posted 11y

3 203

3 replies

former-member

11y

pcaster

11y

former-member

11y

Reply to this topic…

← Go to Investing listing

Daily LEGO Deals

Site Information & Updates

BrickPicker World

Main site

Developer API

Documentation

Blog