Mid Term Rental Analysis Spreadsheet in Excel: The Gap Days That Decide the Deal

A 2-bed near a hospital campus in Columbus rents long term for $1,450 a month. Furnished, on a 13-week travel nurse contract, the same unit rents for $2,300. That is an $850 a month premium, $10,200 a year, in exchange for a couch and a Wi-Fi bill. Every mid term rental analysis spreadsheet in Excel starts with that number, and most of them stop there. So you spend $11,400 furnishing the unit, run it for a year, and finish $282 ahead of the boring long term lease.

The premium is real. It gets eaten by two lines almost nobody models: the vacant days between contracts, and the utilities you keep paying during them. Mid term rentals, 30 days and up, sit in the gap where short term permit rules stop applying and long term lease rates stop applying too. That space pays very well in the right zip code and loses money in the wrong one. The variable that separates the two is not rent. It is roughly 20 vacant days per contract.

Gap Days, Not Rent, Decide a Mid Term Deal

A travel nurse contract is 13 weeks, 91 days. Somewhere around 40 to 50 percent get extended, usually by 4 to 8 weeks. So your average occupied stretch is not 91 days, it is closer to 116. Then the contract ends, and the next one does not start the following morning.

Hospital systems onboard travelers in cohorts, typically every two weeks. A contract ending on a Friday against a cohort that starts 11 days later is not bad luck, it is the calendar. Add a day or two of cleaning and a booking that falls through, and 18 gap days per contract is a normal year, not a pessimistic one.

Here is what those days are worth. Same unit, same $2,300 rate, same expense stack, only the gap changes. The long term lease nets $16,599 in every row.

Break-even lands at 19.7 gap days. Under 20 days of turnover per contract, the furnished play wins. Over 20, you bought a couch to earn less money with more work. A single extra week of gap costs about $1,100 of annual net, which is more than the entire year of consumables and insurance delta combined.

Build the Model So One Property Drives Both Strategies

The mistake in most spreadsheets is entering a monthly rate and multiplying by 12. Mid term rentals are quoted monthly and consumed in days. The model has to convert.

Inputs sheet

The contract cycle engine

Six formulas turn those inputs into a revenue number that reflects how the asset actually gets used.

• B12, average contract length: =B5+(B6*B7) returns 116.2 days. This is the blended stay, not the advertised one, and it is the number your tax classification depends on later.
• B13, full cycle length: =B12+B8 returns 134.2 days. One occupied stretch plus one turnover.
• B14, contracts per year: =365/B13 returns 2.72. Use the fraction, not ROUNDDOWN. Contracts do not respect January 1.
• B15, booked days: =365*(B12/B13) returns 316.
• B17, booked months: =B15/30.4167 returns 10.39. This is the honest answer to "how many months of rent do I collect," and it is 1.61 months short of what the listing rate implies.
• B18, mid term gross: =B17*B4 returns $23,898.

Add one more cell you will use in every negotiation. B19, daily equivalent rate: =ROUND(B4*12/365,2) returns $75.62. A 91-day contract is 2.99 months, not 3, so the correct invoice is =91*B19, or $6,881. Quoting "three months at $2,300" hands the tenant $19 and hands you an argument about the last four days.

The long term benchmark

Give the comparison strategy the same rigor, otherwise you are comparing a modeled number to a fantasy. Long term units turn too.

• B23, vacancy factor: =1-(B22/(B21*30.4167)) with a 24-month turn cycle (B21) and 21 vacant days per turn (B22) returns 0.9712.
• B24, effective gross: =B3*12*B23 returns $16,899.
• Turn cost, annualized: =600/(B21/12) returns $300 a year on a $600 paint-and-clean turn.

Long term net: $16,599. That is the bar the furnished strategy has to clear.

The expense lines mid term adds, and the one that does not scale

These are the only lines that differ between the two strategies. Property tax, insurance base, mortgage, and capital reserves are identical on the same building, so leave them out of the comparison and stop double-counting them.

Look at the utility lines. They are $2,940 a year and they are not multiplied by occupancy. The furnace runs in the 49 vacant days. The router stays on so you can show the unit. The water heater keeps a full tank at temperature. That is $395 of utilities burned inside your gap days, and it is the reason gap days cost more in a mid term model than in a long term one where the tenant pays the bill.

If you hand the unit to a manager, mid term management runs 12 to 15 percent of collected rent against 8 to 10 percent for long term. On these numbers that is $3,300 versus $1,352, a $1,948 swing that erases the entire premium at 18 gap days. Self-manage or do not do it.

Two Traps That Do Not Show Up Until April

The transient tax threshold is a day count, and it is not always 30

Investors move to mid term specifically because a 30-day minimum sidesteps short term rental permits and hotel taxes in most cities. Most, not all. Several states define a taxable transient stay by a much longer window. Florida taxes rentals of living quarters for six months or less. Texas exempts a guest at 30 consecutive days. Arizona uses 30 days. Get the number from your county tax collector in writing, not from a forum post.

Model it as a threshold test, not an assumption:

=IF(B12<B30, B18*B31, 0)

