Excel template for financial analysis of LTRs

Real Estate Deal Analysis & Advice
7yFollow

Excel template for financial analysis of LTRs

Daniel Alvarez
Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes

I've been ruminating over the low rate environment recently and whether to refi one or more of my long-term rentals, but I just struggled visualizing the full gamut of financial impacts, e.g. loan cost inefficiency, 30yrs vs 15yrs mortgages, maximizing cash flows vs cash-outs, tax positions, etc. Personally a cash-out refi could help me get more quickly into all-cash BRRRs or a REI venture in Spain, however I don't know to what extent this is at the expense of rental cash flows, and what is the wiser decision long term.

I've combed through posts online and looked for Excel templates to run some calcs and get a sense, but nothing really filled the void... 

So having some financial modeling background I could not resist any longer. This past week I've invested some hours after my 9 to 5 (between putting the babies to bed and my own bedtime) into a tool that would help me understand the impact of refinancing, depreciating or selling my LTRs. Of course it quickly snowballed so the result so far is a comprehensive, detailed model that captures quite a wide gamut of financial variables. Not shy to admit perhaps too detailed. Next I intend to cross check the calculations (esp. taxes) and simplify the layout. Also currently it just caters for LTRs.

If you can go past the level of detail I would love to hear any feedback from anyone interested, and especially from any Excel gurus, accountants, investors or tax experts out there with a keen eye. I'd love your suggestions, criticisms and comments and I'm happy to address questions as well. I'm an avid reader of BP but I'm also a self-taught investor like many of us here, barely 3 yrs deep into REI, so I'm certain blunders abound. In the least I hope the model is helpful to others, as a start if anything - just like I was searching for before this week.

Reader beware : model is not for the fainthearted. I've seen some simple templates circulating the internet and this is nothing alike.

Link below, there is no pwd on the file but the structure is protected :

https://drive.google.com/open?id=15ehUtK-JkK0px-S5IUxSaLwZSUrwNjSN

PS :  a key standout for me using the model has been the power of cash-out refis. Simply switching the right refis on or off you can see NOLs accumulate or deplete and the impact on returns. As with any model the motto "garbage-in, garbage-out" stands, so more detail often means harder to use and less accurate. Always question what you see and don't take anything for granted. Also the customary caveat, I'm not an expert or adviser of any kind so use at your own risk (and peril).

2Reply
12 views

Most Popular Reply

Kerry BairdPro Member
Rental Property Investor · Melbourne, FL · Member since 2011 · 3k+ posts · 2k+ votes
7y

I am a proponent of “a confused mind says no” and I get burdened with information overload when making decisions.  Hopefully someone with a stronger intellect with spreadsheets will comment on that aspect.  

My comments is on taking action and the profit centers of real estate:

With real estate, time evens out many bumps, including paying too much. An example of that is my parents paying $10,000 for their first house in the 1960s, and their payment of $65 was really difficult for them. Would it matter much if they had paid $11,000 for that house? 

-We have inflation rising in most decades and bringing the prices up.  

-We have debt pay down, as our tenants progress through the mortgage

-We have tax benefits that enable us to trade up or simply claim deductions.

What refinancing does for YOU is allows you to catch those profit centers.  You have 3 properties now, and when the time is ripe, ie the tenants have paid down, inflation has gone up, and your tax situation calls for it, you can refinance and pull out cash and double your holdings with 3 more long term buys.  

And then a few more years go by and those profit centers enable you to refinance all 6 and double your holdings.  This is what I some of us here call going WIDE.  

Start paying them down to free up maximum cash flow, at a time when you want to consider retiring.  This is going DEEP. 

There are infinite variations. In my years of experience, it can be done slow and steady, and still accumulate many doors. 

1Reply
See this reply in the discussion

12 Replies

