Link to home
Start Free TrialLog in
Avatar of marylander123
marylander123

asked on

Formula for Reverse IRR

I am using @Risk to run statistics for a potential real estate transaction.  Is there a formula out there that can take a IRR input to generate a rent output.  I know you can goal seek to a specific IRR by changing the rent cell.  Since the @Risk program runs 1,000 iterations, I need a formula that automatically takes IRR as an input.  Is this possible?  I'm afraid it's not since it works out to a cyclical process.  Thanks for the help.



Avatar of redmondb
redmondb
Flag of Afghanistan image

marylander123,

Apologies if I'm misunderstanding your needs, but as you already have the IRR isn't this a straightforward calculation?

Assuming Principal is 100,000, Interest is 5% and a 20 year Period with annual payments (just to simplify) we get...

 
=PMT(0.05,20,100000,0,0)

Open in new window


.. giving the answer of 8,024.26.

Apologies if I'm missing the point completely!

Regards,
Brian.
Avatar of marylander123
marylander123

ASKER

Sorry if my initial question wasn't clear.  I'm running an IRR calculation off a 10 year cash flow.  The 10 year cash flow is based on the rent.  I can easily goal seek to a rent that will produce a specific IRR percentage.  However, I am running a macros that takes an input range and runs through the cash flow 1,000 times to produce stats such as min, max, standard deviation, etc..  Ultimately, I want to be able to input a range of IRR targets to produce an output of rents.  So far, I haven't been able to figure out how to get around the cyclical reference.  I can't goal seek each of the 1,000 iterations.  Thanks for the help.  
marylander123,

Sorry, I'm still not there. Could you post an anonymized version of your file, please?

Thanks,
Brian.
Sure.  See attached.  At the moment, the cell highlighted in green is the primary input that is running the cash flow - $35.00.  I am trying to find a way to set up the cash flow so the input is the cell highlighted in yellow - 17.66%.  Thanks.
Example-File.xlsx
marylander123,

Thanks, that's helping!

Could I ask for one other thing please? Because I don't have @Risk, the sheet is a mass of errors so could you send me another copy of the sheet but with all the formulas replaced by values (select all cells, copy, paste special values)?

Many Thanks,
Brian
Try this.  Thanks.
Example-File.xlsx
marylander123,

Thanks, I'm going through it now.

Regards,
Brian.
marylander123,

I'm not sure I'm any the wiser, but here goes...

Currently, PSF of $35 sets the cash flow which sets the IRR.

You want it the other way around so that "an" IRR will set "a" PSF. (In quotes because you'll eventually have thousands of IRR's from the @Risk run.)

(So, F80 cell is a constant while D61 is a variable, whereas you want it the other way around?)

Regards,
Brian.

Exactly.  To simplify things, I'm trying to find a way to reverse the IRR formula.  Is this possible?
marylander123,

Yes and no. The PMT formula I gave you above can take the IRR and give you a monthly rent. However, this assumes that the rent is the same every month, whereas I think you have rent increases every few year?

BTW, I see you've opened a new question for this. Could you please delete this (0 points) so I don't have to bother tracking this?

Thanks,
Brian.
Thanks.  There are two of us who share this account, and he must have posted another question.  I'll delete that one and continue with this post.  I had tried out the PMT formula previously.  The percentage would be your target IRR, period would be the year calculated, and the FV would be 0.  What would you use for the PV (principal)?  Am I missing something here?
marylander123,

Yes, Percentage is IRR.
No, Period is number of years.
PV is discounted (at IRR) cash flow.

PMT() would then give you the annual rent required to deliver the required IRR. As I indicated, this would give you the "wrong" answer as you have rent increases, but don't even think about that until we're sure we're on the same page!

Regards,
Brian.
Right.  Period is number of years, but we are calculating the IRR at the end of each FY.  I would just set a different number of years for each FY calculation.  Let's say we flatten out the rent and drop the equation in for FY1.  I'm not getting the correct rent output.  Do you mind taking a shot at it?  
marylander123,

Sure. Can you give me your details?

I hadn't appreciated that you were calculating IRR for each year. What rules are you using about the timing of receipts and expenditure?

Regards,
Brian
marylander123,

OK, dawn is breaking through...

You're effectively working out each year what the IRR would be if you cashed out that year.
You don't care about what the rent is for, say, Year 4's IRR of 9.74% - you already have that.
Instead, what you want to know is what rent would give, say, a Year 4 IRR of 9.6%, 9.7%, 9.8% etc. etc.

Have I finally got it?

Regards,
Brian.
Exactly.  I've just done a horrible job of putting that into words.  Even if I flatten out the rent over the 10 year term, the cash flow will still grow each year due to the increase in expenses and reimbursements for those expenses.  I'm trying to reverse engineer the entire cash flow.  Unfortunately, I can't simplify it anymore than what is in the previously posted example file.
marylander123,

OK, let me work on this. I'll get back to you in the next half hour.

Regards,
Brian.
marylander123,

I'm getting places, but I have to pop out for an hour. I'll let you know when I'm back.

Regards,
Brian.
marylander123,