B30 is the local threshold in days, B31 the combined state, county, and city rate. In a six-month-threshold state, 116-day average contracts are fully taxable. At an 11 percent combined rate, that is =23898*0.11, or $2,629 a year. Mid term net drops from $16,881 to $14,252, which is $2,347 below the long term lease. The strategy does not just get worse, it inverts, and you find out when the assessment arrives with penalties on the back-filed months.

Moving from short term to mid term can cost you the depreciation deduction

This is the expensive one. The non-passive treatment that makes short term rentals attractive to high earners depends on the average period of customer use being seven days or less. At seven days or under, with material participation, the activity is not a rental activity for passive loss purposes and losses can offset ordinary income. Your mid term average stay is 116 days. It is an ordinary rental activity, and losses are passive.

Put a number on it. Say the property and its $11,400 furniture package throw a $19,000 taxable loss in year one after cost segregation. At a 32 percent marginal rate:

The $25,000 special allowance for active participation phases out between $100,000 and $150,000 of modified adjusted gross income. Above it, the loss is suspended and carries forward, worth nothing this year. So the same building, same rent, same tenant quality, is worth $6,080 more to a $95,000 household than to a $185,000 household, purely on classification. Put the test in the sheet:

=IF(AVERAGE(Contracts!D:D)<=7,"NON-PASSIVE ELIGIBLE","PASSIVE, VERIFY ALLOWANCE")

Confirm the treatment with your CPA before you switch a working short term unit to 30-day minimums. The permit problem you are solving may be cheaper than the deduction you are giving up.

Furnishing Payback and the Rate Sensitivity Grid

The reserve line in the expense table is the annualized cost of the furniture, $2,280 a year. Payback answers a different question: when does the $11,400 come back out of the building. Use the advantage before the reserve, or you charge yourself for the couch twice.

=B9/((MTR_net+B9/B10)-LTR_net)

Furniture in a rental lasts about five years. At 18 gap days the package pays for itself with six months to spare before you replace it. At 30 gap days it never pays for itself at all, it just gets replaced.

Two-variable data table

Rate and gap days are the only two inputs worth stress testing, which makes this a textbook two-variable data table. Put net income in the corner cell, gap days down the left column, monthly rate across the top, select the range, then Data, What-If Analysis, Data Table, with B8 as the column input and B4 as the row input. Net income, long term benchmark $16,599:

Read the grid, not the average. At $2,000 a month the furnished strategy only wins at essentially zero turnover, which does not exist. At $2,600 it wins even at 45 gap days. The premium you can charge is set by the hospital and the market. Gap days are the one variable you control, and the grid shows they are worth as much as $300 a month of rate.

Flag it so the sheet argues with you:

=IF(MTR_net<LTR_net*1.15,"PREMIUM TOO THIN, LEASE IT LONG","FURNISH IT")

The 15 percent buffer is not arbitrary. Mid term is more work: more inquiries, more turnovers, more calls about the dishwasher. If the model shows a 2 percent edge, that edge is your unpaid labor.

Track the Real Calendar, Then Re-Forecast

Everything above runs on an assumed gap. After your first two contracts, stop assuming. Build a Contracts sheet and let the building tell you:

Then =SUM(D:D)/365 is your true occupancy, =AVERAGE(E3:E50) is the gap number that replaces your guess in B8, and =AVERAGE(D:D) feeds the tax classification test. Re-forecast every time a contract closes. A unit trending at 26 gap days after three contracts is telling you to sign a 12-month lease with the next inquiry, not to lower the rate.

One demand check before you buy any furniture: count the active furnished listings within three miles of the hospital campus, and count the open traveler contracts that system is posting. If open contracts do not run at least 1.5 times the listing count, you are the unit that sits empty between cohorts. That ratio takes twenty minutes to establish and it is worth more than any rent comp.

What to Do Before You Buy the Couch

1. Get the transient tax threshold and rate for your county in writing. If the threshold is longer than your contract length, stop here and lease it long term.
2. Ask your CPA to confirm the passive classification and whether the $25,000 allowance is available at your MAGI, before you convert anything.
3. Call the housing coordinator at the hospital system and ask for cohort start dates. That schedule sets your gap days, not your marketing.
4. Get an insurance quote for furnished 30-day-plus occupancy in writing. A standard landlord policy is not automatically it.
5. Model at $2,000 a month, not your target rate. If it does not clear the long term net at your realistic gap, the deal depends on a rate you have not proven.
6. Compute payback on the pre-reserve advantage. If it is longer than five years, the furniture wears out before it pays for itself.
7. Log every contract with move-in and move-out dates from day one, and replace the assumed gap with the measured one after two contracts.

The recommendation: run mid term only where your measured gap holds under 20 days per contract and the monthly premium over long term is 55 percent or better. In this example that means $2,250 against $1,450. Below either threshold, the furnished strategy trades a real $16,599 for a hopeful $16,881 plus a few hundred hours of your year.

If you would rather not build the cycle engine, the differential expense stack, the threshold tests, and the data table from scratch, SheetCraft's Rental Property Analyzer has the mid term module wired already: booked-days revenue instead of naive monthly math, gap-day sensitivity against a long term benchmark on the same property, a utilities line that correctly refuses to scale with occupancy, transient tax and passive-loss classification flags, and a contract log that feeds your measured gap back into the forecast. You enter the rate and the calendar. It tells you whether to furnish the unit or sign the lease.