ira (Excel) by shitingting

VIEWS: 5 PAGES: 25

									Example of spreadsheet valuation analyses

Variables settings                          Summary output               Index

Ret. (R)                0.08 *              Tax-free bond    2979.6914   Pg 2
Return                                      Taxable bond     2541.2814   Pg 2
 Div (Rd)              0.064 *              Growth Stock     2783.7531   Pg 2
Return                                      Mixed (Rd, Rc)   2586.8808   Pg 3
 Cap (Rc)              0.016                Mixed (Wd, Wc)   2586.8808   Pg 3
Weight
 Div (Wd)                0.8 *              Roth IRA         2979.6914 Pg 4
Weight                                      Deductible IRA   2804.3274 Pg 4
 Cap (Wc)                0.2
Tax ord inc
 rate (to)               0.4 *
Tax Cap gn
 rate (tc)               0.2 *
Invest.
 bal (I)               2000 *




                                                                                 Mo
                                                                                 Inv
End
ba
Monthly compounding, monthly withholding
Investment type                     Tax-free                 Taxed
                                    Bond                     Bond
                                                                 Net
              Periods       Bal       Int. inc      Bal        Int. inc      Tax
                     0        2000                    2000
                     1   2013.3333   13.333333        2008             8   5.3333333
                     2   2026.7556   13.422222    2016.032         8.032   5.3546667
                     3   2040.2673   13.511704   2024.0961     8.064128    5.3760853
                     4    2053.869   13.601782   2032.1925    8.0963845    5.3975897
                     5   2067.5615    13.69246   2040.3213    8.1287701       5.41918
                     6   2081.3452   13.783743   2048.4826    8.1612851    5.4408568
                     7   2095.2209   13.875635   2056.6765    8.1939303    5.4626202
                     8    2109.189   13.968139   2064.9032     8.226706    5.4844707
                     9   2123.2503    14.06126   2073.1628    8.2596128    5.5064085
                    10   2137.4053   14.155002   2081.4555    8.2926513    5.5284342
             11   2151.6546   14.249369   2089.7813   8.3258219   5.5505479
             12    2165.999   14.344364   2098.1404   8.3591252   5.5727501
             13    2180.439   14.439993    2106.533   8.3925617   5.5950411
             14   2194.9753    14.53626   2114.9591   8.4261319   5.6174213
             15   2209.6084   14.633168   2123.4189   8.4598364    5.639891
             16   2224.3392   14.730723   2131.9126   8.4936758   5.6624505
             17   2239.1681   14.828928   2140.4403   8.5276505   5.6851003
             18   2254.0959   14.927787    2149.002   8.5617611   5.7078407
             19   2269.1232   15.027306    2157.598   8.5960081   5.7306721
             20   2284.2507   15.127488   2166.2284   8.6303922   5.7535948
             21    2299.479   15.228338   2174.8933   8.6649137   5.7766092
             22   2314.8089    15.32986   2183.5929   8.6995734   5.7997156
             23   2330.2409   15.432059   2192.3273   8.7343717   5.8229145
             24   2345.7759   15.534939   2201.0966   8.7693092   5.8462061
             25   2361.4144   15.638506    2209.901   8.8043864   5.8695909
             26   2377.1571   15.742762   2218.7406   8.8396039   5.8930693
             27   2393.0048   15.847714   2227.6156   8.8749624   5.9166416
             28   2408.9582   15.953366    2236.526   8.9104622   5.9403081
             29   2425.0179   16.059721   2245.4721   8.9461041   5.9640694
             30   2441.1847   16.166786    2254.454   8.9818885   5.9879257
             31   2457.4593   16.274565   2263.4718    9.017816   6.0118774
             32   2473.8423   16.383062   2272.5257   9.0538873   6.0359249
             33   2490.3346   16.492282   2281.6158   9.0901028   6.0600686
             34   2506.9369   16.602231   2290.7423   9.1264633   6.0843088
             35   2523.6498   16.712912   2299.9052   9.1629691   6.1086461
             36   2540.4741   16.824332   2309.1049    9.199621   6.1330807
             37   2557.4106   16.936494   2318.3413   9.2364195    6.157613
             38     2574.46   17.049404   2327.6147   9.2733651   6.1822434
             39   2591.6231   17.163067   2336.9251   9.3104586   6.2069724
             40   2608.9006   17.277487   2346.2728   9.3477004   6.2318003
             41   2626.2932    17.39267   2355.6579   9.3850912   6.2567275
             42   2643.8018   17.508622   2365.0805   9.4226316   6.2817544
             43   2661.4272   17.625346   2374.5409   9.4603221   6.3068814
             44     2679.17   17.742848    2384.039   9.4981634    6.332109
             45   2697.0312   17.861134   2393.5752   9.5361561   6.3574374
             46   2715.0114   17.980208   2403.1495   9.5743007   6.3828671
             47   2733.1115   18.100076   2412.7621   9.6125979   6.4083986
             48   2751.3322   18.220743   2422.4131   9.6510483   6.4340322
             49   2769.6744   18.342215   2432.1028   9.6896525   6.4597683
             50   2788.1389   18.464496   2441.8312   9.7284111   6.4856074
             51   2806.7265   18.587593   2451.5985   9.7673247   6.5115498
             52    2825.438    18.71151   2461.4049    9.806394    6.537596
             53   2844.2743   18.836253   2471.2505   9.8456196   6.5637464
             54   2863.2361   18.961828   2481.1355   9.8850021   6.5900014
             55   2882.3243   19.088241   2491.0601   9.9245421   6.6163614
             56   2901.5398   19.215496   2501.0243   9.9642403   6.6428269
             57   2920.8834   19.343599   2511.0284   10.004097   6.6693982
             58    2940.356   19.472556   2521.0725   10.044114   6.6960758
             59   2959.9584   19.602373   2531.1568    10.08429   6.7228601
             60   2979.6914   19.733056   2541.2814   10.124627   6.7497515

