IRR Help

[questionking]

I am getting different returns when calculating for IRR. The far right is the "desired" ending return that should be populating in the IRR column as well (I pasted these numbers in the desired column and am using the formula below for the IRR column). I was able to get the first year to match up but the second and third year are returning different results than what it is supposed to be. Thanks in advance for your help.

=-(((F4+H4)/$F$3)+1)

 

[BenMiller]

Being that you don't have columns in your picture its hard to know what you want. Where is cell F3?

 

[questionking]

Being that you don't have columns in your picture its hard to know what you want. Where is cell F3?

Sorry about that, column F is Cash Flow and F3 is (55,860).

 

[BenMiller]

That's what I figured, but plugging in your formula, you want -(((5328.49+49173.50)/55860)+1), which should equal -1.97568904. How are you expecting -2.43%?

 

[questionking]

I'm thinking they subtracted it from 100 percent somehow. 100-97.568... would get them to the 2.43% that was provided. That's how I suppose they calculated their return for year 1 anyway. I just took the formula and multiplied it by -1 and it came out to that -2.43%. (((F4+H4)/$F$3)+1)*-1

I still don't know/can't figure out how they got Years 2 and 3 projections though.

 

[henrik2h]

For year 2
Cash outlay: -55860
Annual cash flow year 1: 5328
Cash flow year 2: 5588 + 51894
Calculate IRR with above

 

[questionking]

For year 2
Cash outlay: -55860
Annual cash flow year 1: 5328
Cash flow year 2: 5588 + 51894
Calculate IRR with above

This got 12.44% for year 2. If I divide it by the year though it gets much closer to the desired rate. Thanks.

Any clue as to why their returns would level off? Their returns leveled off at around 14% in years 10-30. Conversely the new formula with the dividing by years continues to rise.

 

[henrik2h]

I don't really understand what you mean by "this got 12.44% for year 2". If calculating the IRR with {-55860;+5328;+57482} you get 6.32% as stated in the table.

I don't have any info on what the figures represent but I would reckon that in the early years IRR is much dependant on what you get when it is sold ("Cash to Receive"), whereas after many years it is the annual cash flows that matter the most, hence the levelling out.

 

[joeu2004]

This got 12.44% for year 2. [....] Any clue as to why their [....] returns leveled off at around 14% in years 10-30.

There are many ways to measure a "rate of return".

Your formula in column J is not how to calculate the IRR per se.

Instead, you are calculating the simple rate of return over n years, n=1, 2, 3, etc. And even then, I believe that you are calculating that incorrectly. See below.

The values in column K can be calculated using the Excel IRR function.

The IRR is the discount rate that sets the NPV to zero. See the Excel NPV support page for the mathematical formula.

-----

First, I believe the formula in column I (I call it "net value") should be:

I4: =F4+H4
Copy I4 down the column

That is, it is the accumulated equity (H4) plus the net cash flow (F4), which is assumed to be added at the end of the year.

(I am taking the values in column H for granted. It is unclear if and how they are calculated. Notably, it is unclear if and how they relate to values in column G (COCR).

-----

Then formula in column K is:

K4: =IRR((F3,I4))
K5: =IRR(($F$3:F4,I5))
Copy K5 down the column, formatted as Percentage.

That is, the IRR cash flows are: the initial outlay (F3), followed by n-2 net cash flows(0 for row 4; 1 for row 5; 2 for row 6; etc), followed by the net value (I4).

Note the syntax of the Excel IRR formulas; that is, the parentheses around the list of ranges. The Excel IRR function is one of very functions that permits that form of a "range union".

-----

I believe the formula in column J should be:

J4: =I4/(-$F$3) - 1
Copy J4 down the column, formatted as Percentage

That is mathematically equivalent to -I4/$F$3 - 1 , if you prefer.

The results are -2.43% (-2.43109559613319%) in J4, 2.90% (2.90298961689939%) in J5, 8.43% (0.0842583243823845) in J6, etc.

-----

If you have further questions, please post all data and formulas, ideally in a table generated by the new XL2BB application.

Alternatively, upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files. Caveat: Some contributors object because they cannot or will not download Excel files.

 

[questionking]

I don't really understand what you mean by "this got 12.44% for year 2". If calculating the IRR with {-55860;+5328;+57482} you get 6.32% as stated in the table.

I don't have any info on what the figures represent but I would reckon that in the early years IRR is much dependant on what you get when it is sold ("Cash to Receive"), whereas after many years it is the annual cash flows that matter the most, hence the levelling out.

I used the formula I had before that was getting 12.44% but it sounds like my formula setup was incorrect. Thank you for the help!