---
id: imobiliario/carteira-de-locacao
industry: Real estate
usage: specific
effort: medium
grain: Logical contract (all versions and amendments together); contract x month for the series; bill for delinquency
---

# Rental portfolio

How many leases were active at the end of each month, how many came in and went out, churn on the right base and how much the portfolio generates in management fees, without counting renewals as cancellations.

## Executive summary

**For:** Brokerages that manage third-party rentals and charge a management fee on the portfolio.

**What it solves**

- Rental management knows how many leases were active at the end of each month, even though the system only shows today's snapshot.
- Renewals and amendments stop showing up as one cancelled lease and one new lease.
- Churn is calculated on the portfolio at the start of the month and matches what management counts.

**Why it matters:** The rental portfolio is recurring revenue: each active lease pays a management fee every month. Knowing how much the portfolio grew, how much left and why is what guides the acquisition of properties to rent and owner retention. Without a historical series, the brokerage cannot tell whether it lost leases or just renewed them.

**Business questions it answers**

- How many leases did we have active at the end of each month last year?
- How many leases came in and how many went out this month, and why?
- What is the portfolio's monthly churn and how does it compare with last year?
- How much does the portfolio generate in management fees per month?
- Which leases are under move-out notice or expire in the next 90 days?
- Which tenants have overdue bills?

**What you need**

- The rental system connected to Nekt (or the monthly snapshot exported from it)
- The rule for active, notice and ended leases, agreed with rental management
- The lease's property code linked to the property CRM

**What you get**

- A table per lease with status, dates, parties, rent and fee
- A monthly series with active, new, ended, churn and management fee
- Portfolio dashboard with a 12-month trend
- An AI agent that answers about the portfolio with the right rule

## Detailed

### The problem

The rental system shows leases as they are today and creates a new version on every renewal or amendment. Counting leases by current status gives no history, and counting versions shows a cancellation and a new lease on every renewal. Dates arrive as dd/mm/yyyy text, values with a decimal comma and lease parties in lists, and churn comes out different in every spreadsheet.

### Business rules

1. **The logical contract joins the versions.** Renewals and amendments create a new version of the contract, with the internal code plus a suffix (for example, 1234-1). The logical contract is the code before the hyphen. It is active if any version is active and not deleted; the start is the earliest contract date across versions; the effective end is the latest deletion date when no version is active. Attributes (rent, parties, fee) come from the current version: the active one and, among active ones, the most recent. (Avoid: Counting each version as a contract.)
2. **Lease status in three groups.** Group the system status into active, under move-out notice and deactivated. Under notice still counts as active, with its own flag, until the move-out. The exit reason comes from the status itself or the deletion reason field (termination, property sold, change of manager). (Avoid: Removing the lease from the portfolio on the day of the move-out notice.)
3. **The series comes from the dates, not today's status.** Active on a day = start on or before the day and effective end null or after the day. Generate a series of days or months (for example, the last 5 years) and count active leases on the last day of each month. New in the month by start date, ended by effective end date. When the system has no reliable dates, save a monthly portfolio snapshot starting now. (Avoid: Filtering status active today and calling it a past month's portfolio.)
4. **Churn on the portfolio at the start of the month.** Monthly churn = leases ended in the month / leases active at the end of the previous month. New leases in the month stay out of the denominator. Monthly growth = (new minus ended) / active at start. Compare with the same month last year to remove year-end and back-to-school seasonality. (Avoid: Ended / active at the end of the month.)
5. **A rent receipt confirms the active lease.** To check the portfolio, use the receipts: a lease active in the month has a rent receipt due in the month with a value above zero (in Msys, the rent event in receivables). Keep the last receipt per lease. An active lease without a receipt, or a receipt on an ended lease, goes to review. (Avoid: Trusting only the lease status, which goes stale.)
6. **The management fee comes from the contract.** Monthly management fee = rent x the contract's management percentage. The contract fee (brokerage at signing) is one-off revenue and stays separate. Rent under management (VGL) = sum of rent of leases active at month end. Average rent = VGL / active leases. (Avoid: Adding management fee and contract fee together as recurring revenue.)
7. **Lease parties in a list, with a principal.** A lease can have several landlords, tenants and guarantors, each with an order. The principal is the one with order 1; keep all of them in an array along with the count. When the lease comes without parties at the root, take the first tenant and first owner from the lease's lists. Link the people to the single customer base by document. (Avoid: Joining the lease with landlords and duplicating rent per landlord.)
8. **Delinquency by bill.** Overdue = unpaid bill with a due date before today. For the cash forecast, use a window of 30 days back and 90 forward: total, paid, open and overdue amount per lease. A delinquent tenant is one with at least one overdue bill. (Avoid: Considering every open bill delinquent, including those due tomorrow.)
9. **Text becomes date and number on the way in.** Dates arrive as dd/mm/yyyy, sometimes empty, '-' or 'null'; values with a decimal comma. Convert in the first layer: text shorter than 8 characters becomes null, comma becomes dot, and the result is typed. Never compare dates as text. (Avoid: Sorting and filtering dd/mm/yyyy dates as text.)
10. **A house rule is declared as a house rule.** Exit penalty grace period, index and date of the next rent adjustment and type of rental guarantee come from the contract. Internal rules, such as waiving the penalty after 12 months, live in a named constant with a comment saying they are the brokerage's policy, not law. (Avoid: Hiding an internal policy inside a calculation.)

