Hi All -
I am having an issue when calculating return figures for my portfolio...
My CFs are setup as follows:
Column A: Disposition Year
Column B: CF Dates
Column C: CF Values
I am using an array formula to sift through the data to calculate IRRs based on the year the asset was disposed of. My formula works for years with positive returns, but does not for Years 2010 & 2011 when negative returns occurred (yawn!). When I use a positive guess for '10 & '11, the formula produces a 0.0%. When I use a negative guess for said years, the formula produces an error - #NUM. My array formulas are below.
Array Formula (Positive Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,0.1)}
Array Formula (Negative Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,-0.1)}
F=17 is the Disposition Year...
Thank you for the help!
_Dave
Portfolio Calcs
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 64px"><col style="WIDTH: 22px"><col style="WIDTH: 64px"><col style="WIDTH: 134px"><col style="WIDTH: 64px"><col style="WIDTH: 75px"><col style="WIDTH: 81px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Disp. Yr.[/TD]
[TD="align: center"]CF Date[/TD]
[TD="align: center"]CF[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-06[/TD]
[TD="align: right"](5,129,940)[/TD]
[TD="bgcolor: #0066cc"]Cumulative Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]10.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.0% [/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Apr-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.0% [/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]May-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jun-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jul-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #0066cc"]Dispo Year Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Aug-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]10.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Sep-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"](7.7271%)[/TD]
[TD="align: right"](18,006,526)[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Oct-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"](10.2379%)[/TD]
[TD="align: right"](34,175,705)[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Nov-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]13.3% [/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Dec-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-07[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-07[/TD]
[TD="align: right"]31,078 [/TD]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-07[/TD]
[TD="align: right"]31,078 [/TD]
</tbody>
<tbody>
</tbody>
I am having an issue when calculating return figures for my portfolio...
My CFs are setup as follows:
Column A: Disposition Year
Column B: CF Dates
Column C: CF Values
I am using an array formula to sift through the data to calculate IRRs based on the year the asset was disposed of. My formula works for years with positive returns, but does not for Years 2010 & 2011 when negative returns occurred (yawn!). When I use a positive guess for '10 & '11, the formula produces a 0.0%. When I use a negative guess for said years, the formula produces an error - #NUM. My array formulas are below.
Array Formula (Positive Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,0.1)}
Array Formula (Negative Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,-0.1)}
F=17 is the Disposition Year...
Thank you for the help!
_Dave
Portfolio Calcs
A | B | C | D | E | F | G | H | I | J | |
C | 2009 | |||||||||
C | 2010 | |||||||||
C | 2011 | |||||||||
C | 2012 | |||||||||
D | 2009 | |||||||||
D | 2010 | #NUM! | <== | |||||||
D | 2011 | #NUM! | <== | |||||||
D | 2012 | |||||||||
<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 64px"><col style="WIDTH: 22px"><col style="WIDTH: 64px"><col style="WIDTH: 134px"><col style="WIDTH: 64px"><col style="WIDTH: 75px"><col style="WIDTH: 81px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Disp. Yr.[/TD]
[TD="align: center"]CF Date[/TD]
[TD="align: center"]CF[/TD]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-06[/TD]
[TD="align: right"](5,129,940)[/TD]
[TD="bgcolor: #0066cc"]Cumulative Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]10.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.0% [/TD]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Apr-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.0% [/TD]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]May-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]0.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jun-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jul-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #0066cc"]Dispo Year Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Aug-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]10.2% [/TD]
[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Sep-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"](7.7271%)[/TD]
[TD="align: right"](18,006,526)[/TD]
[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Oct-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"](10.2379%)[/TD]
[TD="align: right"](34,175,705)[/TD]
[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Nov-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="align: right"]13.3% [/TD]
[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Dec-06[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-07[/TD]
[TD="align: right"](114,485)[/TD]
[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-07[/TD]
[TD="align: right"]31,078 [/TD]
[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-07[/TD]
[TD="align: right"]31,078 [/TD]
</tbody>
Spreadsheet Formulas | ||||||||||||||||||||||||||||||||||||||
<tbody> </tbody> | ||||||||||||||||||||||||||||||||||||||
Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! |
<tbody>
</tbody>
Last edited: