# Benefit cost ratio calculation correct?

Can anybody please tell me if benefit cost ratio (bcr) code is correct? I am calculating both NPV and BCR, but NPV gives me positive value (1200) whereas my benefit cost ratio gives .68 (the project not profitable). I think there is something wrong with my code. Can you please help me resolve the mistake I am doing when I am calculating benefit cost ratio please?

``````
library(FinancialMath)
library(dplyr)
library(ggplot2)
library(plyr)
library(tidyverse)
# plant info

q_fuel <- 113 ##million Liters per year
capex <- 284 ##Million USD  capital exp
insurance_tax <- capex*.007

hrs <- 8400 #hours per year

# input info
q_fd <- 0.24 #Million tons per year (million tonnes annually)
q_water <- 1.6 #Million tons per year
q_elec <- 12.11 #Million kWh per year
q_gas <- 0.04 #Million tons per year
q_h2 <- 0.007 #M t/yr
q_lab <- 55 #labor number (quantity of labor)

p_fd <- 500 #USD per metric ton (price of feedstock)
p_water <- .57 #USD per metric tons
p_elec <- 0.11 #USD per kWh
p_gas <- .8 #USD per metric tons
p_h2 <- 1210 #USD per metric tons
p_lab <- 0.043 #USD million per labor (price of labor)

# output info
q_jet <- 106 #million Liters per year  (quantity of jet)
q_dsl <- 19 #million Liters per year  (quantity of jet)
q_lpg <- 32	#million Liters per year  (quantity of jet)
q_nap <- 64	#million Liters per year  (quantity of jet)

p_jet <- 0.47 #USD per liter
p_dsl <- 0.82 #USD per liter
p_lpg <- 0.21	#USD per liter
p_nap <- 0.52	#USD per liter

# financial info
life <- 30 #year
t_const <- 3 # construction period: 8%,60%,32% of capital expenditure in year 1,2 and 3
t_dep <- 7 # depreciation period
t_loan <- 10 # loan term
r_dis <- 0.10 # deflated discount rate (default value)
r_eq <- 0.15 # rate of return
r_tax <- 0.21

#######################################
# NPV calculation
#######################################

# Set inflation rate to 2%
inflation_rate <- 0.02

# Calculate inflation factor for each year
inflation_factor <- (1 + inflation_rate)^(1:30)

CF0 <- capex*0.08*(1+r_dis)^2* inflation_factor+capex*0.6*(1+r_dis)* inflation_factor+capex*0.32* inflation_factor

sale_by <- q_dsl * p_dsl*inflation_factor +
q_lpg * p_lpg*inflation_factor  +
q_nap * p_nap*inflation_factor

sale <- sale_by+q_jet*p_jet* inflation_factor

exp_ut <- q_water * p_water* inflation_factor +
q_elec * p_elec* inflation_factor +
q_gas * p_gas* inflation_factor +
q_h2 * p_h2 * inflation_factor
exp <- exp_ut+q_fd*p_fd*inflation_factor+q_lab*p_lab*inflation_factor +insurance_tax*inflation_factor

# Calculate remaining cash flows

cat("revenue share for jet:",q_jet*p_jet/sale,"\n")
cat("revenue share for dsl:",q_dsl*p_dsl/sale,"\n")
cat("revenue share for lpg:",q_lpg*p_lpg/sale,"\n")
cat("revenue share for nap:",q_nap*p_nap/sale,"\n")

ebitda <- sale - exp

# Calculate annual depreciation
dep_per_year <- capex*.8/ t_dep
dep_schedule <- c(rep(dep_per_year, t_dep), rep(0, life - t_dep))
ebit <- ebitda - dep_schedule
amort <- amort.table(Loan=capex,n=t_loan,pmt=NA,r_dis)[["Schedule"]]

CF1 <- as.data.frame(amort) # cash flow for Y1-10
new_rows <- data.frame(Payment = rep(0, 20),
`Interest Paid` = rep(0, 20),
`Principal Paid` = rep(0, 20),
Balance = rep(0, 20))

names(new_rows) <- names(CF1)

CF <- rbind(CF1, new_rows)
CF\$Year <- 1:30

