Internal Rate of Return (IRR)

S

SpatialAnalysis

New member
Joined
Jun 12, 2020
Messages
5
Hello all. I'm trying to understand if I am applying a correct definition in usage of an IRR formula within Excel. Although I know the formula is working, I need clarification on whether the application of the formula is the correct statistical/mathematical formula to use in such a (business) situation. The requirement I have is to calculate an up front payment to secure an early termination of a (5 year) commercial property lease.

There are three elements to consider:

1) an up front premium payment (in Year 0). This constitutes a surrender payment to exit the commercial lease, plus the cost of fees for legal expenses to execute this deal process
2) the on-going commercial lease payments (rent) over subsequent years - the cash flow series
3) final payments that would have to be paid at the end of the commercial lease (legal fees, dilapidation costs and fees) at the end of Year 5

(for avoidance of doubt, the end final cost payments are provisioned for in an accounting sense. Additionally, the legal fees paid as part of the Year 0 exit premium are the same legal fees that are provisioned for and would have been paid at the end of Year 5)

So my formula is calculating the IRR of

IRR(-Premium,Year 1, Year 2, Year 3, Year 4, Year 5, Final Costs/Fees)

My uncertainty is whether or not an Internal Rate of Return should be measuring the up front payment cost against a cash flow series AND additional end final costs?

Does this comply with the correct application of financial-maths in such a scenario, or should I be using a different formula?

From what I have read, the IRR seems to be the correct formula to use for calculating a measurement of up-front (exit premium) costs versus a cash flow series. It's the end final (provisioned) costs, in addition to the cash flow series, that I'm unsure about.

Thanks all!
 
I am confused. Why do the rents have anything to do with thinking about these fees? It seems you are counting the legal fees twice, once up front and again at the end. Am I correct that what we are talking about really is a penalty for early termination or for failure to exercise a renewal. And what is the IRR supposed to measure? Is it being compared to the IRR if the lease goes for the full term? Or maybe I have it all wrong. Maybe it is a five year lease, and we want a security deposit to cover what, fees, fees plus improvement allowance, fees plus improvement allowance plus rent for the full remaining term or perhaps part of the remaing term in the event of an early termination. If the lease runs full term, must the tenant pay the renter's legal fees at termination. I cannot yet get my head around the business situation.

Here is the issue. You are deep into the problem. I am not. For me to provide any modicum of help, I need to understand the business situation a lot better than I do now.
 
JeffM said:
I am confused. Why do the rents have anything to do with thinking about these fees? It seems you are counting the legal fees twice, once up front and again at the end. Am I correct that what we are talking about really is a penalty for early termination or for failure to exercise a renewal. And what is the IRR supposed to measure? Is it being compared to the IRR if the lease goes for the full term? Or maybe I have it all wrong. Maybe it is a five year lease, and we want a security deposit to cover what, fees, fees plus improvement allowance, fees plus improvement allowance plus rent for the full remaining term or perhaps part of the remaing term in the event of an early termination. If the lease runs full term, must the tenant pay the renter's legal fees at termination. I cannot yet get my head around the business situation.

Here is the issue. You are deep into the problem. I am not. For me to provide any modicum of help, I need to understand the business situation a lot better than I do now.
Click to expand...
Hi there, thanks for the response.

I will try my best to clarify.

There is a 5-year commercial lease in place. At the end of that lease, as part of an exit process, there would be 'termination fees', as I've tried to name them. e.g. legal fees to tie off the exit process, dilapidation fees and costs
The cost of rent and property costs for those remaining 5 years is provisioned for, as are the forecasted termination fees.

To break that lease and to secure an early termination, a surrender premium can be paid. That is an up-front payment to the landlord to, alongside associated legal fees to arrange the agreement.

