4

My 401(k) shows the following:

  • my starting value at the beginning of the year
  • personal contributions
  • employer contributions
  • investment gains/losses
  • Closing value from Yesterday
  • Total Change
  • Personal Investment Performance

No matter which way I work the numbers, the percentage listed on my Personal Investment Performance does not make sense. Is there some formula that I am not aware of?

3

Your Personal Investment Performance or PIP can also be described as the Internal Rate of Return. I found an explanation of PIP on the Governments TSP page; the TSP is the government version of a 401K.

Personal Investment Performance (PIP) — The rate of return earned by your entire account during the 12-month period ending on the date indicated on your annual statement or on your Account Balance page of the TSP website. The PIP is a time-weighted return that has been calculated using a modified-Deitz method (a method used by many financial institutions and an industry standard). The PIP adjusts for the distorting effects of cash flows into or out of your account. It is an estimate; therefore, your PIP may not be the same as the 12-month performance of the TSP funds, which are time-weighted returns.

Let's pretend that you had a starting value of $10,000 and an ending value of $20,000. The simple calculation would imply that you doubled your money. But you and the government put in a total of $7,000. To calculate the return do you base it on the 10K or the 17K? The answer is that you need to know when the deposits were made. With deposits every 2 weeks, this become very complex. So don't expect to be able to calculate it without a lot of work.

Keep in mind it is just there to tell you how your investments did.

  • You were doing great until "don't expect to be able to calculate", excel's IRR (internal rate of return) makes these calculations simple if not trivial. – JoeTaxpayer Feb 4 '12 at 4:35
  • 1
    It still is a lot of work, you will need to enter in the 26 transactions, plus the quarterly dividends...It does take excel. I have found many people can't understand it. Even the en.wikipedia.org/wiki/Beardstown_Ladies got it wrong. – mhoran_psprep Feb 4 '12 at 4:51
  • Understood, but to the OP, there have been only 3 deposits, and adding one more every other week shouldn't be tough. – JoeTaxpayer Feb 4 '12 at 4:57
  • So then what is the formula, or where is an excel file that I could use? I graduated on the deans list of the #2 engineering school in the US - I think I can handle a little math. – tarheel Feb 4 '12 at 21:14
  • 1
    Here is one from the Microsoft website: office.microsoft.com/en-za/templates/… – mhoran_psprep Feb 5 '12 at 4:05
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.