NPV

rate 44.4731% 48.0000%

A B
Period Cash flow Cash flow
0 -10000 -5000
1 5000 4000
2 4000 5000
3 10000 10000
4 5000 15000
5 1000 18000
25000 52000
pv of cash ₹ 10,000.00 ₹ 13,731.42
npv ₹ -0.00 ₹ 8,731.42
irr 44.4731% 115.7729%
if
IRR >DISCOUNT RATE
IRR <DISCOUNT RATE
IRR /=DISCOUNT RATE
pv @24

0.806000 4030.000000 npv= +ve , we select, npv= 0, npv= -ve we reject

0.650000 2600.000000
0.520000 5200.000000
0.422000 2110.000000
0.3410 341.000000
14281.00 4281.00

10000.00
0.00
116%

NPV>0
NPV<0
NPV=0
pv= -ve we reject
xirr xirr helps to calculate compounded annual growth rate when there are mu
DATES PARTICULAR AMOUNT
1/1/2020 purchase of xyz growt -10000
3/5/2020 purchase of abc inco -150000
5/8/2020 purchase of pqr divid -40000 xirr
7/11/2020 divident received of 4000 20.817%
9/13/2020 dividend received of 32000 mirr
11/16/2020 sold pqr divident fund 20000 12.3972%
1/19/2021 purchase of afro asia -200000
3/24/2021 purple star fund 15000
5/27/2021 air light fund 165000
7/30/2021 cool winks fund 22000
9/2/2021 asia fund 210000
Input data
xirr 20.8168% reinvestment rate 25%
mirr 12.3972%

12.397%
formula 36000
function 36000 mirr
syntax /=sum(number 1, number 2,……)
hen there are mutiple dates of investment and dates are uneven
XIRR MODULE

reinve
stmen
Period Values Dates t rate 18%

Initial
Investm
ent -100 1-Jan-10 IRR 21.850%
1 40 2-Feb-10 XIRR 5.56
2 60 3-Mar-10 MIRR 20.144%
3 35 4-May-10
4 -8 5-Jul-10
5 19 6-Oct-10
6 13 7-Dec-10
4030
XYZ ltd. A

Number of
shares of
the
company 100000.00
price per sh 44.00
market
value of
equity 4400000.00
total debt 2000000.00
cost of equi15.00%
cost of debt8.00%
tax rate 0.30
wacc 12.063%
WACC

WT. OF EQUITY+ WT.

OF DEBT *1-TAXRATE

The Up-and-Down company has a cost of equity of 12.2%, a

value of its debt is $10 million and the current market value o
would be the company's WACC if the amount of debt used wa
 Up and Down (a) Up and Down (b)

1
2

25000000 1500000 3
10000000 2000000 4
12.20% 12.20%
8.60% 8.60%
0.40 0.35
10.1886% 8.423%

has a cost of equity of 12.2%, a cost of debt of 8.6%, and a margina

n and the current market value of its equity is $25 million.a)What wo
CC if the amount of debt used was $20 million and equity was $15 m
 Steps

weightage of
equity 0.103125
wt of debt 0.025

tax rate 0.70

WACC 12.06%
12%

%, and a marginal tax rate of 40%. The current market

illion.a)What would be the company's WACC?b) What
equity was $15 million and if the tax rate of 35 %
0.103125
0.025

0.70
12.06%
 During FY19, the xyz ltd. company’s real estate investment generated a return of ~
million and common equity valued at $70.0 million. During that period the compan
free rate of return is 1.5%, the market return is 4.0% and the company’s beta is 1.2x
return of 5.5% exceeds its cost of capital if the tax rate is 32%.

XYZ ltd. Step I Total Capi ###

Total equity
### Step 2wt. of De 0.416667
total debt ### Step 3Cost of D 4%
Interest Expe 2000000 Step 4 wt. of Equ0.583333
risk free retur1.50% Step 5Cost of Eq 4.50%
tax rate 32% Step 6tax 68.00%
beta 1.20 Step 7WACC 3.76%

WT. OF
EQUITY+
WT. OF
DEBT *1-
market Retur 4% TAXRATE

Calculate the WACC for a company with 10M in equity, 2M in

average interest rate of 4%, a beta of 1.2, a risk free rate of 0.
risk premium of 5%.The tax rate is 35 %

XYZ ltd. Step I Total Capi ###

Total equity
### Step 2wt. of De 0.166667
total debt 2000000 Step 3Cost of D 4%
Interest Expe 80000 Step 4 wt. of Equ0.833333
risk free retur0.50% Step 5Cost of Eq 5.10%
tax rate 35% Step 6tax 65.00%
beta 1.20 Step 7WACC 4.68%
market Retur 5%
generated a return of ~5.5%.  As per the latest annual report, the company has an outstandi
hat period the company has incurred $2.0 million as interest expense on its debt. On the ot
company’s beta is 1.2x. Calculate WACC based on the given information and check whethe
%.

rf+beta*(market return- rf)

in equity, 2M in debt with an

isk free rate of 0.5%, and a market
mpany has an outstanding debt of $50.0
e on its debt. On the other hand, the risk-
tion and check whether the investment
Returns the present value of an investment. The present value is the total amount that a serie
loan amount is the present value to the lender.

PV Function
PV(rate,nper,pmt,fv,type)
Rate   is the interest rate per period. For example, if you obtain an automobile loan at a 10 per
month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as
Nper   is the total number of payment periods in an annuity. For example, if you get a four-yea
would enter 48 into the formula for nper.

Pmt   is the payment made each period and cannot change over the life of the annuity. Typica
the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would
fv argument.

Fv   is the future value, or a cash balance you want to attain after the last payment is made. If
For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000
rate and determine how much you must save each month. If fv is omitted, you must include th

