5. Calculating Project Cash Flows and NPV. Pappy?s Potato has come up with a
new product, the Pet Potato (they are freeze-dried to last longer).
Pappy?s paid $120,000 for a marketing survey to determine the
viability of the product. It is felt that Pet Potato will generate
sales of $380,000 per year. The fixed costs associated with this will
be $145,000 per year, and variable costs will amount to 20 percent of
sales. The equipment necessary for production of the Potato Pets will
cost $240,000 and will be depreciated in a straight-line manner for
the four years of the product life (as with all fads, it is felt the
sales will end quickly). This is the only initial cost for
the production. Pappy?s is in a 40 percent tax bracket and has a
required return of 13 percent. Calculate the payback period, NPV, and
IRR.

Hi yourtime4mymoney!!


To start we need to know the total initial investment (I):

I is the sum of the marketing survey plus invests in equipment:

I = $120,000 + $240,000 = $360,000


Follows the calculatation of the cash flow for each year:

For each year Yi (i = 1 to 4) we have that:

If we call Ri = revenues of Yi, Ei = expenses of Yi, and D =
Depreciation, then for each year we will have:

Ri = $380,000  (i = 1 to 4)

Ei = Fixed costs + Variable Costs =
   = $145,000 + 0.20*$380,000 = 
   = $221,000                     (i = 1 to 4)

Depreciation = D = (Invest in equipment) / 4 = 
                 = $240,000 / 4 =
                 = $60,000 

Taxes (Ti) will be:

Ti = T * (Ri - Ei - D) =                with T = 0.4
   = 0.4*($380,000 - $221,000 - $60,000) =
   = 0.4*($99,000) =
   = $39,600                    (i = 1 to 4)


So the cash flow formula for each year is:

CFi = Ri - Ei - T*(Ri - Ei - D)

Then:

CFi = 380,000 - 221,000 - 39,600 = $119,400  (i = 1 to 4).

---------------------------------------------------------

PayBack Period:
Payback Period (PB) calculation:
If Y is the year before the full recovery of the investment, U is
the unrecovered cost at the start of last year and CFi is the CF of
the year Y+1 then:
PB = Y + U/CFi 

Note that at the end of the third year the initial investment is not
recovered, so the payback period is greater than 3:
Y = 3
U = 360,000 - 3*119,400 = 360,000 - 358,200 = $1,800 
CF4 = $119,400 

Then:

PB = 3 + 1,800/119,400 = 3 + 0.015 = 3.015

Note: Each month is the 1/12 (= 0.083) part of the year, and 0.015 is
greater than zero and less than 1/12, so we can "round" the 3.015 to 3
years and 1 month.

---------------------------------------------------------

NPV:

Recall the following definitions:

Present Value (PV):
         CF1           CF2            CF3            CF4  
PV  = ---------  +  ----------  +  ----------  +  ----------
      (1 + r)^1     (1 + r)^2	  (1 + r)^3      (1 + r)^4  

Where r is the required return.


In this problem all the cash flows are equal, so we can use:
      CF             1
PV = ---- * [1 - ---------] 
       r          (1-r)^4

Net Present Value (NPV):

NPV = PV - I         where I = Total Initial Investment



PV = 119,400/0.13 * [1 - 1/(1.13)^4] =
   = $355,151.88 

NPV = PV - I =
    = $355,151.88 - $360,000 =
    = -$4,848.12


----------------------------------------------------------

IRR:

IRR is the discount rate r at which the NPV equals zero:

NPV = PV - I = 0


Then IRR is the discount rate r at which:

PV = I


So you must find the r that solves the following equation:

        CF1         CF2          CF3          CF4  
PV = --------- + ---------- + ---------- + --------- = I
     (1 + r)^1   (1 + r)^2    (1 + r)^3    (1 + r)^4  


You can use different ways to calculate the IRR, for example:
-Trial & Error
-Calculator
-Computer (Excel spreadsheet)

Here is a brief guide to do this using an MS Excel spreadsheet for this problem:
1) Select a column for the project's Cash flows (for example column "A").
2) Input the project's Cash Flows starting from the initial investment
(this is a negative input) and followed by the CF1 to CF4 cash flows,
each one in one cell of the column.
3) Click on the cell where you want your IRR calculated (say B1). 
4) Enter "=IRR(" (without the quotes) and then highlight the column A
then close the parenthesis and hit enter.

For the project A the column A will have:
A1: -360,000 ; A2: to A5: 119,400 ;
B1: =IRR(A1:A5)

You will find that IRR = 12.35% .

-----------------------------------------------------------

I hope that this helps you. If you need further assistance, please
post a request for an answer clarification before rate this answer. I
will gladly respond to your requests.


Regards.
livioflores-ga