You started the year with a pile of assets in your 401k plan valued at $31,200. You spent all year watching money flow out of your paycheck – $100 twice a month – into your 401k account. Your employer even contributed $45 a month in matching funds. Then you added $350 from your December bonus. At the end of the year, your account was up to $37,185. Great news – that's 19.2 percent more than you had in January! But what was your investment return? Unless you can figure your internal rate of return, you simply won't know. Using a simple formula with a calculator can give you an estimate, but to get a precise result, you'll need to use a computer spreadsheet.
List your beginning account balance – $31,200 for this example.
Add in your deposits – $3,290 to continue the example.
Subtract the total of your beginning balance plus deposits ( $31,200 + $3,290) from your year-end balance of $37,185.
Divide that result – $2,695 – by the opening balance.
Move the decimal point on the result – .0863782 – two places to the right for your portfolio's percentage return: 8.6378 percent. The shortcoming to this method is that it is based on the assumption that all transactions were made on the first day of the year. For a precise, annualized total return, you’ll need to spend a few minutes at your computer.
Open an Excel spreadsheet and create three columns: “Cash Flow” in Column A, “Amount” in Column B and “Transaction Date” in Column C. Format Column B for numbers expressed as dollar amounts to two decimal points, and format Column C for dates expressed as day/month/year.
List your beginning balance in Row 2. Cell A2 should read “Beginning Balance”; Cell B2 should show “$31,200,” continuing the example; and Cell C2 should show “01/01/xx” where “xx” is the last two digits of the calendar year.
List all your transactions, consisting of salary deferrals and company matches. Enter deposits as positive numbers and withdrawals, if any, as negative numbers. In this case, your $100 salary deferrals are made on the second and 16th days of the month. The company contributes its match on the 20th of each month.
Confirm that you have 24 salary deferral entries, 12 company match entries and one bonus deferral entry on December 10, filling Rows 3-39, if your contributions are the same as in the example. List your closing balance in Row 40. Cell A40 should be labeled “Closing Balance”; Cell B40 should read "-$37,185"; and Cell C40 should read "12/31/xx" where, again, “xx” is the calendar year. Your closing balance is entered as a negative number, as if you were closing the account and withdrawing it all.
Figure your portfolio return using an "xirr" formula on your spreadsheet; "IRR" stands for “internal rate of return.” After entering “Portfolio Return” in Cell A41, enter the following formula into Cell B41: =xirr(B2:B40,C2:C40) Inside the parenthesis, your entry B2:B40 encompasses all values and C2:C40 includes all dates. Format the number in Cell B41 as a percentage to four decimal points: for example, 8.2615 percent. The difference in investment return demonstrates the problem using the assumption that all investments were made on the first day of the year.
- You don't need to factor in transactions – dividends, interest and barter transactions, such as sales and purchases – conducted inside the portfolio. Such transactions become part of the current value. However, if you had an account in which dividends automatically were swept out of the portfolio and into your checking account, those would be listed as withdrawals or negative numbers.
- An annual rate of return measures a single-period performance; it is a multi-period, average 12-month return. The “xirr” function provides an annualized rate of return, but if you enter cash flow for 12 months, the result would be an annual rate for that specific period. If you enter 24 months, the result would be the annualized or average 12-month return over those two years.
- You also can figure portfolio returns using spreadsheet programs other than Excel or with a financial calculator. If you don't want to do the work yourself, some online investment sites and personal finance software such as Quicken provide annualized portfolio numbers.
Items you will need
- Monthly account statements, including beginning- and year-end values
- Computer with Microsoft Excel installed
- Stockbyte/Stockbyte/Getty Images