Simplify Your Monthly Rent Invoice with Our Template Excel for Real Estate

Monthly rent invoice template excel for Real Estate

Today we're going to check if a rental property is worth buying. And we're going to do that by calculating the cash-on-cash return and the return on investment, or ROI of a potential investment. We're going to use Excel to set up a simple template that you can use to analyze all kinds of investment decisions. So let's say this real estate agent calls you up and offers you a deal. It's a house in your area and the owners are moving out of town. They want to sell it. The real estate agent says the owners just want to have $200 thousand for it, and that you can rent it out for at least $1,400 per month. So, is this a good deal for you? Comment below and let me know. We're going to find out in this video. (upbeat music) Before we get started, a brief thanks to Skillshare for partnering with us in today's video. I'm going to chat more about them later on. Check out the description for more information when you're ready. Okay, so let's see if such an investment that we talked about earlier makes sense. You may think, "Wow, $1,400 a month "as additional income is great." Couple of more houses like that and you can get to quit your job. But not so fast. From our basic accounting videos, we know that income, or profit, is defined as revenue minus expenses. The rental income is just the revenue part of that. We need to look at the expenses too. And we also need to finance such an investment, because most people don't happen to have $200 thousand in cash flying around. So we're probably looking at some external financing. Now let's switch to Excel and crunch some numbers. So first let's analyze the investment. I'm going to be using this template I created. Now you can feel free to download this template as well and use it as you see fit. Let's start with the purchase price for the property. We said that's going to be 200 thousand. So let's plug that in. Then let's account for taxes and fees, closing costs and realtor fees. So these are specific costs that are applicable to property. If you have other type of investments, let's say you're buying machinery or equipment, your costs are going to be different. They could be transport and installation. So just make sure that you really identify the total cash out. So let's say in our case our taxes and fees are going to be 0.5%. Our closing costs, as well as realtor fees, are going to be 1% of the full amount. So that's going to be this number times this number. And I'm just going to fix it using the F4 key. Press enter, and pull this down to here. Okay, so just so you know what's happening on this side, I'm using the FORLMULATEXT function so that you can see the formulas as I'm typing them in here. These are our costs for these. Next comes the rehab costs. You might need to fix up the property before you can put it on the market. For our case, let's assume that we need to do a paint-job on the whole property. We might need to fix the kitchen. And that's going to cost us $5 thousand. If you can think of any other cost, you can put it here. So now let's calculate the total investment. That's basically the sum of all of this. And since I'm in Excel, I can use the shortcut key, Alt and the equal sign, to get an automatic sum. That's the amount of money that I need for this investment. Now these are just average numbers. Obviously, they need to be adjusted for your particular investment. Next, let's specify how much of our own money we're going to put into the investment. That's an important figure, because this is going to be the base for our return calculation. So here, let's assume that we have $40 thousand in equity. Now as a result, the difference to the total investment, so basically this number minus the amount that we're going to put in, that's going to be the amount that needs to be financed some other way. In our case, we're going to go and get a loan from the bank. Now, we go to the bank and we manage to negotiate an annual interest rate of 3% in the loan that we're going to get. And we're going to pay back that loan in 25 years. So let's calculate our monthly loan payment. We can use the PMT function. First argument we need is the rate, that's the interest rate. I want to get that monthly. Here I have the annual interest rate. So I'm going to divide this by 12. Next argument is the number of months for this loan period, that's 25 years. I need it in months, so that's multiplied by 12. Then it's the present value, so the amount that I'm borrowing from the bank, that's the 170 thousand. Next I have the future value. That's the amount that's left at the end of the period. Now obviously the bank wants all their money back so nothing is left. So I can either leave this out or put a zero there. And the last argument is type. That's whether I'm paying this back at the end of the period, or at the beginning of the period. So it's just about timing. Let's say I'm always paying this at the beginning of the month. So I'm going to close bracket, and press enter. $804 is what I'm paying back to the bank every single month. So this covers the interest, as well as the principal amount, that I've borrowed from the bank. Now let's take a look at the potential revenue. That's the cash in by renting out the property, which we're going to assume is $1,400 every month. Now you can add other sources of income as well. So if you're renting parking space, you can put it here. In our case we don't have anything, so we're going to put zero. Tenants are going to move out, and you're going to have periods where there is no cash coming in. So to be realistic, we're going to deduct this risk. The risk of our apartment being vacant. So you can pick a percentage here. I'm going to pick 8%. So I'm assuming that 8% of the time, this place could be empty, and I'm going to get no money for it. So 8% of 12 is almost one, so it's almost one month. So basically, I'm assuming that almost a month this place is going to be vacant. That's a risk that I want to account for. So that's going to be this number, multiply it with this number to get my monthly risk. And since it's a risk, it's going to be negative. Our total revenue, if we use Alt and the equal sign, and press enter, is 1,288 per month. Next, let's take a look at expenses. Here we deduct all monthly costs for the investment. So for our example, I included maintenance and repairs, property management and real estate taxes. These two here, they're based on the monthly rental income. Let's assume this one is 3%, property management is 8%, and real estate taxes is 1.5%. Now let's get the numbers here. So that's 3% of the monthly income. Now I'm going to fix this, and since it's an expense, it's going to be minus. That's $42 for maintenance, and $112 for property management. Real estate, that's based on the full amount, so the property value, that was this much. And that's minus as well, and I want to get monthly so I'm going to divide it by 12. So that's 250 every month. Now for insurance, we can just put a number here. I'm going to put $30. Other expenses you can think of, you can put here. In this case, we're going to go with zero. Total expenses is the sum of this. Alt + equals, $434 per month in expenses. Now obviously these are just examples. These are just average numbers. Just be realistic on planning the revenue in income. In reality, a lot of business cases fail because people use overly optimistic assumptions. So now let's calculate the monthly cash flow. Which is the sum of debt service, monthly revenue, and our monthly expenses. Two of these are negative, because for the first one, we're borrowing money from the bank. That's this much. Then we have our monthly revenue, that's our total revenue right here. And then we have our monthly expenses, which is this much. So what's our cash flow each month? Let's use the sum, $50. That's not much. I guess we're going to have to hold onto that job a little bit longer. Let's just calculate the return on investment to see if, in general, this is a good investment or not. Even though we have to keep our job for now. These are expressed in annual percentage. So we just need to multiply our monthly cash flow by 12. This will give us our yearly cash flow. Our invested equity was our 40 thousand Our cash-on-cash return is our yearly cash flow divided by our invested equity, 1.5%. Now this doesn't sound like much, right? Could be the same as what you might get if you put your money in a savings account. It's okay, but it's not that great. So this is also called the cash-on-cash return, which is an important metric, because cash flow is usually the most important topic when you're investing. You don't want an investment that's not adding cash to your bank account each month. But here's the thing, we're deducting the full mortgage payment with the monthly debt service. This amount that we're paying monthly to the bank pays the interest, of course. But it also reduces the outstanding principal of the loan. Usually as an investor, you wouldn't consider the principal payment an expense because you are building equity, you're buying property. So with every month that you're paying the bank, you own a little bit more of the property. That's something we can account for. And we are going to account for it when doing the ROI, right here. Now to be able to do that, we need to split the interest payment of this 804, and the principal payment. So how much of this is going to cover interest, and how much of it is going to cover our principal payment? Because the principal here is a good thing, it's actually turning into an asset. It's turning into property. The interest part, that's the bad thing. That's what we need to get. Now one way of doing this is to use an Excel function that's called cumulative IPMT. So in Excel, we have the IPMT function. It returns the interest payment for a specific month. But if you use the cumulative IPMT, you get the cumulative interest payment. So we're going to get the cumulative interest payment for the entire period. And then we're going to get a monthly average out of that number. All of these, PMT, IPMT, they're very similar. So we need the rate. Rate is our interest rate that's sitting right here. So remember, this is an annual interest rate. I need to divide this by 12. Next argument is the number of periods, that's 25. But I need to multiply this by 12 to get the number of months. Present value, that's the amount of the loan I got from the bank. That's sitting right here. Then here I have a start period. So from when do I want to get this cumulative interest rate? I want to start from the first period, and I actually want to end on the last period. The last period is 25 times 12. Last argument here is the type. And to be consistent with my PMT formula, I'm going to put a one here, close bracket, press enter. This amount is the cumulative interest payment for the entire period. What I want is to calculate an average monthly amount for it. So I'm actually going to divide this by the number of months, which is 25 times 12. This amount, that's the average interest paymet over the entire period. Now if you're curious if there's a formula to calculate the monthly principal payment for the entire period, yes there is. I'm just going to show it to you really quickly. It's called the cumulative principal. And for that you need the same arguments, in fact. So just to keep things simple, I'm actually going to copy this formula. I'm going to paste it right here. And let's change the formula here. Instead of cumulative IPMT, we're going to go with this one, and press enter. And that's the average monthly principal over the entire period of this loan. So check this out, the sum of these is going to be the loan that you're paying back each month. We can do that right here. $804 that you're paying back to the bank, which is took out the interest part, and took out the principal payment part. Let's take a look at our monthly cash flow, which is actually what we calculated right here. Let's add the monthly principal payment, which is this. But I'm going to put a minus because we want it as a positive number, right? Because this amount is actually turning into property. Now my monthly cash flow here, that's the sum of these, is $617. So now, if you take a look at the yearly cash flow. So let's take this and multiply this by 12. We take a look at our invested equity, which is this. And now we take a percentage of our newly calculated yearly cash flow with our invested equity. What do we get? We get 18.5% return on investment. This that means that for every dollar that we're investing, we're getting an additional 18 cents back. That's actually a pretty good investment. So this is how you can use a simple Excel template to find out if an investment makes sense. So if you're looking for online courses, related to real estate investing, or debt financing, like funding your small business, I can recommend David Haber's class on Skillshare. What I especially liked is how he walks us through the thinking process of whether a new opportunity makes sense, or not. And of course, this includes crunching some numbers to figure out the profitability of the opportunity. Aside from this course, you're going to find thousands of classes that cover a wide range of skills. Including office productivity, entrepreneurship, public speaking, and a lot more. Now, Skillshare is really affordable too. An annual subscription is less than $10 a month, and premium membership gives you unlimited access. So you can join any classes on any topics you're interested in. The great news is because Skillshare is sponsoring this video, you can become a member right now, with the link in the description, and get a two-month free trial. If you enjoyed this video, give it a thumbs up. (button pops) And if you want to improve your skills, consider subscribing. (button clicks) And don't forget to hit that bell (bell dings) so you don't miss out on any new videos here. Thank you for watching, and I'll see you in the next video.