I'm back and picking it up again.

Regards,
Brian
Great.  Thanks for the help.  I'm pretty new to this site.  You're not employed by the website, right?
marylander123,

'Fraid not!

You're wondering "why"? Primarily learning. I've haven't been doing much serious Excel for a while and I felt I was getting rusty. This is a great way of getting back into things again. (Also, I'm mean and this gives me free access to the site.)

Fingers crossed, I can now exactly reproduce the rental from the IRR+Construction+Expenditure+Final Sale. I'm going to tidy it up a bit and give you something to look at shortly.

Regards,
Brian.
marylander123,

Please read this carefully before looking at the attached - this is hugely an in-progress version!

(By the way, I'll stick with you today as long as you need, but I'm heading off for the week-end early tomorrow morning.)

(1) To make things a bit clearer, I've replaced all your formulas by values and deleted all the rows I'm not using.
(2) The bits I've added have a blue background.
(3) To play with this, just replace any of the IRR's in C15:C20.
(4) If you want more IRR's, simply copy down B20:W20. (BTW, B15:B20 doesn't actually do anything it's just heading.)
(5) The "English" behind the formula is ...
=PMT("IRR","No. of Years",-1*("Net Year 0"+NPV("IRR","Exp. Years 1 to n")+"Salvage"/(1+"IRR")^"No.of Years"))

Regards,
Brian.
Example-File-V2.xlsx
I need to head out to a meeting and don't have a lot of time to look at this today.  Maybe we can reconnect on Monday after I've had a chance to spend some time on it.  

Thanks again.  You're a lifesaver.  I don't think I would have been able to figure it out.

This might not be the best place to discuss this, but would you ever be interested in a fee based excel project?  
So if I needed to add another income line item, I would included it in the NPV of the "Gross Expenditure" figures?
marylander123,

... as a deduction, yes. So my "Gross Expenditure" line could probably be more properly called "CashFlow Excl. Rental Income"

(The IRR is the rate which gives a zero NPV. Holding the IRR constant, but removing the rent means that the NPV now becomes a large negative. The formula then just works out the installments needed to give that NPV for that IRR. BTW, my "English" should have a minus sign in front of the PMT.)

If the delay until Monday causes you problems, please feel free to close this (with all points to yourself) and open a new linking question.

Yes, I'd be happy to talk to you about a possible project - once you're satisfied with this first!

Regards,
Brian.
And we can get around the rent bump issue by programming the additional rent in the outer years into the "Gross Expenditure" line.  So far, this looks great.
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
redmondb,

Sorry for the long silence.  This project was shelved for another upcoming deadline and then vacation.  I took a look at the model this morning, and I have one question if you're still around.

Row 9 in the example file will not be a fixed number.  The "sale proceeds" are based on the total rent generated for the building, and the rent is what we are looking for.  Is this a cyclical reference we will not be able to get around?

Thanks again for all the help.
marylander123,

No worries about the delay and thanks kindly for the grade!

Can you give me your "english" around the "Sales Proceeds" formula, please? For example, what would $D$79 normally hold?

Regards,
Brian.
Assuming you meant cell D9, it would read something like this:

=(C5*130000)/.07

C5 is the rent, which is what we are trying to back in to.  "130000" in this case would be the total SF of the building.  ".07" stands in for the cap rate.  The equation is the building value equals the total rent divided by the cap rate.
marylander123,

Thanks for the info.

I'm in the middle of something at the moment, but I'll get on to this in a few hours time.

Regards,
Brian.
Great.  Should I open up another question?
marylander123,

I'm happy to continue looking at it, but it's entirely your choice (particularly if you're in a hurry)!

(If you do open a new one, don't forget to link it to this - saves you having to re-enter details and gets the experts up to speed ASAP.)

Regards,
Brian.
marylander123,

OK, I think we're slightly at cross purposes. In your files, I'm referring to the $D$58 cell (row titled "Net Sale Proceeds @ 7.0% Cap Rate"). Its formula is...
    =(1-$D$79)*(D$46/$B$58)
... where $D$79 is blank, D$46 is "Net Operating Income" and $B$58 is 7%.

The fun starts when looking at D$46. Its formula is ...
    =D16+D44+$D$45
... where D16 is "Effective Gross Revenue". It takes its value indirectly from 14 formulas and 31 constants. This is the part where I could use some "english"!

Thanks,
Brian.
D16 is the sum of the total rent generated for the property and any growth of the amount in D13 over the base year.  To make it easier, disregard or delete Rows 6-10.  I had inserted those rows to take into account any free rent.  

Does that make sense?
marylander123,

Thanks for the update. Unfortunately, I'm now heading off on a week's holiday, so it's definitely time for you to open a new (related) question.  

I'm sure that the guys will sort you sort you out, but in the unlikely event that you still have issues outstanding next week, I'll be glad to pick this up again (on the 22nd, all going well).

Apologies that I couldn't put this to bed for you,
Regards,
Brian.
Did this thread ever become resolved?  I have a similar scenario where I am trying to determine the required revenue and ultimately the rents needed to achieve a set  10 year IRR based on fluctuating construction costs.