Calculates estimated ROI of a real estate investment company.

real-estate-company-financial-estimation.php

<?php

$investors = [];

for ($i = 1; $i < 4; $i++) {
    $starting_capital = (int) ($_POST["investor_{$i}_starting_capital"] ?? 0);
    $investors[] = [
        'name' => "Inv. $i",
        'starting_capital' => $starting_capital,
        'contributions' => (int) ($_POST["investor_{$i}_contributions"] ?? 0),
        'invested' => $starting_capital,
        'earned' => 0,
    ];
}

// The starting capital that is deposited when the company is founded.
$starting_capital = array_reduce($investors, function (int $carry, array $investor) {
    return $carry + $investor['starting_capital'];
}, 0);

// The number of properties to acquire in the acquisition period.
$portfolio_size = (int) ($_POST['portfolio_size'] ?? 1);

// The cash to keep aside per property for repairs and renovations.
$cash_buffer = ((float) ($_POST['cash_buffer'] ?? 2)) / 100;

// How many months we will manage a property before selling it.
$property_management_duration = (int) ($_POST['property_management_duration'] ?? 10) * 12;

// The profit allocation during both phases, to pay out as dividends and to pay down loans.
$phases = [
    ['dividend' => 0, 'paydown' => 0],
    ['dividend' => 1, 'paydown' => 0],
];
for ($i = 0; $i < 2; $i++) {
    foreach (['dividend', 'paydown'] as $target) {
        $key = "{$target}_phase_" . ($i + 1);
        if (!empty($_POST[$key])) {
            $phases[$i][$target] = (int) $_POST[$key] / 100;
        }
    }
}

// Average price of buying a property.
$acquisition_price = (int) ($_POST['acquisition_price'] ?? 100000);

// Monthly rental price.
$rental_price = (int) ($_POST['rental_price'] ?? 450);

// The minimum down payment as a percentage of the buy price.
$down_payment_rate = ((float) ($_POST['down_payment_rate'] ?? 25)) / 100;

// How much a property is expected to appreciate on a yearly basis.
$yearly_appreciation = ((float) ($_POST['yearly_appreciation'] ?? 1)) / 100;

// The costs associated with buying a property, as a percentage of the buy price.
$acquisition_cost = ((float) ($_POST['acquisition_cost'] ?? 6.5)) / 100;

// Costs associated with selling a property.
$sale_cost = ((float) ($_POST['sale_cost'] ?? 2)) / 100;

// Occupancy rate. E.g. 0.917 is 11 out of 12 months.
$occupancy = ((float) ($_POST['occupancy'] ?? 11)) / 12;

// The loan duration, in months.
$loan_duration = ((int) ($_POST['loan_duration'] ?? 30)) * 12;

// The interest rate.
$loan_interest_rate = ((float) ($_POST['loan_interest_rate'] ?? 2.7)) / 100;

$acquisition_cash_required = $acquisition_price * $down_payment_rate + $acquisition_price * $acquisition_cost + $acquisition_price * $cash_buffer;

$monthly_interest_rate = $loan_interest_rate / 12;

class Loan {

    public $remainingMonths;
    public $principal;
    public $monthlyPayment;
    public $monthlyInterestRate;

    public function __construct(int $acquisition_price, float $down_payment_rate, float $monthly_interest_rate, int $loan_duration)
    {
        $this->remainingMonths = $loan_duration;
        $this->principal = $acquisition_price - $acquisition_price * $down_payment_rate;
        $this->monthlyInterestRate = $monthly_interest_rate;
        $this->monthlyPayment = $this->principal * ($monthly_interest_rate + $monthly_interest_rate / ((1 + $monthly_interest_rate) ** $loan_duration - 1));
    }

    public function nextMonth() {
        if ($this->remainingMonths > 0) {
            $this->remainingMonths--;
        }
        $interest_to_pay = $this->principal * $this->monthlyInterestRate;
        $this->principal -= $this->monthlyPayment - $interest_to_pay;
    }
}

class Property {

    /**
     * @var Loan
     */
    public $loan;

    public $acquisitionPrice;
    public $remainingMonths;
    public $value;
    public $monthlyAppreciation;

