I had a couple of suggested enhancements that I've added to my copy, Excel formulas below. Wanted to offer these to validate the math is correct, and to "give back" to yourself and this wonderful community.
For context, your spreadsheet has turned into double duty to provide a historical view - not only of "returns" as you have it, but also to show portfolio progression, including to view "contributions" vs. "gains" - since all the data needed was already here anyway.
I added a small chart in the empty space under "Trailing portfolio returns". While the chart isn't really applicable to that heading - it seemed a good empty place to use... It just shows date and balance, which produces a chart - very similar [or at least mine is] to your "Growth of $10,000" below, but showing actual portfolio growth [which includes contributions]. The source used:
Code: Select all
=Calculations!$D$4:$E$4,Calculations!$D$6:$E$150
Code: Select all
Annual portfolio return (time-weighted return, comparable return)
Year Annual Return w/ Contributions In/Out Gains Year End
2011 1.6% 22.0% $29,267 $2,165 $174,496
2012 15.9% 36.6% $34,295 $29,535 $238,327
2013 25.0% 47.0% $48,074 $64,036 $350,437
Code: Select all
2020 20.6% 30.4%
- w/ Contributions: Shows the "total" change in portfolio (including the "annual return" from the market as well as changes from contributions/withdrawals)
F55 refers to "Year End" below, for the first year it will attempt to look up the start of year balance.
Code: Select all
=IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(NOT(ISNUMBER($F54)),($F55-OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0))/OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0),($F55-$F54)/$F54)))
- In/Out: Basically all "contributions" - "withdrawals", which seems like it works out to a close approximation [best I have] of "new contributions" (minus fees/withdrawals). Presumably this would work for decumulation showing "withdrawals" (before any gains) as well - but I've not reached that point yet...
Note: the above I'd be particularly interested to validate the formula/logic... It seems "good enough" for me, but I realize its not exact as "fees" would be removed which understate "contributions". But using contributions alone overstated things as if I did transfers between accounts, or included "cash" accounts [which I do], then it wasn't even remotely accurate.
Code: Select all
=IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",SUM(OFFSET(Calculations!G$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0):OFFSET(Calculations!G$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0))))
- Gains: Year End [below @ F55] - Year Start [prior year end] - In/Out [above @ D55] = the amount the portfolio "grew" by in the year
Code: Select all
=IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",F55-OFFSET(Calculations!F$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18)-11,0)-D55))
- Year End: End of year portfolio balance, I adjusted mine to show partial year values - reflected in the formula
Code: Select all
=IF($A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(TODAY()<A55,OFFSET(Calculations!E$17,(ROW($A55)-55)*12-(12-MONTH(TODAY())+1)+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0),OFFSET(Calculations!E$17,(ROW($A55)-55)*12+IF(Portfolio!$B$18=1,0,13-Portfolio!$B$18),0))))
- B56 = Year (2nd year - note, first year has a different formula - I didn't change it, but one could surely make it match if the wanted)
Code: Select all
=IF(A55="","", IF(DATE(YEAR(A55)+1,12,31)>DATE(YEAR(TODAY()),12,31),"",DATE(YEAR(A55)+1,12,31)))
- A55 = Annual Return % - for "full" years, it should use your original formula, which I attempted to adapt for a "partial year" outlook.
Code: Select all
=IF(A55="","",IF(AND(YEAR(NOW())=YEAR($A55),MONTH(NOW())=1),"",IF(TODAY()<A55,OFFSET(Calculations!N$17,(ROW(A55)-55)*12-(12-MONTH(TODAY())+1)+IF(Portfolio!B$18=1,0,13-Portfolio!B$18),0),OFFSET(Calculations!O$17,(ROW(A55)-55)*12+IF(Portfolio!B$18=1,0,13-Portfolio!B$18),0))))
- I also added a "Conditional Formatting" [Excel] rule to format the "current year" - to call it out and remind me its a "partial value" with a yellow background for format
Code: Select all
=$A55=DATE(YEAR(TODAY()),12,31)
- I also added a Year heading @ A54 and Annual Return heading at B54 to go with the headings I added above
Code: Select all
Data
NOTE: Additional entries will automatically be added on 1/31/2021
Date Balance
Month Contribution Withdrawal 1/31/2010 $0.00 Change IRR
February 2010 $0.00 $0.00 2/28/2010 $0.00 $0.00 0.0%
...
October 2020 $6,200.00 $980.65 10/31/2020 $150,069.60 -$3,418.50 -2.3%
November 2020 $0.00 $0.00 11/30/2020 $167,607.80 $17,538.20 11.7%
December 2020 $0.00 $702.84 12/31/2020 $173,730.90 $6,825.94 4.1%
- Change: The "change in value" from last month
Code: Select all
=IF(E17<>"",E17-E16+C17-B17,"")
- IRR: The "change" reflected as a percent (not sure if IRR is the "correct" definition - but think I got that from one of my statements)
Code: Select all
=IF(E17<>"",IF(E16=0,0,G17/E16),"")
- Purely cosmetic, but I also added a "conditional formatting" rule to each of the above making text red when "Cell Value < 0".
- Again cosmetic, but I also add a "data bar" (conditional formatting) showing green when positive and red when negative. This gives a quick visual comparison to the relative size of % changes [within each account sheet].
Thanks again for providing this great tool!