PROMPT LIBRARY

AI Prompts for Institutional Real Estate Investors

Sourcing & Screening

Four prompts for building institutional-grade deal evaluation and fund-level screening tools.

INST-SCR-01 Designed for Apers

Back-of-Envelope Acquisition Model with Reverse Solve

Build a back-of-envelope acquisition screening model in Excel. Inputs: purchase price, in-place NOI, projected rent growth rate, hold period, exit cap rate, loan-to-value, interest rate, and amortization period. The model should calculate going-in cap rate, levered and unlevered IRR, equity multiple, and average annual cash-on-cash return. Include a reverse-solve section where the user inputs a target IRR and the model back-calculates the maximum purchase price. Format all inputs in a clearly labeled assumptions block with blue font, and all outputs in a summary section with conditional formatting (green if above target, red if below).

INST-SCR-02 Designed for Apers

Fund-Level Deal Screening Model

Build a fund-level deal screening model in Excel. The model should evaluate whether a prospective acquisition improves overall fund performance. Inputs: current fund size, number of existing assets, existing fund-level IRR and equity multiple, prospective deal size, projected deal-level IRR and equity multiple, and remaining investable capital. The model should calculate the pro forma blended fund IRR and equity multiple with the new deal included, the marginal impact on fund returns, the percentage of remaining capital consumed, and portfolio concentration by deal size. Include a pass/fail check against minimum fund return thresholds set by the user.

INST-SCR-03 Designed for Apers

Quick Leverage Analysis

Build a leverage analysis model in Excel that tests how different debt structures affect equity returns on a prospective acquisition. Inputs: purchase price, Year 1 NOI, annual NOI growth rate, hold period, and exit cap rate. The model should create a comparison table testing 5 debt scenarios side by side, each with its own LTV, interest rate, IO period, and amortization schedule. For each scenario, calculate levered IRR, equity multiple, cash-on-cash yield by year, debt yield, and DSCR at stabilization. Highlight the scenario that maximizes levered IRR and the scenario that maximizes minimum DSCR.

INST-SCR-04

Comparable Transaction Pricing Model

Build a comparable transaction pricing model in Excel. The user inputs 5-10 recent comparable sales, each with: sale price, square footage, number of units (if applicable), in-place NOI at time of sale, and sale date. The model should calculate price per SF, price per unit, and cap rate for each comp. Include an adjustment grid where the user can apply percentage adjustments for location, quality, age, occupancy, and market conditions. Output an adjusted price range (low/mid/high) for the subject property based on adjusted comp metrics, with a weighted average based on user-assigned relevance scores.

Core Underwriting & Pro Forma

Twelve prompts for building comprehensive acquisition and development models across all major property types.

INST-UW-01 Designed for Apers

10-Year Multifamily Acquisition Pro Forma

Build a 10-year multifamily acquisition pro forma in Excel. The model should include a unit mix table with unit type, count, average SF, in-place rent, and market rent for each type. Revenue section: gross potential rent (calculated from unit mix), vacancy and credit loss, concessions, other income (parking, laundry, pet fees, utility reimbursement). Expense section: line-item operating expenses with individual growth rate assumptions for each line. Below-the-line: capital reserves, renovation capex (if value-add), and net cash flow. Debt service section with IO period and amortization. Returns section: levered and unlevered IRR, equity multiple, and cash-on-cash by year. Exit based on a forward NOI cap rate. All assumptions should be in a dedicated blue-font input section at the top.

INST-UW-02 Designed for Apers

Office Acquisition Model with Tenant Rollover

Build an office acquisition model in Excel with tenant-by-tenant lease rollover analysis. Input a rent roll with up to 20 tenants, each with: tenant name, suite, SF leased, current rent per SF, lease start date, lease expiration, annual escalation structure, and renewal probability. The model should project revenue year by year, rolling expiring tenants to market rent (with user-set market rent growth) and applying downtime assumptions (months vacant between tenants), tenant improvement allowances per SF, and leasing commissions as a percentage of lease value. Include an expense section with recoverable and non-recoverable expenses, below-the-line TI/LC reserves, and calculate NOI, cash flow after debt service, and returns. Highlight years with heavy rollover exposure.