Ending net
balance   2979.6914   2541.2814
Growth
Stock

    Bal        Inc
      2000
 2013.3333   13.333333
 2026.7556   13.422222
 2040.2673   13.511704
  2053.869   13.601782
 2067.5615    13.69246
 2081.3452   13.783743
 2095.2209   13.875635
  2109.189   13.968139
 2123.2503    14.06126
 2137.4053   14.155002
2151.6546   14.249369
 2165.999   14.344364
 2180.439   14.439993
2194.9753    14.53626
2209.6084   14.633168
2224.3392   14.730723
2239.1681   14.828928
2254.0959   14.927787
2269.1232   15.027306
2284.2507   15.127488
 2299.479   15.228338
2314.8089    15.32986
2330.2409   15.432059
2345.7759   15.534939
2361.4144   15.638506
2377.1571   15.742762
2393.0048   15.847714
2408.9582   15.953366
2425.0179   16.059721
2441.1847   16.166786
2457.4593   16.274565
2473.8423   16.383062
2490.3346   16.492282
2506.9369   16.602231
2523.6498   16.712912
2540.4741   16.824332
2557.4106   16.936494
  2574.46   17.049404
2591.6231   17.163067
2608.9006   17.277487
2626.2932    17.39267
2643.8018   17.508622
2661.4272   17.625346
  2679.17   17.742848
2697.0312   17.861134
2715.0114   17.980208
2733.1115   18.100076
2751.3322   18.220743
2769.6744   18.342215
2788.1389   18.464496
2806.7265   18.587593
 2825.438    18.71151
2844.2743   18.836253
2863.2361   18.961828
2882.3243   19.088241
2901.5398   19.215496
2920.8834   19.343599
 2940.356   19.472556
