CODEWITHSUNDEEP
excel
JordanN
JordanN

Reputation: 45

excel formula to annualize quarterly returns

I have quarterly return data and I want an excel formula that will create calendar year returns from the quarterly data. So Column A has dates (3/31/2015, 6/30/2015, etc) and Column B has the returns (5.12, -1.26, etc). I'm looking for a formula that would find all quarterly returns in a given year and annualize those returns.

I'm currently using this formula: =((PRODUCT(O36:O39/100+1)-1)*100), but I would rather use a formula that can find the given year, so I don't have to adjust the ranges as new data is added.

Any help is appreciated.

Upvotes: 1

Views: 4325

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Try matching the year with a formula similar to this:

=(PRODUCT(IF((YEAR(A:A)=2016),(1+B:B/100),1))-1)*100

where you can replace 2016 by a link to a cell with the desired year.

This is an array formula, so you'll need to enter it using Ctrl+Shift+Enter.

The formula gives you 1 if the date doesn't match your current year, otherwise it uses the returns in column B.

As a side note, it's generally better to work with percents as their actual values (but number formatted as percentages) rather than having to multiply and divide by 100 whenever you want to work with them.

Upvotes: 1

Related Questions