{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["rev_control","0 - usage","INPUT_to_VBA","OUTPUT_from_VBA","DEBUG_balances","DEBUG_cashflows"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":12,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"rev_control","index":0,"n_rows":7,"n_cols":4,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Date, Time","Rev","Who","What"]
["2010-02-21",0,"E Stewart","Origination during exploration of Quicken calculation"]
["28DEC2017, 2108 EDT",1,"E Stewart","Changed from *.xls to *.xlsx format\nLet excel replace names with cell references\nManaged to replicate some essence of Quicken calculation manually though question \"what IRR would need to be applied to the starting balance to match the cash flows within the period, irrespective of date.\""]
["28DEC2017, 2151 EDT",2,"E Stewart","wrote sub \"IRR\" to calculate IRR for each of the 4 periods in Quicken\nadded calculation of XIRR for year to date and 1 year periods\ncaution: formula corrupt error showed up here, but I can't find the corruption to eliminate it"]
["29DEC2017, 0008 EDT",3,"E Stewart","numbered tabs\n1 - single coupon, corrected report date in transaction list\nadded tabs for 2 coupons in 2 variants, unearthing strange sensitivity to dividend reinvesting\nstarted into direct VBA calculation of IRR based on Quicken investment transaction output ... but am missing cash value of dividends in output"]
["29DEC2017, 1327 EDT",4,"E Stewart","wrote main VBA to match what I figured out manually in the spreadsheet"]
["15JAN2018, 1858 EDT",5,"E Stewart","significant VBA updating to match Quicken approaches (match now within 0.05% for 2 Quicken approaches)\nremoved older debugging and learning tabs, leaving only the VBA driven tabs and VBA debugging tabs\n0 - usage, modified to capture updated state of the spreadsheet\nrenamed to have prefix EMS1001 (part of the EMS1000 series of documents)"]

{"sheet":"0 - usage","index":1,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Category","#","Note"]
["General",1,"This spreadsheet is structured to mimic Quicken IRR (Annual Average Return) calculations on an account basis with an additional IRR calculation approach."]
[null,2,"Validation is done for the dummy cash flow within, for one security in a brokerage account within a Quicken account."]
["Inputs",1,"Investment account transactions."]
["Steps",1,"Export the transactions for an account from Quicken using reports --> investment --> investment transactions, typically for one account."]
[null,2,"Merge multi-line transactions to one line manually, leaving these transactions on the \"INPUT_to_VBA\" tab."]
[null,3,"On the \"OUTPUT_from_VBA\" tab, click the large button."]
[null,4,"Review output on this tab as well"]
[null,5,"In case of need to debug, review the \"DEBUG_balances\" and \"DEBUG_cashflows\" tabs, noting cashflows only records the caseflows for the last IRR calculation reported"]
["Output",1,"One, three, five and ten year IRR calculation by three approaches: starting balance with cash flow, starting and ending balances, market value with cash flow"]
["Caveats",1,"Pricing for securities in Quicken must be correct for the match with Quicken"]
[null,2,"Specific date intervals may give slightly different results (within 0.05%) from Quicken for the Quicken approaches."]

{"sheet":"INPUT_to_VBA","index":2,"n_rows":9,"n_cols":13,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Date","Account","Action","Security","Symbol","Category","Memo","Quote/Price","Shares","Commission","Cash","Amount Invested","Cash+Invest"]
["2006-01-01","AAR_Testing","Cash","-Cash-",null,null,null,null,null,null,100,null,100]
["2012-01-01","AAR_Testing","Bought","dummy","dummy",null,null,100,1,null,-100,100]
["2012-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]
["2013-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]
["2014-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]
["2015-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]
["2016-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]
["2017-06-06","AAR_Testing","Div","-Cash-",null,"_DivInc",null,null,null,null,20,null,20]

{"sheet":"OUTPUT_from_VBA","index":3,"n_rows":10,"n_cols":5,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[null,"1 Year","3 Year","5 Year","10 Year"]
["Report Start Date","2017-01-15","2015-01-14","2013-01-16","2008-01-17"]
["Report End Date","2018-01-16","2018-01-16","2018-01-16","2018-01-16"]
["Days in Reporting Period",366,1098,1826,3652]
["Period Starting Total Balance",200,160,120,100]
["Period Ending Total Balance",220,220,220,220]
["Number of Transactions in Period",2,4,6,8]
["IRR by starting and ending total balance with cash flows",0.10607715249061583,0.13465033173561097,0.18486142754554749,0.10220132470130922]
["IRR by starting and ending total balances (Quicken Acct. Method)",9.9713581800460804E-2,0.11166757941246033,0.12880638241767886,8.1990668177604697E-2]
["IRR by invested balance with cash flows (Quicken Security Method)",0.22594984173774718,0.225845867395401,0.22660073637962344,1.4901161193847656E-8]

{"sheet":"DEBUG_balances","index":4,"n_rows":9,"n_cols":16,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Date",null,"Action",null,null,null,null,null,null,null,"Cash Adj.","Invested Adj.","Total Adj.","Cash Balance, Post Adj.","Invested Balance, Post Adj.","Total Balance, Post Adj."]
["2006-01-01",null,"Cash",null,null,null,null,null,null,null,100,0,100,100,0,100]
["2012-01-01",null,"Bought",null,null,null,null,null,null,null,-100,100,0,0,100,100]
["2012-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,20,100,120]
["2013-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,40,100,140]
["2014-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,60,100,160]
["2015-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,80,100,180]
["2016-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,100,100,200]
["2017-06-06",null,"Div",null,null,null,null,null,null,null,20,0,20,120,100,220]

{"sheet":"DEBUG_cashflows","index":5,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["2008-01-17",-1000000000]
["2012-01-01",0]
["2012-06-06",20]
["2013-06-06",20]
["2014-06-06",20]
["2015-06-06",20]
["2016-06-06",20]
["2017-06-06",20]
["2018-01-16",1000000000]