Portfolio Returns calculation

[mscandizzo]

I have a table with the returns per month of a portfolio and the corresponding dates.

What I need to do is to calculate the quarterly and yearly returns for each month as per the attached table

I have only calculated every 3 months and once a year on the table for visual simplicity, however, i have to do it for every single month

The calculation for the quarterly and yearly returns are the following:

DIVIDE(return in t , return in t-3) - 1

DIVIDE(return in t, return in t-12) -1 

So far i have been able to get the return for t-3 and t-12 using

EOMONTH(Date,-3)

Any ideas how to be able to calculate the return in PowerPivot  in such way that i have a column for each metric?

[v-yulgu-msft]

Hi @mscandizzo,

It seems that this question is more related to PowerPivot, please post threads on Power Pivot Forum so that you can get more professional advice.

Regards,

Yuliana Gu