Question

You are considering starting a fishing charter business in San Juan. The boat would cost $235,000 today and be depreciated straight line over 10 years. The purchase will be partially financed with a 10 year loan. At the end of the 8th year, the boat is to be sold at a salvage value and loan paid off (making scheduled payment in year-8 and also paying off remaining balance). Compute the IRR, NPV and Payback of the business FCFE using a straight line depreciation. Assume the full cost of the boat is depreciated over 10 years, so you must account for the net book value in year 8 when computing after-tax salvage.

Boat	$235,000	T=0
Revenues	$100,000	T=1-8
Operating Expenses	$35,000	T=1-8 (Gas, Bait, Maint, Insurance, etc.)
Tax Rate	35.00%
Depr. Life (straight line)	10
Boat Salvage	$50,000	T=8
Discount Rate	20.00%
Loan Principal	$100,000	Amortizing Loan
Loan Maturity (yrs)	10.00
Loan APR	9%
Loan Payment Freq	Annual	Assume annual for simplicity (otherwise would need to create monthly FCF forecast)
NWC % Revenues	5%	Immediate, then liquidited by end of year 8 (T=8, NWC=0)
NPV	?
IRR	?
Payback	?

Answer 1

For calculating FCFE, we first need to calculate the Depreciation, Loan Schedule, Working Capital and Profit and Loss Account. These are calculated below:

Depreciation Schedule

Depreciation per Year = $ 235000 / 10 years = $ 23,500 per year ....striaght line

Net Book Value = Net Book Value (last year) - Depreciation

Year ending	0	1	2	3	4	5	6	7	8
Depreciation (straight line)		23500	23500	23500	23500	23500	23500	23500	23500
Net Book Value of Boat		211500	188000	164500	141000	117500	94000	70500	47000

Loan Schedule

Repayment: Equal amortization assumed i.e. $ 10,000 repaid every year.

Loan Closing = Opening Loan - Repayment

Interest = 9% * Opening Loan

Year ending	0	1	2	3	4	5	6	7	8
Opening Loan		100000	90000	80000	70000	60000	50000	40000	30000
Less: Repayment		10000	10000	10000	10000	10000	10000	10000	30000
Loan Closing	100000	90000	80000	70000	60000	50000	40000	30000	0
Interest @9% APR		9000	8100	7200	6300	5400	4500	3600	2700

Working Capital

Working Capital = 5% of Revenues = 5% * $1000,000

Working Capital = $ 5,000

The revenues are stable, hence, there will be no change in working capital.

In year 8, when the operation closes, the working capital shall be released.

Year ending	0	1	2	3	4	5	6	7	8
Net Working Capital		5000	5000	5000	5000	5000	5000	5000	0
Change in Working Capital		5000	0	0	0	0	0	0	-5000

Salvage Value

Net Book value at end of Year 8 = $ 47,000 ....as per depreciation schedule

Salvage Value = $ 50,000

Hence, at the end of year 8, a profit of $ 3,000 (50,000 - 47,000) will be booked. The net salvage value is $3,000.

Based on the above Depreciation, Loan Schedule, Net Salvage Value and Working Capital Cycle, the Profit and Loss Account is given below:

P&L Account

Year Ending	0	1	2	3	4	5	6	7	8
Revenues		100000	100000	100000	100000	100000	100000	100000	100000
Less: Operating Expenses		35000	35000	35000	35000	35000	35000	35000	35000
EBITDA		65000	65000	65000	65000	65000	65000	65000	65000
Less: Depreciation		23500	23500	23500	23500	23500	23500	23500	23500
Less: Interest		9000	8100	7200	6300	5400	4500	3600	2700
Add: Net Salvage Value									3000
Profit Before Tax		32500	33400	34300	35200	36100	37000	37900	41800
Less: Taxes		11375	11690	12005	12320	12635	12950	13265	14630
Net Income		21125	21710	22295	22880	23465	24050	24635	27170

Now, the FCFE Can be calculated as:

FCFE = Net Income + Non Cash Expenses (Depreciation) - Change in Working Capital - Capital Expenditure + Net Borrowings - Interest * (1 - tax)

This is calculated below:

FCFE	0	1	2	3	4	5	6	7	8
Net Income		21125	21710	22295	22880	23465	24050	24635	27170
Add: Depreciation		23500	23500	23500	23500	23500	23500	23500	23500
Less: Change in WC		5000	0	0	0	0	0	0	-5000
Less: Capex		0	0	0	0	0	0	0	0
Add: Net Debt Availed	100000	-10000	-10000	-10000	-10000	-10000	-10000	-10000	-30000
Less: Interest * (1 - tax)		5850	5265	4680	4095	3510	2925	2340	1755
Asset Purchase	-235000
FCFE	-135000	23775	29945	31115	32285	33455	34625	35795	23915
NPV	-16854
IRR	15.02%
Payback	4.53

Here, the Net Equity Outflow in Year 0 = Asset Purchase - Debt Availed = $ 235,000 - $ 100,000 = $ 135,000

Post that the FCFE is calculated for each year.

IRR

The IRR can be calculated using the IRR formula in excel i.e. = IRR (Range of cells)

The IRR comes out to 15.02%.

NPV

The NPV can be calculated using the excel NPV formula i.e. = NPV (rate, range of cells)

Here, the rate is given as 20 in the question.

Hence, = NPV (20%, Range of Cells)

The NPV is -$ 16,854

Note: If you notice, the  NPV is negative because the discount rate used is higher than the IRR (15%). Had the discount rate been lower, the NPV would be positive.

Payback Period

The payback of equity invested ($135,000) happens in approximately 5 years i.e. the overall investment is recovered in 5 years. This can be calculated as given below:

The sum of FCFE till Year 0 to 4 = $ -17,880 ...... (INR 17,880 still to be recovered)

The sum of FCFE till Year 0 to 5 = $ 15,575

FCFE in Year 5 = $ 33,455

This shows that the total cash flows turn positive between Year 4 and Year 5. Hence, taking a prorata assumption,

Out of the total FCFE in Year 5 ($33,455), the shortfall ($17,880) would be recovered in = 17,880 / 33455 years = 0.53 years

Hence, the absolute payback period would be = 4 + 0.53 years = 4.53 years