Real Estate Pro Forma Spreadsheet in Excel: Price the Column, Don't Believe It

A broker sends you an offering memorandum for a 24-unit building. Page four carries two columns side by side: T-12 Actual and Pro Forma. The actual column says the property earns $108,948 of net operating income. The pro forma column says $153,521. The asking price is built on the second number. Most buyers open a real estate pro forma spreadsheet in Excel and start arguing with the rent comps behind that second column. That is the wrong fight, and it is the fight the seller wants you to have.

The rent comps are usually fine. In the deal below they are exactly right, and the arithmetic that proves it is in the next section. The problem is not that the pro forma column is dishonest. The problem is that it has no time axis and no cost axis. It names a destination and prints a price for arriving there instantly and for free.

The Pro Forma Column Is a Price, Not a Projection

Cedar Ridge is 24 units in a Midwest secondary market, built in 1974, five units already renovated. Here is what the offering memorandum shows.

The broker applied a 6.00 percent cap rate to the pro forma NOI and asked $2,558,680, which is $106,612 per unit. Apply the same 6.00 percent cap rate to the income the building actually produced and you get $1,815,800.

The difference is $742,880. That is not a valuation debate. That is the seller charging you today, in cash and loan proceeds at closing, for $44,573 of annual income that does not exist yet, that you have to create, with your money, on your schedule, carrying your risk. A 40.91 percent NOI lift is the entire business plan, and the pro forma column hands the whole reward for it to the seller on the day you sign.

Notice what that gap is made of. It is the NOI lift divided by the cap rate, which means the denominator decides the price of the seller's optimism. The work is identical in every one of these markets.

Thirteen renovations, one lease-up, the same nineteen months of contractor scheduling. In a 7.50 percent market the seller wants $594,304 for that plan up front. In a 5.00 percent market the seller wants $891,456 for it, a swing of $297,152 on identical work. Underwriting discipline on the pro forma column matters most exactly when cap rates are low, which is exactly when the market stops exercising it.

Start by Agreeing With the Seller About the Rent

The pro forma column shows one rent line: $1,040 per unit per month. Your rent roll has four rents in it, and three of them cost money to reach.

Blend that column with =SUMPRODUCT(B5:B8,D5:D8)/SUM(B5:B8) and you get $999.17 per unit per month. That is the most the building produces if you execute the plan the seller already has under way and spend not one dollar beyond it. The broker's line says $1,040.

So the broker is overstating rent by $40.83 per unit per month. Here is the part worth slowing down for: the broker is not wrong. Each additional classic unit you renovate moves from $935 to $1,075, a gain of $140 per month. Solve for how many units that takes with =(1040-999.17)*24/(1075-935) and the answer is 7. Renovate seven of the thirteen classic units and the blended rent is $1,040.00 to the cent, because eighteen units at $1,075 and six units at $935 average precisely $1,040.

The market rent in the pro forma column is achievable. It costs seven renovations at $11,500 each, which is $80,500 of capital that appears nowhere in the column you are being asked to pay for. This is the whole trick, and it is not a lie. It is an omission with a price tag. Before you accept any market rent line, rebuild it from a unit-level survey rather than a single blended number, which is what a proper multifamily rent comp survey spreadsheet is for.

Three Errors You Can Prove From the Seller's Own Page

The rent line is an omission. These three are arithmetic mistakes, and you can demonstrate each one using only the numbers already printed in the memorandum.

The management fee does not scale. The fee is contractually a percentage of effective gross income, 5.0 percent here. The T-12 column shows $12,372, which is exactly 5.0 percent of $247,440. The pro forma column shows $12,372 again, against an EGI of $293,549. The correct figure is =0.05*293549, which is $14,677. The column understates the fee by $2,305 per year, and at a 6.00 percent cap that is $38,424 of asking price created by a frozen cell reference. Anchor the fee to EGI with =$C$8*C20 and it can never happen in your model.

The vacancy assumption is below the market it sits in. The building ran 11.48 percent economic vacancy over the trailing twelve months. The pro forma column drops that to 6.0 percent. Meanwhile the Census Bureau's Housing Vacancy Survey put the national rental vacancy rate at 7.3 percent in the second quarter of 2026, with the Midwest at 6.9 percent, up from 5.6 percent nationally four years earlier. That national figure measures the share of rental inventory sitting empty and available, not one property's economic vacancy, so it is a bound rather than a substitute. It is still the wrong direction for a 1974 Class C building to be underwriting 6.0 percent. Underwrite 7.0 percent and the NOI falls $2,845. Split physical vacancy from collection losses while you are in there, because they behave differently and a rental property bad debt allowance calculator sizes the second one properly.

