Create an Excel spreadsheet for a production plant that the company will lease for 5 years at US$1,500,000 per year; it will cost the firm US$4,000,000 in capital (straight-line depreciation, 5 year life) in year 0; it will cost the firm an additional US$150,000 per year after the new production plant is brought online for other expenses; and it will generate an incremental revenue of US$3,500,000 per year. Use a 40% tax rate, a 10% cost of capital, and a 12% re-investment rate.
Assume the company will use cash flow to finance the project.
Discuss how the project would fair under hurdle rate scenarios of 10%, 15%, and 20% (based on MIRR).
SOLUTION PREVIEW
File name:
Create-an-Excel-MIRR.xls
File type:
application/vnd.ms-excel Price: $9
Assume the company will use cash flow to finance the project.
Discuss how the project would fair under hurdle rate scenarios of 10%, 15%, and 20% (based on MIRR).
SOLUTION PREVIEW
Statement showing Net cashflows:
|
|||||||||
Year
|
Initial investment
|
Incremental revenue
|
Lease cost
|
additional costs
|
Depreciation
|
Net profit
|
Taxes (40%)
|
Profit after taxes
|
Cash flows after taxes
|
0
|
-4,000,000
|
||||||||
1
|
3,500,000
|
1,500,000
|
150,000
|
800,000
|
1,050,000
|
420000
|
630,000
|
1,430,000
|
|
2
|
3,500,000
|
1,500,000
|
150,000
|
800,000
|
1,050,000
|
420000
|
630,000
|
1,430,000
|
|
