Profitability index calculation in R

[ibrahimecon]

I have negative cash flows (-19.71286, -19.84066, -19.97366, -20.11566, -20.28666, -20.48666, -20.69706, -20.94406, -21.22146, -21.53686, -20.97026, -20.97026, -20.97026,-20.97026, -20.97026, -20.97026, -20.97026, -20.97026, -20.97026, -20.97026) for 20 years in a project, these are all present values of the annual cash flows and initial investment is 66 MM USD. I want to calculate the profitability index. I calculate it like this:

CF$PV<- c(-19.71286, -19.84066, -19.97366, -20.11566, -20.28666, -20.48666, -20.69706, -20.94406, -21.22146, -21.53686, -20.97026, -20.97026, -20.97026,-20.97026, -20.97026, -20.97026, -20.97026, -20.97026, -20.97026, -20.97026) 
profitability_index <- sum(CF$PV) / initial_investment (66)
profitability_index

I have 30 cases like this and for some cases, I get profitability index of -20, -8, -1.7 and figures like these. My supervisors say that this cannot happen as profitability index cannot be -20, the minimum it can be 0-1. I have calculated profitability index via excel in different ways (NPV/initial investment), but I still get numbers like -20/-8. Please what is the wrong thing I am doing here?

[startz]

The reason a profitability index "can't" be negative is the assumption that cashflows are positive. You have negative cashflows. If the negative cashflows are correct, go back to your supervisors and ask them what they're thinking.

[nirgrahamuk]

This is pretty much exactly the same scenario that we picked over on the previous thread , with one minor difference.
Last time we added a condition that would address a violation of assumption that the main calculation relied on, and adjusted the output to Inf-inity so that the correct value would be returned under the violation scenario.

This is the same, except for instead of adjusting to infinity you would adjust to zero.

[ibrahimecon]

My supervisors mentioned that the way I am calculating profitability index is not correct. I showed them this formula: Profitability index= SUM(Present value of future cash flows)/initial investment. They mentioned that since I have negative cash flows all the years, I should not calculate profitability index this way. Rather, I should consider this formula: PI= SUM(Present value of Benefits)/sum(Present value of costs) and rather than saying profitability index, I should call it benefit cost ratio. Now, what would be the benefits here? They said that benefits are revenues coming out each year. I have calculated benefits (or revenues) by this way:

...
sale_by <- q_dslp_dsl+q_lpgp_lpg+q_napp_nap+q_propanep_propane
sale <- sale_by+q_jet*p_jet
...

Here, jet is the main output and others are co-produced with it. So I calculate revenue by multiplying all the quantity of outputs with their prices. So they mentioned that since this goes on for 20 years (I consider 20 years period for this project to have cash inflows), I should calculate present value of "sale" for all the 20 years. Then, I should also calculate costs in the similar way. For costs, I have initial capital cost and operational costs. Operational costs occur 20 years. I calculate operational costs in the following way: since I have several inputs, so I calculate the operational cost by multiplying input quantity with input prices:

...
exp_ut <- q_waterp_water+q_elecp_elec+
q_gasp_gas+q_h2p_h2
exp <- exp_ut+q_fdp_fd+q_labp_lab

...

So I will have to calculate present value for "exp" for 20 years and add the initial cost which is capital cost. Now then I should calculate the formula this way: sum(present value of all benefits)/( sum(present value of all costs)+present value of capital cost). My question now is:

1. Is the formula correct? I have calculated this way and it gives me 0.33 (which tells that project is not profitable).

2. How do I calculate the present values of benefits and costs (described above) given say a fixed discount rate (8%)?

[ibrahimecon]

For your convenience, I am copying the formula I have used to calculate NPV (this is not my code, but rather of a student who worked with the supervisors before):

...

CF0 <- capex0.08(1+r_dis)^2+capex0.6(1+r_dis)+capex*0.32

exp_ut <- q_waterp_water+q_elecp_elec+
q_gasp_gas+q_h2p_h2
exp <- exp_ut+q_fdp_fd+q_labp_lab #275.56
exp

sale_by <- q_dslp_dsl+q_lpgp_lpg+q_napp_nap+q_propanep_propane
sale <- sale_by+q_jet*p_jet

ebitda <- sale-exp

dep <- capex*0.8
ebit <- ebitda-dep

amort <- amort.table(Loan=capex*debt,n=t_loan,pmt=NA,r_dis)[["Schedule"]]
CF1 <- as.data.frame(amort) # cash flow for Y1-10
CF1$Depreciation <- dep

CF2 <- CF10 # cash flow for Y11-20
CF <- rbind(CF1,CF2)
CF <- cbind(Year=1:nrow(CF),CF)
CF$EBIT <- ebit
CF$EBT <- CF$EBIT-CF$Interest Paid
CF$Flow <- CF$EBT(1-r_tax)+CF$Depreciation-CF$Principal Paid
CF$Discount <- r_dis
CF$PV <- CF$Flow/(1+CF$Discount)^CF$Year
NPV <- sum(CF$PV)-CF0
NPV

...