INST-UW-03 Designed for Apers

Industrial Pro Forma with Escalation Structures

Build an industrial/logistics acquisition pro forma in Excel. The model should handle three types of rent escalation structures that the user can select per tenant: fixed annual dollar increases, CPI-based increases (with a floor and cap), and fair market value resets at specified intervals. Input a rent roll with up to 10 tenants, each with lease terms and escalation type. Project revenue for a 10-year hold with the escalations calculated automatically based on type. Expense structure should be triple-net with CAM, insurance, and tax pass-throughs. Include capital reserves, debt service, and a returns summary.

INST-UW-04

Retail NNN Acquisition Model

Build a retail NNN acquisition model in Excel. The rent roll should include up to 15 tenants with: tenant name, SF, base rent per SF, NNN charges, percentage rent threshold and rate (if applicable), lease expiration, and renewal options. Revenue projection should model base rent with escalations, NNN recovery income, and percentage rent for tenants exceeding their sales breakpoint (user inputs projected sales growth). Model tenant rollover at expiration with user-defined renewal probability, mark-to-market adjustment, and downtime. Expense section: gross expenses with NNN recovery offset, leaving net landlord expense. Include credit tenant analysis showing the percentage of revenue from investment-grade tenants.

INST-UW-05 Designed for Apers

Hospitality Model with RevPAR Forecasting

Build a hotel acquisition model in Excel. Inputs: number of rooms, average daily rate (ADR), occupancy rate by month (12 months of seasonality), and projected annual growth rates for ADR and occupancy separately. Revenue section: rooms revenue (ADR x occupancy x rooms x days), food and beverage as a percentage of rooms revenue, and other income. Departmental expense section: rooms expense, F&B expense, and undistributed expenses (admin, marketing, maintenance, utilities) each as a percentage of their respective revenue line or total revenue. Below the line: management fee (percentage of total revenue), franchise/brand fee, FF&E reserve (percentage of total revenue), insurance, and property taxes. Calculate gross operating profit, NOI, and cash flow after debt service. Returns section with levered IRR and price per key analysis.

INST-UW-06 Designed for Apers

Ground-Up Development Pro Forma

Build a ground-up development pro forma in Excel. The model should have four phases: land acquisition, construction, lease-up/stabilization, and disposition or refinance. Land section: purchase price, closing costs, and entitlement costs. Construction budget: hard costs (with per-SF input and contingency percentage), soft costs (architecture, engineering, permits, legal, each as separate line items), developer fee, and construction loan (interest rate, LTV on cost, interest-only with interest reserve). Draw schedule: monthly construction draws over the build period with cumulative interest carry calculated monthly. Lease-up section: absorption rate (units or SF per month), starting rent, concessions during lease-up, and operating expenses ramping to stabilization. Stabilized valuation based on NOI and exit cap rate. Returns: development yield (stabilized NOI / total cost), developer profit margin, levered IRR, and equity multiple.

INST-UW-07 Designed for Apers

Multifamily Development with Phased Delivery

Build a ground-up multifamily development model in Excel for a phased project. The user inputs 2-4 phases, each with: number of units, construction start month, construction duration, and expected delivery month. Each phase has its own unit mix, target rents, and absorption pace post-delivery. Construction budget is allocated by phase with shared soft costs allocated on a pro-rata basis. The model should show a consolidated monthly cash flow with overlapping construction draws and lease-up across phases, a single construction loan covering all phases with a cumulative draw and interest calculation, and a stabilization date when the full project reaches target occupancy. Calculate phase-level and project-level returns.

INST-UW-08 Designed for Apers

Mixed-Use Development Model

