I have an access database with records of securities in my portfolio as well as my trades for each security.

My aim is to calculate the "Daily Time Weighted Return" then down the line, export it and with a second program, geometrically chain-link for a desired period.

I have tried to implement the following formula to calculate the TWR for each security: enter image description here

In VBA I replicated this in the following way:

Day_Return = (Day_PNL + Day_Dividends) / (Previous_Days_Price * Previous_Days_Holding_Qty)

What I've noticed is that should I trade a stock during the day, the performance calculation will be incorrect even though the actual P&L is fine.

Could someone help me understand where i'm going wrong, or how I can change my calculation/logic so that performance reflects not only the static performance from one day to the next but also the performance on buys/sells?

Any help will be greatly appreciated, including tips on reengineering this question or clarifying anything. Thank you.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.