Collaborate on Proforma Invoice in Excel for Real Estate with Ease Using airSlate SignNow

Proforma invoice in excel for Real Estate

hello and welcome to the definitive guide to Microsoft Excel for real estate what we'll be doing in this guide is stripping back the 500 some odd functions and dozens and dozens of features that Excel offers to you and hone in on the functions and features that actually matter to real estate Financial modeling in preparing for this guide what I did is I scoured the 70 plus real estate Financial models that Michael and I have shared to our library over the years models by the way that can handle nearly every scenario that you may come across in commercial or residential real estate and from those models I pulled out every Excel function used and I created this list this list that is now this guide this resulting list is exhaustive while there are duplicative functions that I've left out for efficiency sake so for instance I assume index match replaces vlookup or hookup in my view the Excel functions taught in this guide are the only functions you really ever need to learn to model real estate now to help you with this guide I've created a source file in Excel there's both a template and a complete version and then throughout this guide I'll go from one function or feature to the next I'll show you how to use it with a very simple example that is common to real estate Financial modeling I've also included a written guide with links to additional resources to help you master each of these functions or features and so with that let's get started first with functions so the first logic to discuss I almost hesitate to bring up because it's so obvious it's addition subtraction multiplication and division logic within our mathematical equations but no definitive guide is complete without them and they are so commonly used in our formulas that I figured I'd at least include them now in our template here what we what I've done is I have highlighted in kind of a Tanish the cells where we we're going to create this logic so here we have again for Section addition subtraction multiplication and division I'm going to take first I'm going to calculate total rent by going equals tenant number one rent or B6 plus and hit the plus key tenant number two rent or D6 I hit enter and that's our addition logic now subtraction logic here I'm going to take total rent equals B10 minus or subtract rent concessions and then I get net rent that's our subtraction logic then I have multiplication logic same goes equals in this case total net rentable area multiplied by and I'll hit the star key 10 or D4 hit enter we get total rent of 10,000 and then finally division logic I will calculate the total rent per tenant by going equals total rent forward slash key 2 so that is addition subtraction multiplication and division logic in Excel next let's talk about the sum and average functions now these functions are two of the most commonly used functions in Excel and especially in real estate Financial modeling and as their names describe the sum function Returns the sum of the values in a range of cells while the average function Returns the simple average of the values uh in a range of cells and their syntax is quite simple as you'll see here it it simply equals sum open parentheses and then a range of values or an individual value and I say value that's a cell reference and if you have cell ranges that aren't next to one another you would separate those ranges with a comma or you can simply uh separate each individual cell reference with a comma and that's the case both for sum and for average and you'll see this right now so the first is separating by comma so I go equals sum open parenthesis I'm going to select my first cell reference or range of cell reference in this case B8 tenant number one rent and I go comma and I choose my second number cell reference or uh range in this case that's d8 close parenthesis hit enter I get 10,000 so that is separating ranges or cells with a comma we can Al also just simply select an entire range of cells that are contiguous to one another so we go here and cell K12 equals sum now I'm going to show you a trick if you hit tab it completes uh the the the writing and automatically adds the parenthesis I'm going to select this range of cells that we want to sum in this case Ka through k11 close parentheses hit enter 5500 now average is the exact same only we use the term average so I start a r now at this point you'll see that that Excel uh autoc completes the function that we want average all I have to do at this point I wrote a r and I hit tab it'll complete that in open parentheses and I select my cells separated by commas hit close parentheses average rent and then the same here equals average this range of cells close parentheses enter and that is the sum and average function so with the basics of adding subtracting multiplying dividing and averaging values let's now turn to true and false logic in Excel now I should point out that true and false logic is the foundation of the conditional logic you'll be creating as part of your real estate Financial modeling most every statement you write will either have a true or false result and that true or false result can be used to build some very powerful and yet simple to follow logic statements or formulas now this form of logic is called bullan logic it's a concept concept I've explored at AC quite a bit and I'll include a link in the written section of this tutorial to see more about bullan logic but in short when a logic statement has an outcome that is true Excel assigns a value of one to that statement whereas when a logic statement has an outcome that is false Excel will assign a value of zero to that statement now in computer science terms bullan logic is a form of Al algebra in which all values are reduced to either true or false and so bullan logic is especially important to computer science because it fits nicely with the binary numbering system in which each bit has a value of either one or zero or another way of looking at it is that each bit has a value of either either true or false and so it is with the logic statements we write in Excel either there's a True Result which Excel will assign a one value to or a false result where Excel will assign a zero value to now let's turn to our workbook here and I'll show you how this works in practice so let's imagine that we want to model a cash flow for a sale at some future point so the current month so the month month we're in right now and if you were imagining modeling a a string of real estate cash flows you'd have month one 2 3 4 5 6 7 8 9 10 11 12 and so forth what we want is for this row of cash flows and we're going to call this sale price to Output zero on all sales or in all months except for in the sale month now in this case here we call salale month 15 and you've noticed that I've I've uh set the the cell font color to be blue under sale month and that denotes that this is an input and so we can change the sale month and that will change whether there is a sale in the current month or not so the statement goes like this right we're going to go ask is the month we're currently in equal to the sale month and it's false now if you recall false results in a zero okay so if we were to multiply this false by any other value the result would be zero because it's zero times any value now if we were to come over here and change the sale month to 10 now it becomes true now in this month there will this will result in a one value and we can multiply this one value by any other value and the result will be any other value all right so let's come back to 15 for a second let me show you the the balance of this so this is that true false concept and we can write any number of true false statements in Excel and either get a true or a false result now here let's write the same thing equals now in this case I'm going to wrap that logic inside parentheses so I do open parenthesis and I'm going to ask is the current month equal to the sale month Clos parenthesis now again I'll get a true or a false however if I multiply this logic statement that's the multiplication sign our star we multiply it by the sale price hit enter Because because the sale month does not equal the current month we get a result of zero however if we change the sale month to 10 then you'll see it outputs a 10,000 or in other words it takes a true statement a one multiplies that one by the sale price and it outputs a sale price but what's cool is any other month it will output a zero now that is true or false logic in Excel you'll be using these statements quite a bit in your real estate analysis so begin getting used to the idea that every statement has or should have a true or false result so with the concept of true and false logic out of the way now let's discuss the most commonly used logic statement in Excel and that's the IF function now I say most commonly used not because it's the best function it has its place and many times it is the best function but what you find is that beginners tend to use overuse the IF function and that's because it is the most intuitive logic statement that Excel offers and so as those beginners grow in their capabilities the use of the if statement persists and so it's important that you learn this statement uh but that you also find Alternatives and understand those Alternatives so that you're using the best function at the time now in terms of the syntax here how it goes is equals if open parenthesis and then you have a logical test now that logical test will result either in a true or false is it retail or not is it industrial or not does x value equal y value if the result is true you'll hit comma after your logical statement and then you'll enter either a cell reference or a value that you want to Output in the case where the logical test is true if the logical test is not true then the final portion of your your formula again uh separated by a comma between if it's true or false will be the result if it's false so let me show you this here what we have is a list of property types retail and office and a rent per net or yeah rent per net rentable area uh call this Market rent 25 per net rentable area for retail 15 for office we want to write a statement that says and here we have an input blue font cell if this is retail we want to Output 25 otherwise output 15 right and and what we're assuming is hey there's only one other property type that's office therefore we can write equals if our input is equal to retail okay and that's our logical test comma what's the True Result 25 otherwise comma value of false 15 and there we add that now what's the problem with the statement well what if this is say hotel well right now Hotel still gives us the office amount and and we want something different so that's where we're going to use now a nested if statement let's come down here and this is where it gets complex so equals if this value equals retail then what the cell here otherwise and now we add another if statement if open parenthesis another logical logical test if our property type input equals office then we'll output 15 all right comma otherwise let's just output zero okay close parentheses on that and we get 15 if it's office if it's retail we get $25 and if it's hotel for now we get zero now what is an alternative to this nested if and and the the problem with this nested if if you look up in here and this is just one if statement inside of a second if statement or two if statements in one formula it gets difficult to follow what's happening here so let me show you the bullan logic that true false concept we we learned in the previous lesson so we're going to accomplish the exact same thing here but without the nested F we go equals and we'll write a bullan logic statement open parenthesis retail equals or our our property type equals retail close parenthesis multiply that by this 25 okay so if our property type equals retail then the resulting bullan logic will be true and true true is equal to 1 we multiply 1 by 25 the result is 25 plus open parenthesis our property type assumption equals office Clos parenthesis multiplied by this 15 close parenthesis hit enter 25 when it's retail 15 when it's office and when it's anything else it will result in zero so the formulas themselves are if you look almost identical in size but the nested if is more difficult to follow and this especially becomes true when you get into three four five nested if statements it's almost impossible to to Really follow those formulas so that is if statements in Excel let's now move on to the next function so let's look at the two most important logic statements Beyond just our standard Boolean does one cell equal the next and these are and and or now an and and or statement yields either a true or false result uh how you use that resulting true or false depends on the situation and one common use of these functions is to include them within the if statement as part of The Logical test so in the case of an and function you might ask if two tests are both true if so return say x otherwise return y in contrast for the or function you might ask if any one of a number of tests is true and if so return X otherwise return y so the the logic goes equals and open parentheses and then you ask a series of logical tests so for instance you might ask does the value in cell A1 equal the value in cell A2 and you do comma the next logical test then goes does the value in cell B1 equal to cell value in cell B2 comma does the value in cell C1 equal the value in C2 close parentheses and now you have three logical tests and if all three are true the result of this and function will be true if any number of or any one of those is not true the result will be false in the and function the or function is virtually the same in terms of syntax only the result will only be false when all statements are false or in other words if any one of the statements is true wrapped in an or statement then the result of that or statement will be true so let me show you how this is used in practice I'm going to show you two examples of how I use it in real estate Financial modeling and the power of what I'm going to show you is that it really is another bullan statement but but a more complex bullan statement with the result true or false being either a one or a zero and then we can use that one or zero in a variety of ways so the first is let's imagine that we have a tenant and that tenant begins paying run rent in say month one and ends or stops paying rent in month 12 and we and so in our model we only want to Output values when the current month is somewhere between month one and month 12 and we can use an and statement to find out is the current month month somewhere between 1 or 12 so right now the current month is month 10 let's ask this so we go equals and open parenthesis and the first logic statement we ask is is the current month greater than or equal to all right you do a greater than sign with an equal to immediate with an equal sign immediately to the right and that is greater than or equal to the start month so that's the first question and month 10 is greater than one so we know this this first logical test or question will be a true we do comma is the current month less than or equal to the end month again we use less than and an equal sign to equal less than or equal to we close parenthesis and we know this will be true because both statements within this and statement are true now now if any statement becomes not true so let's let's take the end month to let's say 8 instead of 10 now this becomes false why well the first statement is true it is greater than the start month but the current month is not less than the end month and as a result we have a false within the an statement and therefore the result of the an statement is false now how do we use that in practice well again we can multiply this this value false or value true equals either zero or one here we go equals again we do the same thing and and we ask is the current month greater than or equal to the start month and comma is the current month less than or equal to the end month close parenthesis and then we multiply that our star key times our contract rent in this month hit enter and the result is a th000 that's what we be output in this month now if let's say uh the end month was month 8 instead of 12 now it will output a zero and it's using now an or with that buan logic now let's look at it or and with bullan logic let's look at an or statement and and this is another situation where I use or a likewise use bullan logic I could I could use an if statement here and wrap the if or the or into the if but I find the bullan logic to be simpler so the first question is we're going to ask is uh the straight line or I mean is this assumption either straight line or detailed and the the way I use this is um when I have a detailed or a straight line Construction forecasting module I output the same value and so I use an or so I just simply go equals or does this value equal the StraightLine forecast method or does this value equal the detailed straight line method and so long as it equals either of those it will output a true if it's scurve it will output a false or anything else right now how do we use that well again I'll write the same statement equals or ises our assumption equal straight line or does our this assumption equal detailed if so I multiply that value by 5,000 that's what I would output in the case of a straight line or a detail otherwise now I'm just going to ask a straight question does the Assumption equal yes curve and if that's true it will output a one if it's false it'll output a zero we multiply that by the amount related to the S curve here 2500 and now we get 5,000 so if we were to change this say to an S curve we get 2500 anything else will result in a zero so that is and and or functions let's move on to the next function so the next two functions the Max and Min functions are likewise used to eliminate the need to write complex if statements uh they're also used to to analyze large data set sets uh and really as as the functions say they return the maximum value or the minimum value in a range of cells so for instance here I have a list of rents and I want to know okay what is the the maximum rent in this a rent Comp List I would write equals Max that range Clos parentheses and the max in this case 75 now in real estate in addition to kind of analyzing data in this way Max and Min functions are essential to Waterfall modeling and so one example is here I'm going to use the Min function now and let's imagine that there was a required return of say a partner 10,000 in a given period and what we want to Output is that required return or how much cash is actually available to distribute to the partner all right and we don't want to take any more than what's available to distribute so we would use a Min so we go equals Min open parenthesis and we want to take the minimum value of either 10,000 B21 comma or the 7500 which is the available cash flow close parenthesis now what happens is let's say that there's 15,000 available to distribute only the 10,000 would be distributed in this case and there'd be 5,000 remaining that'd be distributed in in some other fashion or take this to 5,000 and it'll take the minimum value of whatever is in those range of cells so that's Max and Min all right so the next two functions the count if function and the count a function involve counting ranges so count if specifically counts the number of instances in a Range that meet a certain criteria so you might might count the number of times that the number one appears or count the number of cells that are greater than 17 as an example that's count if count a counts the number of cells in a Range that are not empty and this is helpful when you're getting large data set sometimes there'll be empty uh cells or rows in a large range you want to just count okay how many do we actually have and that's count a and and count if so let's go into first count if and instance where this might be helpful um the case of say a comp set so here we have a hypothetical what a five property comp set some are retail some are office some are multif family and we want to ask how many are let's say retail so there's couple ways we can handle this the first is we go equals count if we select the range and then we select the criteria the first option for selecting the CR criteria is we wrap the criteria in quotation mark So I do quotation mark and then we type let's say retail close quotation marks so three three times the word retail appears in this range but what about if we want that to be dynamic dynamic say to this value here right now if I were to type office nothing happens here so let's come back and instead of a value wrapped in quotation marks let's just simply link say to that cell and what it says now is count the number of times in this range that the value in this cell appears this in this case one we need to change it to retail three multif family will be one hotel zero so you get the idea that's count if now down here count a uh again counts a Returns the number of cells that are not empty in a range and here we have a rent roll and this is where this uh sometimes can be helpful it's a six unit rent roll and some of these tenants re received a rent concession and so we want to count how many tenants received a rent concession so we go equals count a and you'll notice that if they did not receive a rent concession the value is blank so we would simply select that entire range and this will only now count the number of cells that have a value in this case four what if we want to count the number of tenants that did not receive rent concessions well I'm going to use two count A's the first is I'll do count a I'm going to count our entire rent roll so these are the number of units in the building if I close parentheses you'll see there's six six units we subtract now a second count a using this Range close parenthesis and you'll see there are two tenants that did not receive a rent concession now as a quick little bonus let's let's look at count if uh number of tenants that received rent concessions but let's imagine that instead of having blank values we had a zero here now what's the problem with that now it looks like this tenant received a rent concession so what we can do here is we can go equals count if select this range and then the criteria is greater than zero so again we do open quotation mark greater than zero closed quotation mark and now it will only count the number of values in that range that have that criteria so that's count if and count a onto the next functions so let's let's talk irr and npv functions two Finance related functions in Excel that are common to real estate underwriting now the irr function calculates the discount rate at which the net present value of the investment is equal to zero uh what irr is is for a different discussion in a different day uh but nonetheless it's important to note that the irr function assumes that the cash flows of the investment are made in regular intervals now in the concept context of real estate Financial modeling that generally me means annual periods the npv function in Excel calculates both the present value of a string of irregular future cash flows as well as the net present value of those same future cash flows and the distinction is to calculate simply the present value of irregular future cash flows but again using regular intervals uh you simply leave out the time zero cash flow and I'll show you that in the lecture in the example here shortly to calculate The Net Present Value you can either calculate the present value and then subtract out your time zero cash flow or you can actually include the time zero cash flow in your function and out will spit The Net Present Value but again of a cash flow stream that's irregular but that has regular intervals so the the cash flows themselves oscillate or change over time but the intervals in which those cash flows are received or deployed are regular so let's let's talk about how in practice we actually do this so irr what we're going to do is we're going to have a string of starting negative and then and turning to positive cash flows you learn that when you're understanding uh cash flow modeling but we go equals irr of that range of values and it's that simple now excel in there in its syntax offers a guess now in some cases if you have uh cash flows that are revered stting from positive to negative back to positive and back to negative you will actually actually get multiple answers and so Excel includes this guess option where you can guess the irr and then Excel will choose the irr amongst multiple that are closest to that guess in most cases though it's unnecessary such as here where we simply go from negative to positive and the resulting internal rate of return or irr 12.75 5% now what about the npv function well I'm going to calculate both Net Present Value and Present Value to calculate net present value I'll use that same row that we use for irr equals npv and then it asks for a rate now the rate is the discount rate and when our discount rate is equal to our internal rate of return the npv will be equal to zero remember how I said the irr is the discount rate at which the Net Present Value investment is equal to zero well this will prove both that the function's working correctly and that that uh Finance concept is true so here we have the net present value is equal to zero when the discount rate is 12.75 now what about the present value well the present value here if we use a discount rate that's equal to our internal rate of return should be the cost of the investment in time Z 10,000 so we go equals npv choose our rate it says 12.75 and then the values instead of choosing this row we're only going to choose the positive cash flow so we we buy these positive cash flows with some outflow some negative cash flow right so we buy uh the investment for some outflow and we get these positive cash flows in return and sure enough at a discount rate of 12.75% which also so is the internal rate of return the present value is 10,000 or in other words we could pay up to $110,000 today for this future string of cash flows to earn a 12.75% internal rate of return so that's irr and npv functions in Excel so let's talk EO month eate xir and xnpv functions now now you might look at those four functions and say Well they're not related at all why are they com bined here however I've included them together because in the context of real estate Financial modeling they actually work in tandem now first the EO month function Returns the date for the last day of the month that is a given number of months before or after some predefined start date the eate function Returns the date that is a given number of months before or after the predefined date so really what the difference is EO month Returns the last last day of the month eate returns just some num some date some number of months before or after your predefined start date and really in real estate that involves your header so your cash flows happen in some periods and that period should have a date assigned to it and each period should be the same number of days apart from the previous period so when you have a header that has an end of period date that would be the last day of the month generally and therefore you would use an eom month function to find out okay what's the last day of the month next month and you would calculate that and you do that each month thereafter eate Works often when you have a uh date header that starts the beginning of the month the the first day of the month and you would use an e dat function so you would have okay uh January firstus today what's the first day of the month next month you can't just add 30 days or 31 days because you have an irregular number of days in each month and so you would use an e date such that the the date for the next period would be exactly one month ahead of today's date so that's e date versus e month now why they matter is because xir and xnpv given that those two functions are meant to accommodate irregular cash flow intervals which in a real estate context means non-annual periods generally monthly periods or quarterly periods and because they're meant to accommodate irregular cash flow intervals they require a date string right a range of dates that are assigned to your range of cash flows and therefore we need to use the EO month and the E date to build that range of dates to go along with your range of cash flows a mouthful so let's get into how they work in practice so here we have an eom month function the syntax goes equals EO month oops e month and it first asks for a start date so what we have is a start date here January 15th that is a reference date when when I say start date and what we say is okay we got this reference date uh January 15 2022 what is the date that is two months from now but the last day of that month so this is going to go out two months so to February March but then it's going to take the last day of the month in March right so well first we need our start date and then I do a comma two months ahead in this case or some number of months ahead close parenthesis let me expand that okay March 31st 2022 now you'll notice when it did this what that is saying is it's not an error just means that your sell isn't large enough to view the result so we can come up here double click on the column and that will widen it out large enough so we can see so a date that is two months into the future but the last day of that month is here March 31st 2022 let's say if we did 12 months into the future that's now going to be the last day of the month of January uh what about the last day of the previous month we would use a negative one that would be now December 31st 2021 so that's EO month now eate again is similar syntax eate we choose a start date and then we choose a certain number of months before or after that date and it will simply output a date that is exactly that number of months into the future or into the past so again we could do minus one that would be December 15th we could do plus 12 that would be January 15 2023 so with our EO month and our e date we can then build a cash flow header now how this looks let me turn those tan for you is we have some start date so let's say that our time zero cash flow or the period at which our analysis begins it's the period where we go out and we buy some string of future cash flows and then the next period where we receive inflows for or in exchange for that outflow we're going to use so that's our that's our anchor our start date and we go equals EO month we take that date and we go for forward one month okay that gets us now the end of period one what's the end of period two well we can just actually borrow this formula and copied it to the right and now it's taking the previous date no longer our start date but the previous date and it's carrying it Forward one month we copy that all the way out to the right and now we have a header and with this header we can calculate an xir now you'll notice we also have our net property cash flow line negative cash flow followed by some string of positive cash flows and then we use an x equals xir R open parenthesis and it first asks for the values so that's that string and then we need a corresponding range of dates and this range must be the same size exactly as our values why because now Excel assumes because this range here this range of dates is the same size as our range of values that this - 10,000 corresponds to January 1st 2022 the 75 corresponds to February 28 2022 all the way out here such that our reversion 10,575 corresponds to April 30th 2023 we hit enter the internal rate of return of that string of cash flows compounding at the end of each period 13.55 now what about xnpv well again we have our date header here we have a discount rate which the discount rate happens to be equal to our internal rate of return and now I will calculate both the Net Present Value as well as the present value of the string of cels and there's one little nuance we'll get into uh first let's go npv so we go equals xnpv and the rate is whatever discount rate we choose in this case I'm case I'm going to choose the irr and that way you can see that the net present value at that discount rate will be zero the values again starting in Time Zero January 30 January 31st 2022 and it includes both our negative and our positive cash flow flows comma and our dates and again our those dates correspond to the cash flows close parentheses and we'll get a n net present value of zero now what's the present value again the present value is when we when we're looking only at the cash flows that we're buying using those negative cash flows or that time zga - 10,000 and to do this the one Nuance is we have to include a Time Zero date and cash flow only that Time Zero cash flow will be equal to zero so before we take into account our investment cash flow so we go equals n xnpv open parenthesis we use the same rate but this time the values are that line just operating in reversion doesn't include our negative values and then we have the dates that correspond to that and we get a present value of 10,000 which equals the amount that we that we spent to buy those cash flows now again the Nuance here is because we're using dates it's important that we include our first date as our time zero date and but that that value is zero or that doesn't include our investment cash flow value so that's uh EO month e date xirr and xnpv so now let's look at the sum if and sum product functions now these are two functions that uh expand on what you can do with say a sum function and these functions I use a lot with rent rolls in rolling up cash flows in calculating weighted averages uh the sum IF function sums the values in a Range that meet a given criteria uh so let's say you want to in in the case here sum up all uh the rent for all two-bedroom units and sumith will do that in one formula sum product calculates the sum of the products of two or more corresponding ranges so let's say you have one range and you want to multiply the values in that range by a corresponding range and then add up the the sum of those some product will do that you can also uh multiply multiple ranges so three ranges and each each corresponding value across those ranges multiplied by one another and then sum the total uh of of those products so uh that's sum if some product let's look at it in practice so here I have that example I was mentioning where we have some criteria and I've left that a blue font cell here and that's a unit type and this is that criteria range and then we have some value that we want to sum up this is our sum range and in this case it's rent so what I'm saying here is I want to sum any value here that happens to be TW bedroom and I could change this to studio and it'll only sum up the one the the studio rent or I could put one bedroom and only sum up the the one bedroom rent to do that I'll go equals some if then I choose my range now the range is the criteria range like so so it's asking okay where in this range might the criteria appear I do a comma and I choose what the criteria is and I can either as I've done with the count if open quot marks and just type say two bed or I can link that to an actual cell that has the criteria in this case F9 now if the criteria was also the sum range I would be done however in this case the criteria is different from the sum range and so I'll do a comma I'll come out here select my sum Range close parenthesis and hit enter now you'll see that this sums all two bedroom units and I have one two two-bedroom units 1,250 each those sum up to 2500 I could go to say one bed and there's only one that's 1,000 I could do a studio 1800 and so forth so that is sum if now Su product again what it's doing is you have two or more ranges and they're ranges of the exact same size and because there are corresponding values in each range that will be multiplied by one another so what this is going to do before I even write it move this out to the right this is going to say okay range one the first value in range one multiplied by the first value in range two okay that's a 1,00 then it we'll do that math for each of the values second value time second value third value time third value and so forth and then it will sum all of those up so we'll take all of those values that have been multiplied uh by one another and then add them up and in this case 6,800 so if we come over here and we type equals sum product and we choose our arrays this is our first array or range comma we we choose our second here and again they have to be the same size because we'll be multiplying corresponding values in each of these ranges close parenthesis and you'll see the total 6,800 the same as in our test so that is some product and some if so now let's talk about PMT and PV functions PMT meaning payment PV meaning present value now the payment function in real estate is used to calculate the amortising loan payment of a mortgage loan or uh some other loan type um instrument the PV function uh largely is used also in debt underwriting in real estate to calculate the remaining balance of a loan at any given point in time of the loan so uh you generally don't use the PV function in other ways because the PV function assumes a constant payment and real estate rarely has constant payments outside of a debt instrument uh and and so if if you do need to calculate the present value you would use the npv function as we've discussed earlier in this guide so in terms of payment uh what you'll need in order to calculate this is you'll need the interest rate that's a constant interest rate and the in and that's the interest rate for the period in which the payment is being made so if you have a monthly payment it would be the monthly interest rate so let's say that you are looking at a mortgage loan with monthly periods meaning monthly payments but an annual rate you would take that annual rate you divide it by 12 and that would become your monthly rate or your periodic rate so you'll need the rate uh you'll need the number of periods over which your loan will advertise so what that means is uh let's say you have a 360 period loan that's a 30-year loan with monthly payments that would mean that the loan balance over that 300 those 360 periods would go to zero that means fully amortized right and and so the payment that this will output will be a payment that will get you to a loan balance of zero at the end of your periods and then finally you'll need uh what I'm calling here interchangeably present value and Loan balance and they are one and the same and the reason I'm using the term present value is to line up the PV function and show you how the PMT function and the PV function are actually related uh they're two ends of the same calculation so here we have a loan balce so let's imagine we we take out a $100,000 or peso or pound loan uh we have a periodic rate of 3% and 30 periods so this these might be annual periods with a 3% rate and 100,000 uh loan balance that means there would be one payment per year and over 30 years this would amortise down to zero we would go equals PMT open parenthesis and it first asks for the rate here then it asks for what they call the N per that's the number of periods uh of uh to get to a zero balance okay so if it's a 30-year loan and you're doing and you have annual payments then it would be 30 if it's a 30-year loan and with monthly payments then it would actually be 360 and this periodic rate would be 3% divided by 12 but you get the idea and then finally to ask for the present value okay or the PV that is the loan balance now if I were to just simply select this positive value 100,000 for my PV and close parenthesis the payment will out actually output as negative and that's because in finance you either have inflows or outflows and so so if you have a a positive present value today meaning you you received an inflow of 100,000 today you'd actually have an outflow of 5,12 each month over over the next in this case 30 periods and that would yield a 3% rate to whomever lent us this 100,000 in Reverse let's say I reverse this and I made it positive the from the lenders perspective perspective it would invest or lend 100,000 and then receive 5,12 for the next 30 periods and over that period would receive a 3% rate each period plus receive its 100,000 back now when I when I build my PMT I generally add a negative sign at the front and that's just so I can see all of these values in positive and I don't have a negative value that I I then have to incorporate into my model and it complicates things so that's why I added the negative on the front so that's the PMT function now the PV function uses the same inputs rate period but instead of present value it uses the payment okay so what we're doing here is we're saying okay what is the remaining loan balance the present value or the loan balance when there are 30 periods remaining with this payment and this periodic rate so we go equals PV the rate at 3% periods or n per 30 and what's the payment 5,12 we close parenthesis and you'll see now now negative 100,000 or in other words if we invest 100,000 and we receive payments of 5,12 for the next 30 periods with a with a periodic rate or return or an interest rate what however you want to calcul call it a yield of 3% per period uh we we would have to we would invest 100,000 in order to get that or in other words from the from the perspective of the the lender the we lend 100,000 or from the perspective of the borrower our loan would have a outstanding balance at this point of 100,000 now I'm going to add a negative sign at the front of this just to clean that up now something that's cool is what is the outstanding balance of this loan let's say when there are 15 periods remaining all we have to do is change this to 15 and the remaining balance is 6,96 so you can imagine if you're modeling real estate and and you have a 10-year analysis period you could quickly calculate the remaining loan balance of some loan that's on that property at that point in time uh using this calculation and what you'll find is a lot of um newer real estate Financial modeling professionals they'll actually build a full amortization table in order to calculate this something that could quickly be calculated using the PV function so that's PMT and PV functions uh let's head on to our next functions now it's an important discipline in real estate Financial modeling to be constantly checking your work we call this air checking it's it's checking that the formulas you're writing the logic you're using the layout you're you've built uh is largely free of errors or at least free of errors that may adversely impact your investment decision and there are a variety of techniques for air checking some that involve functions some that involve not using functions but just good discipline uh and two functions that I use in my air checking are the if air function and the is a functions uh and if air returns a value you specify if a formula results in an error uh if if that formula doesn't result in an error then the result is simply whatever the formula results uh the is eror is a logic statement that returns either a true if the value WRA your is a function is an error or a false if it's not an error and and you'll find uh that both have their purpose and it just depends on what you're trying to accomplish so let me show you what I mean um the first I'm going to show you an example of an if a uh often times you'll be dividing by numbers and those numbers could be dynamic so let's say you have a rent roll and you have a table on in your summary that calculates the percentage uh of one bedroom two bedroom three bedroom and four bedroom it's a fully Dynamic model meaning you you could have scenarios where you have uh 50% one bedroom and you also may have scenarios where you have no one-bedrooms or in other words you're dividing by a zero and if you recall from math you can't divide anything by zero so for instance if I were to take 10,000 and divide it by zero the result is an error and that could flow then through our entire model and and so you have one air like that and it it ripples across the model so what I'll do here is I'll use an if eror to solve this issue so I go equals if eror and this is rent per tenant so I take the total rent and we divide it by tenant and that's the value that's the formula if that's an error so I go comma what do I want to Output maybe I just want to Output a zero and now it this Cell results in a zero it's a value that doesn't create Ripple errors across my workbook but if this say had two tenants now it calculates properly okay so that's if error is error allows for um multiple results if it's an error or if it's not an error and it's not NE and the result is not necessarily the formula if it's not in error let me show you what I mean so again this is that same uh logic rent divided by tenant we get an error so let's go equals if so I'm going to start with an if statement if is err and if you recall if statement the very first portion of an if is a logical test so the first logical test is I'll ask does this formula result in an error right if it does comma what do I want to have happen well this cell I'm calling an air check and you'll create these along the way where what you're trying to do is alert the user if there's an issue with something that they've done it's a user experience piece it's also a way for you to catch issues and so here I'm going to say okay if this is an error I want this to Output the word error otherwise I want to Output the word okay okay now it tells me that it's an error and by the way I could then use conditional formatting to make this cell turn bright red in the event of an error otherwise leave it white maybe with a green okay so if this were to say be a two now it's not in error and the result is okay that's is air and and you'll see me using that largely part of my air check modules that I build um that that output either an air alert or an okay um and that's if air and is a functions the next two functions to discuss round and round up I use in real estate Financial modeling both for air checking I'll use the round function in my air checking formulas uh there's a Nuance in Excel where Excel only outputs up to 15 places in a number and so if I want to check that two numbers result in the same value occasionally you'll have a number that exceeds that 15 and and therefore it will dis play an erroneous error and therefore I round to a certain number of digits uh I also use round to simplify the results that I'm showing so let's say I have a report and I want to round to the nearest Value or even round to say the nearest thousand uh then I'll use the round function round up I use actually to build year and quarter headers when I have monthly weekly or even daily uh cash flows and and so there's a very simple logic using round up to find out in what year or in what quarter is a given month or a given week or a given day so those are round and round up let me show you how this works in Excel so first round the syntax goes equal round uh you choose a number and then you say how many digits do you want to round that to so for instance here I have a budget number that results in 12,112 344 and uh for whatever reason I I want the actual value rounded to 12,000 okay um now you can do this right so I could remove the decimal places from the visualization now if you see they're still there but Excel is only visualizing to um the nearest one but but those values still exist in the cell and so if I were to multiply this by anything else I would not be multiplying 12,000 And1 by some other value i' be multiplying 12,000 1.12 34397 that long thing there so let's go ahead and first I'll round this to the nearest one oops let's do that so go equals round choose that value and I'll say zero digits okay when I rounded the nearest zero and uh let me widen that cell you'll see that it rounds to 12,01 and no decimal places now if I were to say two that will give me now two decimal places if I were to go neg2 it actually now rounds to the nearest 100th and that's really what I wanted to do I wanted to round to the nearest 100 uh so that this budget line item was rounded to a nice round 12,000 and and for I found for visual purposes especially when you're dealing with tens of millions um when when when you are going you know you got 10,3 98,3 whatever um it's it's tedious to look at and so you can round say the nearest million or or the real the nearest thousand and so forth so that's round uh next we have round up remember how I told you there is a very simple Logic for rounding or to calculate the year in which say a month is in and that's a Roundup now first let me show you how the Roundup works I'm actually going to first use this budget number so let's again here equals round up and I just simply want to take this number round up to the nearest zero decimal places now of course it rounds up to 12,000 and2 because it's slightly above 12,01 and therefore it R rounds up to the next whole number in this case now if I were to say round up with two decimal places it's going to be 1201.1 1 3 13 and if you recall when we rounded it here was 1 two that's because that rounded to the nearest uh hundredth in this case it rounds up to the nearest H hundredth now let me show you now this logic here I have this month 13 I want to find out in what year is month 13 well we know month 13 is in year two and we can find that out by using a round up equals Round Up open parenthesis I choose this value and now I divide it by 12 and in dividing it by 12 it will get to one year and some remainder and that one year and that some remainder means it's in year two I go to zero digits and the result is year two if I were to change this to a blue font value and let's say Okay month 25 is going to be year three right but but month 87 that's year eight month 98 that's year nine month 37 that's year 4 okay so that's the Roundup function and the round function so now let's talk index and match there are two functions that in this context I use in tandem to perform lookups now if you're familiar at all with lookups you may have heard of V lookup or H lookup these are companion or or sister functions in Excel that perform lookups but they are highly inefficient both from a processing power standpoint and from an auditing standpoint meaning it's very difficult to to audit an H lookup or a v lookup index and match more efficient from a processing power standpoint and much easier to audit and so this is my preference for lookups now what what do I mean by a lookup function well imagine you have a table of rents Broken Out by City And property t type so you could identify okay what is the rent in a given City for a given property type and within that table you could identify that rent furthermore imagine you wanted to dynamically output into a completely separate cell the rent in a user specified City for a user specified property type well this combination of index and match makes that possible and I'll show you how first though let's talk about independently the index function and the match function what they do and how they work so first to index the index function Returns the value of a specified cell we've already determine that and the syntax goes equals index open parenthesis and then you select an array or a a range of cells and then within that range of cells you identify the row number and the column number that gets you to the specified value that you want to Output so for instance here I have a range of values uh and this is a table where you see New York retail is 100 and this is rent per net rentable area by property type Tokyo industrial is 30 and so forth and what we want to do is we want to dynamically be able to come in here and say okay let's grab London office and it will output an 80 or London retail and output a 120 so to do this you start with an index fun function so I go equals in index and we'll stick for now just with the index open parentheses we come over we select our range okay so that's the range that we're going to be searching for and we'll search within rows first and then within columns so I go comma and it asks in what row number is the value that you want to find well for visualization purposes uh I've identified the property type and the city that we want to locate so here we want to find retail well where is retail retail is Row one right office row two industrial Row three so we go we enter a one and then we go comma and then it asks for the column number well the columns are this direction New York column 1 London column 2 Tokyo column 3 so we want London London's column two we enter a two close parenthesis hit enter and sure enough outputs a 120 which is Row one column 2 where those intersect is 120 so that's the index function now what was the problem with this we manually entered one and two so it's not Dynamic at this point so match is where we bring in a dynamic element match is going to find the row and column for the values that we're looking up but but first independent of index let's look at how match works so here we have the columns London New York London Tokyo columns one two three and here we have the rows retail office industrial retail Row one two and three and and so how match works is it searches for a specified item in a range of cells and it actually Returns the relative position of that item in the range so for instance if we were to choose this range in our match function it has three values and they're in order 1 2 3 so if we were to look up using the match function retail the match would output a one if we were to look up industrial it would output A3 it's third in this range likewise for our columns this would be our range New York uh c19 through e19 and if we were to look up say Tokyo it would output one two it would output three that's where it falls within this range the relative position of that item in the range so let me show you how this looks let's go equals match and it first asks what's the lookup value well the lookup value is retail we want to find retail within some lookup range and it ask for the lookup array or the lookup range come over here and this is our range and so it's going to go out to that range it's going to look for retail within those three then it asks for a match type and this is important uh you can choose an exact match and that's what I always almost always do or you can choose less than or equal to that match or greater than or equal to that match and that that's for values and again I I rarely use it I think there's a model or two where I use less than or greater to otherwise if you choose an exact match which is zero and I close parenthesis if that value is not found within the range it will give you an error and then you might use your if air to return something uh that doesn't air out your model or You' use data validation in this cell here to ensure that the user only enters one of these three and we'll talk about data validation in a future section so here we have retail and this match tells us that within this range retail is has a relative position of one now what about this column portion now uh the match doesn't know whether you're using a range of columns or a range of rows it simply looks at the entire range and says what's the relative position of the lookup value so the same will happen here we go match open parenthesis choose our lookup value choose our range in this case it's here and then it asks for the match type again we put zero close parenthesis and London Falls number two in this range okay that's its relative position so what you'll you've probably noticed now if we look at our original formula with the index we entered by hand hard-coded one and two when we in uh when we include a match into that function you'll see the match returns one match returns two so what we can do let's first just do an index match and an index match only has one range all right um so for instance we go equals index we choose our range and then it asks what row number is the value well since there's only one row there's not multiple rows we just simply enter one all right and there never will be more than one one row so this is still Dynamic then it asks what column number well it's London and London's column two but what if we were to change this to Tokyo or to change it to New York we want this cell to dynamically change and therefore we'll use a match match will ask for a lookup value we choose London It'll ask for a lookup array well it's that lookup array or that range and what's important is that the size of the range or at least the size of the columns the number of columns in both of those ranges is the same and that they match up that's really important right because New York needs to be above 100 London above two 120 Tokyo above 150 so that when this match outputs A2 the index grabs the second value in its respective range so then we go comma and the match asks for a match type we choose exact match or zero close parenthesis twice now you'll see it outputs the 120 and if we change this to say New York the match in this index match combination we'll look up New York in this range it will identify it as um relative position one and then output a one in our index function and one in the range of our index is 100 all right now we can combine index with two matches a match for the row and a match for the column and that completes this original logic I showed up top so we go equals in index open parenthesis we select the entire range now which includes multiple rows multiple columns and when you have multiple rows and multiple columns in order to make this fully Dynamic we need to choose two matches the first is for our rows and our rows are property type so we select the property type lookup value and then the property type range and then we choose the match type is zero clck close parenthesis and then comma Now we go to the column number again a match our second match lookup value now is the city lookup range is that range of cities comma match type is zero close parenthesis now we get a 120 which is retail London 120 we could change this to office office London 80 what about office Tokyo 100 and so forth so that is index together with the match function uh to perform Dynamic lookups in Excel so to wrap up our section on functions in Excel let me talk now about concate logic now concatenation is a computer science term that refers to joining character strings together and in real estate Financial modeling how we use this technique is to combine say disparate values or inputs into one uh you know more appropriate string of values and the the most common example of this is addresses so you generally want to separate out your inputs for addresses so that you can use those inputs in various ways so it'd be interesting to know okay what's in New York the city what's in New York State uh you could you could separate things out by zip code but then let's say that you want to display a full address address city state ZIP code all together and you don't want to require the user then to re-enter those values in that long address full address form concatenation allows you to do that uh and so let me first show you now I should mention that there are two ways to concatenate in Excel the first is to use actually the concatenate function uh I prefer though to use the second second option and that is simply to use the and sign in between values to join those values together so let me show you this first so we go equals and we choose the first value it's actually a cell reference cell C7 and in cell C7 are the word is the word race and I want to combine that with the value in cell C8 so I go and ass sign and then I simply choose the value in in C8 which happens to be car when you combine the value in C7 and the value in C8 the result is race car all one word combined now you'll notice that there's no space in between those values it simply takes them and smooshes them together into one and there's a lot of ways that you may use this but again in real estate Financial modeling most commonly it's for things like combining addresses or let's imagine that you want to create Dynamic headers and you want always some V some label to appear in the header maybe the the name of the property but then you want the name of the property uh appended with the name of the worksheet you combine those together using this concatenation technique now let me show you though so the the issue with this first one right is that there's no space in between race car and you can put race car together and that is a word race car I together I believe I Googled it and I found them together but let's imagine that you do want a space in between those two words well here I'm going to build out the um address and to build out an address you're not going to combine these all together right because if I were to Simply go C15 c16 c17 and c18 you're going to get them combined but there's no spaces between address city state and zip code and on top of that in address convention you have a comma from between the address and the state and the state and the zip code and so we need to combine or we need to add both Spa a space in between each and a comma Now to do that if you think about it we can put a and sign between anything and it it will combine those and so what do we need to do well we need to add a space and a comma between each of these cell reference