Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

give me a template for excel to compare weekly profit of portfoilio

  1. User

    give me a template for excel to compare weekly profit of portfoilio

  2. 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

    1. Column A: Week
    2. Column B: Investment Name
    3. Column C: Initial Investment
    4. Column D: Opening Balance
    5. Column E: Additional Investment
    6. Column F: Withdrawal
    7. Column G: Profit/Loss
    8. Column H: Closing Balance
    9. 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:
    1. Column A: Week
    2. Column B: Total Opening Balance
    3. Column C: Total Additional Investments
    4. Column D: Total Withdrawals
    5. Column E: Total Profit/Loss
    6. Column F: Total Closing Balance
    7. 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 * 100
    

    Adjust the ranges in the formulas to fit your data set. This should create a template to compare the weekly profit of your portfolio.