Build a mixed-use development model in Excel with three components: residential (apartments), retail (ground floor), and parking (structured or surface). Each component has its own revenue assumptions, expense structure, and capital costs. Shared costs (common area maintenance, management overhead, shared utilities) are allocated across components using a user-defined allocation methodology (by SF, by revenue, or custom percentage). The construction budget has component-level hard costs and shared soft costs. The model should produce component-level NOI and returns, plus a consolidated project-level pro forma showing blended development yield, total cost basis, and blended levered IRR.

INST-UW-09

Build-to-Suit Industrial Model

Build a build-to-suit industrial development model in Excel. Inputs: land cost, building SF, hard construction cost per SF, soft cost budget, and pre-lease terms (tenant name, lease rate per SF, lease term, annual escalations, TI allowance). The model should calculate total development cost, development yield (starting rent / total cost), and stabilized value at a market cap rate. Include a construction timeline with monthly draws, a construction loan with interest carry, and calculate the developer's profit margin at completion. Compare the developer yield to market cap rates to show value creation. Include a sensitivity table showing development yield across construction cost per SF vs. lease rate per SF.

INST-UW-10 Designed for Apers

Value-Add Pro Forma with Renovation Phasing

Build a value-add acquisition pro forma in Excel. The model should layer a renovation program on top of a standard acquisition pro forma. Inputs: unit count, current in-place rents, target post-renovation rents, renovation cost per unit, and renovation pace (units per month). The model should project revenue month by month during the renovation period, accounting for units taken offline during renovation (user-defined downtime per unit), the rent step-up when renovated units come back online, and the blend of renovated vs. un-renovated units over time. After the renovation period, the model transitions to annual projections for the remaining hold period. Calculate returns with and without the renovation program to show incremental value creation.

INST-UW-11

Core-Plus Acquisition Model

Build a core-plus acquisition model in Excel designed for a long-term hold (10-15 years). Inputs should emphasize conservative assumptions: modest rent growth (1-3%), high occupancy (93%+), below-market leverage, and no major capital events. Revenue and expense projections should be straightforward with annual escalations. The focus of this model is on yield: cash-on-cash return by year, average annual cash yield over the hold, and total return decomposition (income return vs. appreciation). Include a debt maturity analysis showing the refi exposure at year 5 and year 10 under different rate environments. Sensitivity table: average cash yield across rent growth rate vs. interest rate at refi.

INST-UW-12

Debt Fund Lending Model

Build a debt fund lending model in Excel. This is not a property acquisition model - it models a fund that originates commercial real estate loans. Inputs: fund size, target number of loans, average loan size, weighted average coupon rate, average loan term, origination fee percentage, leverage on the fund (repo line or CLO), cost of fund-level leverage, management fee, and projected default rate. The model should project interest income, fee income, leverage cost, management fees, and net income to equity investors. Include a loan-by-loan schedule for up to 10 loans with individual terms. Model defaults as a percentage of outstanding loans with assumed loss severity. Calculate fund-level return on equity, net yield, and sensitivity to default rate and fund leverage.

Returns & Capital Structure

Ten prompts for modeling sophisticated waterfall structures, capital stacks, and fund-level return calculations.

INST-RET-01 Designed for Apers

Two-Tier Promote Waterfall

Build a two-tier equity waterfall model in Excel for a GP/LP joint venture. Inputs: total equity, GP co-invest percentage, preferred return rate (compounded annually), and promote split above the pref (e.g., 80/20 then 70/30). The model should take annual cash flows and a terminal disposition as inputs, then distribute them through the waterfall: first, return of capital plus accrued preferred return to all partners pro rata; second, catch-up to the GP until the promote share is reached; third, residual split at the final tier. Output a year-by-year distribution schedule showing LP and GP cash flows separately, plus LP IRR, LP equity multiple, GP IRR, GP equity multiple, and GP promote as a percentage of total GP compensation.

INST-RET-02 Designed for Apers

Three-Tier Waterfall with IRR Hurdles