    public function __construct(int $property_management_duration, int $acquisition_price, float $down_payment_rate, float $yearly_appreciation, int $loan_duration, float $monthly_interest_rate)
    {
        $this->loan = new Loan($acquisition_price, $down_payment_rate, $monthly_interest_rate, $loan_duration);
        $this->acquisitionPrice = $acquisition_price;
        $this->value = $acquisition_price;
        $this->remainingMonths = $property_management_duration;
        $this->monthlyAppreciation = $yearly_appreciation / 12;
    }

    public function nextMonth() {
        if ($this->remainingMonths > 0) {
            $this->remainingMonths--;
        }
        $this->value += $this->acquisitionPrice * $this->monthlyAppreciation;
        $this->loan->nextMonth();
    }

}


$operations_account = $starting_capital;
$buffer_account = 0;
$date = strtotime(date('Y-m-01'));
/** @var Property[] $properties */
$properties = [];

$number_formatter = new NumberFormatter( 'de_DE', NumberFormatter::CURRENCY );

$rows = [];
for ($phase = 0; $phase < count($phases); $phase++) {
    while (continue_phase($phase, $properties, $portfolio_size, $date)) {
        // Deposit monthly cash contributions during phase 1.
        if ($phase === 0) {
            foreach ($investors as $key => $investor) {
                $contribution = $investor['contributions'];
                $operations_account += $contribution;
                $investors[$key]['invested'] += $contribution;
            }
        }

        $profit = 0;

        // Sell properties past expiration date.
        foreach ($properties as $key => $property) {
            if ($property->remainingMonths == 0) {
                // Receive the cash for the property.
                $profit = $property->value;
                // Pay the costs associated with selling a property.
                $profit -= $property->value * $sale_cost;
                // Pay back the bank.
                $profit -= $property->loan->principal;
                // Return the buffer part, count it as profit.
                $buffer = $cash_buffer * $acquisition_price;
                $profit += $buffer;
                $buffer_account -= $buffer;

                unset($properties[$key]);
            }
        }

        // Collect rent and pay loans. Use this to calculate cash on cash.
        $rental_income = 0;
        foreach ($properties as $key => $property) {
            $rental_income += $rental_price * $occupancy;
            if ($property->loan->principal > 0 && $property->loan->remainingMonths > 0) {
                $rental_income -= $property->loan->monthlyPayment;
            }
        }

        $profit += $rental_income;

        $cash_on_cash = count($properties) ? $rental_income * 12 / (count($properties) * $acquisition_price * $down_payment_rate + $acquisition_price * $acquisition_cost) : 0;

        // Keep track of the original profit amount, so that we can calculate the dividends and loan paydowns as
        // percentages of the total profit.
        $total_profit = $profit;

        // Pay out dividends.
        $total_dividend_to_pay = $profit * $phases[$phase]['dividend'];
        $total_invested = array_reduce($investors, function ($carry, $investor) {
            return $carry + $investor['invested'];
        }, 0);

        // Bail out if nothing is being invested, no point in continuing the calculation.
        if ($total_invested === 0) {
            break;
        }

        foreach ($investors as $key => $investor) {
            $share = $investor['invested'] / $total_invested;
            $dividend = $total_dividend_to_pay * $share;
            $investors[$key]['earned'] += $dividend;
            $profit -= $dividend;
        }

        // Pay down the loan with the largest remaining principal.
        if (!empty($properties) && $phases[$phase]['paydown'] > 0) {
            usort($properties, function (Property $a, Property $b) {
                return $a->loan->principal <=> $b->loan->principal;
            });
            $key = array_key_first($properties);

            $paydown_amount = min($total_profit * $phases[$phase]['paydown'], $properties[$key]->loan->principal);
            $properties[$key]->loan->principal -= $paydown_amount;
            $profit -= $paydown_amount;
        }

        // Deposit the remaining profit on the operations account.
        $operations_account += $profit;

        // Buy a new property if possible.
        if ($operations_account > $acquisition_cash_required) {
            // Pay the down payment.
            $operations_account -= $acquisition_price * $down_payment_rate;
            // Pay the acquisition cost.
            $operations_account -= $acquisition_price * $acquisition_cost;
            // Transfer the buffer cash to the buffer account.
            $operations_account -= $acquisition_price * $cash_buffer;
            $buffer_account += $acquisition_price * $cash_buffer;
            // Acquire the property.
            $property = new Property($property_management_duration, $acquisition_price, $down_payment_rate, $yearly_appreciation, $loan_duration, $monthly_interest_rate);
            $properties[] = $property;
        }

        // Total outstanding loans.
        $total_outstanding = array_reduce($properties, function ($carry, Property $property) {
            return $carry + $property->loan->principal;
        }, 0);

        $row = [
            // Date.
            date('Y-m', $date),
            // Operations account.
            $number_formatter->formatCurrency($operations_account, "EUR"),
            // Buffer account.
            $number_formatter->formatCurrency($buffer_account, "EUR"),
            // Number of properties.
            count($properties),
            // Rental income.
            $number_formatter->formatCurrency($rental_income, "EUR"),
            // Cash on cash.
            $row[] = sprintf("%.2f%%", $cash_on_cash * 100),
            // Total outstanding amount.
            $number_formatter->formatCurrency($total_outstanding, "EUR"),
        ];

        foreach ($investors as $investor) {
            // Invested.
            $row[] = $number_formatter->formatCurrency($investor['invested'], "EUR");
            // Earned.
            $row[] = $number_formatter->formatCurrency($investor['earned'], "EUR");
            // ROI.
            $roi = $investor['earned'] > $investor['invested'] ? ($investor['earned'] - $investor['invested']) / $investor['invested'] : 0;
            $row[] = sprintf("%.2f%%", $roi * 100);
            // Yearly rate of return.
            $years_since_start = date('Y', $date) - date('Y');
            $ror = $years_since_start ? $roi / $years_since_start : 0;
            $row[] = sprintf("%.2f%%", $ror * 100);
        }

        $rows[] = $row;

        // Process properties.
        foreach ($properties as $property) {
            $property->nextMonth();
        }

        $date = strtotime(date('c', $date) . ' + 1 month');
    }
}



