The final course of the specialization expands the knowledge of a construction project manager to include an understanding of economics and the mathematics of money, an essential component of every construction project. Topics covered include the time value of money, the definition and calculation of the types of interest rates, and the importance of Cash Flow Diagrams.

From the lesson

Real Estate Finance for Development Projects

Professor Anthony Webster introduces real estate finance providing an overview of the real estate project lifecycle, a discussion on zoning code parameters, and examples of estimating the sales price of a property.

Instructor, Department of Civil Engineering and Engineering Mechanics, Columbia University Director of Research and Founder, Global Leaders in Construction Management

As you know all of the things that we are doing in this chapter and

Â

everything with the mathematics of time value of money that you learned

Â

with professor Odell can be done in Excel.

Â

And actually in the real world virtually everyone

Â

uses Excel to solve almost all financial problems and

Â

certainly all real estate financial problems.

Â

So with that in mind I'd like to show you how we typically solve

Â

some major problems on Excel, particular we're finding NPV is and IRS.

Â

And we'll do this by way of an example.

Â

What I've got here is an example with a company called Lukoil.

Â

Lukoil is an oil company that develops, refines,

Â

and sells gasoline and other oil products.

Â

And they have a gas station in Manhattan at the corner of 10th avenue and

Â

24th street which they believe needs renovating.

Â

So they feel that this renovation if they did it right at

Â

this instant would cost them $650,000.

Â

And then provide for the next ten years, starting a year from now end of year one,

Â

provide them with the net positive cash flow of a 100,000 a year.

Â

And Lukoil believes that based on many gas station renovation,

Â

it's done in this country, that it's opportunity cost of capital or

Â

ROCC for this kind of project is 8%.

Â

So what I'd like to do in Excel with you is find

Â

the NPV of this project and I'd also like to

Â

look at the IRR of this project, although it's not stated here.

Â

And then answer the question should Lukoil go ahead, and

Â

finally, why or why not.

Â

To do that, we're going to jump into

Â

an Excel spreadsheet that shows information very much like this.

Â

So, let's jump in to the Excel right now.

Â

Is also available to you in Coursera.

Â

Okay, so as you can see I've now jumped to the spreadsheet.

Â

This is in your spreadsheet time value in DCF examples.

Â

To show you how we would go about solving this in the real world using XL.

Â

So the first thing I've got here is for

Â

Lukoil I've got their opportunity cost of capital.

Â

Up here and I'm going to be working initially on finding the projects NPV.

Â

And what you want to do is you want to layout

Â

the key items that you've got going here.

Â

And you want to have time zipping the wrong here,

Â

in our case years, could be months, could be quarters, doesn't matter.

Â

And then underneath the time

Â

row we want to have the cash flow that happens at that time.

Â

So t equals zero beginning of project.

Â

Lukoil's going to have to spend 650,000 so I have that as a negative number.

Â

And then for the next ten years,

Â

they think their net cash flow in is going to be 100,000.

Â

Okay, so as we know, to compute the NPV of this project,

Â

we're going to have to take each cash flow and

Â

translate it back to its equivalent at t equals zero.

Â

That way all the cash flows will be on an apples to apples basis.

Â

Once we've gotten them on apples to apples basis we simply add them all up.

Â

Every cash flow for one query at a time, for

Â

one project at a time, we add them up and if the NPV is positive, it's a go.

Â

And if it's a negative or equal to 0, it's not a go.

Â

Okay, so I'm going to start with this 650,000 cash flow so

Â

I've got that, I want the present value of it here in C10.

Â

So I'm going to say =C9, the cash flow itself divide

Â

by 1 plus the opportunity cluster of capital.

Â

And I'm going to work that down which on the PC is function F4.

Â

And that needs to be raised by the amount of time and

Â

years that's passed since the beginning of the project which I have laid up here.

Â

So your time is passed in CA, okay.

Â

So that's looking good, the cashflow at t equals zero discount and

Â

back to t equals zero should just be the cashflow itself.

Â

I think this is a pretty general formula.

Â

So I'm going to just drag it across, all the way through to my tenth year.

Â

And always good to check, so I'm going to check my tenth year

Â

where I have the present value of this tenth year cash flow.

Â

The cash flow itself divided by one plus the opportunity cost of capital,

Â

quantity raised to the ten.

Â

So that's looking good, another check here is if we have uniform cash flows up here.

Â

The more we discount them back the smaller they should get,

Â

which is exactly what I'm seeing.

Â

So I'm pretty confident I've got these right, and

Â

now I've got them in this row all on an apples to apples basis.

Â

They are equivalent at t=0, and I can simply sum them up, every cash flow for

Â

Lukoil involved with this project including anything.

Â

That comes in or out at t=0 and I get an NPV of 21 which looks great.

Â

Okay so that would mean just thinking ahead a little

Â

bit they should go ahead with this project.

Â

Now let's see if the IRR for this project supports that conclusion.

Â

So down here I want to find the project's IRR.

Â

And the way that this works is in

Â

lieu of having a sacrificial worker helping us with this.

Â

I'm going to take a guess at the IRR, some guest, any guest,

Â

10%, 5% doesnt matter it gives a positive numbers small percentage.

Â

And now you using that guest for IRR.

Â

I want to discount back all of my cash flows to t=0,

Â

so I'm going to do the same thing I did up above.

Â

I'm going to lock in I guess for IRR and

Â

I want to discount each cash row by the appropriate number of years.

Â

I think that formula is good I'm not metering this second looks good to me.

Â

Always check the last one, that looks good.

Â

Notice how they're all getting smaller as time goes on, that looks good,

Â

too and as with NPV, I want to sum these up.

Â

All of my cash flows, apples to apple spaces discounted to t=0, okay.

Â

And the IRR method as we know and

Â

as repeated up here says that

Â

the proper internal rate of return for this project is

Â

the internal rate of return that makes the project NPV equal to zero.

Â

Okay, so two ways to go ahead and solve this.

Â

One is if you have a sacrificial intern, sacrificial worker,

Â

you can tell them to keep playing with this IRR number.

Â

And you see how it, every time I change the IRR,

Â

I'm changing the NPV.

Â

And you can tell them to do that until the sum of all the present

Â

values while the cash flows is equal to zero.

Â

If you want to be a little bit sadistic about it, which I don't recommend.

Â

You can tell them to do that to get this equal to zero to 14 significant figures.

Â

That will keep them busy for

Â

long time because excel only works to 11 significant figures.

Â

But most of us don't have a sacrificial helper, a sacrificial intern to help us

Â

do this so instead we're going to use a tool in Excel to help us solve this.

Â

So let's put ourselves in the sum of the PV cell or the NPV cell.

Â

And let's go to data What-If Analysis > Goal Seek, okay?

Â

And we're going to say Set cell, NPV cell, to value 0,

Â

by changing cell by changing at IRR.

Â

And you can see very, very quickly it converges to two significant figures

Â

to 8.7% and you can see all the significant figures there in the cell.

Â

Okay, so this should be working.

Â

IRR when it works, when it's applicable,

Â

and we've talked about that a little bit, when it is and when it is not applicable.

Â

When it is applicable, which it clearly is in this case,

Â

it should lead to the same conclusion for a project that NPV does.

Â

And NPV greater than zero clearly saying we should go ahead.

Â

IRR 8.7%, which is greater than their opportunity

Â

cost of capital, so that says go ahead as well.

Â

So, I'm going to just put down here, NPV greater than zero

Â

We can say that implies that we should go ahead.

Â

And down here, I'll just say IRR greater than the opportunity cost of capital and

Â

that also implies we should go ahead with the project.

Â

So that's how we typically compute things in Excel.

Â

In the real world, so I strongly suggest you try this on your own and

Â

become comfortable and facile with it, if you want to go on in real estate finance.

Â

Okay, so letâ€™s go back now to our slides and continue.

Â

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.