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

#### Christo_19

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

Replies
1
Views
399
Replies
0
Views
2K
Replies
9
Views
463
Replies
0
Views
305
Replies
8
Views
2K

1,196,041
Messages
6,013,042
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back