Question

Case Study w Suppose you are a recent business student graduate who is planning to offer personal financial advice to clients. In preparation for this business venture, you developed the following financial planning model. 30 Lesson 11 Basic Scenario : Date Age Current Salary Current Portfolio Annual Investment Rate Salary Growth Rate Portfolio Growth Rate $60,000 $25,000 5% 4% 8% Year Beginning Balance $25,000 $30, 120 $35,774 $42,011 $48,881 $56,442 $64,753 $73,881 $83,897 $94,879 Salary New Investment $60,000 $3,000 $62,400 $3,120 $64.896 $3,245 $67,492 $3,375 $70,192 $3,510 $72,999 $3,650 $75,919 $3.796 $78,956 $3,948 $82,114 $4,106 $85,399 $4270 Earnings Ending Balance $2,120 $30, 120 $2,534 $35 774 $2,992 $42,011 $3 496 $48.881 $4,051 $56 442 $4,661 $64.753 $5,332 $73,881 $6,068 $83.897 $6.876 $94 879 $7,761 S106,910 Date

Please show the excel simulation model so I can recreate it. If I see it done correctly, I can recreate it and give it more practice. Thank you so much for your help!

Answer 1

Here is the answer version

LoL D EL F G H. А 1 Age 30 2 Current Salary 60,000 3 Current Portfolio $ 25,000 4 Annual Investment Rate 5% 5 Salary Growth Rate 4% 6 Portfolio Growth Rate 8% 7 8 Year Beginning Balance Salary New Investment Earnings Ending Balance Age 1 $ 25,000 $ 60,000 $ 3,000 $ 2,120 $ 30,120 2 $ 30,120 $ 62,400 3,120 $ 2,534 $ 35,774 35,774 $ 64,896 $ 3,245 $ 2,992 $ 42,011 42,011 $ 67,492 $ 3,375 $ 3,496 $ 48,881 48,881 $ 70,192 $ 3,510 $ 4,051 $ 56,442 56,442 $ 72,999 $ 3,650 $ 4,661 $ 64,753 64,753 $ 75,919 $ 3,796 $ 5,332 $ 73,881 73,881 $ 78,956 $ 3,948 $ 6,068 $ 83,897 83,897 $ 82,114 $ 4,106 $ 6,876 $ 94,879 10 $ 94,879 $ 85,399 $ 4,270 $ 7,761 $ 106,910 m 6 $

I am giving you the formula version. Just change the variables in it and you'll get your desired result and can practice as much as you want

1 Age 30 2 Current Salary 60000 3 Current Portfolio 25000 4 Annual Investment Rate 0.05 5 Salary Growth Rate 0.04 6 Portfolio Growth Rate 0.08 7 8 Year Beginning Balance Salary New Investment Earnings Ending Balance Age 9 1 =B3 =B2 =C9*B4 =(B9+D9/2) *$B$6 =B9+D9+E9 31 10 =A9+1 =F9 =C9*(1+$B$5) =C10*$B$4 =( B10+D10/2)*$B$6 =B10+D10+E10 =G9+1 11 =A10+1 =F10 =C10*(1+$B$5) =C11*$B$4 =(B11+D11/2)*$B$6 =B11+011+E11 =G10+1 12 =A11+1 =F11 =C11*(1+$B$5) =C12*$B$4 =(B12+D12/2)*$B$6 =B12+D12+E12 =G11+1 13 =A12+1 =F12 =C12*(1+$B$5) =C13*$B$4 = (B13+D13/2)*$B$6 =B13+D13+E13 =G12+1 14 =A13+1 =F13 =C13*(1+$B$5) =C14*$B$4 =(B14+D14/2)*$B$6 =B14+D14+E14 =G13+1 15 =A14+1 =F14 =C14*(1+$B$5) =C15*$B$4 = (B15+D15/2)*$B$6 =B15+D15+E15 =G14+1 16 =A15+1 =F15 =C15*(1+$B$5) =C16*$B$4 =(B16+D16/2)*$B$6 =B16+D16+E16 =G15+1 17 =A16+1 =F16 =C16*(1+$B$5) =C17*$B$4 =(B17+D17/2)*$B$6 =B17+D17+E17 =G16+1 18 =A17+1 =F17 =C17*(1+$B$5) =C18*$B$4 = (B18+D18/2)*$B$6 =B18+D18+E18 =G17+1 19