Build a three-tier equity waterfall model in Excel using IRR-based hurdles. Tier 1: cash flows split pro rata until LP achieves an 8% IRR; Tier 2: cash flows split 80/20 (LP/GP) until LP achieves a 12% IRR; Tier 3: remaining cash flows split 70/30. All hurdle rates and splits should be user-adjustable. The model must iteratively calculate IRR at each distribution to determine which tier applies - use a monthly cash flow approach to handle mid-year distributions accurately. Include a lookback provision: at final disposition, recalculate whether the GP actually earned the promote distributions it received based on actual total returns. If the GP was overpaid, show the clawback amount.

INST-RET-03

GP Co-Invest Returns Model

Build a GP co-invest analysis model in Excel that separates GP economics into two components: returns on co-invested capital and promote carry. Inputs: total deal equity, GP co-invest percentage, LP equity, waterfall terms (pref rate, promote tiers), and deal-level cash flows. The model should calculate GP total return, then decompose it into: return on GP co-invest capital (at the same rate as LP), promote income above co-invest return, and the effective GP return combining both. Show the GP's blended IRR versus the LP's IRR to illustrate the promote leverage effect. Include a sensitivity table showing GP blended IRR across deal-level IRR vs. GP co-invest percentage.

INST-RET-04 Designed for Apers

Fund-Level Return Attribution

Build a fund-level return attribution model in Excel. Input up to 10 individual assets, each with: equity invested, acquisition date, disposition date, and annual cash flows. The model should calculate each asset's gross IRR and equity multiple. Then roll up to the fund level: calculate gross fund IRR (based on aggregated cash flows with proper timing), net fund IRR (after management fee and carried interest), and LP net IRR. Include return attribution showing each asset's contribution to fund-level returns. Management fee: input as annual percentage of committed or invested capital with a step-down option. Carried interest: user-defined carry percentage, hurdle rate, and catch-up provision.

INST-RET-05 Designed for Apers

Leverage Optimization Model

Build a leverage optimization model in Excel that evaluates 5 debt structures side by side for the same deal. Inputs: property-level cash flows (NOI by year and exit proceeds). Each of the 5 scenarios has its own: loan amount (or LTV), interest rate (fixed or floating with a spread), IO period, amortization, and term. For each scenario, calculate: levered cash flows, levered IRR, equity multiple, DSCR by year (minimum and average), debt yield, and loan-to-value at exit. Include a summary comparison table and highlight the optimal structure based on user-selected priority (max IRR, max minimum DSCR, or max equity multiple).

INST-RET-06 Designed for Apers

Mezzanine and Preferred Equity Model

Build a capital stack model in Excel with three layers: senior debt, mezzanine or preferred equity, and common equity. Inputs for senior debt: loan amount, interest rate, IO period, amortization. Inputs for mezz/pref: investment amount, coupon rate (cash pay and/or accrued), participation percentage (if any), and maturity. Inputs for common equity: remaining amount. The model should take property-level cash flows and distribute them according to the payment waterfall: senior debt service first, then mezz/pref coupon, then residual to common equity. At disposition, distribute proceeds in reverse seniority. Calculate returns for each layer: IRR, equity multiple, and cash-on-cash. Show how common equity returns change as the mezz/pref layer size increases.

INST-RET-07

Subscription Line Impact Model

Build a model in Excel that shows the impact of a subscription credit facility on fund-level returns. Inputs: fund size, capital call schedule (amounts and dates), subscription line size, sub line interest rate, and sub line maximum draw period. Without the sub line: capital is called from LPs at the time of each investment. With the sub line: the fund draws on the line first, then calls capital from LPs on a delayed schedule. The model should calculate gross IRR and net IRR both with and without the subscription line, showing how the delayed capital calls affect return timing. Include a comparison table and a note on the effective IRR boost.

INST-RET-08 Designed for Apers

Capital Call and Distribution Model

