<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Real Estate Investor Waterfall Calculator</title>
</head>
<body class="bg-gray-100 p-6">
<div class="grid grid-cols-1 md:grid-cols-2 gap-1 mb-1">
<div>
<label class="block mb-1 text-black">Total LP Investment: $</label>
<small>This is the total size of your raise</small>
<input type="number" id="totalLpInvestment" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="1000000">
</div>
<div>
<label class="block mb-1 text-black">Minimum LP Share Size: $</label>
<small>This is the minimum size of your share</small>
<input type="number" id="proportionalLpShare" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="100000">
</div>
<div>
<label class="block mb-1 text-black">Pre-Recoupment LP: %</label>
<small>% the LP gets prior to recouping their initial investment</small>
<input type="number" id="preWaterfallLp" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="50">
</div>
<div>
<label class="block mb-1 text-black">Up to 1.5x Return: %</label>
<small>Waterfalled return from 1.0 to 1.5x their initial investment</small>
<input type="number" id="upTo1_5xReturnLp" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="30">
</div>
<div>
<label class="block mb-1 text-black">1.5 to 2.5x Return: %</label>
<small>Waterfalled return from 1.5 to 2.5x their initial investment</small>
<input type="number" id="between1_5xAnd2_5xReturnLp" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="20">
</div>
<div>
<label class="block mb-1 text-black">Above 2.5x Return: %</label>
<small>Waterfalled return above 2.5x their initial investment</small>
<input type="number" id="above2_5xReturnLp" class="block w-full p-1 border rounded-md bg-gray-100 text-black" value="20">
</div>
</div>
<div class="pt-2">
<button onclick="updateTable()" class="bg-blue-500 text-white font-semibold py-1 px-2 rounded mb-2">Update Table</button>
</div>
<div class="overflow-x-auto">
<table id="investmentTable" class="min-w-full bg-white border-collapse border border-gray-800">
<thead>
<tr class="text-sm bg-gray-300 text-black">
<th class="py-1 px-1 border border-gray-800">Year</th>
<th class="py-1 px-1 border border-gray-800">Net Return</th>
<th class="py-1 px-1 border border-gray-800">LP %</th>
<th class="py-1 px-1 border border-gray-800">GP %</th>
<th class="py-1 px-1 border border-gray-800">Total LP $</th>
<th class="py-1 px-1 border border-gray-800">Total GP $</th>
<th class="py-1 px-1 border border-gray-800">Prop LP $</th>
<th class="py-1 px-1 border border-gray-800">LP IRR</th>
<th class="py-1 px-1 border border-gray-800">LP AAR</th>
<th class="py-1 px-1 border border-gray-800">Equity Multiple</th>
</tr>
<tr class="text-xs text-gray-800 bg-gray-700">
<td class="py-1 px-1 border border-gray-800 pb-2"></td>
<td class="py-1 px-1 border border-gray-800 pb-2">Add Your NOI From Your P&L</td>
<td class="py-1 px-1 border border-gray-800 pb-2">LP Percentage</td>
<td class="py-1 px-1 border border-gray-800 pb-2">GP Percentage</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Total Return for LP</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Total Return for GP</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Proportional Return for LP</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Internal Rate of Return for LP</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Average Annual Return for LP</td>
<td class="py-1 px-1 border border-gray-800 pb-2">Return on Equity Multiple</td>
</tr>
</thead>
<tbody id="investmentTableBody">
<!-- Table rows will be added here by JavaScript -->
</tbody>
</table>
</div>
<div class="pt-2">
<button onclick="updateTable()" class="bg-blue-500 text-white font-semibold py-1 px-2 rounded mb-2">Update Table</button>
<button onclick="addYear()" class="bg-gray-500 text-white font-semibold text-xs py-1 px-2 rounded mx-1 mb-1">Add Additonal Year</button>
<!--<button onclick="downloadPDF()" class="bg-red-500 text-white font-semibold py-1 px-2 rounded mb-2">Download PDF</button>-->
</div>
<div class="mt-6">
<h2 class="text-black">LP Cumulative Returns</h2>
</div>
<canvas id="lpChart" class="mt-6"></canvas>
<script src="https://cdn.tailwindcss.com"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.3.1/jspdf.umd.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.3.2/html2canvas.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
let netReturns = [125000, 350000, 400000, 450000, 525000, 575000, 625000, 650000, 700000, 725000];
let lpChart;
function populateInitialTable() {
const tableBody = document.getElementById('investmentTableBody');
tableBody.innerHTML = '';
netReturns.forEach((netReturn, index) => {
const year = index + 1;
const row = document.createElement('tr');
row.innerHTML = `
<td class="text-sm py-1 px-1 border border-gray-800">${year}</td>
<td class="text-sm py-1 px-1 border border-gray-800">
<div class="flex items-center">
<span>$ </span>
<input type="number" class="w-[150px] p-1 border rounded-md bg-gray-100 text-black" id="netReturn${year}" value="${netReturn}">
</div>
</td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpPercentage${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="gpPercentage${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="totalLpReturn${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="totalGpReturn${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="proportionalLpReturn${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpIrr${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpAverageAnnualReturn${year}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="currentEquityMultiple${year}"></td>
`;
tableBody.appendChild(row);
});
}
function addYear() {
const tableBody = document.getElementById('investmentTableBody');
const newYear = netReturns.length + 1;
netReturns.push(0); // Add default net return value for the new year
const row = document.createElement('tr');
row.innerHTML = `
<td class="text-sm py-1 px-1 border border-gray-800">${newYear}</td>
<td class="text-sm py-1 px-1 border border-gray-800">
<div class="flex items-center">
<span>$ </span>
<input type="number" class="w-[150px] p-1 border rounded-md bg-gray-100 text-black" id="netReturn${newYear}" value="0">
</div>
</td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpPercentage${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="gpPercentage${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="totalLpReturn${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="totalGpReturn${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="proportionalLpReturn${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpIrr${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="lpAverageAnnualReturn${newYear}"></td>
<td class="text-sm py-1 px-1 border border-gray-800" id="currentEquityMultiple${newYear}"></td>
`;
tableBody.appendChild(row);
}
function updateChart(data) {
const ctx = document.getElementById('lpChart').getContext('2d');
if (lpChart) {
lpChart.data.labels = data.labels;
lpChart.data.datasets[0].data = data.values;
lpChart.update();
} else {
lpChart = new Chart(ctx, {
type: 'line',
data: {
labels: data.labels,
datasets: [{
label: 'LP Cumulative Return',
data: data.values,
borderColor: 'rgba(75, 192, 192, 1)',
borderWidth: 2,
fill: false
}]
},
options: {
responsive: true,
scales: {
x: {
title: {
display: true,
text: 'Year'
}
},
y: {
title: {
display: true,
text: 'LP Cumulative Return'
},
beginAtZero: true,
ticks: {
callback: function(value, index, values) {
return '$' + value.toLocaleString();
}
}
}
},
plugins: {
tooltip: {
callbacks: {
label: function(context) {
let label = context.dataset.label || '';
if (label) {
label += ': ';
}
if (context.parsed.y !== null) {
label += '$' + context.parsed.y.toLocaleString();
}
return label;
}
}
}
}
}
});
}
}
function updateTable() {
const totalLpInvestment = parseFloat(document.getElementById('totalLpInvestment').value);
const proportionalLpShare = parseFloat(document.getElementById('proportionalLpShare').value);
const preWaterfallLp = parseFloat(document.getElementById('preWaterfallLp').value);
const upTo1_5xReturnLp = parseFloat(document.getElementById('upTo1_5xReturnLp').value);
const between1_5xAnd2_5xReturnLp = parseFloat(document.getElementById('between1_5xAnd2_5xReturnLp').value);
const above2_5xReturnLp = parseFloat(document.getElementById('above2_5xReturnLp').value);
let cumulativeProportionalLpReturn = 0;
let cashFlows = [-proportionalLpShare]; // Initial investment as a negative cash flow
let proportionalLpReturns = [];
let years = [];
netReturns.forEach((netReturn, index) => {
const year = index + 1;
const netReturnValue = Math.round(parseFloat(document.getElementById(`netReturn${year}`).value));
let lpPercentage;
if (cumulativeProportionalLpReturn < proportionalLpShare) {
lpPercentage = preWaterfallLp;
} else if (cumulativeProportionalLpReturn < 1.5 * proportionalLpShare) {
lpPercentage = upTo1_5xReturnLp;
} else if (cumulativeProportionalLpReturn < 2.5 * proportionalLpShare) {
lpPercentage = between1_5xAnd2_5xReturnLp;
} else {
lpPercentage = above2_5xReturnLp;
}
const gpPercentage = 100 - lpPercentage;
const totalLpReturn = Math.round(netReturnValue * lpPercentage / 100);
const totalGpReturn = Math.round(netReturnValue * gpPercentage / 100);
const proportionalLpReturn = Math.round(totalLpReturn * proportionalLpShare / totalLpInvestment);
cumulativeProportionalLpReturn += proportionalLpReturn;
cashFlows.push(proportionalLpReturn); // Add this year's return to cash flows
proportionalLpReturns.push(cumulativeProportionalLpReturn);
years.push(year);
const lpAverageAnnualReturn = cumulativeProportionalLpReturn / proportionalLpShare / year;
const currentEquityMultiple = cumulativeProportionalLpReturn / proportionalLpShare;
const irrValue = approximateIRR(cashFlows);
document.getElementById(`lpPercentage${year}`).innerText = `${lpPercentage}%`;
document.getElementById(`gpPercentage${year}`).innerText = `${gpPercentage}%`;
document.getElementById(`totalLpReturn${year}`).innerText = `$${totalLpReturn.toLocaleString()}`;
document.getElementById(`totalGpReturn${year}`).innerText = `$${totalGpReturn.toLocaleString()}`;
document.getElementById(`proportionalLpReturn${year}`).innerText = `$${proportionalLpReturn.toLocaleString()}`;
document.getElementById(`lpIrr${year}`).innerText = (irrValue < 0 || !isFinite(irrValue)) ? "N/A" : `${(irrValue * 100).toFixed(2)}%`;
document.getElementById(`lpAverageAnnualReturn${year}`).innerText = (year === netReturns.length) ? `${(lpAverageAnnualReturn * 100).toFixed(2)}%` : "N/A";
document.getElementById(`currentEquityMultiple${year}`).innerText = `${currentEquityMultiple.toFixed(2)}`;
});
updateChart({ labels: years, values: proportionalLpReturns });
}
// Simplified IRR approximation function
function approximateIRR(cashFlows, guess = 0.1) {
let npv = calculateNPV(guess, cashFlows);
const tolerance = 0.0001; // NPV tolerance for convergence
const maxIterations = 1000; // Maximum iterations to avoid infinite loop
let iteration = 0;
while (Math.abs(npv) > tolerance && iteration < maxIterations) {
const derivative = calculateNPVDerivative(guess, cashFlows);
guess -= npv / derivative;
npv = calculateNPV(guess, cashFlows);
iteration++;
}
return guess;
}
// Function to calculate NPV
function calculateNPV(rate, cashFlows) {
return cashFlows.reduce((acc, curr, i) => acc + curr / Math.pow(1 + rate, i), 0);
}
// Function to calculate the derivative of NPV with respect to the discount rate
function calculateNPVDerivative(rate, cashFlows) {
return cashFlows.reduce((acc, curr, i) => acc - i * curr / Math.pow(1 + rate, i + 1), 0);
}
function downloadPDF() {
const { jsPDF } = window.jspdf;
html2canvas(document.getElementById('investmentTable')).then(canvas => {
const imgData = canvas.toDataURL('image/png');
const pdf = new jsPDF('l', 'mm', 'a4');
const imgProps = pdf.getImageProperties(imgData);
const pdfWidth = pdf.internal.pageSize.getWidth();
const pdfHeight = (imgProps.height * pdfWidth) / imgProps.width;
pdf.addImage(imgData, 'PNG', 0, 0, pdfWidth, pdfHeight);
pdf.save('investment_table.pdf');
});
}
// Initial table population
populateInitialTable();
updateTable();
</script>
</body>
</html>