function continue_phase($phase, $properties, $portfolio_size, $date) {
    if ($date >= strtotime('2050-01-01')) return FALSE;

    switch ($phase) {
        case 0:
            return count($properties) < $portfolio_size;
        case 1:
            return count($properties) > 0;
    }
}
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
    <title>Real estate company simulation</title>
</head>
<body class="bg-light">
<?php if (!empty($_POST)) { ?>
    <div class="card my-5">
        <div class="card-body">
            <h5 class="card-title">Simulation result</h5>
            <table id="simulation-result" class="display" style="width:100%; font-size: small">
                <thead>
                <tr>
                    <th>Date</th>
                    <th>Account</th>
                    <th>Buffer</th>
                    <th>#</th>
                    <th>Rental income</th>
                    <th>CoC</th>
                    <th>Outstanding</th>
                    <?php
                    foreach ($investors as $investor) {
                        echo "<th>{$investor['name']} invested</th>";
                        echo "<th>{$investor['name']} returned</th>";
                        echo "<th>{$investor['name']} ROI</th>";
                        echo "<th>{$investor['name']} ROR</th>";
                    }
                    ?>
                </tr>
                </thead>
                <tbody>
                <?php
                foreach ($rows as $row) {
                    echo '<tr>';
                    foreach ($row as $col) {
                        echo '<td>' . htmlentities($col) . '</td>';
                    }
                    echo '</tr>';
                }
                ?>
                </tbody>
                <tfoot>
                <tr>
                    <th>Date</th>
                    <th>Account</th>
                    <th>Buffer</th>
                    <th>#</th>
                    <th>Rental income</th>
                    <th>CoC</th>
                    <th>Outstanding</th>
                    <?php
                    foreach ($investors as $investor) {
                        echo "<th>{$investor['name']} invested</th>";
                        echo "<th>{$investor['name']} returned</th>";
                        echo "<th>{$investor['name']} ROI</th>";
                        echo "<th>{$investor['name']} ROR</th>";
                    }
                    ?>
                </tr>
                </tfoot>
            </table>
        </div>
    </div>
<?php } ?>
<div class="container">
    <div class="py-5">
        <h1 class="text-center py-3">Real estate company simulation</h1>
        <p class="lead">
            This simulates the expected ROI of a real estate company. The company is designed to have two operational
            phases: in the first phase a number of properties will be acquired using monthly deposits from a group of
            investors. The properties will be rented out for a number of years.
        </p>
        <p class="lead">
            In the second operational phase no more investments are made, but the properties will be gradually sold off
            and profits are returned in the form of dividends. Once the last property is sold the company will be
            disbanded.
        </p>
    </div>
    <div class="card bg-white p-6">
        <form method="POST" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
            <fieldset class="m-3 p-3 border bg-light">
                <legend class="w-auto">Investments</legend>
                <?php for ($i = 1; $i < 4; $i++) { ?>
                    <div class="form-row">
                        <label class="col-sm-2 col-form-label">Investor <?php echo $i; ?></label>
                        <div class="col">
                            <div class="input-group">
                                <div class="input-group-prepend">
                                    <span class="input-group-text">&euro;</span>
                                </div>
                                <input type="number" class="form-control" name="investor_<?php echo $i; ?>_starting_capital" placeholder="Starting capital" value="<?php echo $investors[$i - 1]['starting_capital']; ?>">
                            </div>
                            <?php if ($i === 3): ?>
                                <small class="form-text text-muted">The starting capital invested.</small>
                            <?php endif; ?>
                        </div>
                        <div class="col">
                            <div class="input-group">
                                <div class="input-group-prepend">
                                    <span class="input-group-text">&euro;</span>
                                </div>
                                <input type="number" class="form-control" name="investor_<?php echo $i; ?>_contributions" placeholder="Monthly contribution" value="<?php echo $investors[$i - 1]['contributions']; ?>">
                            </div>
                            <?php if ($i === 3): ?>
                                <small class="form-text text-muted">The monthly investment made by each investor during phase 1.</small>
                            <?php endif; ?>
                        </div>
                    </div>
                <?php } ?>
                <?php foreach (['dividend', 'paydown'] as $target) { ?>
                    <div class="form-row<?php if ($target === 'dividend') echo " pt-3"; ?>">
                        <label class="col-sm-2 col-form-label"><?php echo ucfirst($target); ?></label>
                        <div class="col">
                            <div class="input-group">
                                <div class="input-group-prepend">
                                    <span class="input-group-text">%</span>
                                </div>
                                <input type="number" class="form-control" name="<?php echo $target; ?>_phase_1" value="<?php echo $phases[0][$target] * 100; ?>" step=".1">
                            </div>
                            <?php if ($target === 'paydown'): ?>
                                <small class="form-text text-muted">The percentage of the monthly profit that is paid out as dividends, or used to pay down loans during phase 1. The remainder is reinvested into the company.</small>
                            <?php endif; ?>
                        </div>
                        <div class="col">
                            <div class="input-group">
                                <div class="input-group-prepend">
                                    <span class="input-group-text">%</span>
                                </div>
                                <input type="number" class="form-control" name="<?php echo $target; ?>_phase_2" value="<?php echo $phases[1][$target] * 100; ?>" step=".1">
                            </div>
                            <?php if ($target === 'paydown'): ?>
                                <small class="form-text text-muted">The percentage of the monthly profit that is paid out as dividends, or used to pay down loans during phase 2. The remainder is reinvested into the company.</small>
                            <?php endif; ?>
                        </div>
                    </div>
                <?php } ?>
            </fieldset>
            <fieldset class="m-3 p-3 border bg-light">
                <legend class="w-auto">Company</legend>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="cash_buffer">Cash buffer</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="cash_buffer" id="cash_buffer" placeholder="Cash buffer" value="<?php echo $cash_buffer * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The initial capital that is used to fund the company.</small>
                    </div>
                    <label class="col-sm-2 col-form-label" for="portfolio_size">Properties to acquire</label>
                    <div class="col">
                        <input type="number" class="form-control" name="portfolio_size" id="portfolio_size" value="<?php echo $portfolio_size; ?>" min="1" required>
                        <small class="form-text text-muted">The number of properties to acquire before starting to pay out dividends.</small>
                    </div>
                </div>
            </fieldset>
            <fieldset class="m-3 p-3 border bg-light">
                <legend class="w-auto">Properties</legend>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="acquisition_price">Acquisition price</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">&euro;</span>
                            </div>
                            <input type="number" class="form-control" name="acquisition_price" id="acquisition_price" placeholder="Acquisition price" value="<?php echo $acquisition_price; ?>" required>
                        </div>
                        <small class="form-text text-muted">The buy price of each property.</small>
                    </div>
                    <label class="col-sm-2 col-form-label" for="down_payment_rate">Down payment</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="down_payment_rate" id="down_payment_rate" placeholder="Down payment" value="<?php echo $down_payment_rate * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The down payment for the loan, as a percentage of the buy price.</small>
                    </div>
                </div>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="acquisition_cost">Acquisition cost</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="acquisition_cost" id="acquisition_cost" placeholder="Acquisition cost" value="<?php echo $acquisition_cost * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The costs of acquiring a property, as a percentage of the buy price.</small>
                    </div>
                    <label class="col-sm-2 col-form-label" for="sale_cost">Sale cost</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="sale_cost" id="sale_cost" placeholder="Sale cost" value="<?php echo $sale_cost * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The costs of selling a property, as a percentage of the sell price.</small>
                    </div>
                </div>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="yearly_appreciation">Yearly appreciation</label>
                    <div class="col-sm-4">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="yearly_appreciation" id="yearly_appreciation" placeholder="Yearly appreciation" value="<?php echo $yearly_appreciation * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The rate at which the property is expected to appreciate on a yearly basis.</small>
                    </div>
                </div>
            </fieldset>
            <fieldset class="m-3 p-3 border bg-light">
                <legend class="w-auto">Renting</legend>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="rental_price">Rental income</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">&euro;</span>
                            </div>
                            <input type="number" class="form-control" name="rental_price" id="rental_price" placeholder="Rental price" value="<?php echo $rental_price; ?>" required>
                        </div>
                        <small class="form-text text-muted">The monthly rental income.</small>
                    </div>
                    <label class="col-sm-2 col-form-label" for="property_management_duration">Years to rent out</label>
                    <div class="col">
                        <input type="number" class="form-control" name="property_management_duration" id="property_management_duration" value="<?php echo $property_management_duration / 12; ?>" min="1" required>
                        <small class="form-text text-muted">The number of years to rent out a property before selling it.</small>
                    </div>
                </div>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="occupancy">Occupancy</label>
                    <div class="col-sm-4">
                        <input type="number" class="form-control" name="occupancy" id="occupancy" value="<?php echo (int) ($occupancy * 12); ?>" min="1" max="12" required>
                        <small class="form-text text-muted">The number of months per year that a property is rented out.</small>
                    </div>
                </div>
            </fieldset>
            <fieldset class="m-3 p-3 border bg-light">
                <legend class="w-auto">Loan</legend>
                <div class="form-row">
                    <label class="col-sm-2 col-form-label" for="loan_interest_rate">Interest rate</label>
                    <div class="col">
                        <div class="input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text">%</span>
                            </div>
                            <input type="number" class="form-control" name="loan_interest_rate" id="loan_interest_rate" placeholder="Interest rate" value="<?php echo $loan_interest_rate * 100; ?>" step=".1" required>
                        </div>
                        <small class="form-text text-muted">The yearly interest rate.</small>
                    </div>
                    <label class="col-sm-2 col-form-label" for="loan_duration">Loan duration</label>
                    <div class="col">
                        <input type="number" class="form-control" name="loan_duration" id="loan_duration" value="<?php echo (int) ($loan_duration / 12); ?>" min="1" max="30" required>
                        <small class="form-text text-muted">The length of the loan.</small>
                    </div>
                </div>
            </fieldset>
            <button type="submit" class="m-3 btn btn-primary" value="submit">Submit</button>
        </form>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
    $('#simulation-result').DataTable( {
        "lengthMenu": [[50, 100, -1], [50, 100, "All"]],
        "paging": false,
        "searching": false
    } );
} );
</script>
</body>
</html>
<?php