Financial Investment-IRR Worksheet Finance and Investments

Document Sample
Financial Investment-IRR Worksheet Finance and Investments Powered By Docstoc
					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
				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:719
posted:1/10/2012
language:Latin
pages:12
Description: This document sets forth a template spreadsheet to calculate the Internal Rate of Return (IRR) on security investments. The investor should enter data about each investment, such as the time frame, yearly fees, entry cost, exit cost, and terminal value. The investor must then insert expected return figures, an investment schedule, and various factors to calculate the weighted average cost of capital, such as the beta, market return, and risk free return. The worksheet then calculates important financial indicators so that the investor can make informed investment choices.