Formula to calculate rolling year-to-date return when entering weekly data and ongoing investments are being made

[Christo_19]

Hello,

I have a spreadsheet which rolls forward an investment account weekly: It starts at beginning balance, +/- cash contribution/withdrawal, +/- any capital gain/loss, = ending balance. This data is rolled forward each week.

I would like to create a formula which would calculate my year-to-date (YTD) return on capital as a %, taking into consideration the timing of new cash contributions.

For example, if I start week 0 with $10K and I earn $100 in week 1 it is easy to calculate my YTD return as 1%. However if I contribute an additional $5K in week 2, what is the best way to calculate my YTD return (%) taking into consideration the timing of the most recent contribution?

Thank you in advance for any insight.
Chris

 

[ITWare2008]

Hello Christo,

Unless I misunderstood your question if you divide your running total by the first contribution and subtract one, you will get your percentage variance.

Hope this help.


Vandalo


BTW - Merry Christmas