Reverse IRR Calculation? (Determine annual cash flow to meet target IRR)

[spaniard25]

Hello,

I have what seems to be a fairly straightforward problem, but I have searched the forums and found limited information on the topic. The problem:

I need to do the reverse of the function =IRR(), which is to spit out the annual cash flow required to meet a target IRR. The initial capital outlay is provided, and the cash flows are 15 constant, annual payments. For a given IRR target (24% in this case), I need to determine what cash flow is necessary (15 periodic annual payments) to hit that target.

Is this possible? Currently I am manually using goal seek. Is a goal seek macro my only option?

Thanks,

spaniard25

 

[MrBeast]

Hi all, was hoping someone could help me with a similar question.

I would like to reverse the IRR function as mentioned above except in a slightly different way.

If I invest $50 today (so an outlay of $50 today) and want to generate 10% IRR in the future after 36 months with one payment back in month 36 (so an inflow at month 36 to get to the target IRR, no intermediate cashflows), how can I solve for what the value needs to be in month 36 to generate the 10% IRR?

I know I can use goalseek to solve for this (the answer is $63.48 using XIRR to account for the 36 month period) but how can I solve this with a formula?

Thank you!

Matt

 

[joeu2004]

If I invest $50 today (so an outlay of $50 today) and want to generate 10% IRR in the future after 36 months with one payment back in month 36 (so an inflow at month 36 to get to the target IRR, no intermediate cashflows), how can I solve for what the value needs to be in month 36 to generate the 10% IRR?

I know I can use goalseek to solve for this (the answer is $63.48 using XIRR to account for the 36 month period)

I get about 66.49 (66.4893309528804) using XIRR and Goal Seek (XL2007). But XIRR returns about 9.97% (9.96656358242035%).

(Differences are due to the estimation limitations of the Goal Seek algorithm.)

If you want a result similar to XIRR, use:

=(1+10%)^((EDATE(TODAY(),36)-TODAY())/365)*50

That returns 66.55.

Alternatively:

=(1+10%)^(36/12)*50
or
=((10%/12)+1)^36*50

The last formula returns about 67.41 (67.4090921209413). It would be used when the intent is to calculate a simple annual interest rate; that is, divide by 12 to derive a monthly rate.

 

[MrBeast]

Duh - of course! Thanks so much

Really, really appreciate it.

 

[vanessa10209]

	A​	B​	C​
1​	target IRR​	24%​	B1: Input
2​	payment​	$249.92 ​	B2: =PMT(24%, 15, B3)
3​	initial outlay​	($ 1,000.00)​	B3: Input
4​	1​	$ 249.92​	B4 and down: =$B$2
5​	2​	$ 249.92​
6​	3​	$ 249.92​
7​	4​	$ 249.92​
8​	5​	$ 249.92​
9​	6​	$ 249.92​
10​	7​	$ 249.92​
11​	8​	$ 249.92​
12​	9​	$ 249.92​
13​	10​	$ 249.92​
14​	11​	$ 249.92​
15​	12​	$ 249.92​
16​	13​	$ 249.92​
17​	14​	$ 249.92​
18​	15​	$ 249.92​
19​	check​	24.00%​	B19 and down: =IRR(B3:B18)

But what is you do not know the "initial outlay"?

 

[joeu2004]

[Quoting SHG's PMT solution to the original question, to wit: 15 equal payments at 24% per payment period]
But what is you do not know the "initial outlay"?

Then there are an "infinite" number of solutions, for example =PMT(24%,15,-RANDBETWEEN(4,4001294)), which generates equal payments between 1 and 1,000,000, an arbitrary limitation.

(Okay, that's only 4,001,291 solutions. Smile.)

I suspect I misinterpreted your question, but only because it is improperly formed.

If you mean: ``how do we determine the initial outlay, given N1 equal payments of P1 at a periodic rate of R1?``, you might write:

=PV(R1, N1, -P1)

If that is not what you mean, I suggest that you post your question in a new thread, providing all the terms of the problem, to wit: the number of payments, the amount of equal payments (or the series of payments), the periodic rate (or the series of rates), the future value (assumed to be zero in the original question here), and when payments are made (beginning or end of all periods, or the series of dates).