Financial Investment-IRR Worksheet Finance and Investments

VIEWS: 719 PAGES: 12

More Info
									SECURITIES INVESTMENT/IRR CALCULATOR


                                                  Instructions:



                                                                                      Scrutinize Output in
                                                                                      Cash Flow.
                                                                    Input Values in   • Output is IRR and
                                                                    WACC.               NPV.
                                              Input Investment
                                              Schedule.
                        Input Expected        • Only enter values
                        Return and              till years over
                        Probabilities.          which investment
  Enter Investment                              is valid.
  Description.          • Total Probability
                          should be 100%.




Note : Only cells marked in yellow require your input.




© Copyright 2013 Docstoc Inc.                                                                                1
Investment Description
Note : Only cells marked in yellow require your input.


Investment     Category Time Frame (Years) Yearly Fees (%) Entry Cost (%) Exit Cost (%) Terminal Value
Investment 1        AA                   3            10%            10%             5%          120%
Investment 2        BB                   4            10%              5%            4%          110%
Investment 3        CC                   7            10%              8%            2%          140%
Investment 4        DD                   8            10%              9%            5%          130%
Investment 5        EE                 10             10%            10%             5%           70%




© Copyright 2013 Docstoc Inc.                                                                            2
Expected Return Output

Investment                           Investment 1 Investment 2 Investment 3 Investment 4 Investment 5
Expected Return 1                            20%          30%          40%          50%          60%
Return 1 - Probability                       20%          20%          20%          20%          20%
Expected Return 2                            25%          30%          40%          50%          60%
Return 2 - Probability                       20%          20%          20%          20%          20%
Expected Return 3                            30%          30%          40%          50%          60%
Return 3 - Probability                       15%          15%          15%          15%          15%
Expected Return 4                            35%          30%          40%          50%          60%
Return 4 - Probability                       15%          15%          15%          15%          15%
Expected Return 5                            40%          30%          40%          50%          60%
Return 5 - Probability                       30%          30%          30%          30%          30%
Total Probability                           100%         100%         100%         100%         100%
Average Return                               31%          30%          40%          50%          60%
Standard Deviation                            8%           0%           0%           0%           0%

Note : Only cells marked in yellow require your input.




© Copyright 2013 Docstoc Inc.                                                                           3
© Copyright 2013 Docstoc Inc.   4
Investment Schedule

Note : Only cells marked in yellow require your input.

 Year                       1      2        3            4      5       6       7          8       9    10

Investments
 Investment 1            100     -        -        -          -       -       -        -       -       -
 Investment 2            100     100      100      -          -       -       -        -       -       -
 Investment 3            100     100      100      100        -       -       -        -       -       -
 Investment 4            100     100      100      100        100     100     -        -       -       -
 Investment 5            100     100      100      100        100     100     100      -       -       -
 Total                   500     400      400      300        200     200     100      -       -       -



Liquidations
 Investment 1              10     10       10       -          -       -       -       -       -       -
 Investment 2              10     10       10       -          -       -       -       -       -       -
 Investment 3              10     10       10        10         10      10     -       -       -       -
 Investment 4              10     10       10        10         10      10      10     -       -       -
 Investment 5              10     10       10        10         10      10      10     -       -       -
 Total                     50     50       50        30         30      30      20     -       -       -

Total Invested Amount
 Investment 1          90         80       70      -           -       -       -       -       -       -
 Investment 2          90        180      270      270         -       -       -       -       -       -
 Investment 3          90        180      270      360         350     340     340     -       -       -
 Investment 4          90        180      270      360         450     540     530     530     -       -
 Investment 5          90        180      270      360         450     540     630     630     630     630
 Total                450        800    1,150    1,350       1,250   1,420   1,500   1,160     630     630




© Copyright 2013 Docstoc Inc.                                                                                5
© Copyright 2013 Docstoc Inc.   6
Weighted Average Cost of Capital

Note : Only cells marked in yellow require your input.

Investment                          Beta Market Return (Rm)      Risk Free Return (Rf) Risk PremiumComponent Cost
Investment 1                        0.56                22%                        5%           17%          14%
Investment 2                           1                22%                        5%           17%          22%
Investment 3                         0.8                22%                        5%           17%          18%
Investment 4                         0.4                22%                        5%           17%          11%
Investment 5                         1.2                22%                        5%           17%          25%

Year                                    1                    2                     3               4             5       6        7        8       9       10

