Calculating a cash outflow based on a set IRR

I have a series of cash flows such as:

``````-125   100   100   100   100
``````

I want to figure out the outflows that would get to a 25% IRR. For this example, it would be:

``````-125   100   70
``````

to get to a 25% IRR, with 30 left over in year 2.

Is there a formula that I could use to determine what the necessary cash flow is for each year would be to get to a 25% IRR, without going over? If the cash flow stream changes, it could be year 3 or year 4 that has leftover.

i.e. in the above example, is there a formula I can use to determine what the cash flow should be in a given year to hit a 25% IRR? The year it happens will change, and the cash flow itself will be different depending on the cash flow stream.

• are you open to a method using formulas and built in functions? – Forward Ed May 16 '19 at 18:00
• Yes, I'm open to any solution that works – user3628240 May 16 '19 at 21:33

I'll make this a different answer because based on your comment, it's a different problem from the other answer.

All of this is based on compound interest. Say you have \$100 today and can invest it for one year at 25% interest. At the end of the year, it will be worth \$125. The future value at the end of any year is the present value x 1.25^Yr. The present is year 0. The end of one year is year 1.

Working backwards, the amount in one year (\$125) is worth 1/1.25 of that amount today (\$100, its present value).

In a simple case, where you have an initial cash flow in one direction, then a series of cash flows in the other direction, the IRR is the equivalent interest rate at which the present value of the future cash flows exactly equals (cancels out) the initial cash flow, so the present value of everything is zero. That's the basis for solving what you described.

• In this chart, the first row is the year (year 0 is the present).
• The second row is the cash flows from your example. Note that they don't need to all be equal.
• Row 3 illustrates the basis for the interest factor described above. Row 4 shows the actual result of that calculation. That's your compound interest factors.
• Row 5 shows the inverse to convert future values back to the equivalent present values.
• Row 6 applies the row 5 factors to your cash flows to put everything on a present value basis.
• Row 7 shows the cumulative results as you start adding the present values for each year. At 25% IRR, that cumulative sum will be zero. Notice that the cumulative passes zero and goes positive in year 2, so the year 2 cash flow must be less than the \$100 original amount and must be the last year.
• At the end of year 1, the cumulative present value stood at -45. Since the total must equal zero, the present value in year 2 must be 45, which is shown in row 8.
• In row 9, the 45 is converted back to its future value by multiplying it by the interest factor in row 4. So the result is that year 2 is the last year, and the year 2 cash flow must be 70.31 in order to achieve a 25% IRR.

All of this detail was just to illustrate how the calculation works. You can consolidate that down to a couple of rows for a working version.

This solution assumes you are looking at equal payments from a certain point onward. Populate column A with value you know and then after the last value you know point it at C3 and make it an absolute reference as follows:

``````A4
=\$C\$3
``````

Copy \$A\$4 down as far as you deem necessary to cover your maximum number of future deposits for your scenario.

In C3 place your anticipated amount of regular future deposits.

In D3 place the number of the deposit you want to evaluate to.

In E3 we will place a formula that will calculate the IRR based on the number of entries you pick. Use the built in Excel formula for IRR as follows:

``````=IRR(A3:INDEX(A:A,ROW(\$A\$3)+D3-1))
``````

Now switch your ribbon to the DATA ribbon and look for the What if Analysis pull down in the DATA TOOLS section:

From the pull down menu select GOAL SEEK. Set the Set Cell value to E3 where the formula to calculate the IRR is located. Next place what you want the IRR value to be. In your example that would be .25 DO NOT ENTER 25. Then set the By Changing Cell value to your regular deposit amount in C3. When you are done is should look like:

Press the OK button and let it run. It may take some time. The process will change the value of C3 over and over again until it get a value in E3 equal to your target value when possible.

When its done, you can then round the value it found to suit your needs.

If I understand your question, I think there's a simple solution. It sounds like what you're describing is essentially the same as figuring out payments on a loan at a given interest rate (your IRR) and number of periods. There's a built-in function for that:

``````=PMT(Rate,NumberPayments,PresentValue)
``````

You're looking at annual cash flows (equivalent to payments in the formula), so the rate is your IRR (0.25). The number of payments is the number of years. The present value is the same as your initial value (-125; at the IRR, the total present value of the payments will equal that).

Here's an example based on 4 years:

The formula in A1:

``````=PMT(0.25,4,-125)
``````

It produces a value that rounds to 52.93. To verify the result, I laid out the cash flows in B1:B5. Cell B6 contains the IRR calculation on those cash flows, yielding your 25%.

Your examples are actually a little different than what I get from the description. It looks like they may be based on annual cash flows based on some undescribed rule, and then you want to figure out what the last year's amount needs to be to make the whole series equal 25% IRR. There's no guarantee that starting with that kind of basis can get you to 25%, so we would need more information to answer that if, indeed, that's your requirement.

• I think what you said at the end with the annual cash flows being based on an undescribed rule is more accurate. If I say that the cash flows will for sure be greater than 25% IRR during some year, would that make it possible to figure out? – user3628240 May 18 '19 at 8:54
• @user3628240, the IRR is the result of all the cash flows taken together. You can't have one year greater. If you're saying that there are arbitrary cash flows every year and prior to some point, the cumulative doesn't reach 25%, but after that point, the cash flow in the last year puts the cumulative over 25%, and you want to know how much you need to reduce that last year so the aggregate equals 25%, you can do that. I'm about to shut down for awhile, but I'll check back later. – fixer1234 May 18 '19 at 9:05