PC Review


Reply
Thread Tools Rate Thread

irr of growing perpetuity

 
 
=?Utf-8?B?cHVycGxlYWNlcw==?=
Guest
Posts: n/a
 
      22nd Jan 2004
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.

 
Reply With Quote
 
 
 
 
=?Utf-8?B?cHVycGxlYWNlcw==?=
Guest
Posts: n/a
 
      22nd Jan 2004


----- 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.
 
Reply With Quote
 
 
 
 
Norman Harker
Guest
Posts: n/a
 
      22nd Jan 2004
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
(E-Mail Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


 
Reply With Quote
 
=?Utf-8?B?cHVycGxlYWNlcw==?=
Guest
Posts: n/a
 
      23rd Jan 2004
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.
 
Reply With Quote
 
Norman Harker
Guest
Posts: n/a
 
      23rd Jan 2004
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
(E-Mail Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


 
Reply With Quote
 
=?Utf-8?B?YmFtYWhva3k=?=
Guest
Posts: n/a
 
      24th Jan 2004
So how would you calculate the IRR for the same problem above?


 
Reply With Quote
 
Norman Harker
Guest
Posts: n/a
 
      24th Jan 2004
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
(E-Mail Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


 
Reply With Quote
 
HarlanGrove
Guest
Posts: n/a
 
      24th Jan 2004
"Norman Harker" <(E-Mail Removed)> 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?


 
Reply With Quote
 
Norman Harker
Guest
Posts: n/a
 
      24th Jan 2004
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
(E-Mail Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


 
Reply With Quote
 
HarlanGrove
Guest
Posts: n/a
 
      25th Jan 2004
"Norman Harker" <(E-Mail Removed)> 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?


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
File size growing growing exponentially Steve Microsoft Excel Misc 6 24th May 2009 06:29 PM
Re: IRR / MIRR and Perpetuity vezerid Microsoft Excel Worksheet Functions 5 20th Dec 2006 01:50 AM
Pagefile size growing and growing =?Utf-8?B?SnVoYQ==?= Windows XP Performance 2 3rd Oct 2006 07:45 PM
BUG? IE6 Windows XP SP2: iexplore.exe ram is growing and growing... Michael Schwarz Windows XP Internet Explorer 0 14th Oct 2004 04:05 PM
NPV with perpetuity growth rate Dave Microsoft Excel Worksheet Functions 2 21st May 2004 05:47 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:33 PM.