Discover the Excel Pro Forma Template for Export that Streamlines Your Documentation

Excel pro forma template for Export

do you need to decide on the best residential property to invest in download our free residential profor spreadsheet in Microsoft Excel or Google Sheets to quickly generate the cut of wealth gains internal rate of return and break even point for any residential property so you can make the best investment possible it comes with a comprehensive Pro fora sheet charts to track key investment metrics an amortization sheet that calculates their monthly loan payment for up to 30 years and more now let's have one of my colleagues show us how to use this spreadsheet the spreadsheet contains a chart sheet that will visualize your 10year return on the real estate property it will show you a a rent Revenue by unit your Equity upside your 5year and 10year breakdown of your wealth gained it will have your net operating income along with any graphs related to your loan as well as any cash reserves that you might need to get started go to the decision sheet and calculate the average Regional grm we've created a little small tip so that you can calculate this for you or find it online then enter all the details about the property that you're looking to purchase at the bottom of this decision sheet you'll have the analysis of your cash flows your principal paid your Equity upside after uh one year and you'll also have the IR and the wealth gains that you will receive after a 10year hold if you must have cash reserves we make that very visually clear so that you understand that you have to put Capital aside for the expenses of the property the performer sheet will show you the performance of your property across a 10-year period you can enter the rent of the units in the property up to four units and you will then be able to visualize the cash flows the tax savings the principal paid the equity upside of the property lastly the amortization sheet will show you a month-by-month balance of the principal interest that you are paying towards your loan so that you can visualize and and see all the numbers very clearly laid out if you're creating this analysis for a client we've put together this appendix prepared buy sheet where you can enter your contact details your log on your photo so that your clients will know how to contact you now let's get started and let me show you how you can use this spreadsheet here I am looking on red fin for a property that has four units so I'll be debating between this $1.5 million property and let's say this $2 million uh property so I'm going to go ahead and enter their data okay all right so I've entered all the details for this property that we found here uh randomly on uh red Fin and the first step um to customize this spreadsheet to your region is to C calculate the average Regional grm but basically the grm is the price divided by the yearly revenue of a number of similar properties in your region so in this example uh we take the price of this property we divide it by the expected yearly rental revenue and the grm might be 16 um for this other property the grm is 15 and you might do five or six properties and then you average those grms to find your original grm this is the only number that you need to change in this model to bring the model to your location to localize it and as I enter different values and I'm sure about that I've entered them correctly I can click on this validated box so that I remember that this is data that I've validated and is is trusted as opposed to just a random guess that I'm just throwing trying to throw in to better understand the investment now if the property is are rental as it is here or at least they have a projected rent which is always an overestimate but regardless you can click on the performa sheet on the performa sheet you can enter the rent for each of the units so this particular property only has two units it is a duplex so we're going to enter zero for unit three and unit 4 and we're going to enter the projected rental uh monthly rent for units one and two this will automatically give you the total yearly Revenue that you're looking to generate from this investment there is a lot of different costs associated with real estate one of them is property taxes now if you live in a state where the property tax is calculated from the purchase price you want to click this check box or write true if you're using Excel so that we use what's called a static property tax for example the state of California in the US is a static property tax the property tax is calculated based on the purchase price and the state will then increment the purchase price by 1 .2% however if you live in a state like Florida Florida's property tax is based on the yearly value of the property so you want to uncheck that or in Excel you want to write false so these numbers are correct for the State of California so I'll continue going down and here you want to enter the expenses that are a percentage of your gross revenue um for the property this includes Property Management vacancy eviction costs Etc I expect to have a 2% vacancy I know property management is about 5% in the area and the eviction costs I'm just going to put 1% of gross revenue these are of course estimates then you want to write the dollar per year fees so for example you might have an accountant or uh an attorney that they'll have a yearly fee for the property so you want to write that in and lastly you want to write all the expenses that relate to the property itself these are building expenses then you have insurance and if you as a landlord are paying utilities you want to include how much water or sewage gas electric internet contractor repairs and future uh improvements on the property you're going to include these expenses are very important that you write them down accurately but after you write all your expenses you're going to be able to calculate what's or the spreadsheet will calculate for you what's called the noi the net operating income the net operating income basically means if you were to have bought the house this property with all cash not Al loone nothing just straight up cash how much profit can you expect at the end of the year now the cap rate will will be calculated out of the noi and we have little notes Here on the right hand side to help you most most Residential Properties are purchased with a loan so there is a financing section here you want to enter the percent down payment that you want to put in and the interest rate that you're going to expect uh to receive interest rates recently went up so we're going to actually write 4.5% and the amortization is the number of months that you're going to have to pay back the loan typically it's a 30-year loan so it's a 360 month period now for many purchasers that pay a down payment less than 20% there is a insurance called PMI that's very typical in the US but in other regions like in Europe or Asia there might be something equivalent if you pay for example 10% down payment the PMI will kick in PMI is an insurance that you're that you have to purchase if your down payment is below a threshold in this case 20% because I'm going to put a down payment of 30% the PMI goes to zero which makes a lot of sense and there's some notes Here lastly if you want to add extra payments so that every year you expect to have an extra $500 because you want to pay off the loan much faster you can enter that amount here finally we have very standard uh loan fees you should probably speak with your bank or your mortgage or your lender um to ask what their loan fees are what their closing costs are as a percentage of the loan and if you're going to end up using an escrow company what is their fee per square foot a rentable square foot so these are all the fees associated with financing and actually making the transaction as it relates to to the loan but there are other one-time costs that you also want to include in your purchase for example any inspections that you need to generate during the due diligence process if you invite a contractor a plumber an electrician to assess the state of the property you want to include those costs and also you might have to pay for an appraisal which is a third-party company that will value the property for the lender so these are important details you want to include that if you need to do any any building fixes such that you can meet the conditions habitable conditions of your region you want to include that here or if you need new appliances Etc you can always add new line items to any one of these sections what you want to do is you want to add the line item above the last row so we're going to insert an extra row above here and we're going to enter some expense for example um roof fixes because let's say in this particular property the roof is bad the reason you want to add it above the last row is so that the sum formula continues to include that in your total otherwise if you add it to the last row the sum function won't included now after you enter the General Building fixes and the new appliances you will have the onetime cost total which basically everything gets summed up in the closing Capital section which says how much Capital money do you need to carry out this transaction this might be higher than your down payment as you may notice the down payment here is 477,000 but to actually complete the transaction it might be $487,000 lastly we have this tax savings region where in the US and in some other countries you can depreciate the building the property not the land but the building across a set number of years in the US it's 27.5 years so that will give you some tax savings which is a type of a liquid gain that you will have and we're going to show in the performa how that tax saving affects the finances and the irr lastly we have a summary section here so you can see on the first year how much cash flow you're going to win or you're going to lose in this particular particular example I've put the data in such a way that you will have a negative cash flow that ultimately it's going to force you to have banking reserves so that you don't go bankrupt or foreclose the property and this is a feature that we've added on this spreadsheet resource because we want to make sure that if you're buying a property that's cash flow negative that you understand the cash reserves that you must have now let's continue and see exactly how can we make this property work as an investment they project that the rent is $3,500 for both units but what if hypothetically you could charge $5,000 let's say you will see that the cash reserves will probably disappear and your cash flow is going to be positive now the great thing about this spreadsheet model is that includes a tremendous amount of charts so you can visualize what is going on you can see your cumulative loss gains you can see your cash on cash and I'm going to go into those details but you can basically see a visual representation of what your 10-year breakdown of wealth gain is going to look like like versus your 5year wealth gain uh representation you can also see your Revenue versus expenses your noi across 10 years uh your amortization uh your loan payment and interest payments and ideally a cash Reserve chart which Ina this case it is now zero now let's go into the performa in the inflation or CPI row you want to enter what's the expected inflation that you're going to have in the next 10 years if one year has a particularly High inflation you can enter that number and it will be highlighted so that you can remember that you edited it if you want to undo that you can always copy the formula next to it and paste so that it it um it goes back to the default value which is 2% the same thing applies to rental increase what is the percent of rent increase that you expect in the coming years rental upside is the percent of gross revenue that you're looking to increase because you're doing a value add play or you have a long-term tenant that moved out that now you can retrofit the partment or unit so that now you can rent it for a higher price so for example we have a tenant that we know is going to leave in 3 to 4 years and when they do that we will be able to increase the gross revenue of the property by 5% when that occurs so you can enter that number here you can also enter values related to other revenue streams from the laundry room from storage parking fees late fees animal fees any other fees as well and if you want to add any particular Revenue Source you can again enter a row somewhere in the middle and enter those those values here is the property tax section where we calculate what is going to be your property tax whether or not you have a static or dynamic model and lastly we're going to calculate your net operating income and your cap rate for all the years for the next 10 years all of those numbers will go into this section here which we call the liquid gains per year positive cash flows are a type of a liquid gain because it's cash goes in your bank the tax savings that you can generate by depreciating the value of the building over the next 10 years it's also cash that remains in your bank so we take that sum of the cash flow and tax savings and we bring it to the present value so taking the inflation that you gave us up here in row number nine we bring to the present value the liquid gains we do the same thing with the Equity upside the equity upside basically is calculated by looking at the difference between the original rent and the current rent as rent prices increase and it is multiplied by that Regional grm that you calculated initially this model basically will look at what is the equity upside based on the rental increases that occur in the area there could be other Equity upsides as well but for this model which which is a pretty simple model we're only looking at rent we're also putting into the non-liquid gains the equity upsite but also the principal paid every month as you're making mortgage payments some percent of that mortgage goes towards the principal so that your balance ultimately decreases that principal paid is equity it's wealth that you're accumulating you will basically be able to extract that value when you sell that property and that's why it's a non-liquid gain so basically we will do the sum of the non-liquid gains we will present find the present value of those gains and we're going to add all of those gains so basically at the end of the 10-year period it is expected that you will gain about 2.1 million by purchasing this property now what does that mean as far as a return on investment goes is it better for you to invest in the stock market or is it better to invest in this particular property given the extremely high rent that I added up here 5,000 is is a really high rental price the internal rate of return is 24% now let's look at a better property I know that there was a property um that we were looking at it cost 1.96 million the grm of that area is 16.5 it is a 4 unit property it is about 4,200 ft and it is about 4,000 ft of livable space all the expenses here are practically the same but in the proforma the rents of that property are about 2,000 2,000 3,000 and 3,000 this is a property close to Culver City and if we were to keep this with no rental upside this is a property that's very interesting the cash flows would dictate that we still have to invest in the property but ultimately it is not as bad uh we do need to have cash reserves the cash and cash is pretty low and ultimately the irr is 11% now the reason that I'm choosing these properties is not because I want to showcase a good investment opportunity it's because I want to showcase the power of this spreadsheet if you invest in different regions across the US you might find 10% 15% even 20% IR Investments and this is a great tool for you to use to identify and more importantly compare two properties so here I have taken two properties let's call it 1241 Main Street and 3705 Main Street and I've entered the Reginal grm for those properties one property was purchased at 1.33 million the other one was purchased at 1.96 million this is a quadplex however this one is a duplex I've entered the loan details that we used to mer to purchase these properties the interest rate was relatively low lower because it was purchased a few years ago but you can see how the irr of these two properties Compares this one's irr is 13% however the irr of this other investment is about 16% so using this spreadsheet and by looking at the different charts and how the growth rate uh comes through you can use this spreadsheet quite effectively to compare the return on investment on two similar properties lastly in the charts sheet you can see a wide variety of charts and pie charts and bar charts so that you can better understand the growth of value and wealth that's generated with this property more specifically closer to the bottom you can see all the loan Rel data charts the cash Reserve charts moving up you can see your net operating income for the next 10 years the revenue versus expenses and cash reserves that you need to have and so on and so forth thank you for watching [Music]