So I am using the IRR function to calculate the cost of early surrender (the premium + fees) against the full provision (5 year's of rent + termination fees).

You're correct in suggesting that the surrender fees (the legal cost) of an early termination are the same legal fees booked in with the termination fees at the end of Year 5.

Ben.
 
OK. That helps me a lot. I am still somewhat confused. You keep talking about "provisioning," which sounds like accounting entries. An IRR is concerned solely with expenditures and receipts rather than accruals and amortization of accruals. For example, you distinguish between the early termination fee and the legal fees for drafting the early termination agreement. In terms of an IRR analysis, we add them together if they are paid at the same time.

But perhaps I am misinterpreting the word. Are you saying by "provisioning" that the rent is not paid in installments over the course of the lease?

Based on US practice, this is how I am interpreting what you have said.

At the start of the first month, three payments are due, one being for the first month's rent and the other being payment for the option to terminate early. (I am using the terms that would be used in the US so we may have need to coordinate terminology.) For the IRR analysis, we lump all three payments together. Then in succeeding months there would be regular rental payments made until the beginning of the 60th month, when two payments would be made, one for the final month's rent and another for the termination fee. Again, these payments would be added together for an IRR analysis.

Am I close to understanding the business situation now?

Now you started by asking whether an IRR analysis is a good way to think about the cost of the option to terminate early. I do not think it is for two different reasons.

First, you are not comparing two things that are strictly comparable. In the one case, you get the use of certain space for five years. In the other you do not, but presumably you would incur some cost for alternative space (unless you are thinking about closing up shop entirely). So to make the comparison valid, you would need to put that alternative cost in. Obviously that would be an estimate, and IRR analysis can be very sensitive to errors in estimates.

Second, the whole analysis is certain to be very sensitive to when the option is exercised. Imagine for a moment that you exercise the option at the start of the 59th month. You will have paid the entire termination fee in order to save one month's rent, and in terms of present value it is even more absurd because you pay the termination fee up front but do not receive the benefit for almost five years.

Therefore, if you do do an IRR analysis, you will need to run multiple versions with different estimates of the cost of alternative space and different estimates of time of termination. And the result you get will be a set interest rates, none of which have any intuitive meaning in this context.

Here is a suggestion that involves fewer estimates and may be more understandable to everyone.

Calculate four scenarios: one assuming that you terminate at the end of year 1, another at the end of year 2, and year 3, and year 4. What percentage reduction in rent would you need to achieve to make the present value of the savings over the remaining term equal to the present value of the termination option costs. That, is the analysis would say something like a 6% reduction over 4 years will economically justify the termination fee, a 14% reduction over 3 years will do so as well, a 30% reduction ...

The percentages will rise rapidly for two reasons. The later you you exercise the option to terminate, the longer you have to wait to get any benefit and the shorter the time over which you will receive the benefit.

Does this make sense to you?
 
Thanks for taking time to work through this. I appreciate your help!

The IRR is calculating the cost of the termination from a specific date, versus the cost of the total rent, by financial year, until the end of the lease at the end of Year 5.

The specific date is the date of the termination deal taking effect. So let's say the annual rent is £100,000 on a lease through to 2025 and as of 15th June 2020, we are exactly half way through the financial year. To make things simple, let's say today is also the agreed termination date to surrender the lease.

The surrender deal is £200,000 and the legal fees are £10,000.

Total cost to exit the lease is £210,000 as of today. That surrender is inclusive of any dilapidation fees and costs.

Total remaining rent is:
Year 0-1: £50,000
Year 1-2: £100,000
Year 2-3: £100,000
Year 3-4: £100,000
Year 4-5: £100,000

Other fees at the end of the lease include:
Dilapidation costs: £25,000
Dilapidation fees: £5,000
Legal Fees: £10,000


Therefore, the IRR calculation would be:

IRR(-210000,490000)

In terms of 'provision', which you asked about. An accounting exception has been created to allow for the total provision of closure of the business, taking into consideration all lease rent and fees until the end of the lease. So, the IRR is a way of measuring the % value worth of doing the surrender deal (paying £210,000 up front now) against the total cost of holding the premises to lease expiry.


I hope this gives a better insight? And thanks once again!

B.
 
Ahh the termination fee is paid when the termination option is exercised, not at the inception of the lease. I had not understood that.

You understand that the result of the IRR calculation is simply the interest rate that makes the present value of the different cash flows equal zero. Now technically you are doing the IRR calculation wrong. The 490,000 is spread over time. It is not a lump sum. To be really exact, you should do it by month. I did it by half year with your numbers. The result is just over 19.9% per semi-annual period, or just about 43.76% annually with semi-annual compounding. It would be somewhat higher if you do it by month (remember that then the interest rate you get is a monthly rate). I still do not see the utility of that number. I do not think it has any economic relevance unless you can actually realize interest rates close to that level.

It makes a great deal more sense to me to estimate your cost of capital. Say 10% per annum, but if that is not reasonable in your case, pick something that is. The present value of the costs (calculated on a semi-annual basis with an interest rate of 5% per half year) of not terminating is approximately 381,175 so the present value of the savings from exercising the option after 6 months is approximately 171,175, which is a number that will make sense for people.

Moreover, if as I suspect you are doing, you want to book an asset when you issue the check for 210000, that makes perfect sense. Of course, you would have to amortize it over time as an expense because it will be worthless if never exercised. And of course you would have to expense it if you do exercise it because that is when you realize the benefit.
 
I should have attached the example. I'm not treating the £490,000 as a lump sum. It's presented as a cash flow series. See attached file (I've attached a .pdf file as, for some reason, you can't upload an Excel document onto here? How odd!)

The IRR function is set as: IRR(D3:J3)
Does this make more sense?
 

Attachments

  • IRR Example.pdf
    103.2 KB · Views: 3
There is either a 4 or 5 hour time difference between us . I'll be free in about an hour if that is not too late for you.
 
I am free now (until my wife dreams up something new for me to do).

The first thing I am going to do is check your computation.

EDIT Oh and if you give me your email address via pm we can exchange spreadsheets
 
Your computation is incorrect if you are thinking that the final payment due is 90,000, 50,000 in rent and 40,000 in termination costs. The periods must have the same length. Furthermore, the aggregate cash flow for a period must be in a single cell to use the IRR function in excel. So, always assuming that I have the timing right, you should come up with an annual rate of 40.2%.

I still have no idea what the business meaning of that number will be. Assuming that you have no need for any space, it will always be to your advantage to exercise the option. The price for the option is a sunk cost. The choice is between paying rent for space you do not need versus not paying rent for space you do not need. It is that simple once you are certain that you no longer need any space.

EDIT: It occurs to me that you may still be considering the value of buying the option, in which case it is not a sunk cost. In that case, I go back to my earlier idea of figuring out the net present value assuming different times at which the option is exercised.
 
It is 4 in the morning here. The dog decided to chase deer, and my wife wants me to wait up for the dog. I am going to go back to bed as soon as the stupid beast returns, but I did send you an email with an attached spreadsheet and explanatory text. If you do not see it, look in your spam folder for something titled spreadsheet from gauss27122@aol.com.
 
You must log in or register to reply here.
Top