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...

Improve this question
1
  • Perhaps this is more of a programming question than one of personal finance and money?
    – Dilip Sarwate
    Commented May 25, 2012 at 13:36

2 Answers 2

Reset to default
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/

Improve this answer
5
  • Thanks for the answer. Unfortunately it doesn't address what I need. I want column D to contain the return in each row for the time period that that cell represents. E.g. D10 should contain the return as of 9/1/10 and D11 should contain the return as of 10/1/10.
    – bstpierre
    Commented Jan 26, 2012 at 12:35
  • Each D cell has it's own XIRR. The range is from the top of the column, to the current row. So each row refers to a larger range. You'll still want to apply my earlier comment to column C.
    – joe
    Commented Jan 26, 2012 at 17:01
  • 1
    +1 for TWIRR. There is a clear worked example exactly fitting the OP's requirement on page 11 here: How to Calculate your Portfolio's Rate of Return.
    – Chris Degnen
    Commented Jun 7, 2016 at 8:05
  • The link to spreadsheets is not longer valid. Is there an updated link?
    – tim.rohrer
    Commented Aug 19, 2022 at 11:11
  • 1
    @tim.rohrer The link has been fixed. Thanks for the heads up.
    – joe
    Commented Aug 20, 2022 at 15:12
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .