Question

A company is considering a project that requires an initial investment of $660,000 and has a useful life of 11 years. Expected cash receipts from the project will be $175,000 each year. The salvage value of the assets used in the project will be $75,000. The company’s tax rate is 30%. For tax purposes, the entire initial investment (without any reduction for salvage value) will be depreciated over 11 years. The company uses a discount rate of 21%.

Provide the variables you entered into Excel and your final calculation of net present value after-tax. (If a variable is not used in the calculation, input a zero (0). Omit the "$" and "%" signs in your response. Round answers to the nearest dollar and use a minus sign ( - ) for negative numbers.)

Excel input:

Rate

%

Nper

PMT

$

PV

$

FV

$

Net present value

$

Required:

Compute the internal rate of return after-tax. Provide the variables you entered into Excel for the calculation. (If a variable is not used in the calculation, input a zero (0). Omit the "$" and "%" signs in your response. Round answers to the nearest dollar / whole number and use a minus sign (-) for negative numbers.)

Excel / calculator input:

Rate

%

Nper

PMT

$

PV

$

FV

$

Internal Rate of Return (IRR)

%

Answer 1

We need to calculate PMT
A	EBITDA (Total Cash receipts)	$	195000
B	Depreciation(660000/11)	$	60000
C	EBT (A-B)	$	135000
D	Tax ( C @35%)	$	47250
E	EAT(C-D)	$	87750
F	Depreciation	$	60000
G	Operating Cashflow ( E+F)	$	147750

· Fair Value = Salavage Value – (Tax rate x Salavage Value)

Fair value =   75000-(0.35*75000)

                Fair Value =   48,750

· Calculate the Net present value we need to input the following :

N per =   11

PMT   =   1,47,750

Discounting Rate = 18%

Fair Value = 48,750

· Net Present Value = Present Value – Initial Investment

     = 6,95,818-6,60,000

     = 35,818

Investement Rate of return (IRR) is the rate where Net Present Value is = 0

	Cashflow	Discounted Cashflow @19.41	Present Value of Discounted cashflows
$	-6,60,000	1	-6,60,000.000
$	1,47,750	0.8374	1,23,725.850
$	1,47,750	0.7013	1,03,617.075
$	1,47,750	0.5873	86,773.575
$	1,47,750	0.4918	72,663.450
$	1,47,750	0.4119	60,858.225
$	1,47,750	0.3449	50,958.975
$	1,47,750	0.2888	42,670.200
$	1,47,750	0.2419	35,740.725
$	1,47,750	0.2026	29,934.150
$	1,47,750	0.1696	25,058.400
$	1,47,750	0.142	20,980.500
$	48,750	0.142	6,922.500
Sum of all discounted cashflows	-96.375
(or)
0

Formula for calculation of Discounted cashflow	= cashflow/(1+r)^t
Where
t = Number of Years
r = Rate at which amount is discounted

For finding IRR we can use this formula.

                                            NPVa

IRR   =          ra +    -------------------------------- (rb –ra )

                                      (NPVa - NPVb )

Where,

ra           = Lower discount rate choosen

rb          = Higher discount rate choosen

NPVa     = NPV at ra

NPVb    = NPV at rb