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.



Microsoft Excel

Avatar of undefined
Last Comment
jgreenlee01

8/22/2022 - Mon
redmondb

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.
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.  
redmondb

marylander123,

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

Thanks,
Brian.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
marylander123

ASKER
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
redmondb

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
marylander123

ASKER
Try this.  Thanks.
Example-File.xlsx
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
redmondb

marylander123,

Thanks, I'm going through it now.

Regards,
Brian.
redmondb

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.

marylander123

ASKER
Exactly.  To simplify things, I'm trying to find a way to reverse the IRR formula.  Is this possible?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
redmondb

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.
marylander123

ASKER
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?
redmondb

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marylander123

ASKER
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?  
redmondb

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
redmondb

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
marylander123

ASKER
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.
redmondb

marylander123,

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

Regards,
Brian.
redmondb

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
redmondb

marylander123,

I'm back and picking it up again.

Regards,
Brian
marylander123

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

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
redmondb

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
marylander123

ASKER
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?  
marylander123

ASKER
So if I needed to add another income line item, I would included it in the NPV of the "Gross Expenditure" figures?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
redmondb

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.
marylander123

ASKER
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
redmondb

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marylander123

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
redmondb

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.
marylander123

ASKER
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.
redmondb

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marylander123

ASKER
Great.  Should I open up another question?
redmondb

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.
redmondb

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
marylander123

ASKER
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?
redmondb

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.
jgreenlee01

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.