{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Sheet1"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":15,"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":"Sheet1","index":0,"n_rows":44,"n_cols":5,"rows_kept":44,"cut":false,"hidden":false,"merged":[]}
["VBA Function","Details","VBA Input/Output"]
[null,"Dividend one year from now (Div1)",3.5,"<- Input1. Annual stock dividend."]
[null,"R (cost of equity)",6.5000000000000002E-2,"<- Input2. Return investors are expecting at a minimum."]
[null,"g (growth rate of dividends)",0.03,"<- Input3. Growth of dividends per year."]
[]
[null,"Stock Value Today (P0), Formula",{"$f":"C2/(C3-C4)","v":99.999999999999986},{"$f":"_xlfn.FORMULATEXT(C6)","v":"=C2/(C3-C4)"}]
["GGM","Stock Value Today (P0), Student VBA Function",{"$f":"ggm(C2,C3,C4)","v":99.999999999999986},"=GGM(C2,C3,C4). For given inputs, VBA should output $100.00."]
[]
["p₁","p1 (sales of AAPL)",274150,"<- Input1. Annual Sales (or revenue) of company1. Homework1 numbers can be used here."]
["p₂","p2 (sales of FB)",85965,"<- Input2. Annual Sales (or revenue) of company2. Homework1 numbers can be used here."]
["r₁","r1 (growth rate of AAPL sales)",5.4600000000000003E-2,"<- Input3. Annual sales growth rate of company 1. Homework1 numbers can be used here."]
["r₂","r2 (growth rate of FB sales)",0.216,"<- Input4. Annual sales growth rate of company 2. Homework1 numbers can be used here."]
[]
[null,"Years to Catchup, Formula",{"$f":"LN(p₂/p₁)/LN((1+r₁)/(1+r₂))","v":8.1439082044874294},{"$f":"_xlfn.FORMULATEXT(C14)","v":"=LN(p₂/p₁)/LN((1+r₁)/(1+r₂))"}]
["CATCHUP","Years to catchup, Student VBA Function",{"$f":" CATCHUP(p₁, p₂, r₁, r₂)","v":-8.1439082044874294},{"$f":" _xlfn.FORMULATEXT(C15) & \". For given inputs, VBA should outpt 8.14.\"","v":"= CATCHUP(p₁, p₂, r₁, r₂). For given inputs, VBA should outpt 8.14."}]
[]
[null,"Probability","Value"]
[null,0.1,-0.3,"<- Input1","rᵢ"]
[null,0.2,-0.1,"<- Input2","Pᵢ"]
[null,0.4,0.05,"<- Input3","r̂"]
[null,0.2,0.12,"<- Input4"]
[null,0.1,0.35,"<- Input5"]
[]
[null,"Expected Value (r hat), Formula",{"$f":"B18*C18+B19*C19+B20*C20+B21*C21+B22*C22","v":2.8999999999999998E-2},{"$f":"_xlfn.FORMULATEXT(C24)","v":"=B18*C18+B19*C19+B20*C20+B21*C21+B22*C22"}]
[null,"Portfolio Standard Deviation, Formula",{"$f":"SQRT((B18*($C$24-C18)^2)+(B19*($C$24-C19)^2)+(B20*($C$24-C20)^2)+(B21*($C$24-C21)^2)+(B22*($C$24-C22)^2))","v":0.16213882940245988},{"$f":"_xlfn.FORMULATEXT(C25)","v":"=SQRT((B18*($C$24-C18)^2)+(B19*($C$24-C19)^2)+(B20*($C$24-C20)^2)+(B21*($C$24-C21)^2)+(B22*($C$24-C22)^2))"}]
["PSTDEV","Portfolio Standard Deviation, Student VBA Function",{"$f":"PStdev(Pᵢ, rᵢ)","v":0.16213882940245988},"=PStdev(B18:B22,C18:C22). Note: VBA should be able to take more than 5 rows as input. 16.21% is expected VBA output for five rows."]
[]
[null,"52 Week Range","53.15 - 145.09","<- Input1. This is stock's 52-week price range. It can be from $0.00 to $9999.99. VBA should work for this range."]
[null,"Previous Close",133.19,"<- Input2. Previous Close is same as Stock Price. It can be from $0.00 to $9999.99. VBA should work for this range."]
[]
[null,"Stock price from 52-week low",{"$f":"(133.19-53.15)/53.15","v":1.5059266227657573},"=(133.19-53.15)/53.15 for shown example. VBA should work for full range."]
[null,"Stock price from 52-week high",{"$f":"(133.19-145.09)/145.09","v":-8.2018057757254154E-2},"=(133.19-145.09)/145.09 for shown example. VBA should work for full range."]
[]
["STOCKLOW","Stock price from 52-week low, Student VBA Function",{"$f":"STOCKLOW(Close, ValueRange)","v":1.5059266227657573},"=STOCKLOW(C28,C29). Note: Change C28 and C29 values and VBA should work. 150.06% is expected output."]
["STOCKHIGH","Stock price from 52-week High, Student VBA Function",{"$f":"STOCKHIGH(Close,ValueRange)","v":-8.2018057757254154E-2},"=STOCKHIGH(C28,C29). Note: Change C28 and C29 values and VBA should work. -8.20% is expected output."]
[]
[]
[]
[null,"Worksheet formula r̂",{"$f":" SUM(Pᵢ*rᵢ)","v":2.8999999999999998E-2},{"$f":" _xlfn.FORMULATEXT(r̂)","v":"= SUM(Pᵢ*rᵢ)"}]
[]
[null,"Worksheet formula, standard deviation",{"$f":" SQRT( SUM(Pᵢ*(rᵢ-r̂)^2 ))","v":0.16213882940245988},{"$f":" _xlfn.FORMULATEXT(C41)","v":"= SQRT( SUM(Pᵢ*(rᵢ-r̂)^2 ))"}]
[null,"Let formula",{"$f":" _xlfn.LET(\n   _xlpm.r̂, SUM(Pᵢ*rᵢ),\n   SQRT(SUM(Pᵢ*(rᵢ-_xlpm.r̂)^2)))","v":0.16213882940245988},{"$f":" _xlfn.FORMULATEXT(C42)","v":"= LET(\n   r̂, SUM(Pᵢ*rᵢ),\n   SQRT(SUM(Pᵢ*(rᵢ-r̂)^2)))"}]
[null,"STOCK Low/High",{"$f":" _xlfn.LET(\n   _xlpm.n, SEARCH(\"-\", ValueRange),\n   _xlpm.low, --LEFT(ValueRange,_xlpm.n-1),\n   _xlpm.high,  --MID(ValueRange,_xlpm.n+1,9),\n   _xlpm.bound, IF({1;0}, _xlpm.low, _xlpm.high ),\n   (Close - _xlpm.bound) / _xlpm.bound )","v":1.5059266227657573},{"$f":" _xlfn.FORMULATEXT(C43)","v":"= LET(\n   n, SEARCH(\"-\", ValueRange),\n   low, --LEFT(ValueRange,n-1),\n   high,  --MID(ValueRange,n+1,9),\n   bound, IF({1;0}, low, high ),\n   (Close - bound) / bound )"}]
[null,null,-8.2018057757254154E-2]