Calculating Lifetime Returns Using XIRR

I feel that one big purpose of the many investing forums, blogs and financial articles is to bring basic financial information to the masses. Consequently there can be a lot of repetition of information. In this post, I will describe a useful financial tool that is known to seasoned investors. So, this post is for those who may not be familiar the topic.

In the investment world we need a simple way to measure the performance of a portfolio. People in finance often refer to it as the compound annual growth rate (CAGR). This is for example very important measure when a investor chooses his mutual fund. From my experience almost all investors look at the 1, 3, 5 and 10 year CAGR that are mandatory in all official fund reports.

The definition of a CAGR is easy to understand in the mutual fund literature. They describe the growth of a $10,000 investment in a fund from, say, 5 years ago to today and calculate the average annual return of that investment assuming all distributions are reinvested. This CAGR is the equivalent annual interest of a daily compounded savings bank account over the same 5 year period.

For example, as of their annual report ending June 30, 2016 the Bruce Fund reported a CAGR with dividends and distributions reinvested of 9.7%. This means that a $10,000 investment fives years ago would be worth $15,866 on June 30, 2016 because 1.097⁵ is 15866.

So far so good, but real-life investors have cashflows in and out of their investment portfolios. How does one find a CAGR of their portfolio to see how they are performing as their own portfolio manager? To do this we need a clear definition of the CAGR of a portfolio with arbitrary cashflow. I see my own CAGR as given above. It is the the equivalent annual interest of a daily compounded savings bank account over the period in question given that the hypothetical bank account receives the same cashflows as my real portfolio account.

I find this result extremely useful because it tells me how much return I will need if I save diligently and I have some retirement goal in mind. For example, suppose I am 60 years old and I want to retire at 65. Over the next 5 years I will contribute $20,000/year to my retirement fund. My retirement fund now has $200,000 and I want to have $400,000 at 65. What return do I need?

The answer is simple using the xirr() function available in all spreadsheet programs such as MS Excel. The xirr() describes the returns given cashflows. The following is the way to enter the data.

• Each cashflow entry should have a date and an amount in one row.
• Each cash flow entry into the retirement amount should be a negative amount.
• Each cash flow entry out of the account should be a positive. 
• The final entry should be a withdrawal of the remaining balance on the account.

So in the above example, assuming that I begin on 4/15/2017 at the age of 60 my initial account balance is $200,000. And on each anniversary I add $20,000. Then on the 5th anniversary, my account has a $400,000 balance. The cashflow entries are entered in order each on a row. As shown below. The xirr() function takes two parameters. One is the region containing the dates of the cashflows, and the other is the amount of the cash flows. I entered the following in my example: =xirr(a1:a7,b1:b7).

And the xirr result is 8.55% in the example shown in yellow.

	A	B
1	4/15/2017	-200000
2	4/15/2018	-20000
3	4/15/2019	-20000
4	4/15/2020	-20000
5	4/15/2021	-20000
6	4/15/2022	400000
7
8	XIRR:	8.55%

The xirr() function can also tell me how much money I will have at retirement given that I can achieve some return. In the above example, I can calculate how much I can have if I can improve my returns to 10%. To do so, I would simply replace different values for the final withdrawal until the xirr value is 10%. The current final withdrawal value in this case is 400,000.

Note that the values of deposits and withdrawals can be any amount and at any time.

Knowing the CAGR for all cashflows is an extremely tool that can answer what a return really translates to in terms of wealth. And it can also be extended to compare the value of future cash streams such as annuities or defined benefit plan such as social security. In doing so, this tool gives the average consumer an objective way to compare different types of retirement products. It can make more clear many financial products that, I feel, are designed to obfuscate the and confuse the consumer through complexity.

I hope you will find this as useful as I do.