Maximizing Excel's Goal Seek Tool When Analyzing Real Estate
Some of the most common questions I receive from Tactica template users revolve around "what-if analysis" and real estate goals. Real estate analysis encompasses infinite toggles that can affect the investment outlook. Changing variables to see how they affect investment performance is a wise course of action, although it can be tedious without proper protocols.
Goal Seek in Excel Article Focus
How to Use Excel Goal Seek
By adjusting a manual input, you can use the Goal Seek analysis tool to find your desired result.
Example: How many widgets would you need to sell to have a profit of $1,000,000?
$1M Profit = Desired Real Estate Goal
Quantity of Widget Sales = Manual Input
Running a Goal Seek analysis would tell you how many widgets you must sell to earn a $1,000,000 profit with no guesswork.
In the Tactica workbook, these desired outcomes relate to a specific return metric, such as IRR, equity multiple, cap rate, cash-on-cash, or net profit.
Example: "How can I quickly figure out how much to pay for a property that will give me a cash-on-cash return of 8%?"
8% Cash-on-Cash = Desired Result
Purchase Price = Manual Input
Let’s follow the imminent steps to solve this in the Tactica Value-Add Template.
Firstly, you can see on the "Investment Summary" tab that no cash-on-cash yield is displayed in the "Project Level Metrics" section.
I would first grab the cash-on-cash yield in Year 1 from the "Returns Summary" tab by hitting "=" and then typing in "Cash-on-Cash Year 1" to the left.
Now, everything is in one central place on the main summary page.
Goal Seek falls under the Data Tools Group, located on the main navigation header at the top of the Excel workbook. Go to Data Tab > What-If Analysis > Goal Seek.
From there, the Goal Seek Dialog box will appear.
With Goal Seek, you have three input values:
1. Set Cell: F39 (cash-on-cash yield cell)
2. To Value: 8% (goal objective)
3. By Changing cell F28 (Price of Property)
Click “OK.”
The Result: Paying $9.47 million would achieve an 8% cash-on-cash return in Year 1.
Useful Analysis Applications of Excel Goal Seek
Loan Amount that Maximizes IRR
Let's say the property's price is firm at $12.5 million. Current underwriting projects a 14.57% leveraged IRR. You have a great relationship with your lender and could utilize more leverage if necessary. You'd like to see a 16% IRR.
1. Set Cell: F33 (IRR)
2. To Value: 16% (goal objective)
3. By Changing cell: C16 (LTV)
80% leverage should get you there, assuming you can follow through on your other projections.
Loan Interest Rate that Maximizes IRR
As in the example above, the price is firm at $12.5 million and LTV at 75%. The loan interest rate in the model is 4.00%. We can test how low interest is to achieve a 16% leveraged IRR.
1. Set Cell: F33 (IRR)
2. To Value: 16% (goal objective)
3. By Changing cell: C18 (interest rate)
A 3.10% interest rate would do the trick. Perhaps it would be a combination of increasing leverage and decreasing the interest rate. Goal Seek is a powerful Excel tool because it can provide multiple solutions, which may play a part in the final resolution to make the numbers work.
Purchase Price that Maximizes IRR
This is probably the most prominent everyday use when Goal Seeking real estate investments. All assumptions are firm; the price is the only toggle left to adjust. I put financing assumptions at 75% LTV and interest at 4.00%. Let's solve for a 16% IRR by changing the cell value that hosts the offering price.
1. Set Cell: F33 (IRR)
2. To Value: 16% (goal objective)
3. By Changing cell: F28 (Price)
The price would need to come down from $12.50 million to $12.15 million to hit a 16% leveraged IRR.
Residual Cap Rate that Minimizes IRR
The residual sales price is one of the most dangerous assumptions when underwriting properties. An aggressive back-end sales price can make any investment opportunity look good. In the Tactica Value-Add Financial Model, a residual cap rate determines the residual sales value. The Tactica model will take the cap rate today and add an annual escalator each year of the investment hold period to determine the residual cap rate.
So, in the current model, where I'm assuming a residual sale in Year 7, the residual cap rate would be increased by ten bps per year (70 bps total) over the estimated 5.25% market cap rate today. Therefore, the residual cap rate in Year 7 is:
5.25% + (.10%) * 7 = 5.95%
Unlike past examples, we will solve for a minimum acceptability threshold. This test is meant to test damage control if there is a cap rate correction during the investment hold.
I currently have $12,000,000 in the model as the offered price, which produces a 16.63% leveraged IRR. I do not want to see the IRR dip below 14.00%. So, I will use Goal Seek to stress the residual cap rate.
1. Set Cell: F33 (IRR)
2. To Value: 14% (goal objective)
3. By Changing cell: N15 (cap rate increment)
The annual increment would jump to 19 bps, meaning the property would sell for a 6.55% cap seven years from now.
5.25% + (.19%) * 7 = 6.55%
6.55% is a massive increase over the current market cap rate of 5.25%. I would be excited about the prospect of this investment given that if there were a correction, cap rates increase, and the residual sales price dropped significantly, I could still achieve a respectable 14% leveraged IRR.
Preferred Return that Maximized GP Profitability
In the final example, I'll show you how Goal Seek can be utilized for GP/LP distribution analysis. Let's say you are the general partner (GP) and plan to pay limited partners (LPs) a preferred annual return of 8%.
Total GP distributions are approximately $1.4 million (in the green column). What would it take to increase net profit to $1.5 million by adjusting the preferred return?
1. Set Cell: D25 (GP total distributions)
2. To Value: $1.5 Million (goal objective)
3. By Changing cell: E10 (preferred return)
You'd need to lower the threshold to 4.71%. It's doubtful your LP investors would take the bait, as 6% -10 % tends to be the standard preferred return offered in a GP/LP partnership.
Excel Solver Add-On
You can even use Goal Seek's more sophisticated counterpart, "Solver," to set up rental increase thresholds for individual floorplans on a value-add analysis. The solver tool is far more advanced and goes beyond the scope of this article. Still, researching the Solver add-on application may be worthwhile if you are looking for what-if analysis on multiple variables at once.
Summarizing Goal Seek Formula
Goal Seek is a powerful tool included in Excel that can help you stress the proforma and back into your desired results. The possibilities are endless, and Goal Seek can be used countless times. Understanding Goal Seek, and its potential can help you unlock solutions that may have gone unnoticed and ultimately give you ideas to entice investors, persuade lenders, or negotiate with a seller.
