Net Present Value Question

Question

I need help with the following problem:

You observe that retail properties in your city are in Phase II: Expansion of the Real Estate Cycle which means they’re likely to keep increasing in profitability and value for the next few years. You identify a vacant piece of land zoned for retail use which you can purchase for $8.00 million today (an amount which will need to be paid immediately).

After detailed analysis you find that constructing retail strip mall will take one year and cost $3.5 million (which will need to be paid at the end of Year 1).

Once the building is completed (at the end of the first year), you plan on renting out the space at annual profit (after all expenses) of $750,000 to be received at the end of each of the three years following the completion of the development.

Finally, having established a track record, you plan on selling the retail strip mall for $13 million at the end of the fourth year.

1) Using a table, lay out the stream of cash flows from this project. Clearly indicate the period, the cash inflow / outflow during each period, and the source (or use) of the cash flow. Hint: Your table should have at minimum six rows to be complete.

My answer:

Year 0 - 8 million (initial deposit) [outflow]

Year 1 -3.5 million (development costs) [outflow]

Year 2 750,000 (rent received) [inflow]

Year 3 750,000 (rent received) [inflow]

Year 4 750,000 (rent received) [inflow]

Year 4 13 million (sale of retail strip) [inflow]

2) Assuming your discount rate is 8%, what is the NPV of this project? Show your work.

I'm trying to put this into Excel, but can't get it to perform right. Is there a website that shows the steps and will calculate NPV? We are able to use any resources to calculate NPV, but I would like to see the steps that go into it.

3) Should you undertake this project? Why or why not? Explain with reference to the NPV rule.

I know if it's greater than or equal to zero, you should undertake the project, but I can't calculate the NPV right - I'm getting ($558,804.16) and I know it's supposed to be a positive number.

4) You are also considering buying and holding the vacant land and selling it at the end of four years to another developer. Following this strategy would mean you’d miss out on the annual profits from the building, but you would avoid the construction costs. Assume there are no direct expenses associated with holding the land and that you expect to sell it for $11 million four years from today. Which plan should you pursue? Explain your reasoning with reference to the NPV of each project.

I put these numbers into the NPV excel formula:

Year 0 - 8 million (initial deposit) [outflow]

Year 1 -3.5 million (development costs) [outflow]

Year 2 750,000 (rent received) [inflow]

Year 3 750,000 (rent received) [inflow]

Year 4 750,000 (rent received) [inflow]

Year 4 11 million (sale of retail strip) [inflow]

and it gives me ($1,819,143.41) which I know must be wrong because the Teacher's Aid said it was supposed to be a positive number.

I need help to find a website that will help me calculate the NPV and show me the steps it took so that I can understand what I'm missing.

Thank you for your help!

Answer 1

You're actually very close! Your cash flow timeline for the first project is correct. The main things to watch are how Excel handles Year 0 and how the second investment option differs from the first.

Let's work through both projects step by step.

1. Cash Flows for the Retail Strip Mall

Year 0: -$8,000,000 (purchase of land)

Year 1: -$3,500,000 (construction costs)

Year 2: +$750,000 (rental profit)

Year 3: +$750,000 (rental profit)

Year 4: +$750,000 (rental profit)

Year 4: +$13,000,000 (sale of completed strip mall)

Since Year 4 has two inflows, combine them when calculating NPV:

Year 4 = $750,000 + $13,000,000 = $13,750,000

2. Calculate NPV Using an 8% Discount Rate

Net Present Value (NPV) measures the value today of all future cash flows, minus the initial investment.

The formula is:

NPV = CF0 + CF1/(1+r) + CF2/(1+r)^2 + CF3/(1+r)^3 + CF4/(1+r)^4

Where:

CF = Cash flow in each year

r = Discount rate = 0.08

Substitute the cash flows:

NPV = -8,000,000 - 3,500,000/(1.08) + 750,000/(1.08)^2 + 750,000/(1.08)^3 + 13,750,000/(1.08)^4

Notice that we do not discount the initial $8 million because it is paid today.

How to Calculate This in Excel

Enter the following:

A1: Year

B1: Cash Flow

A2: 0

B2: -8000000

A3: 1

B3: -3500000

A4: 2

B4: 750000

A5: 3

B5: 750000

A6: 4

B6: 13750000

Then enter this formula into an empty cell:

=NPV(8%,B3)+B2

Important: Excel's NPV function assumes the first cash flow in its range occurs at the end of Year 1, not today. That is why we add the Year 0 cash flow separately.

3. Should You Undertake the Project?

The NPV rule is:

If NPV > 0, accept the project because it is expected to earn more than the required return.

If NPV < 0, reject the project because it is expected to earn less than the required return.

If NPV = 0, the project is expected to earn exactly the required return.

A positive NPV means the investment is financially worthwhile under the assumptions given.

4. Compare With Buying and Holding the Vacant Land

This is where your second calculation needs a correction.

The alternative is to purchase the land today, hold it for four years, and sell the vacant land for $11 million.

There is no construction and no rental income.

Therefore, the cash flows are:

Year 0: -$8,000,000 (purchase of land)

Year 1: $0

Year 2: $0

Year 3: $0

Year 4: +$11,000,000 (sale of vacant land)

The NPV formula becomes:

NPV = -8,000,000 + 11,000,000/(1.08)^4

In Excel, enter:

=NPV(8%,0,0,0,11000000)-8000000

The mistake in your original second calculation was including construction expenses and rental income even though those cash flows would not occur if you simply held the land.