CF\$EBIT <- ebit
CF\$EBT <- CF\$EBIT-CF\$`Interest Paid`
CF\$Depreciation <- dep_schedule
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

# Calculate present value of total costs
loan_amount <- capex
t_loan <- 10
opex <- exp
loan_amortization <- amort.table(Loan = loan_amount, n = t_loan, pmt = NA, r_dis)\$Schedule

loan_amortization <- as.data.frame(loan_amortization)
new_rows <- data.frame(Payment = rep(0, 20),
`Interest Paid` = rep(0, 20),
`Principal Paid` = rep(0, 20),
Balance = rep(0, 20))

names(new_rows) <- names(loan_amortization)
loan_amortization <- rbind(loan_amortization, new_rows)
loan_amortization\$Year <- 1:30

dep_per_year <- capex/ t_dep
dep_schedule <- c(rep(dep_per_year, t_dep), rep(0, life - t_dep))

loan_amortization\$Principal_Paid<- loan_amortization\$`Principal Paid`
loan_amortization\$Interest_Paid<- loan_amortization\$`Interest Paid`
loan_amortization\$Balance<- loan_amortization\$'Balance'

# Calculate total cost of cashflows
total_cost_cashflows <- c(CF0, opex,
(loan_amortization\$Principal_Paid),
(loan_amortization\$Interest_Paid),
-(dep_schedule * (1 - r_tax)))
total_cost_pv <- pv(total_cost_cashflows, rate)

# calculate total benefits
benefits <- sale

# calculate present value of benefits
discount_rate <- 0.1
pv_benefits <- sum(benefits / (1 + discount_rate) ^ (1:life))

bcr <- pv_benefits   /total_cost_pv
bcr

#######################################
# BSP calculation
#######################################

irr <- r_eq
#irr <- r_eq-r_inf
CF0 <- capex*0.08*(1+irr)^2* inflation_factor+capex*0.6*(1+irr)* inflation_factor+capex*0.32* inflation_factor

exp_ut <- q_water * p_water* inflation_factor +
q_elec * p_elec* inflation_factor +
q_gas * p_gas* inflation_factor +
q_h2 * p_h2 * inflation_factor
exp <- exp_ut+q_fd*p_fd*inflation_factor+q_lab*p_lab*inflation_factor+ insurance_tax*inflation_factor

sale_by <- q_dsl * p_dsl*inflation_factor +
q_lpg * p_lpg*inflation_factor  +
q_nap * p_nap*inflation_factor

p_jet_range <- seq(0.1,100,by=0.01)
# search within [0.5,1] to find a value yielding a NPV closest to zero
bsp_jet <- 1000 #default
bsp_npv <- 1000 #default
for (p_jet in p_jet_range){
sale <- sale_by+q_jet*p_jet* inflation_factor
#  cat("revenue share for meal:",q_meal*p_meal/sale,"\n")

ebitda <- sale - exp

dep_per_year <- capex*.8/ t_dep
dep_schedule <- c(rep(dep_per_year, t_dep), rep(0, life - t_dep))
ebit <- ebitda - dep_schedule
amort <- amort.table(Loan=capex,n=t_loan,pmt=NA,irr)[["Schedule"]]

CF1 <- as.data.frame(amort) # cash flow for Y1-10
new_rows <- data.frame(Payment = rep(0, 20),
`Interest Paid` = rep(0, 20),
`Principal Paid` = rep(0, 20),
Balance = rep(0, 20))

names(new_rows) <- names(CF1)

CF <- rbind(CF1, new_rows)
CF\$Year <- 1:30

CF\$EBIT <- ebit
CF\$EBT <- CF\$EBIT-CF\$`Interest Paid`
CF\$Depreciation <- dep_schedule
CF\$Flow <- CF\$EBT*(1-r_tax)+CF\$Depreciation-CF\$`Principal Paid`
CF\$Discount <- irr
CF\$PV <- CF\$Flow/(1+CF\$Discount)^CF\$Year
NPV <- sum(CF\$PV)-CF0
if (abs(NPV)<bsp_npv){
bsp_jet <- p_jet
bsp_npv <- abs(NPV)
}
}

bsp_jet
bsp_npv
#if irr=r_eq, BSP of jet 0.761
#if irr=r_eq-r_inf, BSP of jet 0.665

``````

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.