IRR Lookback and Solver
 

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.

IRR Lookback and Solver
 

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.

IRR Lookback and Solver
 

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.

IRR Lookback and Solver
 

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.

IRR Lookback and Solver
 

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

IRR Lookback and Solver
 

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

IRR Lookback and Solver
 

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