2959.9584   19.602373
2979.6914   19.733056
2783.7531




            Monthly compounding, monthly withholding
            Investment type

                                            Mixed stock
                                             (Rd, Rc)
                                   Cap                       Div
                                                             Net
                                                           int.inc        tax
                                     2000
                                2009.0667    2.6666667           6.4   4.2666667
                                2018.1744    2.6787556    6.4290133    4.2860089
                                2027.3235    2.6908992    6.4581582    4.3054388
                                 2036.514     2.703098    6.4874352    4.3249568
                                2045.7462     2.715352    6.5168449    4.3445633
                                2055.0203    2.7276616    6.5463879    4.3642586
                                2064.3364     2.740027    6.5760649    4.3840432
                                2073.6947    2.7524485    6.6058764    4.4039176
                                2083.0954    2.7649263     6.635823     4.423882
                                2092.5388    2.7774606    6.6659054    4.4439369
                                 2102.025    2.7900517    6.6961242    4.4640828
                                2111.5542        2.8027   6.7264799       4.48432
                                2121.1265    2.8154055    6.7569733    4.5046489
                                2130.7423    2.8281687    6.7876049       4.52507
                                2140.4017    2.8409898    6.8183754    4.5455836
                                2150.1048    2.8538689    6.8492854    4.5661902
                                 2159.852    2.8668064    6.8803355    4.5868903
                                2169.6433    2.8798026    6.9115263    4.6076842
                                 2179.479    2.8928577    6.9428586    4.6285724
                                2189.3593     2.905972    6.9743329    4.6495552
                                2199.2844    2.9191458    7.0059498    4.6706332
                                2209.2545    2.9323792    7.0377101    4.6918068
                                2219.2698    2.9456727    7.0696144    4.7130763
                                2229.3305    2.9590264    7.1016633    4.7344422
                                2239.4368    2.9724406    7.1338576     4.755905
                                2249.5889    2.9859157    7.1661977    4.7774651
                                 2259.787    2.9994519    7.1986845     4.799123
                                2270.0314    3.0130494    7.2313185     4.820879
                                2280.3222    3.0267085    7.2641005    4.8427337
                                2290.6597    3.0404296    7.2970311    4.8646874
                                 2301.044    3.0542129    7.3301109    4.8867406
                                2311.4754    3.0680587    7.3633408    4.9088939
                                2321.9541    3.0819672    7.3967213    4.9311475
                                2332.4803    3.0959388    7.4302531     4.953502
                                2343.0542    3.1099737    7.4639369    4.9759579
                                 2353.676    3.1240722    7.4977734    4.9985156
                                 2364.346    3.1382347    7.5317633    5.0211755
                                2375.0644    3.1524614    7.5659073    5.0439382
                 2385.8314   3.1667525   7.6002061     5.066804
                 2396.6471   3.1811085   7.6346603    5.0897736
                 2407.5119   3.1955295   7.6692708    5.1128472
                  2418.426   3.2100159   7.7040382    5.1360254
                 2429.3895    3.224568   7.7389631    5.1593088
                 2440.4027    3.239186   7.7740464    5.1826976
                 2451.4659   3.2538703   7.8092888    5.2061925
                 2462.5792   3.2686212   7.8446909    5.2297939
                 2473.7429    3.283439   7.8802535    5.2535023
                 2484.9572   3.2983239   7.9159773    5.2773182
                 2496.2223   3.3132763   7.9518631     5.301242
                 2507.5386   3.3282965   7.9879115    5.3252743
                 2518.9061   3.3433847   8.0241234    5.3494156
                 2530.3251   3.3585414   8.0604994    5.3736663
                 2541.7959   3.3737668   8.0970403    5.3980269
                 2553.3187   3.3890612   8.1337469    5.4224979
                 2564.8938    3.404425   8.1706199    5.4470799
                 2576.5213   3.4198584      8.20766   5.4717734
                 2588.2015   3.4353617   8.2448681    5.4965787
                 2599.9347   3.4509354   8.2822448    5.5214966
                 2611.7211   3.4665796    8.319791    5.5465273
                 2623.5609   3.4822948   8.3575074    5.5716716


Net of cap tax   2586.8808
            Mixed stock
             (Wd, Wc)
  Cap                        Div
                             Net
                           int.inc        tax
     2000
2009.0667    2.6666667           6.4   4.2666667
2018.1744    2.6787556    6.4290133    4.2860089
2027.3235    2.6908992    6.4581582    4.3054388
 2036.514     2.703098    6.4874352    4.3249568
2045.7462     2.715352    6.5168449    4.3445633
2055.0203    2.7276616    6.5463879    4.3642586
2064.3364     2.740027    6.5760649    4.3840432
2073.6947    2.7524485    6.6058764    4.4039176
2083.0954    2.7649263     6.635823     4.423882
2092.5388    2.7774606    6.6659054    4.4439369
 2102.025    2.7900517    6.6961242    4.4640828
2111.5542        2.8027   6.7264799       4.48432
2121.1265    2.8154055    6.7569733    4.5046489
2130.7423    2.8281687    6.7876049       4.52507
2140.4017    2.8409898    6.8183754    4.5455836
2150.1048    2.8538689    6.8492854    4.5661902
 2159.852    2.8668064    6.8803355    4.5868903
2169.6433    2.8798026    6.9115263    4.6076842
 2179.479    2.8928577    6.9428586    4.6285724