Build a closed-end fund capital call and distribution schedule model in Excel. Inputs: total fund commitments, number of LPs (up to 10) with individual commitment amounts, management fee rate, and projected deal-level cash flows by investment. The model should generate a call schedule showing the amount and date of each capital call, allocated pro rata across LPs. Distribution schedule should show proceeds from dispositions and operating cash flows, net of carried interest, distributed to each LP. Track each LP's unfunded commitment, contributed capital, distributions received, DPI, TVPI, and net IRR. Include a fund-level summary sheet aggregating across all LPs.

INST-RET-09

Refinance Analysis Model

Build a refinance analysis model in Excel. Inputs: current loan balance and terms, current property NOI, and two refinance options (each with new loan amount, rate, IO period, amortization, and closing costs). The model should compare three paths: hold with existing debt, refinance option A, and refinance option B. For each path, calculate go-forward levered cash flows, cash-out proceeds (if refi amount exceeds current balance), new DSCR and LTV, and updated equity IRR from today through disposition. Include a breakeven analysis: how many years must the investor hold post-refi for the refinance to be accretive versus keeping the existing loan?

INST-RET-10 Designed for Apers

Disposition Timing Model

Build a disposition timing model in Excel that compares selling now versus holding for 1, 2, 3, or 5 additional years. Inputs: current property NOI, NOI growth rate, current market cap rate, projected cap rate at each future sale date, remaining debt balance schedule, and annual capital expenditure forecast. For each scenario, calculate: gross sale price, net proceeds after debt payoff and closing costs, cumulative cash flows from operations during the additional hold period, and total equity IRR and multiple from original investment. Output a comparison table showing returns by exit year, and highlight the optimal exit timing.

Due Diligence & Analytical Models

Six prompts for building comprehensive due diligence tools and analytical frameworks.

INST-DD-01 Designed for Apers

Rent Roll Analysis Model

Build a rent roll analysis model in Excel. Input a rent roll with up to 50 tenants, each with: tenant name, unit/suite, SF or unit count, lease start, lease expiration, current rent (monthly or annual), and market rent. The model should calculate: in-place rent vs. market rent per tenant (with loss-to-lease or gain-to-lease), weighted average lease term (by SF and by revenue), rollover exposure by year (SF and revenue expiring), occupancy rate (physical and economic), and a tenant concentration table showing what percentage of total revenue each of the top 5 tenants represents. Use conditional formatting to flag below-market leases (>10% loss-to-lease) and near-term expirations (within 12 months).

INST-DD-02 Designed for Apers

T-12 Expense Benchmarking Model

Build a trailing-12-month expense benchmarking model in Excel. Input the subject property T-12 with line-item operating expenses. Input 3-5 comparable properties with the same line-item structure. The model should calculate per-unit and per-SF metrics for each expense line across all properties, show the subject property's percentile rank for each line item versus the comps, flag line items where the subject is more than 15% above or below the comp average, and calculate total operating expense ratio. Include a normalized expense section where the user can adjust for non-recurring items, management fee changes, and ownership-specific costs.

INST-DD-03 Designed for Apers

Lease Abstraction Model

Build a lease abstraction model in Excel for commercial properties. Input up to 20 leases, each with: tenant, premises, term (start/end), base rent schedule (with escalations), expense structure (NNN, modified gross, full service), renewal options (number, term, rate), expansion/contraction rights, termination options (date and penalty), co-tenancy or exclusivity clauses, and tenant improvement allowance. The model should output a summary matrix showing all tenants side by side with key terms, a rent schedule projecting each tenant's rent payments over the full lease term including escalations, and a critical dates calendar flagging option exercise deadlines, lease expirations, and rent adjustment dates.

INST-DD-04 Designed for Apers

Tenant Concentration Risk Model

Build a tenant concentration risk model in Excel. Input the rent roll with each tenant's annual rent, lease expiration, industry/sector, and credit rating (if available). The model should calculate: revenue concentration by tenant (top 1, 5, 10 tenants as percentage of total), revenue concentration by industry/sector, a rollover risk score combining lease term remaining and concentration (short remaining term + high concentration = high risk), and a weighted average credit quality metric. Include a stress test: if the largest tenant defaults, what is the impact on NOI, DSCR, and property valuation?

