8

I should say that I'm not married to XIRR, I just want to calculate the return on a running basis in a column next to dates, deposits and balances. Here's a screenshot of a simplified spreadsheet in LibreOffice Calc (f/k/a OpenOffice.org Calc; I believe Google Apps is based on their code base):

spreadsheet screenshot

In F2 I've got the formula I'd use to calculate the return over the entire timespan. Note that I had to include the value at the end as a negative cashflow. This makes it so that (a) I can't easily keep adding rows to the bottom and (b) I can't get a return% value for each row in column D.

Is there a way to include that final cashflow in the formula? Some kind of ad hoc array that can include the value from column C?

I have another spreadsheet where I've duplicated columns A and B (including the final negative cashflow) for every year that I want a return value for. This approach is tedious and error prone.

In case it matters, there are only deposits, never withdrawals -- I'm using this for a retirement account, and I've got a ways to go before I take anything out...

Ganesh Sittampalam
  • 30,396
  • 8
  • 95
  • 119
bstpierre
  • 10,034
  • 4
  • 33
  • 65

2 Answers2

8

Set your xirr formula to a very tall column, leaving lots of empty rows for future additions.

In column C, instead of hardcoding the value, use a formula that tests if it's the current bottom entry, like this:

=IF(ISBLANK(A7),-C6, C6)

If the next row has no date entered (yet), then this is the latest value, and make it negative.

Now, to digress a bit, there are several ways to measure returns. I feel XIRR is good for individual positions, like holding a stock, maybe buying more via DRIP, etc.

For the whole portfolio it stinks. XIRR is greatly affected by timing of cash flows. Steady deposits and no withdrawals dramatically skew the return lower. And the opposite is true for steady withdrawals.

I prefer to use TWRR (aka TWIRR). Time Weighted Rate of Return. The word 'time' is confusing, because it's the opposite. TWRR is agnostic to timing of cashflows.

I have a sample Excel spreadsheet that you're welcome to steal from: http://oldport.moosiefinance.com/static/models/spreadsheets.html (it's the top entry in the list).

Some people prefer XIRR. TWRR allows an apples-to-apples comparison with indexes and funds. Imagine twin brothers. They both invest in the exact same ideas, but the amount of cash deployed into these ideas is different, solely because one brother gets his salary bonus annually, in January, and the other brother gets no bonus, but has a higher bi-weekly salary to compensate. With TWRR, their percent returns will be identical. With XIRR they will be very different. TWRR separates out investing acumen from the happenstance timing of when you get your money to deposit, and when you retire, when you choose to take withdrawals.

Something to think about, if you like. You might find this website interesting, too: http://www.dailyvest.com/

joe
  • 565
  • 4
  • 6
0

I could not figure out a good way to make XIRR work since it does not support arrays. However, I think the following should work for you: Insert a column at D and call it "ratio" (to be used to calculate your answer in column E). Use the following equation for D3: =1+(C3-B3-C2)/C2 Drag that down to fill in the column. Set E3 to: =(PRODUCT(D$3:D3)-1)*365/(A3-A$2) Drag that down to fill in the column. Column E is now your annual rate of return.

Michael
  • 1