Calculating Rate of Return

What rate of return are you earning? For a bank account (savings, money market, or CD), figuring this out is usually pretty straightforward as the bank tells you what the APY and nominal percentage rates are. But what about the annual rate of return on your investments? If you know how much you put in an investment account and when, you can calculate this return using a computer spreadsheet (i.e. Microsoft Excel).

MS Excel has a function called XIRR that calculates the Internal Rate of Return (IRR) of an investment. You must supply the date and amount of each deposit to or withdrawal from the account. Each of these deposits and withdrawals is called a cash flow. Each deposit is considered a negative cash flow, and each withdrawal is considered a positive cash flow.

I was thinking of writing a detailed post about how to calculate your return using the XIRR function in MS Excel. However, I found a very good post that already describes this method in some detail. The blog is Fat Pitch Financials, and the post is here. Look for the link to the file: "Annualized rate of return.xls".

The original blog writer, George, is an actually an economist, so his description is quite involved. By contrast, I'm just a rank amateur on these matters. Nevertheless, I did take six economics/business classes in college, so I will offer you my additional commentary.

First of all, if you are having trouble using the XIRR formula in your spreadsheet, you might need to install what is called the Analysis ToolPak. A symptom of this is if you see "#NAME?" where you use the XIRR formula on your spreadsheet. To add in the Analysis ToolPak (in my version of Excel, at least), go to Tools, Add-ins, and select the Analysis ToolPak. You might need the original Excel (or MS Office) installation disks for this one-time operation.

The other thing that I want to mention is that in addition to the initial value, deposits, and withdrawals, you need to know what the current (or final) value of the account is. Don't forget to include "accrued interest". This is interest that is accrued, but not yet paid to your account. Money market savings accounts usually pay you monthly or quarterly. In the interim between the last time you were paid interest and now, interest accrues, but is not reflected in the balance. You have to estimate the amount of accrued interest, or your results won't be accurate past the last date that interest was paid.

Lastly in the example XL spreadsheet, George includes a line for taxes. Your account value may not include taxes, and you might pay your taxes out of another account. However, in the example, taxes are deemed to be paid out of the investment account. Taxes are, of course, a negative cash flow.

Now you have a general tool that can help you figure your rate of return on investments. In addition to brokerage accounts, this same formula can be applied to CDs, money markets, Prosper.com loans, and just about any other investment that can be valued.

PF Stock