IRR in 'reverse'

I actually made an example for someone yesterday for a question like this. I added an example for non yearly payments as you have. I am not positive of the day count convention that Excel uses but this is what the numbers should be regardless. You can check it by changing the new IRR to match the old IRR.

By definition, the IRR is just the name for the interest rate that makes the PV of all payments = 0.

Originally Posted by Catflappo

A1 1,000,000
A2 -1,100,000

B1 1 Jan 2014
B2 30 June 2014

To calculate the IRR of this, I use =XIRR(A1:A2,B1:B2) and obtain the answer of 21.32%

If you have just two cash flows, you do not need to use XIRR. Ostensibly, the RATE function should suffice, to wit:

=(1+RATE(B2-B1,0,A1,A2))^365-1

In this case, that works. But RATE sometimes fails (returns #NUM) especially when calculating a daily rate, as we are doing here. An reliable alternative is:

=(-A2/A1)^(365/(B2-B1))-1

Note: In all of these cases, the result is an annual compounded rate based on compounding daily. In some regions, notably the US, that might not be the same as the annual interest rate.

Originally Posted by Catflappo

Let's now introduce another cell
C1 15%

What I would like to do, please anyone, is ask "what would cell A2 need to be in order the make the XIRR equation 'correct' for cell C1.

=FV((1+C1)^(1/365)-1,B2-B1,0,A1)

Again, that assumes that C1 is an annual compounded rate, which is not always the same as an annual interest rate.

Note: You might need to change the format of the cell with that formula. Sometimes, Excel insists on setting the format to Currency.[/QUOTE]

	A	B	C
1	Target	20%
2
3	Flows	Check
4	$ 1,000,000	20%	B4: =IRR(A4:A7)
5	$ 100,000
6	$ (30,000)
7	$ (1,836,000)		A7: =-NPV(B1, A4:A6) * (1+B1) ^ (ROWS(A4:A6)+1)

Originally Posted by Catflappo

Sorry - I should have clarified.... I DO have occasions when there are more than one 'pair' of cashflows. In other words I could have something like:
1,000,000
100,000
-30,000
-1,250,000
(all for different dates).

Let's ignore dates, and just say for arguments sake the IRR of the above is 15%.

We cannot arbitrarily remove dates or cash flow frequency. It begs the question of how to interpret the given IRR.

The IRR is timing-sensitive by definition. If you remove cash flow timing altogether, we must assume the cash flows are regularly spaced, and the given IRR is a periodic rate; i.e. a discount rate applied to each cash flow period.

But in that case, the periodic IRR of the above cash flows is not 15%. Instead, it is about 5.39%; specifically, it is =IRR(A1:A4).

Originally Posted by Catflappo

I am looking for a formula, if one exists, which says "what would the -1,250,000 need to be to 'make' the IRR equate to (say) 20%" ?

An easier way to state the problem is: what is the "future value" (FV) or last cash flow, given the earlier cash flows and an IRR of 20%.

It is actually easier to calculate with dates.

So let's assume the corresponding dates are in B1:B4: 31 Dec 1013, 30 Jun 2014, 31 Dec 2014, and 30 Jun 2015.

The (X)IRR is the rate that causes Sigma(CF[i]/(1+irr)^((d[i]-d0)/365), i=0,...,n-1) to be zero for n cash flows labeled CF[0], CF[1], up to CF[n-1], and d0 is the date corresponding to CF[0]. See the XIRR help page.

After some algebraically manipulation, you want CF[n-1] such that:

Please Login or Register  to view this content.

In Excel, assuming R1 contains the given IRR, which we assume is an annual compounded rate:

Please Login or Register  to view this content.

As a double-check, with that formula in A4, note that =XIRR(A1:A4,B1:B4) returns about 20% (19.9999994039536%).

If the given IRR is positive, the Excel formula can be simplified to:

Please Login or Register  to view this content.

Due to a defect (IMHO), XNPV does not work when the IRR is negative. So I prefer to use the SUMPRODUCT formula.

Thanks for your reply, and your suggested formula. It doesn't come up with the 'right' answer yet - I suspect because there is no reference to dates in it, which of course will be critical in coming up wwith the correct answer.

To save any confusion, I am uploading an example.

If you are able to populate cell J16 with the 'right formula' I'd be a very happy man!

Thanks for your help,

Ed

(Sorry - I see there is another reply posted after I wrote this - but hopefully that means there is more than one person able to help with my cell J16!). Thanks!

Originally Posted by seanpeace

Can you do this same IRR in reverse but I need to know how to calculate the first value B13.

To be clear, I have the cash flows of future payments and dates, and then I need to put in the target XIRR and have it give me the initial NPV of those future cash flows. The future cash flows have to have dates and different amounts.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread