Solved

Formula for Reverse IRR

Posted on 2011-03-09
38
3,137 Views
1 Endorsement
Last Modified: 2016-01-01
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.



1
Comment
Question by:marylander123
  • 21
  • 15
38 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 35086068
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.
0
 

Author Comment

by:marylander123
ID: 35086295
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.  
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35086680
marylander123,

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

Thanks,
Brian.
0
 

Author Comment

by:marylander123
ID: 35086806
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35087022
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
0
 

Author Comment

by:marylander123
ID: 35087063
Try this.  Thanks.
Example-File.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35087134
marylander123,

Thanks, I'm going through it now.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35087647
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.

0
 

Author Comment

by:marylander123
ID: 35094648
Exactly.  To simplify things, I'm trying to find a way to reverse the IRR formula.  Is this possible?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35095252
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.
0
 

Author Comment

by:marylander123
ID: 35095405
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35095656
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.
0
 

Author Comment

by:marylander123
ID: 35095718
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?  
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35095952
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35096130
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.
0
 

Author Comment

by:marylander123
ID: 35096222
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35096637
marylander123,

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

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35097127
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:redmondb
ID: 35098181
marylander123,

I'm back and picking it up again.

Regards,
Brian
0
 

Author Comment

by:marylander123
ID: 35098273
Great.  Thanks for the help.  I'm pretty new to this site.  You're not employed by the website, right?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35098723
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35099519
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
0
 

Author Comment

by:marylander123
ID: 35099635
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?  
0
 

Author Comment

by:marylander123
ID: 35099716
So if I needed to add another income line item, I would included it in the NPV of the "Gross Expenditure" figures?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35100035
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.
0
 

Author Comment

by:marylander123
ID: 35108788
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.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 35129607
marylander123,

Hi, I'm back.

Regards,
Brian.
0
 

Author Comment

by:marylander123
ID: 35333350
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35337521
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.
0
 

Author Comment

by:marylander123
ID: 35338413
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35345225
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.
0
 

Author Comment

by:marylander123
ID: 35345273
Great.  Should I open up another question?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35345586
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.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35347494
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.
0
 

Author Comment

by:marylander123
ID: 35383499
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?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35387516
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.
0
 

Expert Comment

by:jgreenlee01
ID: 41392669
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now