### How each lease is read

By logical contract (all versions), on the last day of the month.

- **Some version active and not deleted** → Active
- **Status with move-out notice** → Active, under notice
- **Earliest contract date within the month** → New in the month
- **New version or amendment of the same base code** → Renewal, not churn
- **No active version, latest deletion in the month** → Ended in the month (`fim efetivo`)
- **Unpaid bill, due before today** → Delinquent

### Definitions

| Metric | Definition |
|---|---|
| Active leases at month end | logical contracts with start on or before the last day of the month and effective end null or after it |
| New in the month | logical contracts whose earliest contract date falls in the month |
| Ended in the month | logical contracts whose effective end falls in the month |
| Monthly churn | ended in the month / active at the end of the previous month; null if the base is zero |
| VGL (rent under management) | sum of monthly rent of leases active at month end |
| Average rent | VGL / leases active at month end |
| Management fee for the month | sum of rent x management percentage of leases active in the month |
| Overdue amount | sum of unpaid bills with a due date before today, per lease |

### Pitfalls

- **Renewal turned into churn.** Each amendment created a new version, and the portfolio showed a cancellation and a new lease on every renewal. How to avoid: Group versions by base code before counting.
- **A series that starts today.** The rental system only delivered the current snapshot, and the historical series did not exist for previous months. How to avoid: Rebuild from lease validity and save a monthly snapshot starting now.
- **Lease with no tenant or owner.** The lease table did not carry tenant and owner, and it was impossible to know who rented and who owned. How to avoid: Read the first tenant and first owner from the lease's lists.
- **Rent multiplied.** The join with landlords and guarantors repeated the lease once per person and the VGL doubled. How to avoid: Aggregate the parties into arrays before joining to the lease.
- **Dates as text.** dd/mm/yyyy dates coming from a spreadsheet were filtered as text and entire months disappeared from the range. How to avoid: Convert to date on the way in.
- **Active by a hard-coded month.** The active leases query had the month written into the filter and stayed stuck on that month while the dashboard moved on. How to avoid: Use the month series, never a fixed month in the code.

### Acceptance test

- [ ] Active leases at the end of the last closed month match the rental system's active leases report (difference below 1%).
- [ ] For every month, active at the end of the previous month + new - ended = active at month end.
- [ ] No base code appears more than once in the lease table.
- [ ] The month's VGL equals the sum of rent of active leases, with no duplication by landlord or guarantor.
- [ ] For three chosen renewed leases, no end is recorded on the renewal date.

### Context document

```text
Rental portfolio
- Lease = logical contract: all versions and amendments of the same base code (before the hyphen). Never count versions.
- Active: some version active and not deleted. Under move-out notice counts as active, with its own flag.
- Active in the month: counted on the last day of the month, by validity (start on or before the day and effective end null or after).
- New by start date; ended by effective end date. A renewal is not an end.
- Monthly churn = ended in the month / active at the end of the previous month. New leases stay out of the denominator.
- VGL = sum of rent of active leases at month end. Average rent = VGL / active.
- Management fee = rent x the contract percentage; contract fee (brokerage) is one-off and stays separate.
- Delinquent: unpaid bill with a due date before today.
- Principal landlord, tenant and guarantor are the ones with order 1; the others are in the arrays.
- Rates with a zero denominator return null.
Client exceptions:
- <client>: exit penalty grace period of <months> months, an internal brokerage rule.
- <client>: the portfolio comes from the monthly snapshot <source> before <date> and from lease validity after.
```

### Variations

- **Lease cohorts:** Leases grouped by start month, tracking how many remain active after 6, 12 and 24 months.
- **Vacation rentals and payouts:** For vacation properties, the portfolio is active properties and the core indicator is the payout to the owner: on time, late or missing, from the gap between accrual and cash.
- **Former tenant reactivation:** Ended leases feed the former tenant segment for first-home and new rental campaigns.