2189.3593     2.905972    6.9743329    4.6495552
2199.2844    2.9191458    7.0059498    4.6706332
2209.2545    2.9323792    7.0377101    4.6918068
2219.2698    2.9456727    7.0696144    4.7130763
2229.3305    2.9590264    7.1016633    4.7344422
2239.4368    2.9724406    7.1338576     4.755905
2249.5889    2.9859157    7.1661977    4.7774651
 2259.787    2.9994519    7.1986845     4.799123
2270.0314    3.0130494    7.2313185     4.820879
2280.3222    3.0267085    7.2641005    4.8427337
2290.6597    3.0404296    7.2970311    4.8646874
 2301.044    3.0542129    7.3301109    4.8867406
2311.4754    3.0680587    7.3633408    4.9088939
2321.9541    3.0819672    7.3967213    4.9311475
2332.4803    3.0959388    7.4302531     4.953502
2343.0542    3.1099737    7.4639369    4.9759579
 2353.676    3.1240722    7.4977734    4.9985156
 2364.346    3.1382347    7.5317633    5.0211755
2375.0644    3.1524614    7.5659073    5.0439382
2385.8314   3.1667525   7.6002061     5.066804
2396.6471   3.1811085   7.6346603    5.0897736
2407.5119   3.1955295   7.6692708    5.1128472
 2418.426   3.2100159   7.7040382    5.1360254
2429.3895    3.224568   7.7389631    5.1593088
2440.4027    3.239186   7.7740464    5.1826976
2451.4659   3.2538703   7.8092888    5.2061925
2462.5792   3.2686212   7.8446909    5.2297939
2473.7429    3.283439   7.8802535    5.2535023
2484.9572   3.2983239   7.9159773    5.2773182
2496.2223   3.3132763   7.9518631     5.301242
2507.5386   3.3282965   7.9879115    5.3252743
2518.9061   3.3433847   8.0241234    5.3494156
2530.3251   3.3585414   8.0604994    5.3736663
2541.7959   3.3737668   8.0970403    5.3980269
2553.3187   3.3890612   8.1337469    5.4224979
2564.8938    3.404425   8.1706199    5.4470799
2576.5213   3.4198584      8.20766   5.4717734
2588.2015   3.4353617   8.2448681    5.4965787
2599.9347   3.4509354   8.2822448    5.5214966
2611.7211   3.4665796    8.319791    5.5465273
2623.5609   3.4822948   8.3575074    5.5716716


2586.8808




                                                 Periods       Bal
                                                        0        2000
                                                        1   2013.3333
                                                        2   2026.7556
                                                        3   2040.2673
                                                        4    2053.869
                                                        5   2067.5615
                                                        6   2081.3452
                                                        7   2095.2209
                                                        8    2109.189
                                                        9   2123.2503
                                                       10   2137.4053
                                                       11   2151.6546
                                                       12    2165.999
                                                       13    2180.439
                                                       14   2194.9753
                                                       15   2209.6084
                                                       16   2224.3392
                                                       17   2239.1681
                                                       18   2254.0959
                                                       19   2269.1232
20   2284.2507
21    2299.479
22   2314.8089
23   2330.2409
24   2345.7759
25   2361.4144
26   2377.1571
27   2393.0048
28   2408.9582
29   2425.0179
30   2441.1847
31   2457.4593
32   2473.8423
33   2490.3346
34   2506.9369
35   2523.6498
36   2540.4741
37   2557.4106
38     2574.46
39   2591.6231
40   2608.9006
41   2626.2932
42   2643.8018
43   2661.4272
44     2679.17
45   2697.0312
46   2715.0114
47   2733.1115
48   2751.3322
49   2769.6744
50   2788.1389
51   2806.7265
52    2825.438
53   2844.2743
54   2863.2361
55   2882.3243
56   2901.5398
57   2920.8834
58    2940.356
59   2959.9584
60   2979.6914


     2979.6914
