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[1]+capex*0.6*(1+r_dis)* inflation_factor[2]+capex*0.32* inflation_factor[3]
# Adjust sales for inflation
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
# Adjust expenses for inflation
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[1]+capex*0.6*(1+irr)* inflation_factor[2]+capex*0.32* inflation_factor[3]
# Adjust expenses for inflation
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