Question

A music company is considering creating the world's largest piano. Cost and cash flow details are listed below. The company requires a 12% return on its capital. a) What is the present value of the yearly cash flows? (round to nearest dollar) b) What is the net present value of the project? (round to nearest dollar) c) What is the internal rate of return of the project? (round to two decimal places)

Cost of Project ($2,500,000)

Yr 1 cash flow (300,000)

Yr 2 cash flow (150,000)

Yr 3 cash flow 50,000

Yr 4 cash flow 250,000

Yr 5 cash flow 800,000

Yr 6 cash flow 1,200,000

Yr 7 cash flow 3,500,000

Can you please show me how to do this in Excel!

I am so thankful for all that you guys do for me.

Answer 1

Please see the attachment for the PV, NPV, and IRR calculations.

Present value is the sum of the present value of each of the cash flows from year 1 to 7. PV = $2,452,153

Net present value = PV of cash flows - initial investment = $2,452,153 - 2,500,000 = -$47,847

The internal rate of return is found using the excel function IRR. IRR = 11.69%

Α CD Cost of Capital Year 12% Cash flow PV ($2,500,000 ($300,000) 2 ($150,000) 3 $50,000 4 $250,000 5 $800,000 6 $1,200,000 $3,500,000 $2,500,000) ($267,857) ($119,579) $35,589 $158,880 $453,941 $607,957 $1,583,222 PV NPV IRR $2,452,153 ($47,847) 11.69%

Screenshot with formulas

Cost of Capital 0.12 Year Cash flow PV -2500000 =C4/(1+$C$2)^B4 -300000 =C5/(1+$C$2)^B5 -150000 =C6/(1+$C$2)^B6 50000 =C7/(1+$C$2)^B7 250000 =C8/(1+$C$2)^B8 800000 =C9/(1+$C$2)^B9 1200000 =C10/(1+$C$2)^B10 3500000 =C11/(1+$C$2)^B11 PV NPV IRR =SUM(D5:D11) =D13+D4 =IRR(C4:C11)

Can you please upvote? Thank You :-)