Type   is the number 0 or 1 and indicates when payments are due.

Find out the PV of Rs.1611 receivable at the end of 5 years at 10 p
rate of interest using Excel.

PV of Rs. 1611 1611* PVIF(r,n)

PVIF= 1/((1+r)^n)

₹1,000.30 1000.3043
Pvalue
Using PV Function ₹10,003.04 5years 10 percent

₹1,000.30

present value

schedule 1000
1000
PV years rate of
interest

1000.00 3 4
1000.00 5 5
1000.00 7 6
1000.00 10 7
1000.00 12 8
1000.00 15 10
5 years at 10 percent

fv
1611

variable amount of investment

FV PV years rate
of
intere
st

1124.86 10000.00 3 4
1276.28 15000.00 5 5
1503.63 22000.00 7 6
1967.15 25000.00 10 7
2518.17 32000.00 12 8
4177.25 43000.00 15 10
 FV

11248.64

19144.22

33079.87

49178.78

80581.44

179621.67
If Mr. Mehta invest Rs.10000 today at 10 % rate of interest for a period of 5 years. What he will g

Method 1 FV of Rs. 10000 10000*(FVIF(r,n))

10000*(1+r)^n
16105.10

Method 2 Using FV function 16105.10

Investment 10000
rate of interest 10%
period/time 5
FV 16105.10
PV 10000.00
years. What he will get at the end of the period ?
 A B
1 Data Description
2 12% Annual interest rate
3 12 Number of payments
4 -1000 Amount of the payment
Formula
12682.50 Future value of an investment 12,682.50
Data Description
8% Annual interest rate
10 Number of months of payments
10000 Amount of loan
Formula Description (Result)
-1037.03 Monthly payment for a loan with the above terms (-1,037.03)
-1030.16 Monthly payment for a loan with the above terms, except payments are
due at the beginning of the period (-1,030.16)
A company has a cost of equity of 12.2%, a cost of debt of 8.6%, and a marginal tax rate of 40%. The current m
market value of its equity is $25 million.
a) What is the weighted average cost of capital?
b) What would be the company's WACC if the amount of debt used was $20 million and equity was $15 million?

DCF Inc. to illustrate the computation of WACC. During FY19, the company’s real es
of ~5.5%.  As per the latest annual report, the company has an outstanding debt of
valued at $70.0 million. During that period the company has incurred $2.0 million a
other hand, the risk-free rate of return is 1.5%, the market return is 4.0% and the co
WACC based on the given information and check whether the investment return of
tax rate is 32%.
YEAR AMOUNT
2001 1780
2002 3400
2003 2500 {=(B2-B10)^(1/9)-1}
2004 3700
2005 5600
2006 3624
2007 4850
2008 5248
2009 4860
 wt. of overall
investment portfolio
stockbeta investmeof equity beta
a 1.07 250000 0.29411765 0.31470588
b 0.58 300000 0.35294118 0.20470588
c 1.01 300000 0.35294118 0.35647059
total i 850000 0.87588235
hedge rat 744500 index future 1
2
3
2485 248500 underhedged
497000
745500 overhedged
an investor has purchased stocks worth $5,000. Now, he came to realize that the m
expected to generate a return of 7% during the next year, while the 10-year treasur
per annum. The stocks purchased by him have a beta of 1.5 when compared to the
expected rate of return based on the capital asset pricing model.

