Is it possible to back into a specific IRR, without knowing the ending proceeds? So example, we know how much the initial cash outflow is and assume x each year in returns, and want the ending proceeds to get us to a 12% IRR. Can I solve for the ending proceeds to get to a 12% IRR without goal seek (looking for something more dynamic)?
| +48 | Being asked to stay behind and train my replacement | 14 | 2d | |
| +36 | New Comp Database - Google Form (Now with Data Validation) | 24 | 1h | |
| +33 | Best CRE brokerage firms | 24 | 3h | |
| +30 | What does REPE actually do? | 12 | 5h | |
| +26 | Starting University LP Fund | 6 | 27m | |
| +24 | Public Homebuilders | 9 | 6h | |
| +22 | REPE/Development GPA | 15 | 21h | |
| +17 | MSRE/MSRED with no RE experience; Naive to think I’ll land a job afterwards? | 4 | 2d | |
| +16 | UC Berkeley MRED vs Columbia MSRED? | 2 | 4d | |
| +15 | Hospitality Finance - Perks? | 3 | 5d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
=(starting value)*(1+desired IRR%)^(number of years in hold period)... plug that sucker into your exit year cell
That ignores the cash flows in years 1-5 though
might not be following your q... thought you just needed exit proceeds?
following, we tried to figure out a formula for this a few months ago and couldn't. would be great if someone knows.
IRR is basically a guess and check calculation anyways no? Why can’t you just put the first few cash flows in excel with an IRR output and keep modifying the last cash flow until you get 12%? Would take five minutes unless I’m missing something
Correct, so I've been using goal seek as that 'guess and check'; but we have multiple hurdles for multiple asset classes we're trying to play with. So in this case, we have to use goal seek each time we want to adjust a hurdle versus having something more dynamic.
have you tried automating the goal seek process with the macro as I suggest above?
I have used a macro (via record, no programming needed0 to automate the use of goal seek (with a targetIRR - actualIRR=0 formula cell, so you can dynamically adjust the hurdle IRR as well), then it's just a button click to recalc every time you change something. It requires pressing a button somewhere on the sheet, but this method works pretty much under all circumstances I can think of.
Sure there are others, but this has no limitation I can think of.
Interesting, i'll have to give that a try. So you're saying that tIRR - actualIRR cell is basically your check
I have a cell that just tIRR-actuallIRR, then I have goal seek make it zero by changing a cell that just subtracts into the first tier hurdle (essentially siphons off the cash that should be held for the tier 2 splits).
Then I just recalc tiers 1 and 2 using the adjust line of cashflows that goalseek has now made it hit the target IRR.
This is confusing to type out, but if you are already using goalseek, your spreadsheet is probably set up correctly. You just need to do the record macro of yourself doing the goalseek. If you are typing the target IRR into the goalseek, then just create the cell with target - actual, as you are always optimizing it to zero.
Just use the solve function in Excel
Yes, it's possible.
I'm going to write out the NPV formula just so when I rearrange the terms it makes sense. Since you're trying to back into an IRR target, the NPV you're aiming for is 0.
You'll have to forgive me, WSO is not the best for formatting equations. And I'm going to be handwavy with the yearly cash flows because you've stated they're uneven. But if you can do a normal DCF you can figure out their discounted value and sum them up, right?
This is just the general formula. r is the IRR you want to target, n is the number of periods (if you have absurdly bizarre timing then the formula becomes so ridiculous you should just use goal seek.).
The funny summation in the back is just the discounted sum of cash flows in the middle years. Take the difference between that and the initial investment, multiply by your target IRR over the periods, and you should back into your ending proceeds. I assume you have the excel skills to build this out. Also my math notation may be rusty, so don't hold that against me.
You can solve using the NPV function (or XNPV and yearfrac if you have random dates).
Say you have cash flows of -100, 12, 12 in year 0,1,2, and you want to solve for year 3 proceeds to get 12% IRR. Since -100+NPV(12%, CF1,CF2)+CF3/((1.12)^3) must equal zero, CF3=(100-NPV(12%,CF1, CF2))*(1.12)^3. Which is the formula you put in the cell, in this case you get 112.
This is perfect... thank you. Knew it was simple algebra
Thank you for this, saved my life today
I might be late to the party, and may be wildly off your question.... but think I might have a simpler solution. It is a simple algebra equation. You need to find (1) the present value of the investment (whether an initial investment, or a series of investments), and (2) the present value of your operational cash flows. Then, take the difference of those two figures, and find the Future Value of that amount, in the associated disposition period. Your variables are your Targeted IRR, the investment, and the Cash Flows. I've included a screenshot below. Hope this helps.
EDIT: Think this is the same end result as @Cohle mentioned, but I still count on fingers and equations confuse me.
Enim cupiditate est est impedit neque labore eius. Fugiat magnam sunt voluptatem nihil culpa consequuntur. Aut omnis impedit veniam fuga aliquam. Dolorem veniam iure beatae eos magni id. Possimus sint delectus doloremque consequatur sed aliquid. Laborum architecto maiores tempora quidem praesentium nihil velit.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...