📘
🏠 Rental Portfolio Workbook — Complete Instruction Manual
Instruction Manual · How to use this template
The Rental Portfolio Workbook is a professional Google Sheets template designed for landlords, real-estate investors, and property managers who want a clear, numbers-driven view of every property they own. You enter each property's rent, expenses, mortgage, and lease details once; the workbook then auto-calculates Net Operating Income, cap rates, debt-service coverage, cash-on-cash returns, vacancy risk, and a full 5-year equity projection — across five interlinked sheets and dozens of charts. Whether you hold two doors or twenty, this workbook replaces scattered spreadsheets with one living dashboard so you can spot under-performers, plan refinances, and never miss a lease renewal.
⚡ Quick start
1Step 1 — Open the workbook and read the 'Read Me' tab for a quick orientation, then duplicate the file to your own Google Drive so your edits are saved.
2Step 2 — Go to the 🏠 Property Ledger sheet and enter one row per property: fill in the Property name, Type, Units, Gross Rent, Vacancy %, Expenses, Mortgage payment, and current market Value. All formula columns (marked ƒ) will populate instantly.
3Step 3 — Switch to the 📅 Lease Tracker and add one row per tenant: enter the Property name (must match the Ledger exactly), Tenant name, lease Start and End dates, and the Escalation % for renewal. The sheet auto-pulls rent from the Ledger and calculates months remaining and renewal projections.
4Step 4 — Open 💵 Debt & Leverage and enter each property's Loan Balance, Interest Rate, and Term in years. The sheet pulls market values from the Ledger and computes LTV, equity, principal/interest splits, and DSCR.
5Step 5 — In 📈 Equity Growth, enter the annual Appreciation % you expect for each property. The sheet pulls current values and equity from the other tabs and projects equity out five years.
6Step 6 — Visit 📊 Portfolio Dashboard to see your entire portfolio roll-up — vacancy risk, cash flow, weighted cap rate, and rent concentration — all computed automatically from the other four sheets. Set a Vacancy Goal per property to unlock the recovery analysis.
The Property Ledger is the foundation of the entire workbook. Every other sheet pulls data from here, so accuracy matters. Use it to record each property's core financials — rent, vacancy, operating expenses, mortgage, and market value — and instantly see per-property profitability metrics like NOI, cap rate, DSCR, and cash-on-cash return.
✍️ Step by step
11. In the Property column, type a short, unique name for each property (e.g., '123 Oak St' or 'Elm Duplex'). This exact name is used as the lookup key on every other sheet, so keep it consistent.
22. In Type, enter the property category — for example 'SFH' (single-family home), 'Duplex', 'Triplex', 'Fourplex', or 'Apartment'. This is used to group data on the Portfolio Dashboard charts.
33. In Units, enter the total number of rentable units (enter 1 for a single-family home). This drives the per-unit metrics.
44. In Gross Rent, enter the total monthly rent you charge across all units of that property before any vacancy adjustment (e.g., 3200 for $3,200/month).
55. In Vacancy, enter the vacancy rate as a percentage (e.g., 5 for 5%). This represents the portion of gross rent you expect to lose to vacancies over the year.
66. In Expenses, enter total monthly operating expenses — property tax, insurance, maintenance, management fees, HOA, utilities you cover, etc. Do NOT include the mortgage payment here; that goes in the Mortgage column.
77. In Mortgage, enter the total monthly mortgage payment (principal + interest + escrow if included). If the property is owned free and clear, enter 0.
88. In Value, enter the current estimated market value of the property (e.g., 425000 for $425,000). This is used to compute cap rate, the 1% rule, and per-unit value.
99. Once a row is complete, all ƒ columns populate automatically. Review the ƒStatus and ƒNOI Rank columns to see how each property stacks up.
📋 Column-by-column
| Property | INPUT — Type the unique name or address of the property (e.g., '123 Oak St'). This is the master key used by every other sheet to look up data, so it must be spelled identically everywhere. Keep it short but distinctive. |
| Type | INPUT — Enter the property type or category, such as 'SFH', 'Duplex', 'Triplex', 'Fourplex', 'Condo', or 'Apartment'. This text is used to group and color-code data on the Portfolio Dashboard's 'by Type' charts. Use consistent labels across all properties. |
| Units | INPUT — Enter the number of rentable units as a whole number (e.g., 1 for a single-family home, 4 for a fourplex). This drives per-unit calculations like ƒRent/Unit, ƒCF/Unit, and ƒ$/Unit. If a property has an accessory dwelling unit you rent separately, count it. |
| Gross Rent | INPUT — Enter the total monthly rent collected (or expected) from all units of this property, in dollars, before any vacancy deduction (e.g., 3200). If a duplex rents Unit A for $1,500 and Unit B for $1,700, enter 3200. |
| Vacancy | INPUT — Enter the expected vacancy rate as a percentage number (e.g., type 5 for 5%, not 0.05). A typical residential vacancy rate is 3–8%. Higher values mean you expect more lost rent due to turnover or empty units. |
| ƒNet Rent | FORMULA — Net Rent equals Gross Rent minus the Vacancy loss: Net Rent = Gross Rent × (1 − Vacancy ÷ 100). This is the realistic monthly rental income after accounting for expected vacancies. A healthy Net Rent should be close to Gross Rent; if the gap is large, your vacancy assumption may be high or your occupancy needs attention. |
| Expenses | INPUT — Enter total monthly operating expenses in dollars (e.g., 850). Include property taxes (monthly share), insurance, repairs/maintenance, property management fees, HOA dues, landscaping, and any landlord-paid utilities. Do NOT include the mortgage payment — that is entered separately. |
| ƒNOI | FORMULA — Net Operating Income. NOI = Net Rent − Expenses. This is the property's monthly profit from operations before debt service. A positive NOI means the property covers its operating costs from rent. A negative NOI means you are losing money operationally even before the mortgage. Typical healthy NOI margins (NOI ÷ Gross Rent) are 40–60% for residential rentals. |
| Mortgage | INPUT — Enter the total monthly mortgage payment in dollars (principal + interest, and escrow if your lender includes it). For example, 1450. If you own the property outright with no loan, enter 0. |
| ƒCash Flow | FORMULA — Cash Flow = NOI − Mortgage. This is the actual monthly cash you pocket (or lose) after paying the mortgage. Positive cash flow means money in your pocket each month. Negative cash flow means you are subsidizing the property out of pocket. Even a small positive cash flow (e.g., $100–$300/unit) is considered acceptable for appreciation-focused investors. |
| Value | INPUT — Enter the current estimated market value of the property in dollars (e.g., 425000). Use a recent appraisal, Zillow Zestimate, or comparable sales. This drives cap rate, GRM, the 1% rule test, per-unit value, and equity calculations on other sheets. |
| ƒCap Rate | FORMULA — Capitalization Rate. Cap Rate = (NOI × 12) ÷ Value × 100, expressed as a percentage. It measures annualized return on the property as if you paid all cash. A higher cap rate means higher yield. Typical ranges: 4–6% in expensive/low-risk markets, 7–12% in higher-yield or higher-risk markets. Below 4% may signal overpaying; above 12% may indicate risk. |
| ƒOpEx % | FORMULA — Operating Expense Ratio. OpEx % = Expenses ÷ Gross Rent × 100. It tells you what share of your gross rent is consumed by operating costs. A healthy OpEx % is 35–50% for residential. Above 50% means expenses are eating too much rent and you should investigate cost-cutting or rent increases. |
| ƒDSCR | FORMULA — Debt Service Coverage Ratio. DSCR = NOI ÷ Mortgage. It measures how comfortably rental income covers the mortgage. A DSCR of 1.0 means you barely break even; above 1.25 is considered healthy by most lenders; below 1.0 means you are losing money after the mortgage. If Mortgage is 0 (no debt), DSCR may display as N/A or a very high number. |
| ƒGRM | FORMULA — Gross Rent Multiplier. GRM = Value ÷ (Gross Rent × 12). It estimates how many years of gross rent it would take to equal the purchase price. Lower is better — a GRM of 8–12 is typical for residential. Above 15 suggests the property may be overpriced relative to its income. |
| ƒRent/Unit | FORMULA — Rent per Unit = Gross Rent ÷ Units. Shows the average monthly rent per unit. Use this to compare properties of different sizes on a level playing field. If one property's Rent/Unit is significantly below market comps, there may be room to raise rents. |
| ƒStatus | FORMULA — An automatic health label for each property based on its financial metrics (e.g., cash flow and DSCR). Typical outputs might be 'Strong', 'Watch', or 'Weak'. Use this as a quick visual flag — if a property shows a warning status, dig into its NOI, vacancy, and expenses to diagnose the issue. |
| ƒNOI Rank | FORMULA — Ranks all properties by their NOI from highest to lowest. A rank of 1 means that property produces the most operating income. Use this to quickly identify your top performers and your bottom performers that may need attention or disposal. |
| ƒBreakeven | FORMULA — Breakeven Occupancy = (Expenses + Mortgage) ÷ Gross Rent × 100, expressed as a percentage. It tells you the minimum occupancy rate needed to cover all costs. A breakeven of 75% means you can still cover costs if 25% of units are vacant. Lower is safer. Above 90% means you have very thin margin for vacancy. |
| ƒCF/Unit | FORMULA — Cash Flow per Unit = Cash Flow ÷ Units. Shows how much monthly cash flow each unit generates on average. A widely cited benchmark is $100–$200 per unit per month as a minimum for buy-and-hold investors. Higher is better; negative means you are losing money per door. |
| ƒ$/Unit | FORMULA — Price (or Value) per Unit = Value ÷ Units. This is the cost per door based on current market value. Useful for comparing acquisition efficiency across properties. Lower $/Unit with decent cash flow is generally favorable. |
| ƒNet Yield | FORMULA — Net Yield = (Cash Flow × 12) ÷ Value × 100, expressed as a percentage. Unlike cap rate (which ignores debt), net yield reflects your actual cash return on the property's total value after all expenses and mortgage. A positive net yield above 2–4% is solid for leveraged residential; negative means you are subsidizing the property. |
| ƒ1% Rule | FORMULA — The 1% Rule test checks whether monthly Gross Rent is at least 1% of the property Value. The formula is Gross Rent ÷ Value × 100. A result of 1.0% or higher passes the rule, meaning the property has strong income relative to price. Below 0.7% is considered poor by cash-flow investors. This is a quick screening heuristic, not a definitive measure. |
| ƒCoC Return | FORMULA — Cash-on-Cash Return. CoC = (Cash Flow × 12) ÷ (Value − Loan Balance) × 100, expressed as a percentage. It measures annual cash flow as a return on the equity you actually have invested. An 8–12% CoC is considered good for residential rentals. Below 4% may mean your cash is better deployed elsewhere. Note: this pulls loan balance data from the Debt & Leverage sheet. |
📊 Reading the numbers
• Gross Rent KPI shows your total monthly rental income across all properties before vacancy — compare it month-over-month to track rent growth.
• Portfolio NOI KPI is the sum of all properties' NOI; if it trends down while Gross Rent is flat, your expenses are rising.
• Vacancy Loss KPI shows the dollar amount lost to vacancies; if this exceeds 5–8% of Gross Rent, investigate which properties are dragging.
• Portfolio DSCR KPI is the weighted DSCR across all properties; a value above 1.25 means the portfolio comfortably covers all debt; below 1.0 is a red flag.
• The NOI by Property chart lets you visually spot your top earners and under-performers. The DSCR by Property chart highlights which properties are closest to the danger line of 1.0. The Expenses vs Mortgage chart shows where your money goes — if expenses bars tower over mortgage bars, you may have a cost problem. The Cap Rate by Property chart helps you see which properties offer the best unlevered yield.
⚠️ Avoid these mistakes
• Do not include the mortgage payment inside the Expenses column — it must go in the Mortgage column, or NOI and DSCR will be wrong.
• Make sure each Property name is unique and matches exactly (case and spelling) across all sheets; a mismatch will cause lookup errors.
• Do not type the percent sign in the Vacancy column — just the number (e.g., 5, not 5%). The formulas expect a plain number.
• Do not leave the Value column at 0 or blank — cap rate, GRM, 1% rule, and per-unit value will all error out or show infinity.
💡 Tips• Sort by ƒNOI Rank to quickly focus on your weakest properties and decide whether to improve, refinance, or sell.
• Use the ƒBreakeven column to stress-test: if a property's breakeven is above 85%, even a brief vacancy could push you into negative cash flow.
• Compare ƒ1% Rule results across properties to flag purchases that are priced too high relative to their rent — useful when evaluating a new acquisition.
• If you add a new property, copy an existing formula row and overwrite the inputs to avoid losing formulas.
The Portfolio Dashboard is your at-a-glance command center. It pulls data automatically from the Property Ledger, Lease Tracker, and Debt & Leverage sheets to show portfolio-wide vacancy risk, rent concentration, cash-flow summaries, and cost-recovery analysis. Use it for weekly or monthly portfolio reviews without needing to visit each sheet individually.
✍️ Step by step
11. The Property column auto-lists each property from the 🏠 Property Ledger. Do not type here — it is populated by lookups.
22. Most columns on this sheet are auto-computed (ƒ). The only input column is Vacancy Goal — enter your target vacancy rate as a percentage for each property (e.g., 3 for 3%).
33. Review ƒRisk to see which properties are flagged as high vacancy risk based on their actual vacancy versus the goal.
44. Check ƒRecoverable to understand how much of your vacancy loss could be recovered if you met your Vacancy Goal.
55. Look at ƒRent Share to see each property's contribution to total portfolio rent — high concentration in one property is a risk.
66. ƒLease Left is pulled from the Lease Tracker and shows months remaining on the lease; properties with short lease windows need renewal attention.
77. Use the KPI cards at the top for a quick health check: Net Cash Flow, Weighted Cap Rate, Portfolio CoC, and Rent at Risk.
88. Scroll to the charts to visualize NOI by type, rent share distribution, vacancy cost analysis, and the comparison of losses versus what is recoverable.
📋 Column-by-column
| Property | FORMULA — Auto-populated from the 🏠 Property Ledger. Displays each property name. Do not edit this column. |
| ƒGross Rent | FORMULA — Pulled via lookup from the 🏠 Property Ledger. Shows the total monthly gross rent for each property. See the Ledger's Gross Rent column for details. |
| ƒVacancy % | FORMULA — Pulled from the 🏠 Property Ledger. Displays each property's vacancy rate as a percentage. Compare this to the Vacancy Goal column to assess performance. |
| ƒMo Loss | FORMULA — Monthly Vacancy Loss = Gross Rent × Vacancy % ÷ 100. This is the dollar amount lost each month due to vacancies. A high Mo Loss on a single property may warrant a lease-up strategy or rent adjustment. |
| ƒYr Loss | FORMULA — Annual Vacancy Loss = Mo Loss × 12. Shows the yearly cost of vacancy for each property. Seeing the annualized number makes the impact of even small vacancy rates visceral — e.g., 5% vacancy on $3,000/month rent is $1,800/year. |
| ƒRisk | FORMULA — A risk flag computed by comparing the actual Vacancy % to your Vacancy Goal. If vacancy exceeds the goal, the property is flagged as higher risk (e.g., 'High' or 'At Risk'). If vacancy is at or below goal, it shows a healthier status. Use this to triage which properties need immediate leasing attention. |
| Vacancy Goal | INPUT — Enter your target vacancy rate as a percentage number (e.g., 3 for 3%). This is the vacancy rate you aim to achieve or stay below. The sheet uses it to calculate recoverable income and risk flags. A typical goal for residential is 3–5%. |
| ƒRecoverable | FORMULA — Recoverable Income = the dollar amount you could recover monthly if you reduced vacancy from the current rate down to your Vacancy Goal. Calculated as Gross Rent × (Vacancy % − Vacancy Goal) ÷ 100. If vacancy is already at or below goal, this shows $0. Use this to prioritize which properties offer the biggest financial upside from improved occupancy. |
| ƒRent Share | FORMULA — Rent Share = this property's Gross Rent ÷ Total Portfolio Gross Rent × 100, expressed as a percentage. It reveals concentration risk: if one property accounts for over 30–40% of total rent, losing that tenant could destabilize the portfolio. Diversification is healthier. |
| ƒLease Left | FORMULA — Pulled from the 📅 Lease Tracker via lookup. Shows the number of months remaining on the lease for each property. A low number (under 3 months) means renewal negotiations should begin immediately. If a property has multiple tenants, this may reflect the soonest-expiring lease. |
📊 Reading the numbers
• Net Cash Flow KPI is the sum of all Cash Flow across the portfolio — this is your total monthly take-home after all expenses and mortgages. If negative, you are subsidizing the portfolio.
• Wtd Cap Rate KPI is the portfolio's weighted-average cap rate, giving a single yield number for all properties combined. Compare to market rates — if your portfolio cap rate is below the local market average, you may be overpaying.
• Portfolio CoC (Cash-on-Cash) KPI shows your annual cash return on total equity invested. Aim for 6–12%; below 4% may signal poor deployment of capital.
• Rent at Risk KPI shows the total dollar amount of rent tied to leases expiring soon or properties flagged for high vacancy. A high Rent at Risk relative to Total Rent demands action.
• In the charts, 'Total NOI by Type' reveals which property categories drive your income. 'Vacancy % by Property' spotlights problem assets. 'Loss vs Recoverable' shows how much upside exists if you close the gap between actual vacancy and your goals.
⚠️ Avoid these mistakes
• Do not manually type property names in the Property column — they are pulled automatically. A manual entry could break lookups.
• If you skip the Vacancy Goal column, the ƒRisk and ƒRecoverable columns cannot function properly — always enter a goal for each property.
• Do not confuse Mo Loss (monthly) with Yr Loss (annual) when making decisions; annualized numbers are more appropriate for budgeting.
💡 Tips• Set aggressive but realistic Vacancy Goals (e.g., 3%) and revisit them quarterly; the Recoverable column instantly quantifies the financial reward of better leasing.
• Sort by ƒRent Share descending to identify concentration risk — consider diversifying if one property dominates.
• Use the Annual Vacancy Cost chart in presentations to stakeholders or partners to make the case for investing in tenant retention.
• Cross-reference ƒLease Left with the 📅 Lease Tracker to start renewal conversations at least 90 days before expiration.
The Lease Tracker keeps every lease expiration, renewal, and rent escalation on your radar. Enter one row per tenant, and the sheet auto-calculates months remaining, projected renewed rent, annual uplift, and vacancy exposure. Use it to plan renewals proactively and avoid costly gaps between tenants.
✍️ Step by step
11. In the Property column, type the property name exactly as it appears in the 🏠 Property Ledger. The sheet uses this to look up the current rent for that property.
22. In Tenant, enter the tenant's name (e.g., 'Jane Smith' or 'Unit 2 – Martinez'). This is for your reference only and is not used in formulas.
33. In Start, enter the lease start date in your locale's date format (e.g., 1/15/2024 or 2024-01-15).
44. In End, enter the lease end date in the same format. The sheet calculates all time-based metrics from these two dates.
55. In Escalation, enter the annual rent escalation percentage you plan to apply at renewal (e.g., 3 for a 3% increase). This drives the ƒNew Rent and ƒYr Uplift projections.
66. Review ƒMo Left — if it is under 3 months, begin renewal outreach immediately.
77. Check ƒStatus for a plain-language flag (e.g., 'Active', 'Expiring Soon', 'Expired') to quickly scan which leases need attention.
88. Look at the KPI cards, especially 'Due 90 Days', to see how many leases are expiring within the next quarter.
99. Use the charts to visualize lease timelines, compare current versus renewed rents, and see how rent is distributed across properties.
📋 Column-by-column
| Property | INPUT — Type the property name exactly as it appears in the 🏠 Property Ledger (e.g., '123 Oak St'). Spelling must match perfectly for the rent lookup to work. If a property has multiple tenants, enter one row per tenant with the same property name. |
| Tenant | INPUT — Enter the tenant's name or a unit identifier (e.g., 'John Doe' or 'Unit 3B – Kim'). This is a label for your reference and does not affect any calculations. |
| Start | INPUT — Enter the lease start date (e.g., 01/15/2024). Use a consistent date format. This is used along with End to calculate term length and months remaining. |
| End | INPUT — Enter the lease end date (e.g., 01/14/2025). The difference between Start and End drives ƒTerm Mo and ƒMo Left. Make sure this reflects the actual lease expiration, not a renewal option date. |
| ƒRent | FORMULA — Monthly rent pulled from the 🏠 Property Ledger via VLOOKUP on the Property name. If the property has multiple units and you entered total Gross Rent on the Ledger, this will show the total. You may need to manually adjust per-tenant rent if you track individually. |
| ƒTerm Mo | FORMULA — Lease Term in Months = the total number of months between the Start and End dates. A standard residential lease is 12 months. Short-term or month-to-month leases will show smaller numbers. Use this to see your mix of short vs. long-term commitments. |
| ƒMo Left | FORMULA — Months Left = the number of months from today's date to the End date. If the lease has already expired, this will show 0 or a negative number. Properties with fewer than 3 months left should trigger renewal conversations. This is one of the most actionable columns on the sheet. |
| ƒStatus | FORMULA — A text label indicating the lease's current state, typically 'Active', 'Expiring Soon' (e.g., within 90 days), or 'Expired'. Use this for quick visual scanning — filter or sort by Status to see all at-risk leases grouped together. |
| Escalation | INPUT — Enter the annual rent escalation rate as a percentage number (e.g., 3 for 3%). This is the rent increase you plan to apply when the lease renews. Typical residential escalations are 2–5%. If you plan no increase, enter 0. |
| ƒNew Rent | FORMULA — Projected Renewed Rent = ƒRent × (1 + Escalation ÷ 100). This shows what the monthly rent will be after applying your planned escalation. Use it to forecast future income and compare to market rents to ensure you are not under- or over-escalating. |
| ƒYr Uplift | FORMULA — Annual Uplift = (ƒNew Rent − ƒRent) × 12. This is the additional annual income gained by renewing at the escalated rate versus the current rent. It quantifies the value of each renewal and helps you prioritize which renewals to push hardest on. |
| ƒVacancy % | FORMULA — Pulled from the 🏠 Property Ledger via VLOOKUP. Shows the vacancy rate for the property associated with this lease. A high vacancy rate alongside an expiring lease is a double risk flag — you may lose this tenant into an already under-occupied property. |
📊 Reading the numbers
• Avg Lease Left KPI shows the average months remaining across all leases. If this drops below 6 months, your portfolio has significant near-term renewal exposure.
• Due 90 Days KPI counts how many leases expire within the next 90 days — treat this as your immediate action list.
• Avg Escalation KPI shows the average planned rent increase; compare it to local market rent growth to ensure you are keeping pace.
• Renewal Uplift KPI totals the annual income gain from all planned escalations — this is money you leave on the table if you do not renew.
• The Months Remaining chart gives a visual timeline of all leases; clusters of short bars mean a wave of renewals is coming. The Current vs Renewed Rent chart shows the income gain from each escalation side by side.
⚠️ Avoid these mistakes
• The most critical mistake is misspelling the Property name — even a trailing space will break the VLOOKUP and show an error or zero rent.
• Do not enter dates as plain text (e.g., 'January 15 2024'); use a proper date format (01/15/2024) so the month calculations work correctly.
• Do not forget to update the End date when a lease is actually renewed — stale end dates will cause false 'Expired' statuses and incorrect Mo Left values.
• Do not enter the Escalation as a decimal (e.g., 0.03); enter it as a whole-number percentage (3).
💡 Tips• Sort by ƒMo Left ascending to see your most urgent renewals at the top — make this your weekly check-in view.
• Use Google Sheets' built-in filter to show only rows where ƒStatus = 'Expiring Soon' for a focused renewal task list.
• Compare ƒNew Rent to local market comps before sending a renewal offer — if your escalation is below market, you may be leaving money on the table.
• If you have month-to-month tenants, enter Start and End as the same month and set Term to 1 — the Mo Left of 0 will keep them visible as ongoing risks.
The Debt & Leverage sheet gives you a detailed view of every loan in your portfolio — payment breakdowns, loan-to-value ratios, equity positions, and debt service coverage. Use it to evaluate refinance candidates, understand your leverage exposure, and see how much equity you have built across the portfolio.
✍️ Step by step
11. The Property column and ƒMkt Value are auto-populated from the 🏠 Property Ledger. Do not type in these columns.
22. In Loan Bal, enter the current outstanding loan balance for each property (e.g., 310000 for $310,000). Check your lender's latest statement for accuracy.
33. In Rate, enter the annual interest rate as a percentage (e.g., 6.5 for 6.5%). This is used to compute the monthly interest portion of your payment.
44. In Term Yrs, enter the remaining loan term in years (e.g., 28 for 28 years remaining). This drives the amortization payment calculation.
55. All ƒ columns will auto-calculate: monthly payment, interest/principal split, LTV, equity, status, and DSCR.
66. Review the KPI cards — Wtd Rate shows your blended borrowing cost, and Avg LTV reveals your overall leverage level.
77. Use the LTV by Property chart to spot over-leveraged properties (LTV above 80%) and the Equity by Property chart to see where your wealth is concentrated.
88. The Interest vs Principal chart reveals how much of each payment goes to building equity versus servicing debt — early in a loan term, interest dominates.
📋 Column-by-column
| Property | FORMULA — Auto-populated from the 🏠 Property Ledger via lookup. Displays the property name. Do not edit this column. |
| ƒMkt Value | FORMULA — Market Value pulled from the 🏠 Property Ledger's Value column via VLOOKUP. This is used to compute LTV and equity. If you update the Value on the Ledger, it flows through here automatically. |
| Loan Bal | INPUT — Enter the current outstanding principal balance on the loan, in dollars (e.g., 310000). Update this periodically (quarterly or annually) as you pay down the loan. If the property has no loan, enter 0. |
| Rate | INPUT — Enter the annual interest rate on the loan as a percentage (e.g., 6.5 for 6.5%). For adjustable-rate mortgages, update this when the rate changes. This is used to calculate ƒInt/Mo and ƒPayment. |
| Term Yrs | INPUT — Enter the remaining term of the loan in years (e.g., 28). This is used alongside Rate and Loan Bal to compute the monthly payment via a standard amortization formula. If you refinanced, enter the new remaining term. |
| ƒPayment | FORMULA — Monthly Mortgage Payment calculated using standard amortization: a function of Loan Bal, Rate, and Term Yrs. This should closely match your actual mortgage statement. If it diverges significantly, double-check your Rate and Term Yrs inputs. |
| ƒInt/Mo | FORMULA — Monthly Interest = Loan Bal × (Rate ÷ 100 ÷ 12). This is the portion of your monthly payment that goes to the lender as interest and does NOT build equity. Early in a loan, this is the majority of the payment. |
| ƒPrinc/Mo | FORMULA — Monthly Principal = ƒPayment − ƒInt/Mo. This is the portion of your payment that reduces the loan balance and builds equity. Over time, as the balance decreases, this share grows (amortization). This value also feeds into the 📈 Equity Growth sheet. |
| ƒLTV | FORMULA — Loan-to-Value Ratio = Loan Bal ÷ ƒMkt Value × 100, expressed as a percentage. It measures your leverage. Below 80% is generally comfortable; 80–90% is moderate leverage; above 90% means very high leverage with little equity cushion. Many lenders require below 80% LTV to avoid private mortgage insurance. |
| ƒEquity | FORMULA — Equity = ƒMkt Value − Loan Bal. This is the dollar amount of the property you actually own. Positive equity means the property is worth more than you owe. Negative equity (underwater) means you owe more than the property is worth — a serious situation. |
| ƒStatus | FORMULA — A flag based on leverage and debt metrics (e.g., 'Healthy', 'Caution', 'Over-Leveraged'). Properties flagged as risky may be candidates for extra principal payments or refinancing to improve terms. |
| ƒDSCR | FORMULA — Debt Service Coverage Ratio = NOI (from the 🏠 Property Ledger) ÷ ƒPayment. Same concept as on the Ledger but calculated using this sheet's computed payment. A DSCR above 1.25 is healthy; below 1.0 means the property cannot cover its debt from rental income. |
📊 Reading the numbers
• Wtd Rate KPI is the portfolio's weighted-average interest rate across all loans. If it is significantly above current market rates, refinancing may be worthwhile.
• Total Equity KPI sums equity across all properties — this is your net real-estate wealth. Track it over time.
• Avg LTV KPI shows average leverage; below 70% is conservative, above 80% is aggressive.
• Avg DSCR KPI is the average debt-service coverage; ensure it stays above 1.2 for portfolio stability.
• The LTV by Property chart quickly highlights which properties are most leveraged. The Interest vs Principal chart shows where your money is going — properties where interest far exceeds principal are in the early amortization phase or have high rates. The Equity by Property bar chart shows your wealth distribution.
⚠️ Avoid these mistakes
• Do not enter the interest rate as a decimal (e.g., 0.065); enter it as a percentage number (6.5).
• Do not forget to update Loan Bal periodically — stale balances will overstate LTV and understate equity.
• If you refinanced a property, update Rate, Term Yrs, and Loan Bal simultaneously to keep the payment calculation accurate.
💡 Tips• Sort by ƒLTV descending to find your most leveraged properties — these are your highest-risk positions if values drop.
• Compare ƒPayment on this sheet to the Mortgage column on the 🏠 Property Ledger; they should be close. If they differ, one set of inputs may be stale.
• Use the ƒEquity column to identify HELOC or cash-out refinance candidates — properties with high equity and low rates are prime targets.
• Review ƒInt/Mo vs ƒPrinc/Mo to see which loans are still heavily interest-weighted and might benefit from extra principal payments.
The Equity Growth sheet projects your portfolio's equity trajectory over the next five years, combining property appreciation with monthly principal paydown. Use it to set long-term expectations, compare ROE across properties, and make hold-vs-sell decisions based on projected wealth building.
✍️ Step by step
11. The Property, ƒValue Now, ƒEquity Now, and ƒPrinc/Mo columns are auto-pulled from the 🏠 Property Ledger and 💵 Debt & Leverage sheets. Do not type in these columns.
22. In Apprec %, enter the expected annual appreciation rate for each property (e.g., 3 for 3% per year). Use local market data or conservative national averages (2–4% historically).
33. Once Apprec % is entered, the five-year projection columns (ƒYr 1 through ƒYr 5) populate automatically, showing projected total equity at the end of each year.
44. ƒROE (Return on Equity) calculates your annual equity return, helping you decide if the equity is working hard enough.
55. Review the KPI cards: Equity Now vs Yr 5 Equity shows your projected wealth growth, and Total Growth shows the dollar increase.
66. Use the 'Current vs Yr 5 Equity' chart to visualize each property's equity journey.
77. The 'Equity Growth Path' chart plots all five years so you can see the compounding trajectory.
88. Compare ƒROE across properties — a low ROE on a high-equity property may mean that equity could be redeployed more productively via a 1031 exchange or cash-out refinance.
📋 Column-by-column
| Property | FORMULA — Auto-populated from the 🏠 Property Ledger. Do not edit. |
| ƒValue Now | FORMULA — Current market value pulled from the 🏠 Property Ledger. This is the starting point for the appreciation projection. |
| ƒEquity Now | FORMULA — Current equity pulled from the 💵 Debt & Leverage sheet (Market Value − Loan Balance). This is the baseline for projecting equity growth. |
| Apprec % | INPUT — Enter the expected annual property appreciation rate as a percentage (e.g., 3 for 3%). Conservative estimates are 2–3% for stable markets, 4–6% for high-growth areas. This drives the year-over-year value increase in the projection columns. Be realistic — overestimating appreciation leads to overly rosy projections. |
| ƒPrinc/Mo | FORMULA — Monthly principal payment pulled from the 💵 Debt & Leverage sheet. This is added to equity each month as you pay down the loan. Combined with appreciation, it forms the two engines of equity growth. |
| ƒYr 1 | FORMULA — Projected total equity at the end of Year 1. Calculated by appreciating the current value by the Apprec %, subtracting the loan balance (reduced by 12 months of principal payments), and deriving the new equity. This gives you a one-year-out equity forecast. |
| ƒYr 2 | FORMULA — Projected total equity at the end of Year 2, building on the Year 1 projection with another year of appreciation and principal paydown. The compounding effect of appreciation becomes visible here. |
| ƒYr 3 | FORMULA — Projected total equity at the end of Year 3. By this point, the gap between current equity and projected equity typically becomes meaningful, especially at higher appreciation rates. |
| ƒYr 4 | FORMULA — Projected total equity at the end of Year 4. Properties with both strong appreciation and significant principal paydown will show accelerating equity growth. |
| ƒYr 5 | FORMULA — Projected total equity at the end of Year 5. This is the key long-term planning number — use it for retirement projections, refi planning, or deciding whether to hold or sell. Compare across properties to see where your wealth is heading. |
| ƒROE | FORMULA — Return on Equity = (Annual Cash Flow + Annual Equity Gain) ÷ Current Equity × 100, expressed as a percentage. ROE measures how productively your trapped equity is working. A high ROE (above 15%) means the property is generating strong returns relative to equity. A low ROE (below 5%) on a property with large equity might signal it is time to redeploy that capital. Compare ROE across properties to find lazy equity. |
📊 Reading the numbers
• Equity Now KPI shows your total current equity across all properties — this is your real-estate net worth.
• Yr 5 Equity KPI shows the projected total equity in five years — the gap between this and Equity Now is your expected wealth creation.
• Total Growth KPI is the absolute dollar increase from now to Year 5. Use it for goal-setting.
• Avg ROE KPI averages the return on equity across properties; compare to stock market returns (historically 7–10%) to validate that your real estate is competitive.
• The Current vs Yr 5 Equity chart makes it easy to see which properties contribute most to future growth. The Equity Growth Path chart shows the compounding curve — steeper is better. The 5-Year ROE chart ranks properties by return on equity so you can identify where capital is underperforming.
⚠️ Avoid these mistakes
• Do not enter unrealistically high appreciation rates (e.g., 10–15%) unless you have strong local data; this will create misleadingly optimistic projections.
• Remember that Apprec % is annual, not total over five years — entering 15 means 15% per year, not 15% total.
• If Loan Bal on the Debt sheet is outdated, the equity projections will be off because ƒEquity Now and ƒPrinc/Mo will be inaccurate. Keep the Debt sheet current.
💡 Tips• Run multiple scenarios by duplicating the sheet and using different Apprec % values (e.g., 2% conservative, 4% moderate, 6% optimistic) to stress-test your projections.
• Properties with high ƒROE and low ƒEquity Now are your most efficient wealth-builders — consider focusing reinvestment there.
• If a property shows strong Yr 5 equity but poor annual cash flow, it may be a pure appreciation play — decide if that aligns with your investment strategy.
• Use the Yr 5 projections to plan refinances: a property reaching 50% LTV in 3 years could be a candidate for a cash-out refi to fund the next acquisition.
📖Glossary — what every value means
| NOI (Net Operating Income) | Net Operating Income is rental income minus operating expenses, before mortgage payments. Calculated here as Net Rent − Expenses (monthly). It measures a property's operational profitability independent of how it is financed. A positive and growing NOI is the foundation of a healthy rental investment. |
| Cap Rate (Capitalization Rate) | Cap Rate is the annualized NOI divided by the property's market value, expressed as a percentage: (NOI × 12) ÷ Value × 100. It represents the return you would earn if you bought the property with all cash. Typical residential cap rates range from 4–10%; higher means more income per dollar of value, but may also indicate higher risk. |
| DSCR (Debt Service Coverage Ratio) | DSCR is NOI divided by the mortgage payment: NOI ÷ Mortgage. It measures how comfortably rental income covers debt obligations. A DSCR of 1.0 means you exactly break even; lenders typically require 1.2–1.25 as a minimum. Below 1.0 means the property does not cover its own mortgage from rent. |
| GRM (Gross Rent Multiplier) | GRM is the property value divided by annual gross rent: Value ÷ (Gross Rent × 12). It indicates how many years of gross rent equal the property price. Lower GRM means the property is cheaper relative to its income. Typical residential GRM is 8–15; above 15 may signal an overpriced property. |
| OpEx % (Operating Expense Ratio) | OpEx % is operating expenses as a share of gross rent: Expenses ÷ Gross Rent × 100. It shows what portion of each rent dollar is consumed by costs. A healthy residential OpEx % is 35–50%; above 50% suggests costs are too high or rents are too low. |
| Cash-on-Cash Return (CoC) | Cash-on-Cash Return is annual cash flow divided by total equity invested: (Cash Flow × 12) ÷ Equity × 100. Unlike cap rate, it accounts for leverage (mortgage). A CoC of 8–12% is generally considered good for residential rentals; below 4% may indicate capital is deployed inefficiently. |
| Breakeven Occupancy | Breakeven Occupancy is the minimum occupancy rate needed to cover all costs: (Expenses + Mortgage) ÷ Gross Rent × 100. A breakeven of 75% means you can withstand 25% vacancy before losing money. Lower breakeven means more safety margin; above 90% is risky. |
| Vacancy % | Vacancy percentage represents the portion of potential rental income lost to unoccupied units. Entered as a number (e.g., 5 for 5%). Typical residential vacancy is 3–8%. It is used to compute Net Rent (Gross Rent reduced by vacancy) and feeds into vacancy loss and risk calculations across the workbook. |
| Net Rent | Net Rent is Gross Rent adjusted for vacancy: Gross Rent × (1 − Vacancy % ÷ 100). It represents the realistic monthly income you can expect to collect. The closer Net Rent is to Gross Rent, the better your occupancy. |
| LTV (Loan-to-Value Ratio) | LTV is the loan balance divided by the property's market value: Loan Bal ÷ Market Value × 100. It measures leverage. Below 80% is standard; above 80% typically triggers PMI requirements; above 100% means the property is underwater (you owe more than it is worth). |
| Net Yield | Net Yield is annual cash flow (after all expenses and mortgage) divided by market value: (Cash Flow × 12) ÷ Value × 100. Unlike cap rate, it includes debt service. A positive net yield above 2–4% is solid for leveraged properties; negative means the property costs you money on a cash basis. |
| 1% Rule | A quick screening rule: monthly Gross Rent should be at least 1% of the property's value. Calculated as Gross Rent ÷ Value × 100. Passing (≥1.0%) suggests strong cash flow potential; below 0.7% is generally considered poor for a cash-flow investor. It is a rough heuristic, not a definitive test. |
| ROE (Return on Equity) | Return on Equity measures how productively your equity is working: (Annual Cash Flow + Annual Equity Gain) ÷ Current Equity × 100. A high ROE (above 15%) means the property delivers strong returns on the capital trapped in it; a low ROE (below 5%) on a high-equity property may mean that equity could earn more elsewhere. |
| Escalation | The annual percentage by which rent increases upon lease renewal. Entered as a number (e.g., 3 for 3%). Typical residential escalations are 2–5%. Higher escalation rates accelerate income growth but may increase tenant turnover if above market rates. |
| Wtd Rate (Weighted Average Interest Rate) | The portfolio's average interest rate, weighted by each loan's balance. It gives a single number representing your blended cost of debt. If it is significantly above current market rates, a refinance of one or more loans may reduce costs. |
| CF/Unit (Cash Flow per Unit) | Monthly Cash Flow divided by the number of units: Cash Flow ÷ Units. A widely cited benchmark is $100–$200 per unit per month as a minimum for buy-and-hold investors. Negative CF/Unit means each door is costing you money. |
| $/Unit (Value per Unit) | Property market value divided by the number of units: Value ÷ Units. Useful for comparing acquisition cost efficiency across properties of different sizes. Lower $/Unit with decent cash flow is favorable. |
| Equity | The portion of a property's value that you own outright: Market Value − Loan Balance. Positive equity represents wealth; it can be accessed via sale, refinance, or HELOC. Equity grows through loan paydown and property appreciation. |
| Principal (Princ/Mo) | The portion of each monthly mortgage payment that reduces the loan balance and builds equity. Calculated as Payment − Interest. Early in a loan, principal is a small share of the payment; it grows over time as the balance decreases (amortization). |
| Rent Share | A property's Gross Rent as a percentage of total portfolio Gross Rent. High concentration (above 30–40% in one property) represents risk — if that property vacates, a large portion of your income disappears. |
Every template ships with an AI side-panel. Type in plain language — it fills rows, explains any cell, and analyses your data for you.
How to use it
1To open the AI assistant, click the ✨ sparkle icon in the bottom-right corner of your Google Sheets screen. This opens a side panel where you can type questions or commands in plain English to interact with your workbook data.
2You can ask the assistant to explain any cell — for example, type 'explain B7' or 'what does the Cap Rate formula do?' and it will break down the formula, its inputs, and what the result means in the context of your portfolio. This is especially helpful for understanding the auto-computed (ƒ) columns without needing to read formula syntax.
3The assistant can execute commands that read and modify your sheet — for example, 'sum column C', 'fill the next row with a new property', 'color row 1 gold', or 'sort by NOI descending'. It performs these actions without breaking any existing formulas, so your auto-computed columns remain intact.
4Use the one-click presets tailored to this template for common portfolio analysis tasks — just click a preset button and the assistant runs a pre-built analysis specific to rental portfolio management. You can also scan the entire workbook to get a health-check overview, or attach a screenshot of a rent roll or bank statement and the assistant will read and interpret it.
5In the Tools tab, use 'Analyze All My Data' to generate a comprehensive written report exported to a new sheet — great for quarterly reviews or sharing with partners. Auto-Fit adjusts column widths for clean printing. You can also translate every label into another language if you manage properties internationally, build a visual infographic of your portfolio, and adjust the assistant's tone (Friendly, Professional, or Concise) or apply Smart Styling to polish the workbook's appearance.
6The assistant starts with free trial AI requests so you can explore its capabilities right away. After the trial, a subscription unlocks a larger monthly allowance of AI requests plus Pro features including native chart generation, financial forecasts, and a full multi-page portfolio report. Pro features are marked in the Tools tab.