ReformedCounsel
New Member
- Joined
- Aug 6, 2013
- Messages
- 3
Hi all, stuck on a problem and haven't been able to find an answer elsewhere.
I'm using excel 2007 on Windows 7.
I'm trying to use XIRR on a non-continguous list of dates and values to get returns on a stock portfolio. A simplified version of my sheet is as follows. In A1:A20, I have dates for trades and dividends over financial years 2011-13. In B1:B20, I have corresponding values for trades and dividends over the same time period. Rows 1-5 are FY11, 6-13 are FY12 and 14-20 are in FY13. I also have the end of the financial year dates for those years in C1:C3, the corresponding value of the holding in D1:D3 as at the end of each financial year and in E1:E3, a copy of D1:D3 multiplied by -1. I want to be able to keep the end of financial year data separate from the list of actual transactions.
I want to automatically calculate the returns of the stock from each of the financial years, as well as a total annualised return. So for example, to find the return during FY12, I have used the following formula:
=XIRR(CHOOSE({1,2,3},D1,B6:B13,E2),CHOOSE({1,2,3},C1,A6:A13,C2))
However, instead of returning values in the order of
D1,B6,B7,B8,B9,B10,B11,B12,B13,E2, it returns values in the order of D1,B6,E2,D1,B7,E2,D1,B8,E2... etc.
I can only seem to get the formula to work if the CHOOSE function is refercing arrays of the same length. Is there any way around this? What am I doing wrong?
I'm using excel 2007 on Windows 7.
I'm trying to use XIRR on a non-continguous list of dates and values to get returns on a stock portfolio. A simplified version of my sheet is as follows. In A1:A20, I have dates for trades and dividends over financial years 2011-13. In B1:B20, I have corresponding values for trades and dividends over the same time period. Rows 1-5 are FY11, 6-13 are FY12 and 14-20 are in FY13. I also have the end of the financial year dates for those years in C1:C3, the corresponding value of the holding in D1:D3 as at the end of each financial year and in E1:E3, a copy of D1:D3 multiplied by -1. I want to be able to keep the end of financial year data separate from the list of actual transactions.
I want to automatically calculate the returns of the stock from each of the financial years, as well as a total annualised return. So for example, to find the return during FY12, I have used the following formula:
=XIRR(CHOOSE({1,2,3},D1,B6:B13,E2),CHOOSE({1,2,3},C1,A6:A13,C2))
However, instead of returning values in the order of
D1,B6,B7,B8,B9,B10,B11,B12,B13,E2, it returns values in the order of D1,B6,E2,D1,B7,E2,D1,B8,E2... etc.
I can only seem to get the formula to work if the CHOOSE function is refercing arrays of the same length. Is there any way around this? What am I doing wrong?