Cost of Capital                     18%                   18%                   19%           19%           18%      18%      19%      19%     25%     25%
Investment 1
Booked Value of Investment           90                     80                   70            -             -       -        -
Weight in Portfolio                 20%                    10%                   6%
Cost of Component                   14%                    14%                  14%            14%           14%     14%      14%      14%     14%     14%
Contribution in Total Cost        2.81%                  1.40%                0.86%
Investment 2
Booked Value of Investment           90                    180                  270            270           -       -        -        -       -       -
Weight in Portfolio                 20%                    23%                  23%            20%
Cost of Component                   22%                    22%                  22%            22%           22%     22%      22%      22%     22%     22%
Contribution in Total Cost        4.31%                  4.85%                5.06%          4.31%
Investment 3
Booked Value of Investment           90                    180                  270            360           350   340         340     -       -       -
Weight in Portfolio                 20%                    23%                  23%            27%           28% 24%           23%
Cost of Component                   18%                    18%                  18%            18%           18% 18%           18%     18%     18%     18%
Contribution in Total Cost        3.63%                  4.08%                4.26%          4.84%         5.08% 4.34%       4.11%
Investment 4
Booked Value of Investment           90                    180                  270            360           450   540         530      530    -       -
Weight in Portfolio                 20%                    23%                  23%            27%           36% 38%           35%      46%
Cost of Component                   11%                    11%                  11%            11%           11% 11%           11%      11%    11%     11%
Contribution in Total Cost        2.26%                  2.55%                2.66%          3.02%         4.08% 4.30%       4.00%    5.17%


© Copyright 2013 Docstoc Inc.                                                                                                                               7
Investment 5
Booked Value of Investment         90     180     270     360     450   540    630    630    630    630
Weight in Portfolio               20%     23%     23%     27%     36% 38%      42%    54% 100% 100%
Cost of Component                 25%     25%     25%     25%     25% 25%      25%    25%    25%    25%
Contribution in Total Cost      4.99%   5.62%   5.86%   6.66%   8.99% 9.49% 10.48% 13.56% 24.96% 24.96%




© Copyright 2013 Docstoc Inc.                                                                         8
Cash Flow

NPV                                       961
IRR                                       55%

CASH INFLOW
Note : Only cells marked in yellow require your input.
Year                                         1       2        3     4       5       6     7       8       9    10

Returns
Investment 1                               28       25       22   -     -       -       -     -       -       -
Investment 2                               27       54       81    81   -       -       -     -       -       -
Investment 3                               36       72      108   144   140     136     136   -       -       -
Investment 4                               45       90      135   180   225     270     265   265     -       -
Investment 5                               54      108      162   216   270     324     378   378     378     378
Total                                     191      351      511   625   640     736     786   651     387     388

Terminal Value
Investment 1                              -        -         84   -     -       -       -     -       -       -
Investment 2                              -        -        -     297   -       -       -     -       -       -
Investment 3                              -        -        -     -     -       -       476   -       -       -
Investment 4                              -        -        -     -     -       -       -     689     -       -
Investment 5                              -        -        -     -     -       -       -     -       -       441
Total                                     -        -         84   297   -       -       476   689     -       441



Liquidations
Investment 1                                  10       10    10   -     -       -       -     -       -       -
Investment 2                                  10       10    10   -     -       -       -     -       -       -
Investment 3                                  10       10    10    10    10      10     -     -       -       -
Investment 4                                  10       10    10    10    10      10      10   -       -       -
Investment 5                                  10       10    10    10    10      10      10   -       -       -
Total                                         50       50    50    30    30      30      20   -       -       -

CASH OUTFLOW

Year                                          1        2      3     4       5       6     7       8       9    10

Investment
Investment 1                              100      -        -     -     -       -       -     -       -       -
Investment 2                              100      100      100   -     -       -       -     -       -       -
Investment 3                              100      100      100   100   -       -       -     -       -       -
Investment 4                              100      100      100   100   100     100     -     -       -       -
Investment 5                              100      100      100   100   100     100     100   -       -       -
Total                                     500      400      400   300   200     200     100   -       -       -




© Copyright 2011 Docstoc Inc.                                                                                       9
Costs
Investment 1                                20        9       8   -     -     -      -       -      -     -
Investment 2                                14       23      32    27   -     -      -       -      -     -
Investment 3                                17       26      35    44    35    34     34     -      -     -
Investment 4                                19       28      37    46    55    64     54      53    -     -
Investment 5                                20       29      38    47    56    65     74      63     63    63
Total                                       89      114     149   163   145   162    161     116     63    63
Standard Deviation
Net Cash Inflow/(Outflow)                 (348)     (114)   95    489   325   404   1,021   1,224   324   766

Cost of Capital                            18%      18%     19%   19%   18%   18%    19%     19%    25%   25%
Present Value                             (295)     (81)     57   245   141   149    309     310     44    83

Note : Cells marked in yellow require your input.




© Copyright 2011 Docstoc Inc.                                                                                   10
© Copyright 2011 Docstoc Inc.   11
© Copyright 2011 Docstoc Inc.   12
								
To top