Roth                     Reg IRA


  Int. inc      Bal        Int. inc     Bal         Int. inc      Tax
                  2000                       800
 13.333333   2013.3333    13.333333        803.2   5.3333333    2.1333333
 13.422222   2026.7556    13.422222    806.4128    5.3546667    2.1418667
 13.511704   2040.2673    13.511704   809.63845    5.3760853    2.1504341
 13.601782    2053.869    13.601782   812.87701    5.3975897    2.1590359
  13.69246   2067.5615     13.69246   816.12851       5.41918    2.167672
 13.783743   2081.3452    13.783743   819.39303    5.4408568    2.1763427
 13.875635   2095.2209    13.875635    822.6706    5.4626202    2.1850481
 13.968139    2109.189    13.968139   825.96128    5.4844707    2.1937883
  14.06126   2123.2503     14.06126   829.26513    5.5064085    2.2025634
 14.155002   2137.4053    14.155002   832.58219    5.5284342    2.2113737
 14.249369   2151.6546    14.249369   835.91252    5.5505479    2.2202192
 14.344364    2165.999    14.344364   839.25617    5.5727501        2.2291
 14.439993    2180.439    14.439993   842.61319    5.5950411    2.2380164
  14.53626   2194.9753     14.53626   845.98364    5.6174213    2.2469685
 14.633168   2209.6084    14.633168   849.36758     5.639891    2.2559564
 14.730723   2224.3392    14.730723   852.76505    5.6624505    2.2649802
 14.828928   2239.1681    14.828928   856.17611    5.6851003    2.2740401
 14.927787   2254.0959    14.927787   859.60081    5.7078407    2.2831363
 15.027306   2269.1232    15.027306   863.03922    5.7306721    2.2922688
15.127488   2284.2507   15.127488   866.49137   5.7535948   2.3014379
15.228338    2299.479   15.228338   869.95734   5.7766092   2.3106437
 15.32986   2314.8089    15.32986   873.43717   5.7997156   2.3198862
15.432059   2330.2409   15.432059   876.93092   5.8229145   2.3291658
15.534939   2345.7759   15.534939   880.43864   5.8462061   2.3384824
15.638506   2361.4144   15.638506   883.96039   5.8695909   2.3478364
15.742762   2377.1571   15.742762   887.49624   5.8930693   2.3572277
15.847714   2393.0048   15.847714   891.04622   5.9166416   2.3666566
15.953366   2408.9582   15.953366   894.61041   5.9403081   2.3761233
16.059721   2425.0179   16.059721   898.18885   5.9640694   2.3856277
16.166786   2441.1847   16.166786    901.7816   5.9879257   2.3951703
16.274565   2457.4593   16.274565   905.38873   6.0118774   2.4047509
16.383062   2473.8423   16.383062   909.01028   6.0359249   2.4143699
16.492282   2490.3346   16.492282   912.64633   6.0600686   2.4240274
16.602231   2506.9369   16.602231   916.29691   6.0843088   2.4337235
16.712912   2523.6498   16.712912    919.9621   6.1086461   2.4434584
16.824332   2540.4741   16.824332   923.64195   6.1330807   2.4532323
16.936494   2557.4106   16.936494   927.33651    6.157613   2.4630452
17.049404     2574.46   17.049404   931.04586   6.1822434   2.4728974
17.163067   2591.6231   17.163067   934.77004   6.2069724    2.482789
17.277487   2608.9006   17.277487   938.50912   6.2318003   2.4927201
 17.39267   2626.2932    17.39267   942.26316   6.2567275    2.502691
17.508622   2643.8018   17.508622   946.03221   6.2817544   2.5127018
17.625346   2661.4272   17.625346   949.81634   6.3068814   2.5227526
17.742848     2679.17   17.742848   953.61561    6.332109   2.5328436
17.861134   2697.0312   17.861134   957.43007   6.3574374    2.542975
17.980208   2715.0114   17.980208   961.25979   6.3828671   2.5531469
18.100076   2733.1115   18.100076   965.10483   6.4083986   2.5633594
18.220743   2751.3322   18.220743   968.96525   6.4340322   2.5736129
18.342215   2769.6744   18.342215   972.84111   6.4597683   2.5839073
18.464496   2788.1389   18.464496   976.73247   6.4856074    2.594243
18.587593   2806.7265   18.587593    980.6394   6.5115498   2.6046199
 18.71151    2825.438    18.71151   984.56196    6.537596   2.6150384
18.836253   2844.2743   18.836253   988.50021   6.5637464   2.6254986
18.961828   2863.2361   18.961828   992.45421   6.5900014   2.6360006
19.088241   2882.3243   19.088241   996.42403   6.6163614   2.6465446
19.215496   2901.5398   19.215496   1000.4097   6.6428269   2.6571307
19.343599   2920.8834   19.343599   1004.4114   6.6693982   2.6677593
19.472556    2940.356   19.472556    1008.429   6.6960758   2.6784303
19.602373   2959.9584   19.602373   1012.4627   6.7228601    2.689144
19.733056   2979.6914   19.733056   1016.5126   6.7497515   2.6999006


            2804.3274

								
To top