Data:
Initial Investment
No of Years
First Years Sales
Growth in Sales PA
Variable Cost
Fixed Cost
Tax
SLM Depretiation
Rs. Lakh
25
5
27.9993069
10
40%
15
30%
Projected Income Statement
Less
Less
Less
Less
Add
Sales
Variable Cost
Contribution
Fixed cost
PBDT
Depretiation
PBT
Tax
PAT
Depretiation
CFAT
-25.00
IRR
NPV
1
28.00
11.20
16.80
15.00
1.80
5.00
-3.20
0.00
-3.20
5.00
1.80
1.80
24.00%
10.96
Lakh
(1) Prepare Projected Inco
statement for 5 Years
(2) Calculate NPV at
(3) Calculate IRR
(4) If Desired IRR is 24%,
Calculate Sales require
in the first Year
Lakh
Lakh
Lakh
2
38.00
15.20
22.80
15.00
7.80
5.00
2.80
0.84
1.96
5.00
6.96
6.96
3
48.00
19.20
28.80
15.00
13.80
5.00
8.80
2.64
6.16
5.00
11.16
11.16
4
58.00
23.20
34.80
15.00
19.80
5.00
14.80
4.44
10.36
5.00
15.36
15.36
Loan Repayment Schedule
Loan Amount
30
Interest Rate
10% PA
Equal Principal Repayment on Annual Basis(Reducing Balance Method)
Loan Tenure
4 Years
Prepare Loan Repayment Schedule
Year
1
2
3
4
3.00
Opening Balance
30.00
22.50
15.00
7.50
2.25
Prin. PYMT
7.50
7.50
7.50
7.50
1.50
Cl. Bl
22.50
15.00
7.50
0.00
0.75
Interest
3.00
2.25
1.50
0.75
0
0.00
Cost of Project
Basic Cost
IDC
Total Cost
40
1.03 10%
41.03
IDC means Contruction
Interest is calculated for 1/2 year because once equity if used then Bank
will issue Debt. Since the half portion is equity which will take 1/2 year to
finish hence interest is calculated for 1/2 year.
Means of Finance
Equity
Debt
Total Capital
20.51
20.51
41.03
A Project requirs base
investment of Rs. 40 Lakh.
Duration of Construction is
1Year.
Debt Equity Ratio is 1:1
Prepare Projected Income
statement for 5 Years
Calculate NPV at
Calculate IRR
If Desired IRR is 24%,
Calculate Sales required
in the first Year
Prepare a Balance Sheet for our very first question.
Assume no Debt.
All Equity
Balance Sheet
Reserve = Cumulative PAT
Net Asset = Gross assets-cummulative depretiation
Assume no working capital
Cash = PAT + Depretiationcumulative
Liability
Balance Sheet
Equity
Reserve
Total
Assets
Fixed Asset Gross
Cumulative Depretiation
Net Fixed Asset
Cash
Total
12%
5
68.00
27.20
40.80
15.00
25.80
5.00
20.80
6.24
14.56
5.00
19.56
19.56
1
25
-3.20
21.80
25
5.00
20.00
1.80
21.80
-
Year
Opening Balance
Prin. PYMT
Cl. Bl
Interest
1.00
30.00
7.50
22.50
3.00
2.00
22.50
7.50
15.00
2.25
3.00
15.00
7.50
7.50
1.50
select 5 rows, then = transpose () + Press shft + ctrl + Enter
4.00
7.5
7.5
0
0.75
Out Basic Cost first. Add Basic Cost and IDC Cell to get Total cost (Dont put total cost. Under means of
finance Equity is half of Total cost and Debt is Total cost - Equity. Total capital is addition of Equity and
Debt.
of Rs. 40 Lakh.
Construction is
Ratio is 1:1
No Dividend Distributed
25
-1.24
23.76
25
4.92
29.92
25
15.28
40.28
25
29.84
54.84
25
10.00
15.00
8.76
23.76
25
15.00
10.00
19.92
29.92
25
20.00
5.00
35.28
40.28
25
25.00
0.00
54.84
54.84
Err
Preparation of EMI Schedule
Loan Amount
100000
Payment Term
12
Desired Return by Bank
Prepare EMI Schedule
Month
OP. Bal
Prin Pymt
1
100,000
7,873
2
92,127
7,954
3
84,173
8,036
4
76,137
8,118
5
68,019
8,201
6
59,818
8,286
7
51,532
8,371
8
43,161
8,457
9
34,704
8,544
10
26,161
8,631
11
17,530
8,720
12
8,810
8,810
Monthly Installment
1% per month
Cl. Bal
92,127
84,173
76,137
68,019
59,818
51,532
43,161
34,704
26,161
17,530
8,810
0
Interet
1,027
946
864
782
699
614
529
443
356
269
180
90
-100000
8900
8900
8900
8900
8900
8900
8900
8900
8900
8900
8900
8900
IRR
1.03%
Initial Investment (Basic)
Debt is
Construction Period
Interest on Debt
First Year Sales
Growth In Sales
Variable cost
Fixed cost
Tax
Total Project Has 1 year Constr. and
Loan Tenure
Assumption : SLM Depretiation
48.00
0.67
1
12%
35
10%
45%
3
30%
5
4
Lakh
of Total Investment
Year
Per Annum
Lakh
Per Annum
Lakh
Years of Operation
Years
(1)
(2)
(3)
(4)
(5)
(6)
(7)
(8)
(9)
(10)
(11)
Prepare Cost of Project and Means of Finance
Prepare Loan Repayment Schedule on Reducing Bal. Method
Projected Inccome Statement
Project IRR
Equity IRR
If Desired IRR is 25%, Calculate feasible Investment (Basic)
Tax will not be negative. Losses will be carried forward
Show Highest PAT on any year through conditional formating
Balance Sheet
Projected Inccome Statement
Less
Less
Less
Less
Add
Sales
Variable Cost
Contribution
Fixed cost
PBDT
Depretiation
PBT
Tax
PAT
Depretiation
CFAT
-48.00
IRR
NPV
Cost of Project
Basic Cost
48.00
IDC
2.70
Total Cost
50.70
Year
1
2
3
4
1
35.00
15.75
19.25
3.00
16.25
10.14
2.05
0.616
1.44
10.14
11.58
11.58
17.70%
7.75
1
33.80
8.45
25.35
4.06
3
42.35
19.06
23.29
3.00
20.29
10.14
8.12
2.437
5.69
10.14
15.83
15.83
4
46.59
20.96
25.62
3.00
22.62
10.14
11.47
3.440
8.03
10.14
18.17
18.17
Balance Sheet
5
51.24
23.06
28.18
3.00
25.18
10.14
15.04
4.513
10.53
10.14
20.67
20.67
2
25.35
8.45
16.90
3.04
Liability
Debt
Equity
Reserve
Total
Assets
Fixed Asset Gross
Cumulative Depretiation
Net Fixed Asset
Cash
Total
Reserve =
Debt :
Means of Finance
Debt
33.80
12% Equity
16.90
Total Capital 50.70
Opening BalancePrin. PYMT
33.80
8.45
25.35
8.45
16.90
8.45
8.45
8.45
Year
Opening Balance
Prin. PYMT
Cl. Bl
Interest
2
38.50
17.33
21.18
3.00
18.18
10.14
4.99
1.498
3.49
10.14
13.64
13.64
Cl. Bl
25.35
16.90
8.45
0.00
Interest
4.06
3.04
2.03
1.01
3
16.90
8.45
8.45
2.03
4
8.45
8.45
0.00
1.01
1
25.35
16.90
1.44
43.69
50.70
10.14
40.56
3.13
43.69
Err
0.00
Cumulative.. PAT
At Closing side
2
16.90
16.90
4.93
38.73
3
8.45
16.90
10.62
35.97
50.70
20.28
30.42
8.31
38.73
Err
0.00
50.70
30.42
20.28
15.69
35.97
Err
0.00
Asset Side:
Fixed Assets: At Gross - Cumulative Depretiation = Net Block
Cash Bal. :
PAT + Depretiation - Principal Repayment of Loan..Cumulative
11.58 13.64
15.83
18.17
8.4507042 8.4507 8.450704225 8.450704225
3.13
5.18
7.38
9.72
3.13
8.31
15.69
25.41
20.67
0
20.67
46.08
Project IRR
Initial investment (Negative figure) = Total investment including IDC
Cashflow = PAT + Depretiation - Interest
Each Year
Equity IRR
Initial negative figure = Equity as per means of finance
Cashflows = PAT + Depretiation - Principal Amount Repayment
Add
Less
Cash Account
Op. Balance
Profit
Depretiation
Interest
Cl. Balance
For Equity IRR
PAT
Depretiation
Loan Repayment
-16.90
Equity IRR
Project IRR
-50.70
Project IRR
1
0
1.44
10.14
4.06
7.52
1.44
10.14
8.45
3.13
32.02%
15.63
21.52%
2
3.13
3.49
10.14
3.04
13.72
3.49
10.14
8.45
5.18
16.68
3
8.31
5.69
10.14
2.03
22.11
5.69
10.14
8.45
7.38
17.86
Each Year
4
15.69
8.03
10.14
1.01
32.84
8.03
10.14
8.45
9.72
19.18
5
25.41
10.53
10.14
0.00
46.08
10.53
10.14
0.00
20.67
20.67
mulative
4
0.00
16.90
18.64
35.55
5
0.00
16.90
29.17
46.08
50.70
40.56
10.14
25.41
35.55
0.00
50.70
50.70
0.00
46.08
46.08
0.00
0
0
0
0
225
0
15
0
15
