Calculations I Formulas PV=C

Calculations
I Formulas
PV=C(1+r)n, where:
C: cash flow at period1,
r: rate the of return,
n: number of periods
NPV=-C0+C11+r+C2(1+r)2+…+CT(1+r)T, where:
–C0: initial investments,
C: cash flow,
r: discount rate,
T: time
IRR=t-1tCt(1+r)t-C0, where:
Ct: net cash inflow during the period t,
r: discount rate,
t: number of time periods,
C0: total initial investment costs
Payback Period (PP) = years full recovery + (unrecovered cost at beginning of last year/cash flow in following year)
III Calculations
Aspire Project
Project Aspire Project cash flows Time 0 1 2 3 4 5 6
£’000 £’000 £’000 £’000 £’000 £’000 £’000
Initial investment -2,250.00 Scrap value 375.00 Cash inflows (+7.5% p.a.) 650.00 698.75 751.16 807.49 868.05 Variable costs (+6.75%) -27.00 -28.82 -30.77 -32.84 -35.06 Net operating cash flows 623.00 669.93 720.39 774.65 1,207.99 Tax rate @ 20% 0.00 -124.60 -133.99 -144.08 -154.93 -241.60
capital allowance -600.00 -390.00 -345.00 -300.00 -240.00 Tax benefits 120.00 78.00 69.00 60.00 48.00
Net cash flow -2,250.00 623.00 665.33 664.40 699.57 1,113.06 -193.60
Cumulative cash flow -1,627.00 -961.67 -297.27 402.30 1,515.36 1,321.77
WACC 10% DF @10% 1.00 0.91 0.83 0.75 0.68 0.62 0.56
PV -2,250.00 566.36 549.86 499.18 477.82 691.12 -109.28
NPV 425.06 IRR 17% Payback period (years) 3.45 WolfProject Wolf Project cash flows Time 0 1 2 3 4 5 6
£’000 £’000 £’000 £’000 £’000 £’000 £’000
Initial investment -2,250.00 Cash inflows 955.00 955.00 955.00 955.00 955.00 Material costs (+7.5% p.a.) -14.00 -15.05 -16.18 -17.39 -18.70 Other expenses (-7.5% p.a.) -18.00 -16.65 -15.40 -14.25 -13.18 Rental income -75.00 -75.00 -75.00 -75.00 -75.00 Net operating cash flows 848.00 848.30 848.42 848.36 848.13 Tax rate @ 20% -169.60 -169.66 -169.68 -169.67 -169.63
Net cash flows -2,250.00 848.00 678.70 678.76 678.68 678.45 -169.63
Cumulative cash flow -2,250.00 -1,402.00 -723.30 -44.54 634.14 1,312.59 1,142.97
WACC 10% NPV 380.84 IRR 17% Payback period (years) 3.07 III Calculation in Excel Spreadsheet (formulas demonstration)
A B C D E F G H
1 Project Aspire 2 Project cash flows 3 Time 0 1 2 3 4 5 6
4 £’000 £’000 £’000 £’000 £’000 £’000 £’000
5 Initial investment -2,250.00 6 Scrap value 375.00 7 Cash inflows (+7.5% p.a.) 650.00 C7*(1.075) D7*(1.075) E7*(1.075) F7*(1.075) 8 Variable costs (+6.75%) -27.00 C8*(1.0675) D8*(1.0675) E8*(1.0675) F8*(1.0675) 9 Net operating cash flows sum(C7:C8) sum(D7:D8) sum(E7:E8) sum(F7:F8) sum(G6:G8) 10 Tax rate @ 20% -C9*0.2 -D9*0.2 -E9*0.2 -F9*0.2 -G9*0.2
11 Capital allowance -600.00 -390.00 -345.00 -300.00 -240.00 12 Tax benefits -C11*0.2 -D11*0.2 -E11*0.2 -F11*0.2 -G11*0.2
13 Net cash flow B5 C9+C10+C12 D9+D10+D12 E9+E10+E12 F9+F10+F12 G9+G10+G12 H9+H10+H12
14 Cumulative cash flow C13+B13 C14+D13 D14+E13 E14+F13 F14+G13 G14+H13
15 WACC 10% 16 DF @10% 1/(1+10%)^B3 1/(1+10%)^C3 1/(1+10%)^D3 1/(1+10%)^E3 1/(1+10%)^F3 1/(1+10%)^G3 1/(1+10%)^H3
17 PV B16*B13 C16*C13 D16*D13 E16*E13 F16*F13 G16*G13 H16*H13
18 NPV sum(B17:H17) 19 IRR irr(B13:H13,1%) 20 Payback period (years) 3+(E14/E13*-1) 21 22 Project Wolf 23 Project cash flows 24 Time 0 1 2 3 4 5 6
25 £’000 £’000 £’000 £’000 £’000 £’000 £’000
26 Initial investment -2,250.00 27 Cash inflows 955.00 955.00 955.00 955.00 955.00 28 Material costs (+7.5% p.a.) -14.00 C28*(1.075) D28*(1.075) E28*(1.075) F28*(1.075) 29 Other expenses (-7.5% p.a.) -18.00 C29*(1-0.075) D29*(1-0.075) E29*(1-0.075) F29*(1-0.075) 30 Rental income -75.00 -75.00 -75.00 -75.00 -75.00 31 Net operating cash flows sum(C27:C30) sum(D27:D30) sum(E27:E30) sum(F27:F30) sum(G27:G30) 32 Tax rate @ 20% -C31*0.2 -D31*0.2 -E31*0.2 -F31*0.2 -G31*0.2
33 Net cash flows -2,250.00 C31 sum(D31:D32) sum(E31:E32) sum(F31:F32) sum(G31:G32) sum(H31:H32)
34 Cumulative cash flow -2,250.00 B33+C33 C34+D33 D34+E33 E34+F33 F34+G33 G34+H33
35 WACC 10% 36 NPV npv(10%,C33:H33)+B33 37 IRR irr(B33:H33,1%) 38 Payback period (years) 3+(E34/E33*-1) IV Project Aspire without capital allowances
Project Aspire
Project cash flows without capital allowances
Time 0 1 2 3 4 5 6
£’000 £’000 £’000 £’000 £’000 £’000 £’000
Initial investment -2,250.00 Scrap value 375.00 Cash inflows (+7.5% p.a.) 650.00 698.75 751.16 807.49 868.05 Variable costs (+6.75%) -27.00 -28.82 -30.77 -32.84 -35.06 Net operating cash flows 623.00 669.93 720.39 774.65 1,207.99 Tax rate @ 20% 0.00 -124.60 -133.99 -144.08 -154.93 -241.60
capital allowance 0.00 0.00 0.00 0.00 0.00 Tax benefits 0.00 0.00 0.00 0.00 0.00
Net cash flow -2,250.00 623.00 545.33 586.40 630.57 1,053.06 -241.60
Cumulative cash flow -1,627.00 -1,081.67 -495.27 135.30 1,188.36 946.77
WACC 10% DF @10% 1.00 0.91 0.83 0.75 0.68 0.62 0.56
PV -2,250.00 566.36 450.68 440.57 430.69 653.87 -136.38
NPV 155.80 IRR 13% Payback period (years) 3.84