To calculate the rate of return of your portfolio, in which you have regular (or not so regular) ongoing contributions throughout the time period, you will need a spreadsheet. You will need a row for all of the following:
- Starting date and balance
- Ending date and balance as a negative number (this will typically be your current balance, unless you're trying to do a calculation for a specific time period like the end of the year)
- Every contribution (positive number) or withdrawal (negative number) and date.
Do not include rows for the following additions or subtractions, as they will be reflected in the ending balance:
- Dividends, assuming you do reinvest them and don't withdraw them
- Deductions from fees, if any
So, suppose you're a relatively simple investor who has a 401(k) and an IRA and makes biweekly 401(k) contributions and an annual IRA contributions. You would then have in your spreadsheet the following rows:
- Starting balance for 401(k) account
- Starting balance for IRA account
- One row for the IRA contribution, and the date
- 24 rows for 24 401(k) contributions (combine employee contributions with employer match, if any)
- Ending balance for 401(k) account
- Ending balance for IRA account
That's 29 total rows.
You can then use the XIRR function across all these rows to calculate the rate of return of the portfolio. I have made this work in both Google sheets and Excel.
Here's a great explanation of this: Excel XIRR- How to Calculate Your Return | The White Coat Investor.