+ Reply to Thread
Results 1 to 5 of 5

Can I use the IRR Function without an Initial Investment?

  1. #1
    Registered User
    Join Date
    11-18-2004
    Location
    San Jose, CA
    MS-Off Ver
    Windows 2010 with Office Suite 2016
    Posts
    6

    Can I use the IRR Function without an Initial Investment?

    This is a Practice Question for my Econ class so the answer is provided. The problem I'm having is the IRR Function returns #NUM and by all accounts, it is because I did not enter an initial investment amount as I have none.
    I have tried to use the ($475)* as an initial cost but I get -3.41%, which is incorrect.

    In order for me to derive an 8.3% IRR, I would have to input an initial investment of ($398) of which I have no idea WHERE that would come from.

    EXTRA PRACTICE #1: Investing in school

    Consider an individual who lives for two periods. In the first period, the individual may either work or go to school (but not both). In the second period, the individual will work regardless of what was done in the first period. If the individual foregoes schooling and decides to work, she will earn $200 in period 1 and $250 in period 2. If she decides to get an education in the first period, her wage in period 2 will be $575. The direct cost of schooling in period 1 is $100.

    Q1: If the individuals discount rate is 10%, will she work or go to school in the first period if her utility is a function of income only?
    A1: She will not go to school

    Q2: Calculate the internal rate of return.
    A2: 8.3%

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Can I use the IRR Function without an Initial Investment?

    How much of this question is "business math" and how much is Excel? My disclaimer: I understand the syntax of entering data into Excel and using the IRR() function, but I have no special training in business math.

    I note that on the Excel side of the question, the help file doesn't say anything about requiring an initial investment. The only requirement that the help file gives is that there must be at least one positive and one negative value in the list of cash flows in order for the IRR() algorithm to work. This should make sense, as the IRR() function is attempting to find the IRR that causes the NPV to be 0. It is impossible to add a bunch of positive (or negative) numbers together and get a value of 0. As far as the Excel entry and syntax, it should be a matter of entering the correct array of cash flows and entering the IRR() function -- which, it seems, you already know how to do. The only thing I might add (it might only apply to the XIRR() function, though) is that I have seen instabilities in Excel's implementation of the XIRR() function that other spreadsheets implementation don't exhibit. Some XIRR() problems where Excel has trouble calculating a result are calculated just fine in other spreadsheets. I don't know if that spills over into the IRR() function, as NPV function is simpler than the XNPV function.

    Which leads me to believe there is more business math behind this question than Excel. What does an IRR mean when you don't have an initial investment? That is probably what this homework question is trying to see if you understand. I don't know the answer. I have seen those who know business math on this forum suggest that one needs to -- in lieu of an initial investment -- include some kind of (hypothetical?) buy out/cash out/final liquidation value at the end of the cash flows in order to calculate an IRR. I don't know if that would apply in this case or not, but I offer that thought in the spirit of brainstorming.

    We have a few users on here who are knowledgeable in business math, and we may need to wait until they see this and respond. Until then, I suggest you research what IRR() means when you don't have an initial investment. If nothing else, this long treatise will bump your topic back to the top where those more knowledgeable can see it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: Can I use the IRR Function without an Initial Investment?

    Hi,

    The IRR is simply calculating the discount rate that makes the Net Present Value (NPV) zero

    See the calculation below
    In Scenario 1 you earn money in both years, in the second year there is a cost in year 1 and an income in year 2.

    Although the question is not explicitly phrased like this, (I happen to think it's badly worded and not explicit enough), I think you are being asked to calculate the IRR if the individual chooses scenario 2 rather than scenario 1. -
    Hence in that case you need to evaluate the net diffrences in each period and apply the IRR function to those.
    Attached Images Attached Images
    Last edited by Richard Buttrey; 11-10-2018 at 12:37 PM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Can I use the IRR Function without an Initial Investment?

    Quote Originally Posted by Richard Buttrey View Post
    I think you are being asked to calculate the IRR if the individual chooses scenario 2 rather than scenario 1. -
    Hence in that case you need to evaluate the net diffrences in each period and apply the IRR function to those.
    Interesting interpretation, insofar as it does derive an IRR that seems similiar to the expectation.

    But what sense does that make? And does your methology work only by coincidence?

    If we were asked to analyze the choice of scenario 1 instead of scenario 2, would we subtract #2 from #1, in which case the IRR is not computable?

    More to the point, if the numbers were different such that subtracting #2 from #1, as you did, does not result in "net" cash flows with different signs, the IRR would not be computable.

    In general, one way of comparing alternative financial scenarios is by comparing the IRR or NPV of each, not their difference(s). After all, it is not always a binary choice.

    And I would certainly choose NPV for Q1, since the discount rate is given.

    But for Q2, I agree that the davism's presentation of the assignment is unclear.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: Can I use the IRR Function without an Initial Investment?

    As I said I think the way the question has been presented lacks clarity and is confusing.

    When calculating an IRR, expected cash flows for a project or investment are given and the NPV equals zero. The initial cash investment for the beginning period will be equal to the present value of the future cash flows of that investment (cost paid = present value of future cash flows. Hence, the net present value = 0).

    Implicit in that definition is that there cannot be a series of cash flows that are either all positive or all negative. Necessarily the arithmetic behind the calculation can NEVER evaluate to zero and the very idea of an IRR in that event is meaningless.

    Think of it as if you are given a sum of money to buy a property in year 1 which you then rent out in years 2...n
    You have positive cashflows in all the years 1..n. You have not laid out a sum of money against which you will derive future returns and there is therefore no 'investment' on which to base a return.

    You say "If we were asked to analyze the choice of scenario 1 instead of scenario 2, would we subtract #2 from #1, in which case the IRR is not computable?" That of course is correct, but it seems to me that is the wrong way to look at it.

    Think of it instead as a question that is not about the IRR on the absolute cash flows of either choice, but about an IRR which is based on the RELATIVE gain you make between two different choices.

    Maybe the question should have been phrased something like, "
    What is the IRR of the opportunity cost or benefit of choosing scenario 2 instead of scenario1?
    That would then have immediately implied you're not looking at the absolute cost of either scenario but on the opportunity cost/benefit when choosing one RATHER than the other.
    Last edited by Richard Buttrey; 11-10-2018 at 02:40 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Break-even Analysis related to initial Investment
    By JesseV92 in forum Excel General
    Replies: 0
    Last Post: 06-25-2016, 11:59 AM
  2. Replies: 3
    Last Post: 03-05-2015, 02:33 PM
  3. [SOLVED] IsBlank function run on form will not allow initial save of bank value to send to users
    By lengwer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2014, 10:03 AM
  4. Replies: 6
    Last Post: 12-25-2012, 11:45 PM
  5. Initial Declaration of Non-Static Array in Function
    By Extradimensional in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-11-2011, 09:22 PM
  6. initial investment
    By evanhughes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-24-2010, 02:12 PM
  7. problems with RATE function returning #NUM-Investment Date
    By jaedend@charter.net in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1