Streamlining Real Estate Investment Modeling with ChatGPT

This post is a bit of a deviation from my usual thoughts and musings, but it demonstrates a few interesting points I wanted to share.

Lately, I’ve been modeling multiple investment scenarios for various projects. I often need to adjust share classes, GP/LP splits, and introduce waterfalls to see how they impact the overall model.

ChatGPT-4o has been incredibly helpful in this process. Although it sometimes loses context and introduces bugs, it has significantly sped up my ability to understand and adjust models on the fly without making extensive edits in Excel or Google Sheets.

In my experiments, I was able to get GPT to analyze a dataset consisting of 15 years of P&Ls. I provided it with some adjustable variables and then had it export the results (with formulas) to Excel.

Impressed with the results, I decided to take it a step further. On a whim, I asked GPT to generate some JavaScript and a lightweight front end where I could manipulate the variables directly. After applying some Tailwind styles and cleaning up the output, I had a very useful prototype for quickly modeling and adjusting investor returns.

As an added bonus, I added a dynamic chart to show cumulative returns over the investment period.

All in, it took about 1-2 hours to create and iterate on the MVP and another hour or so to integrate it directly into this webpage.

Is this code battle-tested and production-ready?

Probably not.

But it works today and helps me do my job better.

Just a few years ago, creating and debugging something of this scale would have taken more than a week.

As GPTs continue to improve, they are streamlining processes and making it easier for us to tackle complex tasks efficiently, turning what once were formidable challenges into simple routines.

Play around with the calculator below or scroll to the bottom of the page to see the source code .

Investor Waterfall Calculator

Total LP Investment: $ This is the total size of your raise

Minimum LP Share Size: $ This is the minimum size of your share

Pre-Recoupment LP: % % the LP gets prior to recouping their initial investment

Up to 1.5x Return: % Waterfalled return from 1.0 to 1.5x their initial investment

1.5 to 2.5x Return: % Waterfalled return from 1.5 to 2.5x their initial investment

Above 2.5x Return: % Waterfalled return above 2.5x their initial investment

Year	Net Return	LP %	GP %	Total LP $	Total GP $	Prop LP $	LP IRR	LP AAR	Equity Multiple
	Add Your NOI From Your P&L	LP Percentage	GP Percentage	Total Return for LP	Total Return for GP	Proportional Return for LP	Internal Rate of Return for LP	Average Annual Return for LP	Return on Equity Multiple
1	$	50%	50%	$62,500	$62,500	$6,250	N/A	N/A	0.06
2	$	50%	50%	$175,000	$175,000	$17,500	N/A	N/A	0.24
3	$	50%	50%	$200,000	$200,000	$20,000	N/A	N/A	0.44
4	$	50%	50%	$225,000	$225,000	$22,500	N/A	N/A	0.66
5	$	50%	50%	$262,500	$262,500	$26,250	N/A	N/A	0.93
6	$	50%	50%	$287,500	$287,500	$28,750	4.90%	N/A	1.21
7	$	30%	70%	$187,500	$437,500	$18,750	8.03%	N/A	1.40
8	$	30%	70%	$195,000	$455,000	$19,500	10.43%	N/A	1.59
9	$	20%	80%	$140,000	$560,000	$14,000	11.74%	N/A	1.74
10	$	20%	80%	$145,000	$580,000	$14,500	12.81%	18.80%	1.88

LP Cumulative Returns

Source Code

	<!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>

view raw gistfile1.txt hosted with ❤ by GitHub

Streamlining Real Estate Investment Modeling with ChatGPT

Streamlining Real Estate Investment Modeling with ChatGPT

Investor Waterfall Calculator

LP Cumulative Returns

Source Code

Schedule a FREE Call

Subscribe to My Newsletter

BRET MORGAN GROUP