project finance spreadsheet

TWO PROJECTSDiscount rate 12%

Year Project A Project B0 -1000 -8001 500 4202 500 4203 500 4204 500 4205 500 420

NPV 802.39 714.01 #VALUE!

A B C D

1

23456789

101112

TWO PROJECTSDiscount rate 12%


IRR 41% 44% #VALUE!

A B C D

1

23456789

101112

SIMPLE CAPITAL BUDGETING EXAMPLEDiscount rate 15%

Year Cash flow0 -1,0001 1002 2003 3004 4005 5006 600

PV of future cash flows 1,172.13 #VALUE!NPV 172.13 #VALUE!IRR 19.71% #VALUE!

NPV0% 1,100.00 #VALUE!3% 849.34 #VALUE!6% 637.679% 457.83

12% 304.1615% 172.1318% 58.1021% -40.8624% -127.1427% -202.7130% -269.16

Discountrate

-3% 0% 2% 5% 7% 10% 12% 15% 17% 20% 22% 25% 27% 30%

-400

-200

0

200

400

600

800

1,000

1,200

NPV of Cash Flows

Discount rate

NP

V

A B C D E

1

23456789

1011121314151617

18

1920212223242526272829303132333435363738

-3% 0% 2% 5% 7% 10% 12% 15% 17% 20% 22% 25% 27% 30%

-400

-200

0

200

400

600

800

1,000

1,200

NPV of Cash Flows

Discount rate

NP

V

F G

1

23456789

1011121314151617

18

1920212223242526272829303132333435363738

RANKING PROJECTS WITH NPV AND IRRDiscount rate 15%


NPV 74.42 119.96 #VALUE!IRR 19.77% 27.38% #VALUE!

TABLE OF NPVs AND DISCOUNT RATES

0% 450.00 350.00 #VALUE!2% 382.57 311.53 #VALUE!4% 321.69 275.906% 266.60 242.848% 216.64 212.11

10% 171.22 183.4912% 129.85 156.7914% 92.08 131.8416% 57.53 108.4718% 25.86 86.5720% -3.22 66.0022% -29.96 46.6624% -54.61 28.4526% -77.36 11.2828% -98.39 -4.9330% -117.87 -20.25

Calculating the crossover point

Year Project A Project B0 -500 -500 0 #VALUE!1 100 250 -150 #VALUE!2 100 250 -1503 150 200 -504 200 100 1005 400 50 350

IRR 8.51% #VALUE!

Project ANPV

Project BNPV

Differential cash flows:cash flow(A) - cash flow(B)

0% 5% 10% 15% 20% 25% 30%

-200

-100

0

100

200

300

400

500

Project ANPV

Project BNPV

Discount rate

NP

V

A B C D E

1

23456789

1011121314

15

16

1718192021222324252627282930313233

34

35

3637383940414243

0% 5% 10% 15% 20% 25% 30%

-200

-100

0

100

200

300

400

500

Project ANPV

Project BNPV

Discount rate

NP

V

F

1

23456789

1011121314

15

16

1718192021222324252627282930313233

34

35

3637383940414243

IGNORE SUNK COSTSHouse cost 100,000Fix up cost 20,000

Year0 -120,000 -20,0001 90,000 90,000

IRR -25% 350% #VALUE!

Cash flowwrong!

Cash flowright!

A B C D

1

234

5

678

SALLY & DAVE'S CONDOCost of condo 100,000Sally & Dave's tax rate 30%

Annual reportable income calculationRent 24,000Expenses Property taxes -1,500 Miscellaneous expenses -1,000Depreciation -10,000Reportable income 11,500 #VALUE!Taxes (rate = 30%) -3,450 #VALUE!Net income 8,050 #VALUE!

Net income 8,050 #VALUE!Add back depreciation 10,000 #VALUE!Cash flow 18,050 #VALUE!

Rent 24,000Expenses Property taxes -1,500 Miscellaneous expenses -1,000Depreciation 0Reportable income 21,500 #VALUE!Taxes (rate = 30%) -6,450 #VALUE!Net income without depreciaiton 15,050 #VALUE!

