Quote:
Originally Posted by darklord700
Sorry to sidetrack a bit but I have problems figuring out my IRR. Between all the transfers to and from different investment accounts, changing jobs and so on, I lost track of everything.
The only thing I know is my salary histories and that I maxed out on the 18% between employer contribution and my own. How do you keep track of your IRR?
|
There's a little trick. You need to use XIRR function in Excel instead of an IRR function, because XIRR calculates the return AND takes the time value of money into account based on the timing you've made your contributions (investments) and withdrawals. Input your initial investment amount/value and date. Then, input each additional investment/contribution as positive numbers and withdrawals as negative numbers against the dates you've made the investments/withdrawals. I don't bother with actual dates and enter Dec-31 of each year with the total net amount of contribution for the year. So, now you have two columns - one with dates and another with contributions/withdrawals against each date. Important - don't forget to format your date column as "date".
Here's the trick: at the bottom of the contributions column - enter the current market value of your portfolio
with a negative sign against today's date. This is to assume that you've sold and cashed your portfolio today. Then use an XIRR function: select the contributions column as your data range and select the corresponding date column as your date range (use your guessed IRR as whatever your guess is or leave the guess field of the formula blank, it doesn't matter). This will calculate and produce your real IRR based on the time value of your investment.