Is there a method for calculating a targeted IRR (an irr lookback)
without using Solver? I need to know what the last period's cash flow
should be to achieve a targeted IRR without using Solver to determine
the last period's cash flow.

Scott M -

Try Excel's Goal Seek feature.

Set cell: the cell containing the IRR function

To value: your targeted IRR value

By changing cell: the cell containing the last period's cash flow

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



"Scott M" wrote in message
...
Is there a method for calculating a targeted IRR (an irr lookback)
without using Solver? I need to know what the last period's cash flow
should be to achieve a targeted IRR without using Solver to determine
the last period's cash flow.

On Oct 11, 1:00*am, "Mike Middleton" wrote:
Scott M *-

Try Excel's Goal Seek feature.

Set cell: the cell containing the IRR function

To value: your targeted IRR value

By changing cell: the cell containing the last period's cash flow

- *Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

"Scott M" wrote in message

...

Is there a method for calculating a targeted IRR (an irr lookback)
without using Solver? I need to know what the last period's cash flow
should be to achieve a targeted IRR without using Solver to determine
the last period's cash flow.

I should have been more specific. I'm looking for an alternative way
of doing this without "Goal Seek", "Solver", etc.

I should have been more specific. I'm looking for an alternative way

of doing this without "Goal Seek", "Solver", etc.


I need to know what the last period's cash flow
should be to achieve a targeted IRR without using Solver


Hi. If I'm not mistaken, there is no closed-form solution to an IRR formula with more than 3 cash flows.
This is because the polynomial equation becomes greater than 4. This is why a recursive solution is used.

Here's a solution if you only have 3 cash flows...
Suppose we have:
=IRR({-5000,1000,2000,3000},10%)
8.20826354827781%

Now that we have 8.208%, we are to calculate the unknown 3,000 value. (last period to get 8.208%)

=IRR({-Vint,v1,v2,v2})
Then the equation would be...

v1*(r+1)^3+v2*(r+1)^2+v3*(r+1) = (r+1)^4*Vint

This small example already has a 4th deg. Poly. Anymore cash flows and it becomes difficult to solve closed-form.
You want to solve for v3, the last payment.
It's a real mess, but it reduces to the following. I'll use vba for checking...

'Option Explicit
Sub Demo()
Vint = 5000
v1 = 1000
v2 = 2000
'v3 = ???
r = 0.08208263548341
Debug.Print (1 + r) ^ 3 * Vint - (1 + r) * (v1 + r * v1 + v2)
End Sub

Returns 3,000 for the last payment using the given 8.208% Rate.

Again, with more cash flows, I don't believe there is a closed-form solution.
--
HTH :)
Dana DeLouis


"Scott M" wrote in message ...
On Oct 11, 1:00 am, "Mike Middleton" wrote:

Scott M -

Try Excel's Goal Seek feature.

Set cell: the cell containing the IRR function

To value: your targeted IRR value

By changing cell: the cell containing the last period's cash flow

- Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

"Scott M" wrote in message

...


Is there a method for calculating a targeted IRR (an irr lookback)
without using Solver? I need to know what the last period's cash flow
should be to achieve a targeted IRR without using Solver to determine
the last period's cash flow.


I should have been more specific. I'm looking for an alternative way
of doing this without "Goal Seek", "Solver", etc.

Hi. If I'm not mistaken, there is no closed-form solution to an IRR formula with more than 3 cash flows.

Oops. My mistake. Please disregard this. This is totally incorrect!

--
Dana DeLouis

<<snip

Let me try this again.

Suppose we have the following IRR problem.

=IRR({-15000,1000,2000,3000,4000,5000,6000},0.1)
The Rate is
8.252383124190070%

Now, suppose we have this rate, and just missing the 6000.
Let's make a constant k equal to the Rate +1.
k = 1.08252383124190

Then...
=(15000*k^7-(1000*k^6+2000*k^5+3000*k^4+4000*k^3+5000*k^2))/k

Returns 6000.

Hopefully, you can see the pattern and go from there.
--
Dana DeLouis


"Dana DeLouis" wrote in message ...

Hi. If I'm not mistaken, there is no closed-form solution to an IRR formula with more than 3 cash flows.


Oops. My mistake. Please disregard this. This is totally incorrect!

--
Dana DeLouis

<<snip

Ok. I'm having a bad math day. Here's another version that's slightly easier to enter, or to make a custom vba function.

'=IRR({-15000,1000,2000,3000,4000,5000,6000},0.1)
'The Rate is
'8.252383124190070%

Sub Demo()
'// Solve for missing last payment of 6000
'// Interest Rate + 1
Const k = 1.0825238312419
Debug.Print k * (k * (k * (k * (k * (k * 15000 - 1000) - 2000) - 3000) - 4000) - 5000)
End Sub

Returns 6000

--
HTH :)
Dana DeLouis
<snip