IRR Lookback and Solver

[Scott M]

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.

 

[Mike Middleton]

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]

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

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

 

[Dana DeLouis]

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>>

 

[Dana DeLouis]

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]

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>

 

[dickgee]

Dana: I really liked your approach to the lookback IRR. For my situation, I need to use monthly cash flows. I've tried to set you K factor to a monthly rate but didn't get the desired result - any suggestions?

Thanks, Dick

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>

 

[dickgee]

Dana

I really like your solution to the lookback IRR. I need to use monthly cash flow for my particular situation. I've tried using a monthly interest rate for your K factor but don't get the desired result - any suggestions?

Thanks, Dick