Home > About Us > News & Events > Article

Conducting an IRR Lookback


If you require Estate Master to calculate returns based on a certain percentage of profit share distribution beyond a specified IRR, this can be easily calculated. This is commonly known as the ‘IRR Lookback Approach’.

Let's assume a scenario where a developer has agreed to share 50% of all profit with an investor, but only once the project itself has reached an IRR of 20%.

Step 1: Setup the Investor in the Financing Section

Setup the investor as one of the ‘Loans’ in the Financing Section (say Loan 1), making sure at this stage the ‘Profit Spilt to Lender 1’ cell is set to 0%

Step 2: Enter all other Development Costs & Revenues

Ensure all other development costs and revenues have been entered in the model so that the necessary project returns are being calculated on the 'Summary' report. In this case, prior to Profit Spilt, the Project IRR is 28%.

Step 3: Run a Goal Seek Scenario

Using the 'Goal Seek' function in the 'Tools' menu, run the following scenario:

  • Set Cell = Select the 'Project IRR' output on the Summary sheet.
  • To Value = The desired IRR, being 20%.
  • By Changing Cell = Select the 'Profit Split to Lender 1' input in the Financing section.

    Press 'OK' to start the Goal Seek scenario.

Step 4: Check answer for Profit Split

The Goal Seek will then automatically calculate a new percentage on the 'Profit Split to Lender 1' input, indicating how much profit the developer can forgo to achieve their desired return of 20% Project IRR.

Step 5: Adjust Profit Split

However, since the agreement was only to share 50% of the profit above the desired Project IRR, that percentage needs to be manually multiplied by 50% to give an adjusted profit split.

Date Published: 17 Oct 2013
Category: Hints and Tips

Want a test drive?

All products are available to try for 14 days

Speak to a Live Agent now