INST-DD-05

Tax and Insurance Reassessment Model

Build a post-acquisition operating cost reassessment model in Excel. Inputs: current assessed value, current tax rate, current annual tax bill, purchase price, jurisdiction reassessment rules (percentage of purchase price assessed, or specific formula), current insurance premium, and projected post-acquisition insurance rate. The model should calculate the expected post-acquisition property tax bill based on reassessment, the year-over-year increase, and the impact on NOI and cap rate. Do the same for insurance if the property will be re-underwritten at the new basis. Show the difference between underwriting to the seller's trailing costs versus the buyer's projected costs.

INST-DD-06 Designed for Apers

Deferred Maintenance and Capital Reserve Model

Build a capital needs assessment model in Excel. Input a list of building components (roof, HVAC, elevator, parking lot, plumbing, electrical, etc.), each with: estimated remaining useful life (years), replacement cost, and current condition rating (1-5). The model should project the capital expenditure schedule over a 10-year hold, calculate an annual reserve contribution needed to fund projected replacements, prioritize items by urgency (short remaining life + poor condition first), and calculate total deferred maintenance as of today. Include a sensitivity showing how total capital needs change if component life estimates are off by 20%.

Asset Management & Portfolio

Eight prompts for portfolio-level tracking, budgeting, and asset management operations.

INST-AM-01 Designed for Apers

Portfolio Summary Model

Build a portfolio summary model in Excel covering up to 20 assets. For each asset, input: property name, property type, acquisition date, purchase price, current equity invested, current year budget NOI, actual year-to-date NOI, underwriting NOI, and current estimated value. The model should calculate portfolio-level metrics: total AUM, weighted average cap rate, aggregate NOI budget vs. actual variance, aggregate NOI actual vs. underwriting variance, and portfolio-level IRR to date. Use conditional formatting to flag assets that are more than 5% below budget or more than 10% below underwriting.

INST-AM-02 Designed for Apers

Quarterly Reforecast Model

Build a quarterly reforecast model in Excel for a single asset. Inputs: the original annual underwriting pro forma (10-year) and actual year-to-date results. The model should replace completed months with actuals, reforecast the remaining months of the current year based on run-rate actuals (with user-adjustable overrides), and reproject future years based on the revised current-year base. Show the original underwriting, the reforecast, and the variance for every line item. Recalculate projected returns (IRR and equity multiple) under the reforecast and compare to the original underwriting.

INST-AM-03 Designed for Apers

Annual Operating Budget Model

Build an annual operating budget model in Excel with monthly granularity. Revenue section: rent roll with individual unit/tenant projections by month, vacancy and credit loss assumptions, and other income. Expense section: each operating expense line item budgeted by month (allowing for seasonal variation in utilities, landscaping, snow removal, etc.). Below the line: capital expenditure budget by month with project-level detail. The model should auto-calculate NOI by month and year, expense ratio, and variance to the prior year actual results. Include a column comparing the new budget to the current year actuals and the original underwriting.

INST-AM-04

Disposition Analysis Model

Build a disposition analysis model in Excel that compares holding versus selling an asset. Inputs: current property performance (trailing NOI), projected go-forward NOI, remaining debt terms, current estimated market value (broker opinion or appraisal), estimated closing costs, and remaining business plan milestones. The model should calculate: net sale proceeds today, projected hold-period cash flows, projected future sale proceeds at multiple exit dates, and IRR and equity multiple for each scenario (sell now, hold 1 year, hold 3 years, hold to maturity). Include the opportunity cost of redeploying sale proceeds into a new investment at an assumed reinvestment return.

INST-AM-05 Designed for Apers

Portfolio-Level Returns Model

