ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Equity Analysis of a Project
2
INPUT SHEET: USER ENTERS ALL BOLD NUMBERS
3
INITIAL INVESTMENT
CASHFLOW DETAILS
DISCOUNT RATE
4
Initial Investment=₹50,000.00
Revenues in year 1=
₹40,000.00
Approach(1:Direct;2:CAPM)=
2
5
Opportunity cost (if any)=₹7,484.00
Var. Expenses as % of Rev=
50%
1. Discount rate =
10%
6
Lifetime of the investment10
Fixed expenses in year 1=
02a. Beta0.9
7
Salvage Value at end of project=₹10,000.00
Tax rate on net income=
40%
b. Riskless rate=
8.00%
8
Deprec. method(1:St.line;2:DDB)=2
If you do not have the breakdown of fixed and variable
c. Market risk premium =
5.50%
9
Tax Credit (if any )=10%
expenses, input the entire expense as a % of revenues.
d. Debt Ratio =
30.00%
10
Other invest.(non-depreciable)=0
e. Cost of Borrowing =
9.00%
11
Discount rate used=
10.69%
12
WORKING CAPITAL
13
Initial Investment in Work. Cap=₹10,000.00
14
Working Capital as % of Rev=25%
15
Salvageable fraction at end=100%
16
17
GROWTH RATES
18
12345678910
19
Revenues
Do not enter
10.00%10.00%10.00%10.00%0.00%0.00%0.00%0.00%0.00%
20
Fixed Expenses
Do not enter
10.00%10.00%10.00%10.00%0.00%0.00%0.00%0.00%0.00%
21
Default: The fixed expense growth rate is set equal to the growth rate in revenues by default.
22
YEAR
23
012345678910
24
INITIAL INVESTMENT
25
Investment₹50,000.00
26
 - Tax Credit₹5,000.00
27
Net Investment₹45,000.00
28
 + Working Cap₹10,000.00
29
 + Opp. Cost₹7,484.00
30
 + Other invest.₹0.00
31
Initial Investment₹62,484.00
32
33
SALVAGE VALUE
34
Equipment₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹10,000.00
35
Working Capital₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹14,641.00
36
37
OPERATING CASHFLOWS
38
Lifetime Index1111111111
39
Revenues₹40,000.00₹44,000.00₹48,400.00₹53,240.00₹58,564.00₹58,564.00₹58,564.00₹58,564.00₹58,564.00₹58,564.00
40
 -Var. Expenses₹20,000.00₹22,000.00₹24,200.00₹26,620.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00
41
 - Fixed Expenses₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00₹0.00
42
EBITDA₹20,000.00₹22,000.00₹24,200.00₹26,620.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00₹29,282.00
43
 - Depreciation₹10,000.00₹8,000.00₹6,400.00₹5,120.00₹4,096.00₹3,276.80₹2,621.44₹485.76₹0.00₹0.00
44
EBIT₹10,000.00₹14,000.00₹17,800.00₹21,500.00₹25,186.00₹26,005.20₹26,660.56₹28,796.24₹29,282.00₹29,282.00
45
 -Tax₹4,000.00₹5,600.00₹7,120.00₹8,600.00₹10,074.40₹10,402.08₹10,664.22₹11,518.50₹11,712.80₹11,712.80
46
EBIT(1-t)₹6,000.00₹8,400.00₹10,680.00₹12,900.00₹15,111.60₹15,603.12₹15,996.34₹17,277.74₹17,569.20₹17,569.20
47
 + Depreciation₹10,000.00₹8,000.00₹6,400.00₹5,120.00₹4,096.00₹3,276.80₹2,621.44₹485.76₹0.00₹0.00
48
 - ∂ Work. Cap₹0.00₹1,000.00₹1,100.00₹1,210.00₹1,331.00₹0.00₹0.00₹0.00₹0.00₹0.00
49
NATCF-₹62,484.00₹16,000.00₹15,400.00₹15,980.00₹16,810.00₹17,876.60₹18,879.92₹18,617.78₹17,763.50₹17,569.20₹17,569.20
50
Discount Factor11.106851.2251169231.3560206661.5009114741.6612838651.8387920462.0352669762.2527352522.4934400142.759864079
51
Discounted CF-₹62,484.00₹14,455.44₹12,570.23₹11,784.48₹11,199.86₹10,760.71₹10,267.57₹9,147.58₹7,885.30₹7,046.17₹15,294.30
52
53
Investment Measures
54
NPV =₹47,927.65
55
IRR =23.55%
56
ROC =60.12%
57
58
59
BOOK VALUE & DEPRECIATION
60
Book Value (beginning)₹50,000.00₹40,000.00₹32,000.00₹25,600.00₹20,480.00₹16,384.00₹13,107.20₹10,485.76₹10,000.00₹10,000.00
61
Depreciation₹10,000.00₹8,000.00₹6,400.00₹5,120.00₹4,096.00₹3,276.80₹2,621.44₹485.76₹0.00₹0.00
62
BV(ending)₹50,000.00₹40,000.00₹32,000.00₹25,600.00₹20,480.00₹16,384.00₹13,107.20₹10,485.76₹10,000.00₹10,000.00₹10,000.00
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100