in excel

Part 1:

Upland Orchard has acquired 10 acres of land and is deciding on what to plant.

They are considering Cherry rootstock, Plum rootstock, and Apple rootstock.

The opportunity cost of capital (discount rate) for Upland Nursery is 8%.

(1) Cherry Rootstock

Initial investment: $96,000

Will generate a cash inflow of $9,500 per year for 20 years. This investment has a salvage value of $30,000.

USE THE PV FUNCTION TO CALCULATE THE NPV OF THIS INVESTMENT.

HINT: Initial investment + PV (THERE IS NO PLACE IN THE PV FUNCTION FOR THE INITIAL INVESTMENT BUT IT DOES HAVE AN ARGUMENT FOR SALVAGE VALUE).

(2) Plum Rootstock (USE NPV FUNCTION)

Initial investment: $105,000

Cash inflows are $5000 in year 1 and go up by $1000 each year after. Plum has salvage value of $1500 include this amount in the last cashflow.

(3) Apple rootstock (USE NPV FUNCTION)

Initial investment: $109,000

Cash inflows are $15,000 in year 1 and go down by $500 per year. Apple has salvage value of $3000, include this in the last cashflow.

Calculate the IRR for each investment. DONT FORGET THE SALVAGE VALUES.

Which investment should they make? Why?

Make a copy of the worksheet and determine what the initial investment in Cherry rootstock would need to be in order to make the IRR for Cherry equal the IRR of Plum. (Use solver instead of goal seek.)

Year Cherry Plum Apple

0 -$96,000.00 -$105,000.00 -$109,000.00 Discount Rate: 8.00%

1 $5,000.00 $15,000.00

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

NPV

IRR