irr of growing perpetuity

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Jan 22, 2004.

  1. Guest

    Guest Guest

    Given the Estimated Profit Potential for cast inflows and Construction and Maintenance Expenses for cash outflows how would I calculate the IRR? I calculated the NPV to be negative -$0.60. I'm not sure this is correct, NPV, but if it is how would the IRR be calculated? Further, if anyone calculates the NPV to be a different then -$0.60 chime in please. I'm attempting to use an excel IRR function and need help using it.



    Estimated Profit Potential

    Calculations using the current price of electricity and the power calculations for the site indicate that year–end revenues from the Big Crick River facility (with one turbine on line) will total $190 million in year 1.

    Preliminary negotiations have begun with the Steerville utilities committee regarding the possible purchase of electricity from the proposed Big Crick River facility. The Steerville committee is interested in purchasing the plant's entire energy production in year 1 in order to supplement its current supply. In addition, there are indications that the town's electricity needs will be growing each year in the forseeable future (see attached newspaper article); again, the committee has indicated that it would like to meet this increased demand through a purchase from the Big Crick River facility. Calculations based on projections of future usage provided by the Steersville utilities committee indicate that profits from this facility will increase at a rate of 5 percent per year. The additional energy supply would be provided by bringing the second turbine online as needed.

    Construction and Maintenance Expenses

    Total cost for the construction of the facility, purchase and installation of turbines and generators, and running of necessary external supply cables is estimated at $75 million. This expense is a one–time, up–front cost. The cost of bringing the second turbine on line is negligible. Annual operating expenses are projected to total $180 million in year 1, resulting in a profit of $10 million for that year.
     
    Guest, Jan 22, 2004
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    ----- purpleaces wrote: ----

    Given the Estimated Profit Potential for cast inflows and Construction and Maintenance Expenses for cash outflows how would I calculate the IRR? I calculated the NPV to be negative -$0.60. I'm not sure this is correct, NPV, but if it is how would the IRR be calculated? Further, if anyone calculates the NPV to be a different then -$0.60 chime in please. I'm attempting to use an excel IRR function and need help using it



    Estimated Profit Potential

    Calculations using the current price of electricity and the power calculations for the site indicate that year–end revenues from the Big Crick River facility (with one turbine on line) will total $190 million in year 1

    Preliminary negotiations have begun with the Steerville utilities committee regarding the possible purchase of electricity from the proposed Big Crick River facility. The Steerville committee is interested in purchasing the plant's entire energy production in year 1 in order to supplement its current supply. In addition, there are indications that the town's electricity needs will be growing each year in the forseeable future (see attached newspaper article); again, the committee has indicated that it would like to meet this increased demand through a purchase from the Big Crick River facility. Calculations based on projections of future usage provided by the Steersville utilities committee indicate that profits from this facility will increase at a rate of 5 percent per year. The additional energy supply would be provided by bringing the second turbine online as needed

    Construction and Maintenance Expense

    Total cost for the construction of the facility, purchase and installation of turbines and generators, and running of necessary external supply cables is estimated at $75 million. This expense is a one–time, up–front cost. The cost of bringing the second turbine on line is negligible. Annual operating expenses are projected to total $180 million in year 1, resulting in a profit of $10 million for that year

    Oh, I'm using an interest rate of 10%, I forgot to supply that information above
    Thanks.
     
    Guest, Jan 22, 2004
    #2
    1. Advertisements

  3. Hi purpleaces!

    You have a fair amount of missing data and unusual assumptions.

    Time for construction of the facility?
    Profit forecast to increase at 5%. Usually better to apply revenue and
    cost inflators separately with profit being difference between
    inflated revenue and inflated cost.
    Estimated life of the project and / or terminal value?


    With initial outlay at point 0 of -75 and income of 10 after 1 year
    and thereafter inflating at 5%, I calculate NPV at 123.00 using a
    discount rate of 10%.

    A1:
    -75
    A2:
    10
    A3:
    =A2*(1+.05)
    Copied down to A100

    =NPV(10%,A2:A100)+A1
    returns 123.000622626796

    =IRR(A1:A100,0)
    returns: 18.3332367027205%

    Homework project?
    --
    Regards
    Norman Harker MVP (Excel)
    Sydney, Australia

    Excel and Word Function Lists (Classifications, Syntax and Arguments)
    available free to good homes.
     
    Norman Harker, Jan 22, 2004
    #3
  4. Guest

    Guest Guest

    Yep, it's a homework assignment, Finance class. Seems like I'm learning two skills here, finance and Excel. I'm really glad I found this site. NPV's have not been too diffcult, but IRR or growing perpetuity sent me for a loop.
    Thanks.
     
    Guest, Jan 23, 2004
    #4
  5. Hi purpleace!

    Lucky guess!

    There is a formula for calculating the present value of an income
    stream that increases at a constant rate. However, you need a discount
    rate and that's what you're trying to find.

    I'd cheat! Use a very long term and you'll find that it's equivalent
    to a perpetuity. And you can check your error by calculating the NPV
    using the discount rate to see how close you are to 0.

    For homework purposes:

    1. Make sure that you fully understand the solution.
    2. Make sure you use crosschecks of IRR and NPV functions.
    3. State all assumptions that you have made.
    4. Outline limitations.
    5. Acknowledge all sources of assistance.

    Try to "value add" all solutions.
    --
    Regards
    Norman Harker MVP (Excel)
    Sydney, Australia

    Excel and Word Function Lists (Classifications, Syntax and Arguments)
    available free to good homes.
     
    Norman Harker, Jan 23, 2004
    #5
  6. Guest

    Guest Guest

    So how would you calculate the IRR for the same problem above?
     
    Guest, Jan 24, 2004
    #6
  7. Hi bamahoky

    How would I do it. I'm not really supposed to say for homework
    examples, but it's an important general question.

    A pretty simple solution!

    It's:
    10/75 + 5%
    returns: 18.33333%

    It's a dirty question aimed at getting you to do a lot of work or to
    look at the formula involved.

    Briefly:

    PV = PMT*(1+e)^-1 + PMT*(1+g)/(1+e)^-2 + .....
    This involves a geometric progression which can be summed:
    PV = PMT*(1+e)^-1 + PMT*(1-(1+g)^n/(1+e)^n)/(1-(1+g)/(1+e))
    Since n is infinite this resolves to
    PV = PMT*1/(e-g)
    A standard formula for valuing an infinite cash flow (in arrears) with
    a constant growth rate.
    Re-expressed
    e = PMT/PV + g
    In the example:
    =10/75 + 5%
    Returns: 18.3333333%

    If you set up a dirty cash flow
    A1: -75
    A2: 10
    A3:
    =A2*(1+.05)
    Copied down to (say A200

    =IRR(A1:A200,0)
    Returns: 18.3333333%

    And in the days of financial calculators some students couldn't
    understand how others finished so quickly and got 100% and they got
    the same answer after a lot of sweat and only got 60%. I always
    awarded marks for method!

    Other students were failed because they failed to properly acknowledge
    assistance in coursework assignments!!!
    --
    Regards
    Norman Harker MVP (Excel)
    Sydney, Australia

    Excel and Word Function Lists (Classifications, Syntax and Arguments)
    available free to good homes.
     
    Norman Harker, Jan 24, 2004
    #7
  8. "Norman Harker" <> wrote...
    ....
    >Other students were failed because they failed to properly acknowledge
    >assistance in coursework assignments!!!

    ....

    And how would the OP's instructor be able to learn of any unacknowledged
    assistance? Carrying the point in the other direction, when may one stop
    acknowledging the same sources? Should I continue to acknowledge the
    interest theory text I read back in the early 1980's and from which I
    learned annuity functions?
     
    Harlan Grove, Jan 24, 2004
    #8
  9. Hi Harlan!

    Re: "And how would the OP's instructor be able to learn of any
    unacknowledged assistance? "

    With newsgroups and website assistance it's common for use to Google
    Search.

    Re: Carrying the point in the other direction, when may one stop
    acknowledging the same sources? Should I continue to acknowledge the
    interest theory text I read back in the early 1980's and from which I
    learned annuity functions?

    More difficult but I'd suggest a closer nexus between work submitted
    and source. On occasions I've acknowledged texts from way back when.
    If in doubt, acknowledge or ask the instructor.

    In any event most academic institutions produce guides to plagiarism,
    collusion and cheating. I wrote one for our Faculty that was promptly
    copied without acknowledgement <vbg>

    --
    Regards
    Norman Harker MVP (Excel)
    Sydney, Australia

    Excel and Word Function Lists (Classifications, Syntax and Arguments)
    available free to good homes.
     
    Norman Harker, Jan 24, 2004
    #9
  10. "Norman Harker" <> wrote...
    >Re: "And how would the OP's instructor be able to learn of any
    >unacknowledged assistance? "
    >
    >With newsgroups and website assistance it's common for use to Google
    >Search.

    ....

    I see. So no doubt 'purpleaces' and 'bamahoky' are enrolled under those
    names? Or would the instructor only need to have a vague impression of who
    they might be in order to fail them?
     
    Harlan Grove, Jan 24, 2004
    #10
  11. Hi Harlan!

    With these issues proof is difficult and varies with the nature and
    extent of the unacknowledged material.

    I have had notable successes. Like a project report copied extensively
    from a private firm's unpublished report. With proper acknowledgement
    and a bit of value added the students would have passed easily. It was
    only by chance that I knew of the probable existence of the report and
    which firm might have produced it. Biggest clue was that the work was
    just too good for students at that stage.

    If work included the formula derivation and notes in entirety, it
    wouldn't be too hard to either track it down or to prove failure to
    acknowledge. If it just contained the formula, it's impossible as
    =PMT/PV+Growth is pretty common.

    We don't pretend to be able to catch 100%. I'd be happy with a 10%
    success rate and imposition of appropriate penalties. But I'd be
    happier still to see balanced assessment regimes.

    --
    Regards
    Norman Harker MVP (Excel)
    Sydney, Australia

    Excel and Word Function Lists (Classifications, Syntax and Arguments)
    available free to good homes.
     
    Norman Harker, Jan 24, 2004
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim

    IRR function returns #DIV/0!

    Jim, Aug 17, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    178
  2. Gray

    Formula Referencing the Result of an IRR Calc

    Gray, Sep 12, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    331
    Dave Ramage
    Sep 12, 2003
  3. Dave

    NPV with perpetuity growth rate

    Dave, May 21, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    19,022
    Norman Harker
    May 21, 2004
  4. vezerid

    Re: IRR / MIRR and Perpetuity

    vezerid, Dec 18, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    1,958
    Harlan Grove
    Dec 20, 2006
  5. joeu2004
    Replies:
    1
    Views:
    208
    joeu2004
    Oct 22, 2014
Loading...

Share This Page