gpt-4-0314· OpenAI
WildChat-4.8M (AI2)
give me a template for excel to compare weekly profit of portfoilio
- User
give me a template for excel to compare weekly profit of portfoilio
- Assistant
I'm unable to create an actual Excel file here, but I can provide you with a template structure and instructions on how to set it up on Excel. Here's a simple template to compare the weekly profit of your portfolio:
Step 1: Setup Columns
- Column A:
Week - Column B:
Investment Name - Column C:
Initial Investment - Column D:
Opening Balance - Column E:
Additional Investment - Column F:
Withdrawal - Column G:
Profit/Loss - Column H:
Closing Balance - Column I:
ROI (%)
Step 2: Fill out the rows
- For each week, input values for every investment you have in your portfolio.
- Group rows together based on which week they are in (each week gets its own "set" of investments).
Step 3: Fill out initial investment for each investment in column C
- Update the initial investment for each holding in your portfolio.
Step 4: Applying formulas
- In cell D3 (since we'll start the table from row 3):
=IF(A3=A2, H2, C3)-
This formula ensures we set our opening balance as last week's closing balance, or the initial investment if it's the first week.
-
Drag this formula down for all the investments.
-
In cell G3:
=H3-D3-
Drag this formula down for all the investments.
-
In cell H3:
=D3+E3-F3+G3-
Drag this formula down for all the investments.
-
In cell I3:
=(H3-C3)/C3 * 100- Drag this formula down for all the investments.
Step 5: Weeks summary
- Create a separate table in the same sheet or another sheet with the following columns:
- Column A:
Week - Column B:
Total Opening Balance - Column C:
Total Additional Investments - Column D:
Total Withdrawals - Column E:
Total Profit/Loss - Column F:
Total Closing Balance - Column G:
Overall ROI (%)
- Fill out the rows for each week based on the values in the main table.
- Use the SUMIF formula for columns B, C, D, E, and F.
- For example, in cell B2, write:
=SUMIF('Portfolio'!$A$3:$A$100, A2, 'Portfolio'!$D$3:$D$100)- In Column G (Overall ROI), the formula would be:
=(F2-B2)/B2 * 100Adjust the ranges in the formulas to fit your data set. This should create a template to compare the weekly profit of your portfolio.
- Column A: