So, now let's evaluate this project. We know everything we need to know. We know all the cash flows for lender. We know all the cash flows for sponsor, for the developer. And let's go ahead and evaluate the project for each one of them in turn. So, we start with the comprehensive cash flows, we're working with the lender right now. And we wanted to use our time value of money discounted cash flow evaluation method, which for the lender is always going to be IRR. Why? That's the way lenders usually view these projects, okay? So, we get for the lender a monthly IRR of 1.36%, and we use our EAIR formula. 1.36%, most people don't think in monthly terms, so we want to translate that to annual. So their Internal weight of return annually from the EAIR formula is almost 18%. That's fine, that's pretty good for a construction loan. And that's nice, also a fun thing to look at is whenever we take a lender's comprehensive cash flows with everything except the fees, when we get an IRR from that it should always be the interest rate. And that's what it is here that is being charged to the developer. So, that's a good way to check your work sometimes, get the lender's comprehensive cash flows, go through do this exercise here, but then just take out all the fees and do the same IRR exercise, you should get the interest rate. Rule, whenever there's a loan with no fees, they IRR for the lender is the same at the interest rate. The IRR is bigger here with the fees. Why? Because of the fees. The fees increase the positive cash flows for the lender and drive up the lender's IRR accordingly. So, we have a summary of everything here for the lender. You can take a look at that. And all of the comprehensive cash flows in this case A and this case B are identical except here where we have no fees, All right. So, now let's do the same thing for the sponsor of the project for equity. We need to start with, of course, her comprehensive cash flows, right? We've developed those, can't do anything without those. And for developers, we're interested In NPV's. And I've computed the NPV the exact same way I showed you for Luke Oil in the previous module. Our review of mathematics of money and DCF project valuation tools here. So, one thing we always need to know is what's the firms opportunity costs of capital. Their opportunity costs of capital is about 8% yearly. We use EAIR to get that back to monthly. We know all the cash flows. We discount them at the monthly opportunity cost of capital or 0.64%. And we're doing that in this line and we add them all and get 50,000, and this is greater than 0 and this implies good project. We could also do an IRR for this. I don't like doing that because the developers cash flows are more random than the lenders. There might be a cash infusion unanticipated to have to put in in the middle of the project et cetera, et cetera. Which can cause problems as we know this calculation. None the less, they've got 1.13% monthly IRR if we compute the IRR, and if we annualize that they get about 14.5%. Okay, so that's about it. So, why compute the NPV for the sponsor, but not the lender? Lenders just always work with IRR. Lenders traditionally work with. And we want to compute the NPV for the sponsor because the NPV is bullet proof. Better for sponsor. The sponsor has more variable cash flows. There, it's going to be the contingencies maybe there's going to be a huge influx of cash. They may sell off a piece of this acre if they can, and then there will be a positive cash flow in one month. That mathematically would completely screw up the calculation of IRR and make it actually such that the IRR that they could compute that you'd get out of a computer would not be guaranteed to be the correct number. So, for all those reasons, better with equity to stick with NPV. All right, so what I'd like to do now is jump back to the spreadsheet for the Apex Financial Plan. And I am on actually a new page here, Apex-FP-DemonstrateNPV.IRR. And I want to jump back to the spreadsheet just to show you exactly how to compute some of the key numbers. Specifically, the IRR for the lender and the NPV for the sponsor, in excel, to show you how I did that. So, you can work your way down in the spreadsheet to around row 133, the financial evaluation of project for lender and equity, and I'm going to start with the lender. And what I've got here are the lenders comprehensive cash flows for the whole project as discussed previously, and now I want to get the IRR. So I'm going to do the same thing we always do in excel to get the IRR. I'm going to take a guest at this let's say, 5%, that's my guess. And now using that guess I'm going to take each of the lenders cash flows for each period, comprehensive net cash flow for the period, and I'm going to discount it back to T=0. So, there's the cash flow in period 0 for the lender is 0. So, I'm going to take 1 plus my guess at the IRR, and lock that down, and that's going to be raised to the to 0, because this is happening at T=0, and I better get 0, which I do. I think that formula is good, I'm going to go ahead because I think it's good as a nice general formula, and drag it all the way across. And I am seeing the present value version of all these cash flows is as it should be, less than the row of cash flow taken at the time it happens. So this row, as usual gives us our apple to apples T=0 equivalent for every single cash flow for the lender. And now we know for the IRR method that we have the correct IRR when the sum of the present values of all the cash flows is equal to 0. Ie, when the NPV of all the cash flows is equal to zero. So, I just want to sum these up now. Clearly I have something that is not equal to 0 here. So, I'm going to go back to what we always do, I'm going to go to data and I'm going to put myself in the cell I want to be 0. I'm going to go to what if analysis, goal seek, I'm going to say set that NPV to value 0 by changing cell my guestimate on IRR. And It tells me that my IRR in monthly time units, remember we're working with months now, is 1.36%. Another thing I'd like to show you here is that I have computed this down here in row 144, using Excel's IRR function. I do not recommend using this function or any other complicated function of Microsoft's for financial quantities. If you happen to omit a month or a year or something like that, you'll still get the right number up here, but this will give you the wrong answer and you won't know why. And your project won't work out the way you expect it to. And you'll get lawsuits and you'll be out of business. And you'll find yourself flipping burgers in McDonald's. So, that's not good. So, I always recommend going from first principles to solve for IRR and other financial quantities in Excel. Okay so, let's move down to he developer, or the sponsor, or the equity of this project. And as we know, the preferred method for evaluating a project for the sponsor, be it a development project, cash flow producing property project, doesn't matter, is NPV. So, what I've got here starting in row 167, I've got the sponsor's comprehensive cash flows for each month. That's their net cash flow that we assume happens at the end of each month. And to compute the NPV, I need to know the sponsor's opportunity cost of capital in monthly units. We were given, up above, that their opportunity cost of capital in annual units was 8%. So to get to monthly units, I just use the EAIR formula and I solved that for R given the EAIR for 8%, so you can see how I've done that, in this cell here, see 170. And you can also see it in my algebraic version starting in cell D170. All right, so measuring time in months, cash flow at the end of the month. Gotta use opportunity cost of capital in months so I have consistent units everywhere. And let's go ahead and get the present value of each of the sponsor's cash flows. So this is going to be, as usual, the cash flow itself divided by 1 plus the opportunity cost of capital. And I'll walk that down with a function of 4, or just type in the dollar signs. If you're using a Mac and I want to to raise that to the amount of time that's elapsed since T=0, beginning of project, which is 0. So I get 0 for that, I think this formula is good, I'm not sure, I'm going to be bold, copy it over to all 12 months and take a look. All of these are smaller than the numbers up above and always check the last one pretty carefully, O167, that's right divided by 1 + c, 170, that's right raised to the O165 which is 12. So, that's all looking good and now the NPV is just the sum of all of these cash flows on their apples to apples discounted to T=0 basis. So, I'm just going to say down here equal to the sum of the present value of every stinking cash flow for the sponsor. And there it is, $56,971. Also, I did use the Excel IRR formula to calculate the developer's IRR here. Again, not recommended for developers. NPV is always bullet proof for the equity sponsors of any project. Okay, that's it. So, I hope that's helped you understand how to compute these quantities that you see in the spreadsheet in Excel.
