Reverse IRR calculations, sorting data formulas, faster goal seek options

Please help me solve the following problems. This was for a job interview i had. I ran out of time and did not get the position. Now i am trying to educate myself so i can do better in the future. I really appreciate your help here. Thank you.
1.

ermtud.jpg
----------------------------------------------------------------------

2.

Determine year 10 cash flows to achieve desired IRR:

xnswzr.jpg

15qd7kp.jpg

This is for 20+ references so goal seek will not work. I need a formula that can be copied over and over and not take forever to adjust manually

----------------------------------------------------------------------
3.
35m0bxj.jpg

------------------------------------------------------------
4.
35civk6.jpg

dude111 welcome to the forum

Before we start offering help, can you tell.show us what you have tried so far - what worked, and what didnt (and do you know why it didnt?)

You said you wanted to learn, and we will be more than happy to help in that regard - but we will not do all the work for you

So, lets see how far you get/got with this on your own?

Originally Posted by FDibbins

dude111 welcome to the forum

Before we start offering help, can you tell.show us what you have tried so far - what worked, and what didnt (and do you know why it didnt?)

You said you wanted to learn, and we will be more than happy to help in that regard - but we will not do all the work for you

So, lets see how far you get/got with this on your own?

Dont respond to my post if your not offering to help. You clearly dont know how to do this either since your asking me what worked and what didnt. What a joke. [Offensive comment removed by moderator] and stay away from my post.

As a brand new member to our site, you may not have noticed, but I am an administrator here and I would ask that you keep your responses to a courteous level. Whether I do or dont know how to answer the questions you failed at, is not the point, we do not do homework questions, but we will assist you help improve yours skills.

So, I ask again, what have you tried and what did not work for you?

Originally Posted by dude111

This was for a job interview i had. I ran out of time and did not get the position. Now i am trying to educate myself so i can do better in the future.

That's a good objective. However, since this was posted on Sat and updated later on Sat, I'm relunctant to offer turnkey solutions for fear that you might still be able to use them for an interview on Mon or thereabouts, notwithstanding your claim that it is too late.

So perhaps the following will point you in the right directions; and at the very least, enough time will eventually pass that I would be willing to answer any of your follow-up questions with turnkey solutions.

For future note, I think it would be better to post each question in a separate thread, because each one might result in a protracted discussion.

Originally Posted by dude111

1. Attachment 506544

The interview question seems ambiguous to me, insofar as what it is looking for in an answer.

First, the question asks for a "dynamic formula". If they mean a formula with "dynamic references", a Google search would reveal that typically that refers to range references derived using INDIRECT, OFFSET or INDEX.

In real life, I would avoid using any of those, opting instead to use range references that are long enough to cover any reasonable growth. The operative word is "reasonable". Instead of using a column reference like A:A, which usually impacts workbook performance adversely, I would use A2:A100 or A2:A1000 or whatever is reasonable.

But in situations where I might want a truly "dynamic" reference, I would try to use INDEX. Although INDIRECT and OFFSET are easier to use and read, they are "volatile" functions. Consequently, their usage causes unnecessary recalculations every time the workbook is edited, often resulting in poor editing performance and sometimes "non-responsive" Excel windows.

In any case, it is unclear to me whether you are expected to use "dynamic references" for all of the questions you label #1-4. Such references seem unnecessary in some cases.

Arguably, this issue might be moot if the interview questions assume Excel 2007 or later, and they expect you to use "structured references" for table objects. But that would seem at odds with the "dragging" caveat. So I assume that is not the case.

Finally, for the average cost of units for each person, you should know that the average would be, for example:

(sum of total costs for Brian) / (sum of units for Brian)

Look at the SUMIF help page.

Originally Posted by dude111

2. Determine year 10 cash flows to achieve desired IRR:
Attachment 506546
Attachment 506547

This interview question is interesting (to me) because the two answers offered in the second image are incorrect(!).

The difference is less than 0.40 (40 cents). See footnote [1] for details.

