irr of growing perpetuity

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

1. GuestGuest

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

2. GuestGuest

----- 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

3. Norman HarkerGuest

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
4. GuestGuest

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
5. Norman HarkerGuest

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
6. GuestGuest

So how would you calculate the IRR for the same problem above?

Guest, Jan 24, 2004
7. Norman HarkerGuest

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
8. Harlan GroveGuest

"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
9. Norman HarkerGuest

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
10. Harlan GroveGuest

"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
11. Norman HarkerGuest

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