Pro forma inputs versus calculations
Inputs are the judgments a person types into the model: market rent, rent growth, vacancy, expense growth, capital reserves, interest rate, exit cap rate and hold period. Calculated lines apply arithmetic to those inputs, and that group includes effective revenue, net operating income, debt service, value, return on cost and the headline return. A calculated line can still carry an assumption when a growth factor or a plug sits inside its formula, so the useful sort follows what each line depends on.
Where an assumption hides inside a formula cell
A cell is an input when a person chose its value. It is a calculation when a rule produces its value from other cells. That definition sorts most of a pro forma in a few minutes.
The difficulty starts with formulas that contain a typed number. A rent line written as units multiplied by rent multiplied by a typed growth factor is a calculation with an assumption welded to it. The sheet will display the product with the same confidence whether the factor was researched or guessed.
Two reviews apply to the same sheet and they answer different questions. An arithmetic review confirms that the formulas do what they say. An assumption review asks whether the future rents, capital expenditures, interest rates and exit cap rates are defensible. Some fund processes give the second job to a team separate from the one that built the model, and that team tests the defensibility of those four assumptions.
Diligence questionnaires for non-listed vehicles ask a manager to name the method behind exit cap rates, rental growth, lease-up vacancy, capital expenditures and refinancing. Those five make a workable short list of lines that need a stated method before anyone relies on the output.
A fast test is to scan every formula for typed constants. Each constant found is an input and belongs on the assumptions page, where a reader can see it next to its source.
Inputs also differ in how much support they carry. Trailing operating results and signed lease rents are observations with documents behind them. Forecast rents, forecast vacancy and the exit cap rate have only judgment behind them, and the pro forma treats both kinds as numbers in cells.
A line-by-line sort of an operating pro forma
Revenue inputs are market rent and rent growth, plus vacancy, credit loss and lease-up timing. Expense inputs are expense growth and reserve levels. The capital expenditure budget sits with the financing inputs: loan amount, interest rate and the amortization term. The valuation inputs are the hold period and the exit cap rate.
Calculated lines start with potential rental revenue, which is units or area multiplied by rent. Effective revenue is potential revenue less vacancy and credit loss, plus other income. Net operating income is effective revenue less operating expenses. Debt service follows from the loan amount, the rate and the amortization term. Coverage ratios and debt yield divide net operating income by debt service or by the loan, and the thresholds lenders apply vary by lender.
Exit value is capitalized income divided by the exit cap rate. Cash flow after debt service is net operating income less debt service and any capital spending. The multiple and the internal rate of return are calculated from those cash flows and the timing the model assigns them.
Some lines straddle the boundary. In-place rent comes from a rent roll and is an observation, while rent on rollover is an assumption. A loan amount sized to a coverage test is a calculation, yet the test threshold is a lender input and the rate used in the test is another typed number.
The sorting rule that survives these cases is to trace each cell back through its formula. A typed constant or a judgment buried in the formula is an assumption, and a cell built only from other cells by arithmetic is a calculation.
Exit value is a calculation resting on one typed rate
Capitalized value equals one year of net operating income divided by a cap rate. The division is exact, so the exit value line looks like a calculation. It carries two judgments: which income is being capitalized and which rate is applied to it.
The rate and the income need the same basis. A cap rate drawn from actual or trailing twelve month income does not pair with a projected stabilized income, and the reverse holds too. Mixing the bases credits the property with income that the rate never priced.
Support for the rate is thin when trades are scarce. Sparse transactions weaken the sales comparison approach, and shifts in rate expectations move discount rates and exit cap rate assumptions together. A reader who sees an exit cap rate in a quiet market is looking at an input with little observable anchor.
Because value divides by the rate, equal steps in the rate do not produce equal steps in value. The worked example below shows the size of the move.
Return on cost divides two forecasts
Return on cost is the property’s potential net operating income divided by total project cost. Both sides are projections in a ground-up deal. The numerator depends on rent and vacancy at stabilization, and the denominator depends on the budget, along with the contingency and the schedule.
One illustration of the metric sets a 5.2% return on cost against 3.5% cap rates. The spread between the two is calculated, yet it exists only if the income forecast and the cost budget both hold. Cost growth lowers the ratio directly, since the denominator rises while the numerator waits for lease-up.
A clean way to stress the denominator is to raise the cost inputs and hold the sale price output unchanged. That isolates the effect of higher spending. A scenario that also lifts the expected price answers a different question and hides the cost effect inside a price assumption.
Rent growth reaches every calculated line
Rent growth is one typed factor that compounds. It flows into potential revenue, then effective revenue, then net operating income, then exit value through the cap rate, and finally the return. A change to that one cell moves most of the calculated lines below it.
Underwriting that scrutinizes rent growth and potential exits matters most where supply is heavy and vacancies are rising. A steady growth line does not hold in those markets, and the pro forma is where that shows up first.
Growth sits beside other inputs that condition it. Competition and cap-rate movement belong on the same assumptions page, together with the cost of debt, because each changes what a given rent forecast is worth to an equity investor.
Pro forma figures serve as a reference point and are rarely accurate. Across one sponsor’s deals, 0% of projects have hit the exact forecasted return. A model that separates inputs from calculations makes any gap traceable to specific cells.
“We can no longer assume that rent is going to grow steadily over time anymore.”
“They know pro forma numbers are a useful reference point but rarely accurate.”
Worked example: one-year sale of a hypothetical property, base case against stressed case
The hypothetical purchase price is $10,000,000, funded with a $6,000,000 interest-only loan at 6%. Equity is the price minus the loan: $10,000,000 minus $6,000,000 equals $4,000,000. The loan is 60% of price ($6,000,000 divided by $10,000,000, multiplied by 100). Potential revenue is $1,200,000 and operating expenses are $480,000 in both cases. The hold is one year with a sale at year end. Fees and selling costs are excluded, and so is capital spending, so both cases use the same capital and period.
The typed inputs are the purchase price with its loan terms, plus the revenue and expense figures, the vacancy rate and the exit cap rate. The base case uses 5% vacancy and a 6% exit cap rate. The stressed case uses 10% vacancy and a 7.5% exit cap rate. Everything else below is calculated.
Base operations: vacancy loss is $1,200,000 multiplied by 5%, or $60,000. Effective revenue is $1,200,000 minus $60,000, or $1,140,000. Net operating income is $1,140,000 minus $480,000, or $660,000.
Stressed operations: vacancy loss is $1,200,000 multiplied by 10%, or $120,000. Effective revenue is $1,200,000 minus $120,000, or $1,080,000. Net operating income is $1,080,000 minus $480,000, or $600,000.
Interest is $6,000,000 multiplied by 6%, or $360,000. Cash flow after interest is $660,000 minus $360,000, or $300,000 in the base case, and $600,000 minus $360,000, or $240,000 in the stressed case. The going-in cap rate on the same $10,000,000 price is $660,000 divided by $10,000,000, or 6.6% in the base case, and $600,000 divided by $10,000,000, or 6% in the stressed case.
Exit value is net operating income divided by the exit cap rate. Base: $660,000 divided by 6% equals $11,000,000. Stressed: $600,000 divided by 7.5% equals $8,000,000.
Base equity return: sale value less the loan is $11,000,000 minus $6,000,000, or $5,000,000. Adding the $300,000 of cash flow gives $5,300,000 of total receipts. Profit is $5,300,000 minus $4,000,000, or $1,300,000, which is 32.5% of equity ($1,300,000 divided by $4,000,000, multiplied by 100).
Stressed equity return: sale value less the loan is $8,000,000 minus $6,000,000, or $2,000,000. Adding the $240,000 of cash flow gives $2,240,000 of total receipts. Equity of $4,000,000 minus $2,240,000 leaves a shortfall of $1,760,000, which is a 44% loss of equity ($1,760,000 divided by $4,000,000, multiplied by 100). Total receipts fell by $3,060,000 between the cases ($5,300,000 minus $2,240,000). That equals a $3,000,000 drop in sale value ($11,000,000 minus $8,000,000) plus a $60,000 drop in cash flow ($300,000 minus $240,000), and the fixed loan puts all of it on equity.
Isolating the two shocks shows how the pieces combine. Vacancy alone, at the base 6% cap rate, gives $600,000 divided by 6%, or $10,000,000, a $1,000,000 loss of value. The exit cap shock alone gives $660,000 divided by 7.5%, or $8,800,000, a $2,200,000 loss. Added together those are $3,200,000, which is $200,000 more than the combined drop of $3,000,000. The combined case applies the higher rate to a smaller income, so less value remains to lose.
Common mistakes that hide assumptions in calculated output
A typed exit value placed beside a calculated net operating income is an input that looks like an output. The cap rate it implies equals the income divided by the value, and that implied rate is the assumption the typed value is making. A sheet that never shows it leaves the reader unable to compare the exit pricing with the going-in pricing.
A cap rate taken from actual or trailing income and applied to a stabilized pro forma income mixes two bases. The pro forma income is higher than the actual income it is being priced against, so the result credits the property with income the rate did not capitalize.
Vacancy applied twice shrinks income without any formula being wrong. If the starting revenue is already net of vacant space and a later line deducts a vacancy allowance again, net operating income falls by the second deduction. Capitalization then divides that shortfall by the cap rate, so the error in value is a multiple of the error in income.
Capital items expensed above the net operating income line depress the income that exit value capitalizes. Each dollar of misclassified spending removes that dollar from income and removes the dollar divided by the cap rate from value, while the same dollar also appears in project spending if the model books it twice.
A stress case that raises cost inputs and also lifts the expected price tests nothing about cost. The price assumption offsets the cost assumption, and the reported return reflects the offset.
A reproduced internal rate of return confirms that the stated cash flows generate that rate. Even when the cash flows are arithmetically correct, a spreadsheet that carries unsupported rent growth or an aggressive exit cap rate will return a confident IRR and equity multiple that look rigorous while resting on guesses, so each input needs its own check against comparable leases and recent sales.
Checklist questions for sorting a pro forma
Does every typed value show its source and whether it describes an existing condition or a forecast? That label tells a reader what kind of support the input needs.
Does any formula contain a typed constant? A growth factor or plug inside a formula is an assumption that the assumptions page does not list.
Does vacancy start from revenue that still includes the vacant space? A consistent starting basis keeps the same loss from entering twice.
Does the cap rate use the same income basis as the income being capitalized? Actual or trailing income and projected income carry different meanings for the rate.
Does the model show the implied cap rate on any entered value? The implied rate is net operating income divided by value and exposes the pricing the entry assumes.
Does each stress case name which inputs moved and which stayed fixed? That boundary separates an isolated sensitivity from a combined scenario.
Do capital expenditures sit in one place, either above or below net operating income? One location prevents the same spending from reducing income and cash flow together.
Do rent growth, competition, debt cost and cap-rate movement each have a stated method? Those inputs shape the output of an equity return.
Are the arithmetic review and the assumption review recorded separately? The two conclusions show whether the sheet is reproducible and whether its forecasts were examined.