Jump to latestLatest
  • Kerry BairdPro Member
    Rental Property Investor · Melbourne, FL · Member since 2011 · 3k+ posts · 2k+ votes
    7y

    I am a proponent of “a confused mind says no” and I get burdened with information overload when making decisions.  Hopefully someone with a stronger intellect with spreadsheets will comment on that aspect.  

    My comments is on taking action and the profit centers of real estate:

    With real estate, time evens out many bumps, including paying too much. An example of that is my parents paying $10,000 for their first house in the 1960s, and their payment of $65 was really difficult for them. Would it matter much if they had paid $11,000 for that house? 

    -We have inflation rising in most decades and bringing the prices up.  

    -We have debt pay down, as our tenants progress through the mortgage

    -We have tax benefits that enable us to trade up or simply claim deductions.

    What refinancing does for YOU is allows you to catch those profit centers.  You have 3 properties now, and when the time is ripe, ie the tenants have paid down, inflation has gone up, and your tax situation calls for it, you can refinance and pull out cash and double your holdings with 3 more long term buys.  

    And then a few more years go by and those profit centers enable you to refinance all 6 and double your holdings.  This is what I some of us here call going WIDE.  

    Start paying them down to free up maximum cash flow, at a time when you want to consider retiring.  This is going DEEP. 

    There are infinite variations. In my years of experience, it can be done slow and steady, and still accumulate many doors. 

    1Reply
  • Patrick LiskaPro Member
    Investor · Verona, NJ · Member since 2014 · 1k+ posts · 832 votes
    7y

    Daniel,

    Seems you do have a lot, it seems there are a lot of "predictions" to values / costs increasing in there, one thing i saw is that you have loan Interest payments/ cost increasing every year too, if you have a fixed loan those costs will not increase, they will decrease and your Principal payments will increase.

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y

    @Kerry Baird thank you for the perspective!

    Often I’m easily cursed with information overload around investment decisions - I find it’s easier to let the analytical mind take over, so I have to work harder at letting my “gut” chime in.

    When the brain takes over I find myself troubled with more questions than answers, one reason I decided to build the model. Experience with models tells me they’re great confidence building tools, but never a good replacement for common sense - and the wisdom of experience. So I much appreciate the perspective.

    Would you say there is a trade off of going wide vs going deep or is it just a matter of personal preference? It feels going deep is less risky, which I like, but are returns lower to match the lesser risk...

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y

    @Patrick Liska thanks I will take a look. I agree interest payments should only go up if there’s a refi.

    When it comes to all the cost and growth assumptions one thing I lack is the experience to understand what to expect... so most long term inputs are educated guesses.

    What the model does help with then is understanding which ones are my returns most sensitive to. So far it’s mostly common sense, e.g. rent increases really make a difference...

    0Reply
  • Kyle McCorkel
    Rental Property Investor · Hummelstown, PA · Member since 2015 · 638 posts · 653 votes
    7y

    @Daniel Alvarez

    I have using my spreadsheet to track performance for a few years now. I update the numbers monthly. It definitely is cumbersome and I could spend the time to “re-tool” it but I’m too busy hunting for deals and managing rehabs! I guess that’s a good thing.

    My tool focuses on past performance and calculates all the metrics you would want to know. Cash flow, debt pay down, ROE, Cash on Cash, etc. it also shows me my equity position on each property and overall.

    0Reply
  • Patrick LiskaPro Member
    Investor · Verona, NJ · Member since 2014 · 1k+ posts · 832 votes
    7y

    Daniel,

    As long as you invest in an area that sees a steady growth or the ability to raise rents every year. I just looked at the sheet again, you must have that growth built into your formulas ( i have not downloaded it, just looking at your link) you should have a way to have a growth input %, some areas do not Appreciate like others and you should be able to adjust that.

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y

    @Kyle M. Good point. The model also has a section for monthly “actuals”, which when filled out (even zero) they will override any calculations. This helps track past performance and calibrate future assumptions if I need to (and hopefully simplify tax time). Certainly sometimes simpler IS better, just depends what questions you want answered.

    Once I decide if a cash-out refi is the way forward I will need to spend a lot more time looking for deals. That’s definitely the way to go! Then if time allows I’d also like to add each property in a separate tab and maybe create a dashboard showing the full portfolio. Shouldn’t take me much time and I’m thinking this will also help look at the combined accumulated losses and assist financial planning.

    If you don’t mind me asking, do you have specific tools you use for finding deals?

    I once downloaded and analyzed Zillow data to find markets with attractive cap rates...

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y

    @Patrick Liska certainly growth rates can be input yearly for the key revenue and cost lines. The key one for me is whether it makes sense to drop in some “correction” rates (i.e. negative growth) or if in the end it all evens out long term as @Kerry Baird writes...

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y
    Originally posted by @Patrick Liska:

    Daniel,

    Seems you do have a lot, it seems there are a lot of "predictions" to values / costs increasing in there, one thing i saw is that you have loan Interest payments/ cost increasing every year too, if you have a fixed loan those costs will not increase, they will decrease and your Principal payments will increase.

    I checked the interest payments and yes they go up around around a refi, which tells me it's fine. Also when a refi is done mid year you see an annual increase across two years as the full annual amount catches up. Switching refis off in cells G79 and H79 shows this.

    Btw I did noticed the model doesn't display well when clicking the link, and it doesn't work in google sheets so I'll look into that. I guess right now only Excel will show it correctly.

    0Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    7y

    Does anyone have strong views on whether on rental properties 

    1. ALL the interest from a cash-out refinance can be tax deductible (even if the cash is invested to buy or fix up another property), or
    2. Only up to the original loan amount, or
    3. Something else.

    Currently assuming 1) but not at all certain...

    1Reply
  • Daniel Alvarez
    OP
    Rental Property Investor · Tysons Corner, VA · Member since 2016 · 51 posts · 13 votes
    6y

    @Brandon Wong

    Hello, I put together a spreadsheet model a while ago for calculating IRRs on rental investments, as an attempt to capture comparable returns by discounting future cash flows (in REI you also hear about cash-on-cash, which is typically at a point in time), and it sounded like you are having a related predicament. The post below has a link to it if you're curious:

    https://www.biggerpockets.com/topics/693527

    It was my attempt to model the key financial incentives for REI, which members have mentioned above, like inflation protection, capital appreciation, tax benefits, equity build up, and of course the beauty of refinancing.

    If you’re familiar with project financing you’ll know as value of real capital appreciates regearing will boost returns quite significantly. It increases risk, undoubtedly, so being aware of the perils of indebtedness is key -the more aggressively you leverage the more fragile you become. Risk and return.

    The file is limited on what it can do, and misses the mark on things like depreciation recapture or like-kind exchanges, which will impact returns, but it’s fairly sophisticated relative to what you can find out there or if you were doing it yourself with limited knowledge.

    Anyway I happen to have a forum alert set for “Spain” and this post popped up in my radar!

    1Reply
  • Howard L.
    Investor · Carrollton, TX · Member since 2013 · 12 posts · 2 votes
    1y

    @Daniel Alvarez 

    Old thread, but just wanted to say I found your spreadsheet useful. Hope all is well.

    0Reply
Join the conversationCreate a free account to reply, vote on answers and follow this thread.
Log InJoin for Free