Build a portfolio-level returns aggregation model in Excel. Input up to 15 assets, each with: equity invested, date invested, annual operating cash flows, and disposition proceeds (actual or projected). The model should calculate each asset's standalone IRR and equity multiple, then aggregate into a portfolio-level gross IRR using time-weighted cash flows. Layer on fund-level costs: management fees (annual percentage of committed or invested capital), fund expenses, and carried interest. Calculate net fund IRR, net equity multiple, DPI, and TVPI. Include a return attribution showing the contribution of each asset to the portfolio gross and net IRR.

INST-AM-06 Designed for Apers

NAV Calculation Model

Build a net asset value (NAV) calculation model in Excel for a real estate fund. Input each asset's current estimated value (from appraisal or internal valuation), outstanding debt balance, accrued interest, and any working capital items. At the fund level, input management fee accrual, promote accrual (if applicable), fund-level liabilities, and cash on hand. The model should calculate gross asset value, net asset value, and NAV per unit (or per LP ownership share). Include a quarter-over-quarter comparison showing NAV change and its components (appreciation, income, fees, leverage effect).

INST-AM-07

Capex Tracking Model

Build a capital expenditure tracking model in Excel for an asset under renovation or development. Input a budget with up to 20 line items, each with: description, budgeted amount, contractor, and projected completion date. The model should track: actual spend to date by line item, remaining budget, percentage complete, change orders (separate column for approved changes to original budget), retainage held, and lien waiver status. Calculate total budget vs. actual variance, projected final cost (actual to date + remaining estimates), and a completion timeline. Use conditional formatting to flag line items over budget or behind schedule.

INST-AM-08 Designed for Apers

Lease-Up Tracking Model

Build a lease-up tracking model in Excel for a development or value-add project. Inputs: total leasable SF or units, target rent per SF or per unit, projected absorption pace (units or SF per month), concession schedule during lease-up, and stabilized occupancy target. The model should track: actual leases signed (date, tenant, SF, rent, concessions) versus the projected absorption pace, economic occupancy versus physical occupancy over time, and projected stabilization date based on actual pace. Calculate the deviation from the original business plan and the impact on projected stabilized NOI and returns.

Scenario & Sensitivity

Six prompts for building robust scenario analysis and stress testing models.

INST-SCEN-01 Designed for Apers

Three-Scenario Model with Toggle

Build a scenario analysis model in Excel with three scenarios: base case, bull case, and bear case. The model should have a scenario selector (dropdown or input cell) that switches all assumptions simultaneously. Each scenario defines: rent growth rate, expense growth rate, exit cap rate, vacancy rate, and interest rate. The model should calculate a full pro forma under each scenario, with a summary page showing IRR, equity multiple, cash-on-cash, and NPV side by side for all three scenarios. Include a probability-weighted blended return where the user assigns probability weights to each scenario.

INST-SCEN-02 Designed for Apers

Interest Rate Stress Test

Build an interest rate stress test model in Excel. Input a base case pro forma with a floating-rate loan. The model should test five interest rate environments: base rate, +100bps, +200bps, +300bps, and a user-defined stress rate. For each environment, calculate: annual debt service, DSCR by year, levered cash flow by year, levered IRR, and equity multiple. Flag any scenario where DSCR falls below 1.25x or cash flow turns negative. Include a rate cap analysis: if the borrower purchases a rate cap at a specified strike rate, show the capped debt service and the cost of the cap.

INST-SCEN-03

Occupancy Sensitivity Model

Build an occupancy sensitivity model in Excel. Input a base case pro forma. The model should test stabilized occupancy at 5 percentage point intervals from 70% to 95%. For each occupancy level, calculate: effective gross income, NOI, DSCR, levered cash flow, levered IRR, and equity multiple. Include a breakeven analysis showing the minimum occupancy required to cover debt service, and the minimum occupancy required to achieve the target IRR. Output as a formatted sensitivity table with conditional formatting showing green/yellow/red zones.

INST-SCEN-04

