Calculate Year to Date Return

[chuckforgo]

I'm trying to work out how to calculate the YTD return from a list of periodic returns that spans over a year. Since I'll be adding to the list as the year progresses it would be helpful to have it re-calculate without having to edit the formula each time around.

To get a Cumulative Return (vs a sum of the returns) my formula is: {=PRODUCT(1+D6:D27)-1}

I'm trying to figure out how to get it to recognize which dates are from this year and to calculate the cumulative return using only those periods.

[63falcondude]

Based on the sample that you shared, what would the expected result be?

[chuckforgo]

26.60% which is the cumulative return of 1/10/18-7/25/18.

[63falcondude]

Try this:

=PRODUCT(IF(YEAR(A6:A100)=2018,1+D6:D100))-1 Ctrl Shift Enter

[chuckforgo]

That's exactly what I couldn't figure out, thanks a ton FalconDude. All I changed was making 2018 to A1 so in 2019 I just have to edit the Year.

[63falcondude]

You're welcome. Happy to help.

You can also change A1 to YEAR(TODAY()) to do that for you.