Short-Term Rental Pro Forma: Every Line, Its Formula and a Calculator

What goes in a short-term rental pro forma: every line and its formula

A short-term rental pro forma is one year of the property as a statement. Gross revenue minus operating expenses is net operating income (NOI). NOI minus debt service is cash flow. Cash flow divided by the cash you put in is the cash-on-cash return.

The table below is the template. There is no file to download: you rebuild it in a blank Excel or Google Sheets file. Inputs go in column B: B2 purchase price, B3 down payment %, B4 closing costs %, B5 interest rate, B6 loan years, B7 furnishing, B8 nightly rate, B9 occupancy, B10 average stay, B11 cleaning fee charged, B12 cleaning cost, B13 supplies per stay, B14 platform fee %, B15 management fee %, B16 reserve %, B17 property tax a year, B18 insurance a year, B19 utilities and internet a month, B20 other fixed costs a month.

I build pricing arithmetic; I do not own or host rentals. Every input is an assumption to replace with your own quotes, bills and booking estimate. Nothing here forecasts what a property will earn.

Short-term rental pro forma calculator (free, no signup)

Enter the purchase, the loan, bookings you can defend and the costs. The calculator returns the main result lines of the layout, from gross revenue and NOI to cash-on-cash return, DSCR and break-even occupancy, on a 30-year fixed-rate loan and a 365-day year.

It opens on the worked example below. Test a poor year too; a loss shows as a negative amount.

Worked year 1: a $335,000 house at $205 a night

At 61% occupancy the example house grosses $53,595.04, keeps $22,499.88 as NOI and has $2,944.65 left after the mortgage: $245.39 a month.

• Revenue. 222.65 booked nights in 63.61 stays bring $45,643.25 at the nightly rate and $7,951.79 in cleaning fees.
• Operating expenses. Platform fee $8,307.23, reserve $2,679.75, cleaning and supplies $7,888.17, fixed costs $12,220.00: $31,095.15 in all, 58.02% of revenue.
• NOI. $22,499.88, a 6.72% cap rate on the price.
• Financing. The $251,250.00 loan costs $1,629.60 a month, $19,555.23 a year.
• Returns. Cash invested is $83,750 down, $10,050 of closing costs and $18,000 of furnishing: $111,800.00. Cash-on-cash is 2.63%; DSCR is 1.15.

One line misleads. The $125 cleaning fee appears to cover $124 of cleaning and supplies, but the platform fee and the reserve are charged on it too, so each stay falls $24.63 short. A booked night leaves $155.94 towards fixed costs and the mortgage.

NOI vs cash flow: why the mortgage is not an operating expense

NOI describes the property; cash flow describes your deal. Operating expenses are what any owner pays to run the house. The mortgage depends on how much you borrowed, so it sits below the NOI line.

The common mistake is to put the mortgage in the expenses and then divide by the price. In the example that turns a 6.72% cap rate into 0.88%, which describes neither the property nor your cash. Keep three ratios apart:

• Cap rate = NOI / purchase price. No loan in it. See the cap rate calculator guide.
• Cash-on-cash return = cash flow after debt service / cash invested, furnishing included. See the cash-on-cash return calculator guide.
• DSCR (debt service coverage ratio) = NOI / annual debt service. At 1.15 the NOI covers the loan payments with 15% to spare; below 1.00 it does not cover them.

Lenders set their own DSCR minimum, and I found no official page that fixes one figure. Ask the lender for its number and how it counts short-term rental income.

How occupancy and nightly rate move NOI, cash flow and DSCR

Fixed costs and the mortgage do not move with bookings, so a few points of occupancy decide the year. Each point is 3.65 booked nights and $569.18 of cash flow in the example.

Break-even is 55.83%, or 16.98 booked nights a month; the example books 18.6 nights and sits 5.17 points above it. At 45% the year shows a loss of $6,162.20; at 75% it clears $10,913.15. NOI is positive in every row, so NOI alone would not warn you.

A nightly rate 10% lower, $184.50, removes $3,628.64 and leaves a loss of $683.99.

Self-managed vs a property manager: what the fee does to the pro forma

A management fee is a percentage of gross revenue. In the example one point of fee is $535.95 a year, and cash flow hits zero at a fee of 5.49%.

The 20% is a placeholder, not a market rate; use the manager's written quote. At 20% the fee is $10,719.01, NOI falls to $11,780.87, the year shows a loss of $7,774.36 and break-even occupancy rises to 80.76%.

Ask what the percentage is charged on (nightly rate only, or cleaning fees too) and which costs the manager then covers. Self-managing costs time; the pro forma does not price your hours.

Seasonal months: the same year, a different cash balance

