Author Topic: Renting up the Case Study Spreadsheet  (Read 319 times)

brightblade81

  • 5 O'Clock Shadow
  • *
  • Posts: 20
  • Location: SF Bay Area
Renting up the Case Study Spreadsheet
« on: October 23, 2020, 07:17:53 PM »
Hi,
In reference to the MMM Case Study spreadsheet, I have been unable to find instructions searching the forums on properly entering rentals kept during retirement, how do I properly enter my rental income and home loans in the spreadsheet so it properly calculates how much stash I need for FI?

I entered my 3 rental properties under loans, which I plan to keep once I retire, but I don't think its applying my rental income correctly during retirement.  Am I supposed to enter my expected rental income(after removing expenses such as property taxes, insurance + maintenance + vacancies) in the A166 "Extra Income after RE"?

If you want to see or comment directly, here is a link to my filled out spreadsheet:
https://drive.google.com/file/d/1gE-EbkNAisIZY5YOU9rm1tMKB3Pahf6b/view?usp=sharing

Here is the rental details:

Rental:Monthly Rent.Annual Property TaxesHome InsuranceOther Annual Expenses
SFH Rental Huckle$2600$4460$410$2000
Condo Rental$2250$2600$330$7000(HOA)
SFH Rental Suther$4200$5300$660$2000

Loans:Monthly Principal+InterestOrig. Prin.Orig. LengthCurr. Prin.Yrs leftRate
SFH Rental Huckle$990$220,57530$181,000223.5%
Condo Rental$553$112,50030$91,000224.25%
SFH Rental Suther$1756$385,00030$383,000303.625%

I'd like to earmark $20,000 per year to be on the safe side for large expenses, i.e. roof replacements.

Thank you ahead of time!

MDM

  • Senior Mustachian
  • ********
  • Posts: 10400
Re: Renting up the Case Study Spreadsheet
« Reply #1 on: October 23, 2020, 09:30:34 PM »
At some point, one's financial situation may get beyond the scope of tools such as the Case Study Spreadsheet.  But let's see if we can devise a functional approach:
- total rental income goes in B33
- total mortgage interest is added to property taxes, insurance, maintenance, etc., and goes in B34
- depreciation goes in D35
- total mortgage principal payments go in D56

You could calculate totals offline and type the results into the cells above, or find a blank section on the 'Calculations' tab (e.g, P68:T72) and tie results there to the cells above, etc.

Does any of that work for you?

brightblade81

  • 5 O'Clock Shadow
  • *
  • Posts: 20
  • Location: SF Bay Area
Re: Renting up the Case Study Spreadsheet
« Reply #2 on: October 28, 2020, 06:33:32 PM »
Hi,
Thank you for the advice, is there a tool you'd recommend I use in this complicated financial situation involving rentals to determine how long until I can FIRE?

Per your advice, I updated the fields for rental income, expenses (including loan interest), and depreciation.  I then cleared out all the rental properties under the loans section rows 133 to 137.

I reviewed the stash needed(B194) to see if it uses the rental income and it does not significantly change based on different income from rentals.  I followed the calculations, I can see rental income is not taken into account, the rental income is only used to calculate the projected stash size so it assumes once you retire, you stop earning all rental income(and it assumes no rental expenses during retirement too).  I intend to keep my rentals into retirement.  Reviewing it further, I realized the RE Total annual expenses exclude all loans(rental + primary)!

To ensure my rental income and expenses are properly used in the retired section, I tried:
-Added to "Extra income after RE"(B166) with my "Rental taxable income" in D36
-"Change in spending after RE"(B187), I added my primary residence annual expense(D76) AND the rental's principal payment(D56) but I then subtracted out the rental depreciation (D35) because while the depreciation is tax deductible, that is cash in my pocket I can use against expenses.
-Changed "Taxable income"(L168) to include my Extra income after RE(B166)
-Reduced "Rental equity" in B175 to $0

With these changes, does that sound correct?  The calculated Stash needed still seems high just to generate an additional ~$29K income per year, I'd of expected around $800k, not $1.3M...

Its still not perfect because Rental Depreciation will disappear at some point but also the loan interest will decrease and principal portion increase until paid off affecting my taxable income but I'm not sure how to properly model that with this tool or if its important for FIRE.

Here is the snippet for the retire section(or refer to google link):

Time to FI?:
Extra income after RE (pension, SS, etc.)$56486/year =D36+N("Rental taxable income")
Guess at time to FI4.years
Safe Withdrawal Rate4.00%percent
Real return on tax-deferred investments5.00%percent
Real, after tax, return on taxable investments4.25%percent
Current Savings
Taxable stocks & bonds$105,000
Tax-deferred (e.g. trad. IRA/401k)$415,000
Roth + HSA$55,000
Projected Savings at Retirement
Taxable$617,950
Tax-deferred (e.g. trad. IRA/401k)$634,773
Roth + HSA$97,455
Total projected stash$1,350,178
Projected Expenses in Retirement
Non-loan, non-work expenses$57,031
Change in spending after RE$19,283D76+D56-D35-(806*12)+N("806 is rental portion of my primary residence which is duplex, covered in rental portion")
Annual non-tax retirement expense$76,314
Income taxes$8,663
Total$84,977
Total loan principal due at FI$561,601
Stash needed for retirement @4.0% SWR$1,273,880
Have $76,298 extra.
« Last Edit: October 28, 2020, 06:54:21 PM by brightblade81 »

MDM

  • Senior Mustachian
  • ********
  • Posts: 10400
Re: Renting up the Case Study Spreadsheet
« Reply #3 on: October 28, 2020, 07:17:13 PM »
It's a bit dated, but Best and/or Recommended Retirement Calculator - Bogleheads.org has some suggestions.

At a quick glance, your modifications seem appropriate for your situation.  If I see anything noteworthy after a closer look, I'll post that.

Depending on how many years it will be until retirement, merely being "approximately correct" may suffice for planning purposes.