Rent Growth Scenario Model

Build a rent growth scenario model in Excel. Input a base case pro forma. The model should test five rent growth paths: 0% (flat), 2%, 3% (base case), 4%, and a user-defined rate. For each path, project revenue through the hold period and calculate NOI, exit value (based on terminal NOI and exit cap rate), levered IRR, and equity multiple. Include a net present value calculation at a user-defined discount rate for each scenario. Output a comparison table and highlight the breakeven rent growth rate needed to achieve the target return.

INST-SCEN-05 Designed for Apers

Cap Rate Expansion Stress Test

Build a cap rate sensitivity model in Excel focused on exit valuation risk. Input a base case pro forma with a specific exit cap rate assumption. The model should test exit cap rates at +0, +25, +50, +75, and +100 basis points from the base assumption. For each exit cap, calculate: gross sale price, net sale proceeds, levered IRR, equity multiple, and profit above/below the equity invested. Include a two-way table: exit cap rate vs. hold period (3, 5, 7, 10 years), showing IRR at each intersection. Conditional formatting: green above target IRR, red below.

INST-SCEN-06 Designed for Apers

Construction Cost Overrun Model

Build a construction cost overrun sensitivity model in Excel for a development deal. Input the base case development pro forma with total hard costs, soft costs, and contingency. The model should test cost overruns at 0%, 5%, 10%, 15%, and 20% above the base hard cost budget. For each overrun level, calculate: revised total development cost, additional equity required (assuming the construction loan is maxed), revised development yield, revised levered IRR, and revised equity multiple. Show whether the contingency is sufficient to absorb each overrun level before requiring additional equity. Include the impact on the construction loan: does the overrun push LTC above the lender's maximum?

Reporting

Four prompts for building institutional-grade LP reporting and investor communications.

INST-RPT-01 Designed for Apers

LP Quarterly Report Model

Build an LP quarterly reporting model in Excel. Input: fund-level data (committed capital, called capital, distributed capital, NAV) and asset-level data (up to 10 assets with current quarter NOI actual vs. budget, occupancy, and estimated value). The model should produce a fund performance summary (net IRR, DPI, TVPI, RVPI), a capital account statement (calls, distributions, ending balance), an asset-level summary table with key performance metrics, and a quarter-over-quarter comparison showing changes. Format all outputs as a clean, presentation-ready report with headers, borders, and number formatting suitable for direct export or print.

INST-RPT-02

Investor Pitch Model

Build an investor pitch model in Excel that shows projected fund-level returns under different deployment scenarios. Inputs: target fund size, projected number of investments, average deal size, average projected deal-level IRR and equity multiple, management fee, and carried interest terms. The model should project fund cash flows under three deployment pace scenarios (fast, moderate, slow), showing: vintage year returns, time to first distribution, cumulative distributions by year, and net LP IRR and equity multiple for each pace scenario. Include a fee drag analysis showing gross-to-net return impact.

INST-RPT-03

Deal-Level Investor Summary

Build a single-page deal-level investor summary model in Excel. Input: deal name, property type, location, purchase price, equity required, projected hold period, and annual projected cash flows. The model should produce a formatted one-page summary sheet with: investment overview (key facts), sources and uses table, projected returns (IRR, equity multiple, average cash-on-cash), a simplified annual cash flow summary, and a sensitivity table (IRR across exit cap rate vs. rent growth). Format for print on one page with professional headers and consistent number formatting.

INST-RPT-04 Designed for Apers

Annual Fund Performance Model

Build an annual fund performance model in Excel. Input: fund vintage year, committed capital, called capital by year, distributions by year, and current NAV. The model should calculate: net IRR since inception, TVPI, DPI, RVPI by vintage year, and a J-curve showing cumulative net cash flows over the fund life. Include a PME (public market equivalent) calculation where the user inputs the benchmark index annual returns, and the model calculates the PME ratio. Output a formatted performance summary suitable for inclusion in an annual report.