risk free
return (rf) 4
market return
(rm) 7
beta 1.5
Expected Rate of Return is calculated using the CAPM Re= Rf + β * (Rm –
Formula given below

Expected
Rate of
Return 8.5 8.5
 realize that the market is currently
the 10-year treasury bills are trading at 4%
n compared to the market. Calculate

Re= Rf + β * (Rm – Rf)

An investor has stock for a period of one year and he had received growth of 8%,whil
generated a return of 9%. The relevant 10-year treasury bills are trading at 4.5% per a
have a beta of 1.2 when compared to the market, i.e. the stock is riskier than the mark
evaluate whether the stock generated adequate return given its risk level. Calculate th
on the capital asset pricing model.

rf 4.5
rm 9
beta 1.2
Expected Rate of Return 9.9
growth of 8%,while the stock purchased by him has
ding at 4.5% per annum. The stocks purchased
kier than the market. The investor wants to
level. Calculate the expected rate of return based
The formula for CAPM can be derived by using the following steps:

Step 1: Firstly, determine the risk-free rate of return prevalent in the market. Typically return
or Treasury bills are used as a proxy for the risk-free rate of return as these securities are con
risk. It is denoted by Rf.

Step 2: Next, determine the rate of return expected in the broader market based on certain
stock market index. It is denoted by Rm.

Step 3: Next, calculate the market risk premium for the security by deducting the risk-free rate of return (step 1) from the rate

Market Risk Premium = Rm – Rf

Step 4: Next, determine the beta of the security based on its relative movement with respec
index. Basically, it is the measure of the volatility of the stock’s returns which is computed by
vis-à-vis the movement witnessed in the overall market. It is denoted by β.

Step 5: Finally, the formula for CAPM can be derived by adding the risk-free rate of return (
the security (step 4) and market risk premium (step 3) as shown below.

Re= Rf + β * (Rm – Rf)

Relevance and Uses of Capital Asset Pricing Model Formula

From the perspective of a financial analyst, it is important to understand the concept of cap
extensive application in the finance industry. One of its primary application includes calculat
eventually used in the calculation of the weighted average cost of capital (WACC). Further, W
application, that includes financial modeling, calculation of net present value by discounting
determination of enterprise value and equity value.
A firm is considering two financial plans with a
view to examining their impact on EPS. The total
funds required for investment in assets are Rs
500000. The earnings before interest and tax are
assumed as Rs, 50000, 75000, 125000. The rate of
tax is 50 %. Calculate EPS.

Financial PlaPlan 1 Plan 2

Debt ( Inter 400,000 100,000
Equity Share 100,000 400,000
Total financ 500,000 500,000
No of equity 10,000 40,000

Income Statement
EBIT 50000 EBIT 75000 EBIT 125000
Plan I Plan II Plan I Plan II Plan I
EBIT 50000 50000 75000 75000 125000
- Interest 40,000 10,000 40,000 10,000 40,000
EBT 10,000 40000 35000 65000 85000
-T 5,000.0 20000 17500 32500 42500
EAT 5,000.0 20000 17500 32500 42500
(-)Dividend 0 0 0 0 0
EAESH 5,000.0 20000 17500 32500 42500
Equity share 10,000 40,000 10000 40000 10000
EPS 0.5 0.5 1.75 0.8125 4.250000
False False True False True
EPS EPS =
= (Net
(Ne Income –
t Preferred
Inco Dividend
me s) /
– Weighte
Pref d
erre Average
d Shares
Divi Outstand
den ing
ds)
/
End
of
peri
od
Sha
T 125000
Plan II
125000
10,000
115000
57500
57500
0
57500
40000
1.4375 1.5
False
Calculating Stock Beta in Excel ibm s&p
var 69.066 627.718
covar 121.37
1 index y x
Date Adj Close Adj Close Returns (%)Return (%) Method 1

IBM S&P500
IBM S&P500 (^GSPC)
1/3/2011 145.61 1271.87
1/4/2011 145.77 1270.2 0.110% -0.131%
1/5/2011 145.19 1276.56 -0.398% 0.501%
1/6/2011 146.78 1273.85 1.095% -0.212%
1/7/2011 146.05 1271.5 -0.497% -0.184%
1/10/2011 145.77 1269.75 -0.192% -0.138% Beta 0.8
1/11/2011 145.41 1274.48 -0.247% 0.373%
1/12/2011 147.21 1285.96 1.238% 0.901%
1/13/2011 146.93 1283.76 -0.190% -0.171% Method 2
1/14/2011 148.1 1293.24 0.796% 0.738%
1/18/2011 148.74 1295.02 0.432% 0.138% Beta = SLOPE(range o
1/19/2011
1/20/2011
153.72
153.82
1281.92
1280.26
3.348%
0.065%
-1.012%
-0.129%
change of equity, rang
1/21/2011 153.53 1283.35 -0.189% 0.241% change of index)
1/24/2011 157.61 1290.84 2.657% 0.584%
1/25/2011 159.39 1291.18 1.129% 0.026% Beta 0.8
1/26/2011 159 1296.63 -0.245% 0.422%
1/27/2011 159.03 1299.54 0.019% 0.224% 0.8168789432
1/28/2011 157.19 1276.34 -1.157% -1.785%

1/31/2011 159.95 1286.12 1.756% 0.766%

2/1/2011 161.49 1307.59 0.963% 1.669% Beta is a measure of the vo
whole. Beta is used in the cap
2/2/2011 161.23 1304.03 -0.161% -0.272% on its beta a
2/3/2011 161.46 1307.1 0.143% 0.235%
2/4/2011 161.92 1310.87 0.285% 0.288%
2/7/2011 162.73 1319.05 0.500% 0.624%
2/8/2011 164.59 1324.57 1.143% 0.418%
2/9/2011 163.21 1320.88 -0.838% -0.279% IBM
2/10/2011 162.65 1321.87 -0.343% 0.075% IBM 68.589440095
2/11/2011 162.41 1329.15 -0.148% 0.551% S&P500 (^GS121.36954916
2/14/2011 161.79 1332.32 -0.382% 0.238%
2/15/2011 161.41 1328.01 -0.235% -0.323%
2/16/2011 161.97 1336.32 0.347% 0.626% Column 1
2/17/2011 162.8 1340.43 0.512% 0.308% Column 1 68.589440095
2/18/2011 163.39 1343.01 0.362% 0.192% Column 2 121.36954916
2/22/2011 160.53 1315.44 -1.750% -2.053%
2/23/2011 158.78 1307.4 -1.090% -0.611%
Alpha measures the amount that
other broad benchmark that it is c
performed in comparison to a ben
It is an indication of its relative ris
Beta indicates how volatile a stoc
A high alpha is always good.
A high beta may be preferred by a
steady returns and lower risk.

2/24/2011 159.36 1306.1 0.365% -0.099%

2/25/2011 160.86 1319.88 0.941% 1.055%

2/28/2011 160.46 1327.22 -0.249% 0.556%

3/1/2011 158.57 1306.33 -1.178% -1.574%
3/2/2011 158.76 1308.44 0.120% 0.162%
3/3/2011 162.05 1330.97 2.072% 1.722%
3/4/2011 160.41 1321.15 -1.012% -0.738%
3/7/2011 158.53 1310.13 -1.172% -0.834%
3/8/2011 160.86 1321.82 1.470% 0.892%
3/9/2011 164.41 1320.02 2.207% -0.136%
3/10/2011 160.6 1295.11 -2.317% -1.887%
3/11/2011 161.01 1304.28 0.255% 0.708%
3/14/2011 159.97 1296.39 -0.646% -0.605%
3/15/2011 157.63 1281.87 -1.463% -1.120%
3/16/2011 151.66 1256.88 -3.787% -1.949%
3/17/2011 152.83 1273.72 0.771% 1.340%
3/18/2011 154.52 1279.21 1.106% 0.431%
3/21/2011 156.3 1298.38 1.152% 1.499%
3/22/2011 156.61 1293.77 0.198% -0.355%
3/23/2011 158.13 1297.54 0.971% 0.291%
3/24/2011 158.64 1309.66 0.323% 0.934%
3/25/2011 160.76 1313.8 1.336% 0.316%
3/28/2011 159.96 1310.19 -0.498% -0.275%
3/29/2011 161.45 1319.44 0.931% 0.706%
3/30/2011 162.17 1328.26 0.446% 0.668%
3/31/2011 161.64 1325.83 -0.327% -0.183%
4/1/2011 162.83 1332.41 0.736% 0.496%
4/4/2011 162.81 1332.87 -0.012% 0.035%
 4/5/2011 162.55 1332.63 -0.160% -0.018%
4/6/2011 162.6 1335.54 0.031% 0.218%
4/7/2011 162.94 1333.51 0.209% -0.152%
4/8/2011 162.61 1328.17 -0.203% -0.400%
4/11/2011 162.51 1324.46 -0.061% -0.279%
4/12/2011 161.82 1314.16 -0.425% -0.778%
4/13/2011 162.51 1314.41 0.426% 0.019%
4/14/2011 163.52 1314.52 0.622% 0.008%
4/15/2011 164.75 1319.68 0.752% 0.393%
4/18/2011 164.48 1305.14 -0.164% -1.102%
4/19/2011 163.95 1312.62 -0.322% 0.573%
4/20/2011 163.31 1330.36 -0.390% 1.351%
4/21/2011 166.8 1337.38 2.137% 0.528%
4/25/2011 166.2 1335.25 -0.360% -0.159%
4/26/2011 167.01 1347.24 0.487% 0.898%
4/27/2011 168.88 1355.66 1.120% 0.625%
4/28/2011 169.28 1360.48 0.237% 0.356%
4/29/2011 169.08 1363.61 -0.118% 0.230%
5/2/2011 170.64 1361.22 0.923% -0.175%
5/3/2011 171.35 1356.62 0.416% -0.338%
5/4/2011 169.12 1347.32 -1.301% -0.686%
5/5/2011 166.98 1335.1 -1.265% -0.907%
5/6/2011 168.16 1340.2 0.707% 0.382%
5/9/2011 168.37 1346.29 0.125% 0.454%
5/10/2011 169.64 1357.16 0.754% 0.807%
5/11/2011 168.77 1342.08 -0.513% -1.111%
5/12/2011 171.49 1348.65 1.612% 0.490%
5/13/2011 169.18 1337.77 -1.347% -0.807%
5/16/2011 168.13 1329.47 -0.621% -0.620%
5/17/2011 169.76 1328.98 0.969% -0.037%
5/18/2011 169.7 1340.68 -0.035% 0.880%
5/19/2011 169.85 1343.6 0.088% 0.218%
5/20/2011 169.42 1333.27 -0.253% -0.769%
5/23/2011 167.53 1317.37 -1.116% -1.193%
5/24/2011 167.26 1316.28 -0.161% -0.083%
5/25/2011 167.02 1320.47 -0.143% 0.318%
5/26/2011 166.46 1325.69 -0.335% 0.395%
5/27/2011 166.77 1331.1 0.186% 0.408%
5/31/2011 168.2 1345.2 0.857% 1.059%
6/1/2011 165.84 1314.55 -1.403% -2.278%
6/2/2011 165.37 1312.94 -0.283% -0.122%
6/3/2011 164.33 1300.16 -0.629% -0.973%
6/6/2011 164.04 1286.17 -0.176% -1.076%
6/7/2011 162.98 1284.94 -0.646% -0.096%
6/8/2011 163.63 1279.56 0.399% -0.419%
 6/9/2011 164.13 1289 0.306% 0.738%
6/10/2011 162.47 1270.98 -1.011% -1.398%
6/13/2011 162.46 1271.83 -0.006% 0.067%
6/14/2011 163.41 1287.87 0.585% 1.261%
6/15/2011 161.63 1265.42 -1.089% -1.743%
6/16/2011 161.96 1267.64 0.204% 0.175%
6/17/2011 163.73 1271.5 1.093% 0.305%
6/20/2011 164.3 1278.36 0.348% 0.540%
6/21/2011 165.5 1295.52 0.730% 1.342%
6/22/2011 164.96 1287.14 -0.326% -0.647%
6/23/2011 165.4 1283.5 0.267% -0.283%
6/24/2011 164.35 1268.45 -0.635% -1.173%
6/27/2011 166.89 1280.1 1.545% 0.918%
6/28/2011 169.27 1296.67 1.426% 1.294%
6/29/2011 169.8 1307.41 0.313% 0.828%
6/30/2011 170.81 1320.64 0.595% 1.012%
7/1/2011 173.78 1339.67 1.739% 1.441%
7/5/2011 174.67 1337.88 0.512% -0.134%
7/6/2011 176.94 1339.22 1.300% 0.100%
7/7/2011 175.71 1353.22 -0.695% 1.045%
7/8/2011 175.72 1343.8 0.006% -0.696%
7/11/2011 174.23 1319.49 -0.848% -1.809%
7/12/2011 173.3 1313.64 -0.534% -0.443%
7/13/2011 173.56 1317.72 0.150% 0.311%
7/14/2011 173.47 1308.87 -0.052% -0.672%
7/15/2011 174.78 1316.14 0.755% 0.555%
7/18/2011 174.52 1305.44 -0.149% -0.813%
7/19/2011 184.41 1326.73 5.667% 1.631%
7/20/2011 182.85 1325.84 -0.846% -0.067%
7/21/2011 184.1 1343.8 0.684% 1.355%
7/22/2011 184.38 1345.02 0.152% 0.091%
7/25/2011 182.9 1337.43 -0.803% -0.564%
7/26/2011 182.14 1331.94 -0.416% -0.410%
7/27/2011 180.56 1304.89 -0.867% -2.031%
7/28/2011 181.01 1300.67 0.249% -0.323%
7/29/2011 181.06 1292.28 0.028% -0.645%

MEAN RETURN 0.157% 0.014%

 0.193351

Sep-12 Sep-13

Alfa 0.00145

0.00145

SLOPE(range of % Beta = COVAR (range of % change of

equity,range of % change of
of equity, range of % index)/VAR(range of % change of index)
of index)

Beta is a measure of the volatility, or systematic risk, of a security or a portfolio in comparison to the market as a
hole. Beta is used in the capital asset pricing model (CAPM), which calculates the expected return of an asset based
on its beta and expected market returns. Beta is also known as the beta coefficient.

S&P500 (^GSPC)

623.3887

Column 2

623.3887
ures the amount that the investment has returned in comparison to the market index or
benchmark that it is compared against.Alpha shows how well (or badly) a stock has
comparison to a benchmark index.Beta measures the relative volatility of an investment.
ation of its relative risk.
es how volatile a stock's price has been in comparison to the market as a whole.
is always good.
may be preferred by an investor in growth stocks but shunned by investors who seek
ns and lower risk.
e of

index)
LDC Ltd. has a net income of $1 million in the third quarter. The company announces divi
$250,000. Total shares outstanding is at 11,000,000. Calcualte the EPS

The EPS of LDC Ltd. would be:

​EPS = ($1,000,000 – $250,000) / 11,000,000

EPS = $0.068

Since every share receives an equal slice of the pie of net income, they would each receiv

EPS = (Net Income – Preferred Dividends) / End of period Shares Outstanding

EPS = (Net Income – Preferred Dividends) / Weighted Average Shares Outsta

Net Income 1000000
Dividends 250000
Outstanding Shares 11000000
EPS 0.0681818
A firm is considering two financial plans with a view to examining their impact on EPS. The total funds
required for investment in assets are Rs 700000. The earnings before interest and tax are assumed as Rs,
50000, 75000, The rate of tax is 35 %. Debt is 300000 and interest rate is 10 %.Number of equity shares is
10000
 PROBLEM -1 OPENING STOCK
Revenue/Sales: Rs. 15,00,000/- CLOSING STOCK
Cost Of Production: Rs.
6,30,475/- COST OF PRODUCTI
Opening Stock: Rs. 4,04,525/- COGS
Closing Stock: Rs: 2,60,000/- REVUNUE/SALES
Operating Expenses: 2,00,000/- OPERATING EXPENS
EBIT= Revenue – COGS –
Operating Expenses EBIT
COGS = Total Cost of Production
+ Opening Stock – Closing Stock

= 15,00,000 – (6,30,475 +
4,04,525 – 2,60,000) – 2,00,000

= 15,00,000 – 7,75,000 – 2,00,000

= 5,25,000

PROBLEM- 2

Revenue/Sales: Rs. 15,00,000/- NET INCOME

Cost Of Goods Sold: Rs.

7,75,000/- INTEREST
Gross Profit: Rs. 7,25,000/- TAX
Operating Expenses: 2,00,00 EBIT

EBIT= Net Income +

Income Taxes: Rs: 35,000/- Interest + Income Taxes
Interest Expenses: Rs.
1,25,000/-
 Net Income: Rs. 3,65,000/-

= 3,65,000 + 1,25,000 + 35,000

= 5,25,000/-

PROBLEM -3

Net Income: Rs. 3,65,000/- NET INCOME

Depriciation: Rs. 85,500 /- DEP
EBITDA: Rs. 4,40,000/- EBITDA
Income Taxes: Rs. 35,000/- TAX

Interest Expenses: Rs.

1,25,000/- INTEREST EXPENSES

EBIT= EBITDA + Depreciation +

Amortization EBIT
 404525 OPENING STOCK
260000 CLOSING STOCK
630475 COST OF PRODUCTION
775000 REVUNUE/SALES
1500000 OPERATING EXPENSES
200000 COGS

525000 EBIT

365000

125000
35000
525000
365000
85500
440000
35000

125000

525500
 404525
260000
630475
1500000
200000
775000

525000
 Sales:
$1,000,000 Sales 1000000
CGS:
$650,000 CGS 650000
Gross Profit:
Operating
$350,000 GP 350000
Expenses:
Interest
$200,000 OP EXP 200000
Expense:
Income
$50,000 INTEREST 50000
Taxes:
$10,000 TAX 10000
Net Income:
$90,000 NET INCO 90000
EBIT 150000
EBIT 150000

PROBLEM 2
SALES 5E+06 5E+06
VARIABLE
COST (12%
OF SALES) 600000 600000
FIXED COST 200000 200000
CONTRIBUTIO 4E+06 4E+06
EBIT 4E+06 4E+06
Sales 15 million and 5% increment Per Annum.,
Contribution Margin is – 60%, 75%, 77%, 80% and 65% of Sales each year respectively,
Fixed Cost is 155,000. Calculate EBIT for 5 years.
60 75 77 80 65

1 2 3 4 5
sales 2E+07 2E+07 2E+07 2E+0718232594
5% increment 750000 787500 826875 868219
contirbution 9E+06 1E+07 1E+07 1E+0711851186
Fixed Cost 155000 155000 155000 155000 155000
EBIT 9E+06 1E+07 1E+07 1E+0711696186
ear respectively,

60 75 77 80 65

1 2 3 4 5

5000000 5350000 5724500 6125215 6553980

350000 374500 400715 428765.1

3500000 4012500 4407865 4900172 4260087

125000 125000 125000 125000 125000

3375000 3887500 4282865 4775172 4135087

Production UNITS 1000
Contribution per unit 30
operating leverage 6
combined leverage 24
tax 30
calculate EBIT
CL= OL*FL
24=6*FL
FL=4
TOTAL CONTRIBUTION 30000
Operating Leverage= CONTRIBUTION/EBIT
6=30000/EBIT
EBIT 5000
Operating Leverage 15
Combined Leverage 25
Fixed Cost – (Excluding Interest) 4.04
Sales 4000000
12% Debentures 15.25
Tax Rate 30
Calculate EBIT
SOLUTION CALCULATION OF CONTRIBTUION

SALES 4000000 OL= C/EBIT

(-)VC EBIT= C- FC

CONTRIBUTION 14=C/C-4.04

(-) FIXED COST 14 C= 46.56/

EBIT
TION OF CONTRIBTUION Operating Leverage = Contribution /EBIT
14= Contribution/ Contribution- Fixed Cost
14= Contribution/ Contribution- $2.04 million
14 Contribution – $28.56 million = Contribution
Contribution= $ 28.56 million/13
Contribution= 2.196923 million
straddle long call op
5300
5400
5500
5600
5700
5800
5900
rt to buy 6000 200
6100
6200
6300
6400
6500
6600
6700
6800
pr/loss long put op

200 60000 100

FCFE Formula = Net Income + Depreciation & Amortization + Changes in WC

FCFF = Cash Flow From Operations + Interest Expense * (1 – Tax Rate) – Capital Ex
(CAPEX)

FCFF = Net Income + Non Cash Charges + Interest Expense * (1 – Tax Rate) – Inve
Working Capital – Capital Expenditures (CAPEX)

FCFF = Earnings Before Interest and Taxes (EBIT) * (1 – Tax Rate) + Depreciation &
Amortization – Long-Term Investments – Investments In Working Capital
FCFF = Earnings Before Interest, Tax, Depreciation And Amortization (EBITDA) * (
Rate) + Depreciation & Amortization * Tax Rate – Long-Term Investments – Inves
Working Capital
+ Changes in WC + Capex + Net Borrowings

Rate) – Capital Expenditures

– Tax Rate) – Investments In

e) + Depreciation &
ng Capital
zation (EBITDA) * (1 – Tax
nvestments – Investments In
Particulars Amount
Sales 100
(-) Operating Cost 20
EBITDA 80
(-) Depreciation 20
EBIT 60
(-) Interest 30
EBT 30
(-) Tax 12
EAT 18
Working Capital Investment 10
Fixed Capital Investment or C 10
Tax Rate(Tax Expenses of 12 is 40%
debt repayment 6
Interest*(1-tax rate) 18
FCFF= Net Income +Non Cash Charges+ Interest Expenses*(1-
taxrate)- Investments in WC- Capital Expenditure
FCFF 36
FCFE= FCFF- new debt payments-debt repayments
FCFE= 30.00
36
Year FCFF Int FCFE
Expen
ses
1 90.00 40.00 50.00
2 100.00 40.00 60.00
3 108.00 40.00 68.00
4 116.20 40.00 76.20
5 123.49 40.00 83.49
Terminal 2363.00 1603.00
Value

Value of F 1873

Value of Eq 1073
Long-term 10%
borrowing Rate

Tax Rate 50%

Current Market 1073.00
Value of Equity
Value of 800.00
Outstanding Debt
Total 1873.00
Cost of Debt 0.05
Cost of Equity 0.13625
WACC 0.099410705
4TG ,F K
% MARKS PASS OR FAIL
40 Fail Pass
45 Pass Pass
30 Fail fail
97 Pass Pass
56 Pass
39 Pass
67 Pass
79 Pass
80 Pass
Particulars Numbers
sanya 54 4 4
tina 54
gina 47
rina 86
rina
1 2
 Work Sheet
Company
The value is out of range Rs. In Crore

S.No Particulars Abbre. 2012-13 2011-12

1 Sales S 542.38 511.02
2 Other Income Profit (Loss) OI 0.07 0.06
3 Profit Before Dep., Interest and Tax PBDIT 12.53 13.85
4 Profit Before Interest and Tax PBIT 10.72 12.21
5 Profit Before Tax PBT 3.51 3.92
6 Profit After Tax PAT 3.01 2.61
7 Interest Payment INTP 7.21 8.29
8 Depreciation Provision for the Yr. DEP 1.81 1.64
9 Total Assets TA 122.98 127.81
10 Fixed Assets (net) FA 31.71 27.14
11 Current Assets CA 90.67 100
12 Current Liabilities CL 65.01 89.59
13 Share Capital SC 4.93 4.93
14 Reserve & Surplus RES 25.62 22.6
15 Long Term Loans DEBT 27.39 10.68
16 Working Capital (11-12) WC 25.66 10.41
17 Investment + Misc. Exp. INVST 0.6 0.66
18 Net Worth or Equity (13+14) NW 30.55 27.53
19 Capital Employed (10+16+17) or (15+18) CE 57.97 38.21
20 Dividends DIV 10 12
21 Inventories INV 36.35 53.72
22 Sundry Debtors DRS 42.21 34.8
23 Economic Value Added EVA 0 ###
24 No. of outstanding Shares NoS 0.49 0.49
25 Mkt price per share as on date MPS 100 120
 RATIOS ANALYSIS
e Company Name :

2010-11 2009-10 Ratios Formula

342.33 256.15 LIQUIDITY
0.56 0.05 Current Ratio CA/CL
11.67 7.66 Acid Test Ratio (CA-INV)/CL
10.4 6.47
3.42 2.12 SOLVENCY
2.01 1.3 Debt. : Equity Debt/Equity
6.98 4.35 Interest Coverage in Times PBIT/INT
1.27 1.19 Debt Service Coverage Ratio Times (PAT+DEP)/INT
122.98 72.92
19.97 14.34
102.96 58.53 PROFITABILITY
19.63 11.48 Profit Margin (%) PBIT/Sales *100
4.67 4.67 Profit on Share Capital PAT/ Share Capital
17.63 15.72 Profit on Net Worth PAT/NW * 100
78.61 38.83 Return on Investment (ROI) PBIT/CE * 100
83.33 47.05 Book Value Per Share (Rs.) NW/No. of Shares
0.05 0.05 EPS (Rs.) PAT/ No. of Shares
22.3 20.39 DPS (Rs.) Div/No. of Shares
103.35 61.44 P/E Ratio Mkt Price per shar
12 13
71.31 31.18 OTHERS
24.56 20.65 Dividend Yield (DPS/MPS)*100
0 0 Dividend Pay out Ratio DPS/EPS
0.46 0.46 CE Turnover (Times) S/CE
130 125 Total Assets Turnover S/TA
Fixed Assets Turnover S/FA
Current Assets Turnover S/CA
W.C. Turnover S/WC
Inventory Turnover S/INV
Debtors Turnover S/DRS
No. of Days Inventory Stock 365*INV/S
No. of Day Debtors 365*DRS/Credit Sa
(Assuming all sales is credit sales)
S ANALYSIS
Rs. In: Crore Financial Health of the Company

2012-13 2011-12 2010-11 2009-10 2012-13 2011-12 2010-11

1.39 1.12 5.25 5.10 NOT OK OK NOT OK

0.84 0.52 1.61 2.38 NOT OK NOT OK OK

0.90 0.39 3.53 1.90

1.49 1.47 1.49 1.49
0.67 0.51 0.47 0.57

1.98 2.39 3.04 2.53

61.05 52.94 43.04 27.84
9.85 9.48 9.01 6.38
18.49 31.95 10.06 10.53
62.35 56.18 48.48 44.33
6.14 5.33 4.37 2.83
20.41 24.49 26.09 28.26
16.28 22.53 29.75 44.23

20.408163 20.40816 20.06689 22.6087

3.3222591 4.597701 5.970149 10
9.36 13.37 3.31 4.17
4.41 4.00 2.78 3.51
17.10 18.83 17.14 17.86
5.98 5.11 3.32 4.38
21.14 49.09 4.11 5.44
14.92 9.51 4.80 8.22
12.85 14.68 13.94 12.40
24.46 38.37 76.03 44.43
28.41 24.86 26.19 29.43
e Company

2009-10

NOT OK
OK
The terminal value formula helps in estimating the value of a business beyond the ex
projections, the terminal value formula = FCFF6 / (WACC – Growth Rate)

In DCF Valuation, there are 3 ways in which you can find terminal value. they are as
1. Perpetuity Growth Method/ Gordon Model
2. Exit Multiple Growth Method
3. No Growth Perpetuity model
Perpetual Growth Method is also known as the Gordon Growth Perpetual Model
 Perpetuity Growth Method
is the most preferred method. In this method, the assumption is made that the gro
of the company will continue, and return on capital will be more than the cost of ca
Terminal Value =     FCFF6 / (1 + WACC)6 + FCFF7 / (1 + WACC)7 + …..+ Infinity
If we simplify the formula  it will be,
Terminal Value = FCFF6 / (WACC – Growth Rate)
FCFF6 can be written as, FCFF6 = FCFF5 * (1 + Growth Rate)
Now, use Formula in the above equation given,
Terminal Value = FCFF5 * (1 + Growth Rate) / (WACC – Growth Rate)

This method is used for companies that are mature in the market and have stable growth company Eg.  FMCG companies,

FCFF
explicit forecast period. In a DCF model with a 5 year free cash flow

as follows:-
GIVEN FORECAST
 2018 2019 2020 2021
TAX RATE 30%
EBIT 45 56 59 72
FREE CASH FLOW TO FIRM
eatEBIT*(1-t) 31.5 39.2 41.3 50.4
(+) DEPRECIATION 20 24 29 32
(-) CAPEX 21 28 32 36
(-)CHANGES IN WC 3 3 3 4
FCFF 27.5 32.2 35.3 42.4
GIVEN
WACC 10%
GROWTH RATE 4%
TERMINAL VALUE- PERPETUAL GROWTH MODEL/ Gordon Model
FCFF FORECAST
TV

Terminal Value =
FCFF5 * (1 + Growth
Rate) / (WACC –
Growth Rate)
 2022 2023

82 95

57.4 66.5
38 40
40 42
4 4
51.4 60.5

/ Gordon Model
62.92 62.92 1048.667
1048.66666667 1048.66666666667

al Value =
(1 + Growth
(WACC –
Rate)
WACC 8%
FCFF 500
GROWTH RATE 6%
FCFF FOR FORECAST Y 530
TV 23500
 SYSTEMATIC INVESTMENT PLAN
CALCULATOR
MONTHLY INVESTMENT AMOUNT

EXPECTED END VALUE OF MONTHLY INVESTMENT OF

EXPECTED NUMBER OF YEARS
RETURN 5 10 20
8.00% Rs587,815 Rs1,463,568 Rs4,712,163
10.00% Rs619,497 Rs1,638,760 Rs6,074,951
15.00% Rs708,596 Rs2,201,736 Rs11,977,916
20.00% Rs814,066 Rs3,008,762 Rs24,877,215
STMENT PLAN
TOR
Rs8,000

HLY INVESTMENT OF Rs8,000

R OF YEARS ($587,815)

25 30
Rs7,608,211 Rs11,922,876
Rs10,614,667 Rs18,083,903
Rs25,948,237 Rs55,386,237
Rs67,882,294 Rs183,822,702
 DEPRECIATION MODEL
YEAR SLN DB DDB SYD
1 ₹ 900 ₹ 2,060 ₹ 2,000 ₹ 1,636
2 #VALUE! #VALUE! #VALUE! #VALUE!
3 #VALUE! #VALUE! #VALUE! #VALUE!
4 #VALUE! #VALUE! #VALUE! #VALUE!
5 #VALUE! #VALUE! #VALUE! #VALUE!
6 #VALUE! #VALUE! #VALUE! #VALUE!
7 #VALUE! #VALUE! #VALUE! #VALUE!
8 #VALUE! #VALUE! #VALUE! #VALUE!
9 #VALUE! #VALUE! #VALUE! #VALUE!
10 #VALUE! #VALUE! #VALUE! #VALUE!
prepare depreciation model, SIP calculator and income tax calculator, ratio analysis and interpretation, sharpe's single index m
tation, sharpe's single index model, EPS calculator
An education loan is issued at the beginning of year 1. The principal is $15,00,000 the interest rate is 1% per month and the te
Repayments are to be made at the end of each month. Loan must be fully repaid by the end of the term.

Principal 1,000,000

Interest rate (monthly) 15

Term (months) 0

Repayments per month (EMI) : 0

Month Beginning balance Interest

1 1,000,000 15,000,000
2 16,000,000 240,000,000
3 256,000,000 3,840,000,000
4 4,096,000,000 61,440,000,000
5 65,536,000,000 983,040,000,000
6 1,048,576,000,000 ###
7 16,777,216,000,000 ###
8 268,435,456,000,000 ###
9 4,294,967,296,000,000 ###
10 68,719,476,736,000,000 ###
11 ### ###
12 ### ###
13 ### ###
14 ### ###
15 ### ###
16 ### ###
17 ### ###
18 ### ###
19 ### ###
20 ### ###
21 ### ###
22 ### ###
23 ### ###
24 ### ###
25 ### ###
26 ### ###
27 ### ###
28 ### ###
29 ### ###
30 ### ###
31 ### ###
32 ### ###
33 ### ###
34 ### ###
35 ### ###
36 ### ###
37 ### ###
38 ### ###
39 ### ###
40 ### ###
41 ### ###
42 ### ###
43 ### ###
44 ### ###
45 ### ###
46 ### ###
47 ### ###
48 ### ###
49 ### ###
50 ### ###
51 ### ###
52 ### ###
53 ### ###
54 ### ###
55 ### ###
56 ### ###
57 ### ###
58 ### ###
59 ### ###
60 ### ###
s 1% per month and the term is 60 months.

Balance before Monthly

payment payment
16,000,000 0
256,000,000 0
4,096,000,000 0
65,536,000,000 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
### 0
m is 60 months.

Principal repaid Ending Balance

-15,000,000 16,000,000
-240,000,000 256,000,000
-3,840,000,000 4,096,000,000
-61,440,000,000 65,536,000,000
-983,040,000,000 ###
-15,728,640,000,000 ###
-251,658,240,000,000 ###
-4,026,531,840,000,000 ###
-64,424,509,440,000,000 ###
-1,030,792,151,040,000,000 ###
-16,492,674,416,640,000,000 ###
-263,882,790,666,240,000,000 ###
-4,222,124,650,659,840,000,000 ###
-67,553,994,410,557,400,000,000 ###
-1,080,863,910,568,920,000,000,000 ###
-17,293,822,569,102,700,000,000,000 ###
-276,701,161,105,643,000,000,000,000 ###
-4,427,218,577,690,290,000,000,000,000 ###
-70,835,497,243,044,700,000,000,000,000 ###
-1,133,367,955,888,710,000,000,000,000,000 ###
-18,133,887,294,219,400,000,000,000,000,000 ###
 -290,142,196,707,511,000,000,000,000,000,000 ###
-4,642,275,147,320,180,000,000,000,000,000,000 ###
-74,276,402,357,122,800,000,000,000,000,000,000 ###
-1,188,422,437,713,970,000,000,000,000,000,000,000 ###
-19,014,759,003,423,400,000,000,000,000,000,000,000 ###
-304,236,144,054,775,000,000,000,000,000,000,000,000 ###
-4,867,778,304,876,400,000,000,000,000,000,000,000,000 ###
-77,884,452,878,022,400,000,000,000,000,000,000,000,000 ###
-1,246,151,246,048,360,000,000,000,000,000,000,000,000,000 ###
-19,938,419,936,773,700,000,000,000,000,000,000,000,000,000 ###
-319,014,718,988,380,000,000,000,000,000,000,000,000,000,000 ###
-5,104,235,503,814,080,000,000,000,000,000,000,000,000,000,000 ###
-81,667,768,061,025,200,000,000,000,000,000,000,000,000,000,000 ###
-1,306,684,288,976,400,000,000,000,000,000,000,000,000,000,000,000 ###
-20,906,948,623,622,500,000,000,000,000,000,000,000,000,000,000,000 ###
-334,511,177,977,959,000,000,000,000,000,000,000,000,000,000,000,000 ###
-5,352,178,847,647,350,000,000,000,000,000,000,000,000,000,000,000,000 ###
-85,634,861,562,357,600,000,000,000,000,000,000,000,000,000,000,000,000 ###
-1,370,157,784,997,720,000,000,000,000,000,000,000,000,000,000,000,000,000 ###
-21,922,524,559,963,500,000,000,000,000,000,000,000,000,000,000,000,000,000 ###
-350,760,392,959,417,000,000,000,000,000,000,000,000,000,000,000,000,000,000 ###
-5,612,166,287,350,670,000,000,000,000,000,000,000,000,000,000,000,000,000,000 ###
-89,794,660,597,610,700,000,000,000,000,000,000,000,000,000,000,000,000,000,000 ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###
### ###