Depreciation tax shield 3,000 #VALUE!Cash flow 18,050 #VALUE!

Cash flow, method 1:Add back depreciation

Cash flow, method 2:Compute after-tax income withoutdepreciation, then add depreciationtax shield

A B C

1

23456789

101112131415

16

17181920

21

222324252627282930313233

This is what the net income would have been if depreciation were not an expense for tax purposes.

The effect of depreciation is to add a $3,000 tax shield.

D

1

23456789

101112131415

16

17181920

21

222324252627282930313233

SALLY & DAVE'S CONDO--PRELIMINARY VALUATIONDiscount rate 12%

Year Cash flow0 -100,0001 18,0502 18,0503 18,0504 18,0505 18,0506 18,0507 18,0508 18,0509 18,050

10 18,050

Net present value, NPV 1,987 #VALUE!Internal rate of return, IRR 12.48% #VALUE!

A B C

1

23456789

101112131415161718

Cost of condo 100,000Sally & Dave's tax rate 30%

Annual reportable income calculation

Rent 24,000Expenses Property taxes -1,500 Miscellaneous expenses -1,000

Depreciation -10,000Reportable income 11,500 #VALUE!Taxes (rate = 30%) -3,450 #VALUE!Net income 8,050 #VALUE!


Discount rate 12%

Year Cash flow0 -100,0001 18,050 #VALUE!2 18,0503 18,0504 18,0505 18,0506 18,0507 18,0508 18,0509 18,050

10 74,050 #VALUE!

NPV of condo investment 20,017 #VALUE!IRR of investment 15.98% #VALUE!

Data table--Condo IRR as function of annual rent and terminal valueRent

15.98% 18,000Terminal value --> 50,000 Err:504

60,000 Err:50470,000 Err:50480,000 Err:504

SALLY & DAVE'S CONDO: PROFITABILITYAND TERMINAL VALUE

Cash flow, method 1Add back depreciation

=B36

A B C

1

2345

6

789

10

11121314

15

16171819202122232425262728293031323334353637

38

394041424344

90,000 Err:504100,000 Err:504110,000 Err:504120,000 Err:504130,000 Err:504140,000 Err:504150,000 Err:504160,000 Err:504

$26,000 per year) and terminal value (from $50,000 to $160,000). Data tables are very useful though not trivial to compute. See Chapter 30 for more information.

Note: The data table above computes the IRR of the condo investment for combinations of rent (from $18,000 to

=B36A B C454647484950515253545556

Terminal value

80,000Book value 0Taxable gain 80,000 #VALUE!Taxes 24,000 #VALUE!

56,000 #VALUE!

Data table--Condo IRR as function of annual rent and terminal value

20,000 22,000 24,000 26,000 28,000Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504

Estimated resale value,year 10

Net after-tax cash flow from terminal value

D E F G H

1

2345

6

789

10

11121314

15

16171819202122232425262728293031323334353637

38

394041424344

Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504Err:504 Err:504 Err:504 Err:504 Err:504

$26,000 per year) and terminal value (from $50,000 to $160,000). Data tables are very useful though not trivial to compute. See Chapter 30 for more information.

