Year To Date Return Formula - automation needed

**brains** · 07-24-2009, 10:28 AM

Hi, I am looking for some help before I got absolutely crazy!

I am looking for a formula that will let me calculate a YTD return from a list of monthly returns.This is simply a product calculation of the relative returns. My problem is i need something that will recognise the year and when it starts so that when i drag down the formula i dont need to reselect the cells everytime the year changes! ~Any advice will be greatly appreciated!! See attached file.

**DonkeyOte** · 07-24-2009, 10:59 AM

Not sure I follow... are you looking to just Sum B, ie:

H3: =SUM($B3:INDEX($B:$B,ROWS(B$1:B3)-(MONTH($A3)-1)))

or

H3: =SUM(OFFSET($B3,,,-MONTH($A3),1))

the latter though more succinct is Volatile (Offset)

or are you looking to do something more complex ? If so can you provide desired output ?

**brains** · 07-24-2009, 11:14 AM

I have attached the file again and tried to highlight what i want to do. Basically i want a single formula that i can drag from e3:e31 returning the values shown in D3:d31. The cells in green show that each time the year changes. i need to change the formula manually. I am looking for a formula i can drag from top to bottom that captures the year change in the YTR return calculation....

**brains** · 07-24-2009, 11:18 AM

I have just realised that that has worked! thankyou so much for your help! I spent days looking at that! Thanks again

**brains** · 07-24-2009, 11:28 AM

That is a cool solution. So if that is the YTD formula, how then would i calculate a QTD figure?

**DonkeyOte** · 07-24-2009, 11:36 AM

Not sure which you're using ... I suspect perhaps OFFSET ?

Using your last sample file, for Qtr:

F2: =PRODUCT(OFFSET($C2,,,-(1+MOD(MONTH($A2)-1,3)),1))
copied down

**brains** · 07-24-2009, 11:40 AM

you are the man...thanks for that!

**cartman88** · 02-05-2015, 03:04 PM

Hi all,

Hoping to revive this old thread, it has been very helpful but I'm encountering an issue for accounts without a full first year of performance. Neither the index nor offset formulas work for YTD if I delete the first few months of the year. See attached for an example.. is there any way to get around this?

YTD Calc Help (1).xls