# Question to OpenOffice users: how to calculate XIRR?



## mrPPincer (Nov 21, 2011)

Just downloaded OpenOffice and am new to this type of office suite software.
Anybody familiar with OpenOfice here, and that knows how to use it to calculate XIRR? 
If so a brief run-through would be greatly appreciated, thanks for any assistance


----------



## GoldStone (Mar 6, 2011)

1. Enter dates and values:

First line: opening portfolio balance (positive value)
Lines 2 to n: deposits (positive) and withdrawals (negative)
Last line: closing portfolio balance (negative)

2. Enter the formula:

=XIRR( Start_Value_Cell : End_Value_Cell ; Start_Date_Cell: End_Date_Cell ; Guess )


----------



## mrPPincer (Nov 21, 2011)

Awesome, thanks!


----------



## GoldStone (Mar 6, 2011)

More awesomeness for you! :encouragement:

*How to track multi-year returns:*










Note what's going on in rows 6 and 7, highlighted in yellow:

- Row 6 closes year 2011
- Row 7 opens year 2012
- Row 7 is a copy of Row 6, except it reverses the sign of portfolio balance.

C6 calculates 1yr return for year 2011: =XIRR( B2:B6 ; A2:A6 ; 0.2 )

C11 calculates 1yr return for year 2012: =XIRR( B7:B11 ; A7:A11 ; 0.1 )

D11 calculates two-year return: =XIRR( B2:B11 ; A2:A11 ; 0.1 )


----------



## slacker (Mar 8, 2010)

neat trick


----------



## mrPPincer (Nov 21, 2011)

Nice! this helps a lot. A picture is worth a thousand words 
I've just crunched my numbers this morning and my xirr is a fraction of a % lower than I'd thought it would be; just double-checking all my numbers now.
Thanks again Goldstone


----------



## Rusty O'Toole (Feb 1, 2012)

Can an Open Office beginner ask another question? I am using information downloaded as a spreadsheet, one of the columns contains the names of the items which are too long for the cells. The information is compressed into a standard column width with red triangles at the sides. Is there a way to expand the column and read the information in full?


----------



## Synergy (Mar 18, 2013)

Using Open Office I've calculated XIRR for each of my individual accounts (TFSA, RRSP, CDN Non-Reg, US Non-Reg) for 2013. Is there an easy way to calculate my overall return without having to manually combine all my accounts into one XIRR formula? I figured there must be a way to simply create a new formula within one cell without having to manually put in all the dates, totals, etc. again. 

I was either going to leave the US Non-Reg account out of the overall calculation (having a total return for both CDN and US) or convert the account to CND dollars and have one overall rate of return for my entire portfolio. Any thoughts?


----------



## Retired Peasant (Apr 22, 2013)

Rusty, Double-click on the line to the right of the column header. i.e. in the picture above, if you wanted to widen column A you'd double click on the line between the 'A' and 'B'. You can drag that line to the right manually; the double-click will widen the column to fit the longest item in the column.


----------



## GoldStone (Mar 6, 2011)

Synergy said:


> Using Open Office I've calculated XIRR for each of my individual accounts (TFSA, RRSP, CDN Non-Reg, US Non-Reg) for 2013. Is there an easy way to calculate my overall return without having to manually combine all my accounts into one XIRR formula? I figured there must be a way to simply create a new formula within one cell without having to manually put in all the dates, totals, etc. again.
> 
> I was either going to leave the US Non-Reg account out of the overall calculation (having a total return for both CDN and US) or convert the account to CND dollars and have one overall rate of return for my entire portfolio. Any thoughts?


You don't have to manually combine the accounts. Structure your spreadsheet as shown below:










Column E (Portfolio) is a sum of columns B, C and D. You can enter the SUM formula for the entire range. Enter the balances and transactions for each of your accounts, same as you do now. Column E automatically combines them for you.

You can easily extend this structure to handle US dollar accounts. Add another column to record the exchange rate on each date. Change the formula in the Portfolio column to:

= SUM( CAD accounts ) + SUM( USD accounts ) * exchange rate


----------



## Synergy (Mar 18, 2013)

^ That's awesome, thanks GoldStone!


----------



## debanjanray2003 (Dec 5, 2015)

*Wrong CAGR through XIRR*

There is a problem in XIRR formula if you do sale in between years. I shall explain with an example for buy and sell of stocks:

Date Buy/Sell #stocks Price/stock total cost years run Matured value
1-Jan-12 Buy 3 50 -150 3 -320.88
1-Jan-12 Buy 2 50 -100 3 -213.92
1-Jan-13 Buy 4 60 -240 2 -398.27
1-Jan-14 Sell 3 125 +375 1 483.07
1-Jan-15 Sell 6 75 +450 0 450

The "Matured value" column is calculated as "total cost"*(1+CAGR)^ (years run).
where, CAGR in % is to be changed on trial and error basis so that the total sum value of "Matured value" column is zero. 
In the above example with trial and error, CAGR comes as 28.82% ( You can do trial and error automatically in excel by invoking Data > What if Analysis > Goal seek function).
If you apply XIRR, on the column total cost and date, you shall get the same value i.e. 28.82%.

Now here is the problem: XIRR assumes that though you have sold 3 shares on 1-Jan-14, and got Rs. 375, but it continues to increase with the same CAGR for another 1 year and fetches you another 483.07-375=Rs.108.07, which actually you did not earn. Your sold value Rs.375 is no reinvested to fetch Rs.483.07.

A more accurate method would have been to replace the value $483.07 in the last column by Rs.375, in which CAGR is computed iteratively as 22.79% , which is far less than 28.82%.
Note: In the above example, I have shown 2 buy transactions on 1-Jan-12, just to show corresponding sales.

Conclusion: If you sale your portfolio and do not reinvest that one, XIRR shall artificially show you inflated return.

with best wishes,
Debanjan Ray


----------



## debanjanray2003 (Dec 5, 2015)

A correction of my previous post. Replace Rs. with $. The error is because I am Indian.


----------

