Assume that the $4.
1 million in new manufacturing plant and
equipment has to be financed by a loan, and the rate of that loan
is 6%, ammortized loan, over 5 years.
Contract (Stamps) 100,000,000
Duration (Year) 5
Land Value 2,700,000
Land Salvage after Tax 3,200,000
PPE 4,100,000
Useful Life 5
Depreciation 820,000
PPE Salvage before tax 540,000
Initial NWC 600,000
Subsequent WC 50,000
Production cost cents 0.50
Production cost dollar 0.005
Fixed operating cost 950,000
Tax Rate 34%
Required Return 12%
Variable Operating Cost 500,000
Total Operating Cost 1,450,000
lant and
of that loan
0 1 2
OCF 935,160.61 963,962.69
NWC (600,000) (50,000) (50,000)
NCS (2,700,000)
($727,325.24) ($770,964.76)
FCF (3,300,000) 157,835 142,998
Goal Seek
PV of FCF (331,163.30)
First, I determined the NWC (Ne
OCF = Net Income + Depreciation followed by the NCS (Net Capita
EBIT = Sales - Cost- Depreciation loan amortization schedule and
Net Income = (Sales - Cost - Depreciation)*(1 - Tax Rate) repayments as negative cash flo
(Operating Cash Flow), I used th
OCF = ((EBIT - Interest) × (1 - Tax Rate)) + Depreciation Sales – Costs – Depreciation – I
amortization, multiplied by (1 –
Sale (Goal Seek) 2,690,485.77 back depreciation.
Bid price dollars 0.0269 I initially calculated the project'
Bid price cents 2.69 OCFs. Then I calculated the PV w
and then used Goal Seek in Exc
annual sales where the NPV eq
I substracted interest from OCF be
due to financing and interest is tax
3 4 5 Loan 4,100,000
994,492.89 1,026,854.91 1,061,158.64 Loan interest rate 6%
(50,000) (50,000) 800,000 Duration 5
3,200,000
356,400 Amortization Schedule
($817,222.64) ($866,256.00) ($918,231.36) Year Beginning balance
127,270 110,599 4,499,327 1 4,100,000
2 3,372,674.76
3 2,601,710.00
4 1,784,487.36
I determined the NWC (Net Working Capital), 5 918,231.36
wed by the NCS (Net Capital Spending). I created a
amortization schedule and included the principal 246,000 202,360
ments as negative cash flows in the NCS. For the OCF
rating Cash Flow), I used the formula:
– Costs – Depreciation – Interest from the loan
tization, multiplied by (1 – Tax Rate), and then added
depreciation.
ally calculated the project's FCF without including the
Then I calculated the PV with the incomplete FCFs
hen used Goal Seek in Excel to determine the level of
al sales where the NPV equals zero.
tracted interest from OCF because it is a real cash flow
o financing and interest is tax deductable.
Payment Principal Interest End Balance
$973,325.24 $727,325.24 246,000 3,372,674.76
$973,325.24 $770,964.76 202,360 2,601,710.00
$973,325.24 $817,222.64 156,103 1,784,487.36
$973,325.24 $866,256.00 107,069 918,231.36
$973,325.24 $918,231.36 55,094 -
156,103 107,069 55,094