: The data table above computes the IRR of the condo investment for combinations of rent (from $18,000 to

D E F G H454647484950515253545556

Cost of condo 100,000Sally & Dave's tax rate 30%

Annual reportable income calculation Terminal value

Rent 24,000Expenses Book value Property taxes -1,500 Taxable gain Miscellaneous expenses -1,000 Taxes

Depreciation -10,000Reportable income 11,500 #VALUE!Taxes (rate = 30%) -3,450 #VALUE!Net income 8,050 #VALUE!


Discount rate 12%

Year Cashflow0 -100,0001 18,050 #VALUE!2 18,0503 18,0504 18,0505 18,0506 18,0507 18,0508 18,0509 18,050

10 74,050 #VALUE!

NPV of condo investment 20,017 #VALUE!IRR of investment 15.98% #VALUE!

Data table--Condo IRR as function of annual rent and terminal valueRent

15.98% 18,000 20,000Terminal value --> 50,000

SALLY & DAVE'S CONDO: PROFITABILITYAND TERMINAL VALUE

Estimated resale value,year 10

Net after tax--cashflow from terminal value

Cash flow, method 1Add back depreciation

A B C D

1

2345

6

789

10

11121314

15

16171819202122232425262728293031323334353637

38

394041

60,00070,00080,00090,000

100,000110,000120,000130,000140,000150,000160,000

=B36

A B C D4243444546474849505152

Terminal value

80,0000

80,000 #VALUE!24,000 #VALUE!

56,000 #VALUE!

22,000 24,000 26,000 28,000

E F G H

1

2345

6

789

10

11121314

15

16171819202122232425262728293031323334353637

38

394041

BUYING A MACHINE--NPV ANALYSISCost of the machine 800Annual anticipated sales 1,000Annual COGS 400Annual SG&A 300 NPV AnalysisAnnual depreciation 100 Year Cash flow

0 -800Tax rate 40% 1 220Discount rate 15% 2 220

3 220Annual profit and loss (P&L) 4 220Sales 1,000 5 220Minus COGS -400 6 220Minus SG&A -300 7 220Minus depreciation -100 8 220Profit before taxes 200 #VALUE!Subtract taxes -80 #VALUE! NPV 187Profit after taxes 120 #VALUE!

Calculating the annual cash flowProfit after taxes 120Add back depreciation 100Cash flow 220

A B C D E F

1

23456789

1011121314151617181920212223

BUYING A MACHINE--NPV ANALYSIS

#VALUE!#VALUE!

#VALUE!

G

1

23456789

1011121314151617181920212223

Cost of the machine 800Annual anticipated sales 1,000Annual COGS 400Annual SG&A 300 NPV AnalysisAnnual depreciation 100 Year Cash flow


3 220Annual profit and loss (P&L) 4 220Sales 1,000 5 220Minus COGS -400 6 220Minus SG&A -300 7 220Minus depreciation -100 8 400Profit before taxes 200 #VALUE!Subtract taxes -80 #VALUE! NPV 246Profit after taxes 120 #VALUE!


Calculating the cash flow from salvage valueMachine market value, year 8 300Book value, year 8 0Taxable gain 300 #VALUE!Taxes paid on gain 120 #VALUE!Cash flow from salvage value 180 #VALUE!

BUYING A MACHINE--NPV ANALYSISwith salvage value

A B C D E F

1

23456789

101112131415161718192021222324252627282930

#VALUE!#VALUE!

#VALUE!

#VALUE!

BUYING A MACHINE--NPV ANALYSISG

1

23456789

101112131415161718192021222324252627282930



3 220Annual profit and loss (P&L) 4 220Sales 1,000 5 220Minus COGS -400 6 220Minus SG&A -300 7 530Minus depreciation -100Profit before taxes 200 #VALUE! NPV 232Subtract taxes -80 #VALUE!Profit after taxes 120 #VALUE!


Calculating the cash flow from salvage valueMachine market value, year 7 450Book value, year 7 100Taxable gain 350 #VALUE!Taxes paid on gain 140 #VALUE!Cash flow from salvage value 310 #VALUE!


Machine sold at end of year 7

A B C D E F

1

23456789

101112131415161718192021222324252627282930

#VALUE!#VALUE!

#VALUE!

#VALUE!



G

1

23456789

101112131415161718192021222324252627282930



3 220Annual profit and loss (P&L) 4 220Sales 1,000 5 220Minus COGS -400 6 220Minus SG&A -300 7 290Minus depreciation -100Profit before taxes 200 #VALUE! NPV 142Subtract taxes -80 #VALUE!Profit after taxes 120 #VALUE!


Calculating the cash flow from salvage valueMachine market value, year 7 50Book value, year 7 100Taxable gain -50 #VALUE!Taxes paid on gain -20 #VALUE!Cash flow from salvage value 70 #VALUE!



A B C D E F

1

23456789

101112131415161718192021222324252627282930

#VALUE!#VALUE!

#VALUE!

#VALUE!



G

1

23456789

101112131415161718192021222324252627282930

Discount rate 12%

Year Cash flow0 -3001 1852 2493 1554 1355 420

NPV 498.12 #VALUE!IRR 62.67% #VALUE!

Discount rate 12%

Year Cash flow

0 -5001 1852 2493 1554 1355 420

NPV 298.12IRR 31.97%

DON'T FORGET THE COST OF FOREGONE OPPORTUNITIES

The $300 direct cost + $200 <-- value of the existing machines

A B C

1

23456789

101112131415161718

19

2021222324252627

SELL THE PHOTOCOPIER OR FIX IT UP?Annual cost savings (before tax) after fixing up the machine 8,000Book value of machine 15,000Market value of machine 5,000Rehab cost of machine 17,000Tax rate 40%Annual depreciation if machine is retained 3,000Annual copying costs In-house 25,000 Outsourcing 33,000Discount rate 12%

Alternative 1: Fix up machine and do copying in-houseYear Cash flow

0 -10,2001 -13,8002 -13,8003 -13,8004 -13,8005 -13,800

NPV of fixing up machine and in-house copying -59,946

Alternative 2: Sell machine and outsource copyingYear Cash flow

0 9,0001 -19,8002 -19,8003 -19,8004 -19,8005 -19,800

NPV of selling machine and outsourcing -62,375

Subtract Alternative 2 CFs from Alternative 1 CFsYear Cash flow

0 -19,2001 6,0002 6,0003 6,0004 6,0005 6,000

NPV(Alternative 1 - Alternative 2) 2,429

A B

1

23456789

101112131415161718192021222324252627282930313233343536373839404142

SELL THE PHOTOCOPIER OR FIX IT UP?

#VALUE!#VALUE!

#VALUE!

#VALUE!#VALUE!

#VALUE!

#VALUE!#VALUE!

#VALUE!

C

1

23456789

101112131415161718192021222324252627282930313233343536373839404142

CAPITAL BUDGETING WITH ACCELERATED DEPRECIATIONMachine cost 10,000Annual materials savings, before tax 3,000Salvage value, end of year 5 4,000Tax rate 40%Discount rate 12%

Accelerated depreciation schedule (ACRS)

Year1 20.00% 2,000 800 #VALUE!2 32.00% 3,200 1,280 #VALUE!3 19.20% 1,920 768 #VALUE!4 11.52% 1,152 461 #VALUE!5 11.52% 1,152 4616 5.76% 576 230

Terminal valueYear 6 sale price, estimated 4,000 #VALUE!Year 6 book value 576 #VALUE!Taxable gain 3,424 #VALUE!Taxes 1,370 #VALUE!Net cash flow from terminal value 2,630 #VALUE!

Net present value calculation

Year Cost Terminal value0 -10,0001 1,800 8002 1,800 1,2803 1,800 7684 1,800 4615 1,800 4616 2,630

Net present value 657 #VALUE!IRR 14.36% #VALUE!

ACRSdepreciationpercentage

Actualdepreciation

Depreciationtax shield

After-tax cost savings

Depreciation tax shield

The book value at the end of year 6 is the initial cost of the machine ($10,000) minus the sum of allthe depreciation taken on the machine through year 6 ($9,424).

The net cash flow from the terminal value equals the year 6 sale price minus applicable taxes.

A B C D E

1

234567

8

9

10111213141516

17

18192021222324

25

26

27282930313233343536

CAPITAL BUDGETING WITH ACCELERATED DEPRECIATION

Total cash flow-10,0002,600 #VALUE!3,0802,5682,2612,2612,630

The book value at the end of year 6 is the initial cost of the machine ($10,000) minus the sum of allthe depreciation taken on the machine through year 6 ($9,424).

The net cash flow from the terminal value equals the year 6 sale price minus applicable taxes.

F G

1

234567

8

9

10111213141516

17

18192021222324

25

26

27282930313233343536

project finance spreadsheet

Documents