The IRR is the rate that causes the NPV to be zero or nearly so. See the NPV help page for the mathematical formula.

Algebraically, solve for the last term in the formula, namely CF[10].

Use the SUMPRODUCT function to calculate mathematical Sigma. See the SUMPRODUCT help page. I would use COLUMN in an expression to derive index "i". See the COLUMN help page.

Notational note: I (and most of the world) label the first cash flow CF[0], not CF[1] as it is in the NPV help page. So the Sigma is over i=0,...,10, not i=1,...,11. That fits the data in the interview question, as well.

But when we use the Excel NPV function, it is important to note that the first term uses (1+rate)^1, not (1+rate)^0. Mathematically, it makes no difference when "rate" is the IRR.

For this question, the form of the cell references is critical to allowing us to copy the formulas down and across the table of what-if IRRs. We need to use the correct "mixed" cell references like $B2:$K2 and L$1, M$1, etc appropriately.

Originally Posted by dude111

3. Attachment 506542

This interview questions raises a number of spreadsheet design issues, which are unclear to me.

For example, is the data in the Duration and Start columns literally "3 Periods" and "Period 2"?

For a simpler design, I would assume that they are simply the number 3 and 2, using a Custom format to add the suffix " Periods" and prefix "Period ". That applies to the column titles in the Fees Per Period table as well.

Thus, the major challenge of this question seems to be getting the type of cell reference correct so that formulas can be copied without manual edits; that is, absolute, relative and "mixed" references like $A$2, A2, $A2 and A$2. And it might be best to use named references, at least in some cases.

I'm afraid I cannot say more without giving turnkey answers to what I consider to be a key aspect of this particular interview question.

The design for this question would benefit significantly from using table objects and structured references. But I had "ruled that out" in my thinking about question #1 above.

The key to the arithmetic in the Fee Per Period table might be a nested IF() expression that implements the following logic:

If Period = Start, the fee is 25% of 3.5% of Capitalization,
Else if Period = Start+1, the fee is 10% of 3.5% of Capitalization,
Else if Period >= Start and Period < Start+Duration, the fee is ((1 - 25% - 10%) of 3.5% of Capitalization) divided by Duration-2,
Else display the null string ("")

where 3.5%, 10% and 25% should be replaced by cell references.

That is probably more than I should give you. Then again, I omitted details that might be needed to address potential issues, namely: rounding; and ensuring that the sum across the periods is exactly 3.5% of Capitalization (rounded?). Again, the interview question is unclear on these points, IMHO.

Originally Posted by dude111

4. Attachment 506543

Perhaps the point of this interview question is for you to realize that when there are only two cash flows, a PV and a FV, the IRR is simply the CAGR.

Google CAGR to see how to calculate it. Note that for this calculation, we use unsigned cash flows.

Hope that helps. Post follow-ups with specific questions. But I might delay any response until I'm comfortable that they cannot benefit your interview. (Others might be more forthcoming sooner.)


-----
[1] Re: the example answers for question #2 (10th year cash flow for 12.3% IRR). The difference is just 0.04 and 0.36 for the two examples. Originally, I thought they might have used Goal Seek or Solver, even though you cannot. But even with default precision, they give the same results as the exact formula, rounded to the cent. There is always the possibility of typos in the data, be it mine, yours or in the image. But I have checked mine multiple times. Finally, there might be rounding errors in the presentation (displayed values), be it yours or in the image. But the differences seem too small. For the first example (343.17), the magnitude of one cash flow would need to change by at most 0.03, depending on the CF. For the second example (580, which I interpret as 580.00), the magnitude of one cash flow would need to change by 0.11 to 0.32. Neither seems likely. On the other hand, it does seem unlikely that the 10th cash flow is "exactly" 580.00, rounded to the cent. Finally, there is no consistent presentation error for the 12.3% IRR. The magnitude would need to vary between 0.0006% and 0.0028%, which is not feasible. Of course, any combination of these deviations might apply. In any case, the difference between the example and actual answers is small. I just want to forewarn you that there might be seemingly inexplicable differences. "Close enough for government work".