The other income lift competes with the rent lift. The column raises other income from $9,120 to $12,000, mostly by billing back utilities. It also raises rent by 15 percent. Both dollars come out of the same tenant's paycheck in the same month. Stack them at full value and you have counted the tenant's budget twice. Underwrite $11,400 and treat the rest as upside you did not pay for.

Run those corrections together with an honest expense basis and the three columns look like this.

The single most revealing cell is repairs and maintenance. The pro forma column cuts it from $18,720 to $16,800 in the same document that proposes thirteen renovations. Buildings under renovation do not spend less on maintenance. A 47.70 percent expense ratio on a 1974 Class C building with part-time payroll is the kind of number that should stop you before any of the line items do, which is the fastest use of a multifamily operating expense ratio calculator.

Corrected, the stabilized NOI is $143,271, not $153,521. The seller's own destination, priced honestly, is $10,250 per year thinner than the column claims.

The Third Column: Grade Every Line by Evidence

Now add the two axes the pro forma column is missing. In your spreadsheet, every line that separates the T-12 from stabilized gets three more cells: what kind of evidence supports it, how many months until it shows up in the bank account, and what it costs to get there.

Grade the evidence in exactly three buckets, and let the grade decide the treatment rather than applying a haircut you invented.

• Contracted. A signed lease at the new rent, a signed service contract at the lower price, a tax abatement already granted. Zero months, zero cost. Pay full value for it.
• Demonstrated. Somebody has actually done this thing, in this building or on this block, and you can point at the unit. Price it with real months and real dollars.
• Assumed. It is plausible and nobody has done it here. Value it at zero. Not a discount, zero. It is your upside, and you do not buy your own upside.

Cedar Ridge is unusually clean on this test because five units are already renovated and leased at $1,075, which makes the renovation premium Demonstrated rather than Assumed. That is worth real money and it is why this deal is worth underwriting at all. Had those five units not existed, the entire $1,040 line would be Assumed and the correct value of the pro forma column would be zero.

The per-line value formula puts time and cost on the same row as the income:

=E5/$C$4/(1+$C$5)^(F5/12)-G5

Column E is the annual NOI the line adds, $C$4 is the cap rate, $C$5 is your unlevered cost of capital, column F is months to realization and column G is the cash it takes. The seller's implicit version of that same formula is =E5/$C$4, with F and G silently set to zero. Every dollar of difference between those two formulas is a dollar of negotiation you can defend line by line, which is a different conversation from telling a broker his rents are too high.

The Ramp Is Where the Money Actually Goes

Thirteen units need renovating after closing: the two vacant ones, the four scheduled to turn, and the seven the $1,040 line quietly requires. A 24-unit building with roughly 50 percent annual turnover gives you about one turn per month, and that is the constraint, not the contractor. Lay the plan out monthly.

Three things fall out of that grid that no annual pro forma can show you.

Month 6 earns less than month 3. That is not an error. Units under renovation pay no rent, so a month in which two units are down is worse than the month before you started. The pro forma column describes month 19 and charges you from month 1, and the shape in between dips below where you started.

Your going-in NOI is not $108,948 and it is not $153,521. Annualize month 1 and it is $99,660, because you are carrying renovation downtime on top of the trailing performance. Every lender sizing your loan will test coverage against something close to that number, not against the pro forma column. Run your debt service against the ramp rather than the destination, which is the only honest way to use a DSCR calculator for real estate on a value-add deal.

Stabilization arrives in month 19, and total capital is $155,800: thirteen renovations at $11,500 plus three ordinary turns at $2,100. Check that number with =13*$C$7+3*$C$9 rather than typing it, because it is the figure most likely to drift between drafts.

Now the part that surprises people. Push the whole schedule back, which is what happens when the contractor is booked, the permit takes a quarter, or turnover runs slower than the rent roll suggested.

Each month of slippage costs $7,923 of value. That is roughly two thirds of one month of stabilized NOI, permanently, for every month you are late. It is also the cheapest thing on this page to protect: a contractor under contract before you remove your financing contingency costs nothing and buys the first four months of that table.

The Bridge From Ask to Offer

Stack every correction in one column and the offer stops being a negotiating posture and becomes a calculation.

Your price is $2,051,178, or $85,466 per unit, against an ask of $2,558,680. The gap is $507,502, which is 19.83 percent. That is a large number and it is not a posture: every line above it is defensible in a room, and four of the seven lines are corrections to the seller's own arithmetic rather than differences of opinion.