An annual pro forma hides when the money arrives. Split the same 61% into high and low months: the year still totals $2,944.65, but 4 of the 12 months run negative.

The monthly occupancies below are invented to show the method; use your own market's calendar. Every month owes the same $2,647.93: one-twelfth of fixed costs plus the mortgage payment.

July brings in $1,654.43; February loses $988.74. Close in November and the four low months come first: $2,765.15 out before the first good month. That figure, not the annual total, is the least cash reserve to plan for on top of the cash invested. In a sheet, add a row per month with its own occupancy.

Pro forma formulas for Excel and Google Sheets

The layout table gives a formula for every line. These five carry the financing and the returns; cell references match the layout, and percentages are entered as percentages (61%).

Microsoft's PMT function page gives the syntax as PMT(rate, nper, pv, [fv], [type]) and notes that the payment "includes principal and interest but no taxes, reserve payments, or fees". That is why property tax, insurance and the reserve have their own lines. Google Sheets accepts the same formulas.

Year 2 and later

Copy the year-1 column and grow the inputs, not the totals. Next year's nightly rate is =B8*(1+rate growth); each cost is =cost*(1+cost growth). On a fixed-rate loan the payment in B42 stays the same. The growth rates are your assumptions: keep them in their own cells and test zero.

Fees, tax and local rules the pro forma has to allow for

Three inputs come from outside the property. Check each at its source.

• Platform fee. The example uses 15.5% of gross revenue. Read 9 October 2026, Airbnb's service fees page describes a single fee where "the entire fee is deducted from the host's payout. Most hosts pay 15.5%, remaining hosts typically pay 14%-16%", and says the split-fee structure "is being phased out". Check your own account before relying on it.
• Income tax. The pro forma stops before it. IRS Topic No. 415 (reviewed 24 September 2026) lists mortgage interest, real estate taxes, insurance and depreciation among the expenses that "will reduce the amount of rental income that's subject to tax". IRS Publication 527 (2025) says that if you provide substantial services for the tenant's convenience, "such as regular cleaning, changing linen, or maid service, you report your rental income and expenses on Schedule C". Ask a tax professional which rules apply to you.
• Local rules. Airbnb's responsible hosting page tells hosts to "look up any permitting, zoning, safety, and health regulations that may apply". Put recurring permit and licence fees in other fixed costs, and confirm with the city that short stays are allowed at the address.

The statement also leaves out loan principal paid down and any change in the home's value.

Template, calculator or paid sheet: which to use for which job

Use the calculator to screen a purchase, the layout when you want a saved file per property, and a tracking sheet once bookings are real.

What to put in your template: the 19 inputs, lines B23 to B48, a column per occupancy case and a row per month.

ProSheet Studio's paid file here is the short-term rental profit calculator spreadsheet, $14.99 once. It is not this pro forma. It models one listing's average month: nightly rate, occupancy, average stay, rent or mortgage as one cell, utilities, internet, supplies, cleaning per turnover, platform fee, management fee and one-time furnishing cost in; net monthly and annual profit, margin, furnishing payback and break-even occupancy out. It has no purchase price, loan, NOI, cap rate, cash-on-cash, DSCR, reserve or month-by-month cells.

For a house let on a lease, the rental property deal analyzer works from monthly rent and returns cash flow, cash-on-cash and cap rate. The Airbnb profit calculator spreadsheet guide covers the revenue maths behind the first five lines, and the Airbnb expense spreadsheet guide covers logging actual costs against this plan. The Airbnb profit spreadsheet is the monthly view as a file.

Step-by-step: Short-Term Rental Pro Forma: Every Line, Its Formula and a Calculator

1. Enter the purchase and the loan. Price, down payment %, closing costs %, interest rate, loan years, furnishing. Example: $335,000, 25%, 3%, 6.75%, 30 years, $18,000.
2. Build gross revenue from nights and stays. Booked nights = 365 x occupancy; stays = nights / average stay. Nights x rate plus stays x cleaning fee: $53,595.04.
3. Subtract operating expenses to get NOI. Platform fee, management fee and reserve as % of gross revenue; cleaning and supplies per stay; tax, insurance, utilities, other fixed costs: $31,095.15, no mortgage. NOI is $22,499.88, a 6.72% cap rate.
4. Work out debt service with PMT. =-PMT(rate/12, years*12, loan) gives $1,629.60 a month; times 12 is $19,555.23 a year.
5. Work out cash flow, cash-on-cash and DSCR. NOI minus debt service is $2,944.65. Divided by $111,800.00 invested: 2.63%. NOI / debt service: 1.15.
6. Stress the occupancy and split the year into months. Rerun at lower occupancy and rate, then give each month its own occupancy. The example's four low months lose $2,765.15 in a row.

Related guides