Share
Explore BrainMass

Creating a NPV & IRR Spreadsheet

Suppose you own a concession stand that sells hot dogs, peanuts, popcorn, and beer at a ball park. You have three years left on the contract with the ball park, and you do not expect it to be renewed.
Long lines limit sales and profits. You have developed four different proposals to reduce the lines and increase profits.

The first proposal is to renovate by adding another window. The second is to update the equipment at the existing windows. These two renovation projects are not mutually exclusive; you could take both projects. The third and fourth proposals involve abandoning the existing stand. The third proposal is to build a new stand. The fourth proposal is to rent a larger stall in the ball park. This option would involve $1,000 in up-front investment for new signs and equipment installation; the incremental cash flows shown in later years are net of lease payments.
You have decided that a 15% discount rate is appropriate for this type of investment. The incremental cash flows associated with each of the proposals are:

Incremental Cash Flows
Project Investment Year 1 Year 2 Year 3
Add a new window -$75,000 44,000 44,000 44,000
Update existing equipment -50,000 23,000 23,000 23,000
Build a new stand -125,000 70,000 70,000 70,000
Rent a larger stand -1,000 12,000 13,000 14,000

• Using the internal rate of return rule (IRR), which proposal(s) do you recommend?
• Using the net present value rule (NPV), which proposal(s) do you recommend?
• How do you explain any differences between the IRR and NPV rankings? Which rule is better?

Solution Summary

The solution provides an excel file that allows you to input cash flows and other variables to determine NPV or IRR.

$2.19