Sort the data by Account and Date (ascending)

Add a new column for Year to easily group the data by year. In Column D (titled "Year")

Formula in Coulmn D =YEAR(A2) "Drag this formula down to fill all rows"

use Pivot Tables and some helper columns to simplify the calculation of annual performance.

Create a Pivot Table: Select your data range (columns A through D). Go to the Insert tab and select PivotTable. Create a new Pivot Table in a new worksheet.

Rows=Add Account and Year to the Rows area. Values= Add Value twice First as Minimum Value for the year (using the Min summary function). Second as Maximum Value for the year (using the Max summary function)

In a column next to the Pivot Table, calculate the annual return using the formula =(End_Value / Start_Value - 1) * 100

Refer the Max value as the End_Value and the Min value as the Start_Value for each account and year. YTD Calculation for the Current Year:

For the current year, use the latest available value (end of the current month) as the End_Value and the value from the beginning of the year as the Start_Value. Use the same return formula to calculate the YTD value.

Now create a summary table that consolidates annual returns for all accounts using LOOKUP or referencing formulas

new worksheet, set up a summary table

Columns = Accounts as headers Rows = Years (including a "YTD" row for the current year).

Use the GETPIVOTDATA function to pull the calculated annual return values from the Pivot Table into your summary table

Example of Calculating Return in Excel Suppose your Pivot Table has the following columns:

Account Year Start_Value (Min) End_Value (Max) Account 1 2017 1.000 1.820 Account 1 2018 1.820 2.327

In the next column, add: Return (%) for each year: =(D2/C2 - 1) * 100

combination of Pivot Tables and Calculated Columns, you can generate an annual return table efficiently for multiple accounts