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)

 

[questionking]

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.

That makes sense. I knew it wasn't a true IRR but I couldn't figure out how they got their results. The IRR functions weren't getting the same results as they got so I was trying to reverse engineer it to see how they calculated their expectations to match them up in the future. Thank you so much for your help, it's all working now!

 

[joeu2004]

Any clue as to why their returns would level off? Their returns leveled off at around 14% in years 10-30.

I neglected to answer that question.

For the correct IRR calculation (my formula in column K), we are adding the present value of each net cash flow, which is calculated by CF/(1+r)^t, where "r" is the discount rate (IRR eventually) and t is the relative year number (0, 1, etc).

For a constant CF (which you do not have), we expect the PV to decline each year because 1/(1+r)^t gets smaller for a constant rate. Thus, each later net cash flow would have less influence on the sum of the present values (NPV). So, the derived rate would not change much as we add cash flows.

Of course, that is a lot of hand-waving based on a lot of assumptions. If your later CFs varied widely, we might see a very different progression. But at the very least, it should convince you that the progression that you see is not unusual.

 

[joeu2004]

you are calculating the simple rate of return over n years, n=1, 2, 3, etc [....] I believe the formula in column J should be:
J4: =I4/(-$F$3) - 1

In this case, it is the "return on investment" (ROI).

As noted, that is a simple rate over n years. But the IRR is a compounded annual rate (in this case).

So, for an apples-to-apples comparison, we might calculate the compounded average ROI (aka CAGR) using the following in J4 and copying down:

=IF(A4="", "", (I4 / (-$F$3))^(1/A4) - 1)

I was trying to reverse engineer it to see how they calculated their expectations to match them up in the future. Thank you so much for your help, it's all working now!

You're welcome. But I still cannot figure out the "Equity Accumulation" column. Can you tell us how that is calculated, just for my edification?

(If not how it is calculated, can you us what it is based on. For example, perhaps the terms of the mortgage: payment frequency, initial principal, annual or periodic interest rate, etc.)

The following table offers some insight into the computation of the other columns.

It also demonstrates how to use the XL2BB add-in for this forum's new interface. That is better than an uploaded image. Go to XL2BB - Excel Range to BBCode to download the Excel add-in.

It can be suitable for including small examples like mine. For larger examples -- perhaps like yours -- I still prefer an Excel file that is uploaded to a file-sharing website.

#VALUE!

You can see formulas (identified by cells with a triangle in the upper-left corner) by hovering the cursor over the cell in the posted table. For posterity, they are:

F4: =IF(A4="", "", C4-D4-E4)
G4: =IF(A4="", "", (C4-E4-D4) / (-$F$3))
I4: =IF(A4="","", F4+H4)
J4: =I4/(-$F$3) - 1
K4: =IF(A4="", "", IRR((F3,I4)))
C5: =IF(A5="", "", C4*(1+2%))
D5: =IF(A5="", "", $D$4)
E5: =IF(A5="", "", E4*(1+2%))
K5: =IF(A5="", "", IRR(($F$3:F4, I5)))

Apparently, annual income and expenses are assumed to increase by 2% each year.

The formula for COCR in G4 is based on How Cash-on-Cash Returns Work .

As noted previous, the formula in G4 and J4 can be written as -(C4-E4-D4)/$F$3 and -I4/$F$3 - 1 . I chose -$F$3 to emphasize the source of unary minus, to wit: all values are positive, notably the initial investment (F3).

 

[questionking]

Yes the equity accumulated takes into account the initial down payment and the principal paid for each follow on year until maturity. I appreciate your feedback and help!