Question

PLEASE SHOW THE EXCEL INPUTS AND FORMULAS AS I AM VERY NEW TO THIS SUBJECT AND WANT TO LEARN EXCEL INPUTS.

Problem 7-18 Abandonment We are examining a new project. We expect to sell 6,300 units per year at $57 net cash flow apiece for the next 10 years. In other words, the annual operating cash flow is projected to be $57 × 6,300 = $359,100. The relevant discount rate is 12 percent, and the initial investment required is $1,740,000. After the first year, the project can be dismantled and sold for $1,610,000. Suppose you think it is likely that expected sales will be revised upward to 9,300 units if the first year is a success and revised downward to 4,900 units if the first year is not a success. a. If success and failure are equally likely, what is the NPV of the project? Consider the possibility of abandonment in answering. (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) b. What is the value of the option to abandon? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.)

A. NPV

B. Option Value

Answer 1

period	units Yo	units Y1	Probabilty	Expected units	sale value	cash flows	pvaf@12%	Pv of cash flows
		9300	0.5	4650
1to 10	6300
		4900	0.5	2450
				7100	₹ 57.00	₹ 404,700.00	5.650	₹ 2,286,645.26
		pv cash inflows		₹ 2,286,645.26
		Initital investment		₹ 1,740,000.00
		NPV		₹ 546,645.26

workings
Risk free rate		0.12
present value annuity factor for 10 years
1	0.893
2	0.797
3	0.712
4	0.636
5	0.567
6	0.507
7	0.452
8	0.404
9	0.361
10	0.322
	5.650

npv as per excel
Rate	12%
period	0.00	1	2	3	4	5	6	7	8	9	10
Inflows	-1740000.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00	₹ 404,700.00
	NPV as per Excel	₹ 546,645.3

In excel,

Formula for NPV is =NPV(Rate, Value1,(value2),...)+Value i

Here Value 1, value 2,...... are Inflows and Value i should be outflow (initial Investment)

Calculation value of abanded Option
Year 0	Year 1	sale	Cash flow	Disposal cost	Payoff	Probabilty	Expected value
	9300	₹ 57.00	₹ 530,100.00	₹ 0.00	₹ 0.00	0.50	₹ 0.00
6300
	4900	₹ 57.00	₹ 279,300.00	₹ 1,610,000.00	₹ 1,330,700.00	0.50	₹ 665,350.00
	Since payoff at the end of year 1 so present value payoff					₹ 594,062.50
		Value of abanded option			₹ 594,062.50