Look at the last two rows together. The capital is $155,800 and the time is $180,866. Waiting costs more than building. Buyers negotiate renovation budgets down to the dollar and then accept a schedule with no contractor under contract, which is backwards. Getting your general contractor committed before closing is worth more than shaving $10,000 off the scope.

One line in that bridge deserves its own warning, because it bites twice. Property taxes reassess on sale, and the new bill follows your purchase price. At $2,051,178 the reassessed bill is $31,793, up $2,993 from the seller's $28,800. At the full ask the bill would be $39,660 instead, and at a 6.00 percent cap that extra $7,866 a year destroys $131,105 of value on its own. Model the line properly with a commercial real estate NOI calculator before you sign anything. Note the circularity: your offer sets your tax bill, which sets your NOI, which sets your offer. Three iterations of =$C$12*OfferPrice feeding back into the expense block converges in a spreadsheet without needing a solver.

What the Ask Requires You to Believe

Run the model the other direction. Hold the plan, the timing and the capital exactly as the broker implies, pay the full $2,558,680, execute every renovation on schedule, and resell in month 19 at the same 6.00 percent cap rate. The unlevered return is negative 6.81 percent.

The nominal version needs no discount rate at all. You put out $2,714,480, which is the purchase price plus $155,800 of renovation capital. You take in $2,442,391, which is $185,651 of NOI collected across nineteen months plus $2,256,740 from the sale. You are down $272,089 in plain dollars, before financing, before fees, having done everything right.

That resale figure is lower than the $2,387,844 in the bridge above, and the reason is the sharpest argument in this article. Your purchase price resets the assessment. Pay $2,558,680 and the county bills $39,660 a year instead of the $31,793 it would bill at a disciplined offer, which is $7,866 of income gone permanently. Overpaying does not merely cost you the overpayment. It makes the building you just bought worth less than the building you were looking at.

Then invert it once more. Keep the schedule, keep the capital, keep the 11 percent unlevered target, and ask what stabilized rent would make $2,558,680 a fair price. The answer is $1,198.48 per unit per month. That is 15.2 percent above the broker's own pro forma rent of $1,040, and 11.5 percent above $1,075, which is the best rent the best renovated unit in this building achieves today.

Read that sentence again, because it is the whole article. The seller's own most optimistic column does not support the seller's own price. You do not need a market view, a rate view, or a better comp set to reach that conclusion. You need the seller's numbers, a time axis and a cost axis.

The external picture makes the required rent harder rather than easier. The Bureau of Labor Statistics rent of primary residence index rose 2.75 percent in the twelve months to August 2026, the fourth consecutive year of deceleration from 7.76 percent in August 2023, 4.97 percent in 2024 and 3.50 percent in 2025. A plan that needs 11.5 percent above the best unit in the building is asking a decelerating market for an acceleration.

Once your price exists, stress it rather than defending it. The two inputs worth a data table here are the stabilized rent and the exit cap rate, not the renovation budget, and a proper real estate sensitivity analysis in Excel will tell you in one grid how much of your margin lives in an assumption you cannot control.

Build the Column That Prices the Column

The next time an offering memorandum lands, do not read the pro forma column as a forecast. Read it as an invoice for work you have not started. Then answer it in four steps.

1. Rebuild the market rent line from the rent roll, bucket by bucket, and calculate how many renovations the broker's blended number silently requires. At Cedar Ridge it was exactly seven, at $80,500.
2. Recompute every expense that is a percentage of something, and every expense that resets on sale. The frozen management fee alone was $38,424 of price, and the tax basis moves another $131,105 between the ask and a disciplined offer.
3. Grade every remaining line Contracted, Demonstrated or Assumed. Pay full value for the first, price the second with months and dollars, and pay nothing for the third.
4. Lay the plan out monthly, discount it, and subtract the capital. Then compare your number to the ask and send the bridge, not the number.

That last point is the practical one. A broker who receives an offer 19.83 percent below ask hangs up. A broker who receives a seven-row bridge, four rows of which correct his own spreadsheet, takes it to the seller. The bridge is the offer.

Building this from scratch takes a long evening and the first version will have a frozen management fee in it somewhere, because everyone's does. The Rental Property Analyzer ships with the structure already wired: the rent roll bucket build-up that produces the blended rent, the evidence grade column with the time and cost cells beside it, the monthly ramp with renovation downtime modeled per unit, the percentage-driven expense block that cannot be frozen by accident, and the tax reassessment loop that settles your offer against your own tax bill. You supply the rent roll and the T-12. It gives you the bridge, in the format a broker will actually carry to a seller.

The seller priced a destination. Price the trip, and send him the arithmetic.