parse_for_currency_value <-
function(x) {
value <-
x %>%
readr::parse_number() %>%
formattable::currency()
return(value)
}
parse_for_percentage <-
function(x) {
value <-
x %>%
parse_number()
if (value >= 1) {
value <-
value / 100
}
value <-
value %>%
percent()
return(value)
}
parse_multiple <-
function(x) {
value <-
x %>%
parse_number
return(value)
}
cap_rate_valuation <-
function(cap_rate = .0615,
net_operating_income = "$27,500,000",
cost_of_sale = "5%",
debt_balance = "$350,000,000",
return_wide = T) {
noi <-
net_operating_income %>%
parse_for_currency_value
debt <-
debt_balance %>%
parse_for_currency_value()
pct_cap_rate <-
cap_rate %>%
parse_for_percentage()
pct_sale_cost <-
cost_of_sale %>%
parse_for_percentage()
amountValuationGross <-
noi / cap_rate
amountCostSale <-
-((pct_sale_cost %>% as.numeric) * amountValuationGross)
amountValuationNet <-
amountValuationGross + amountCostSale
amountDebtRepayment <-
-min(amountValuationNet, debt)
amountEquityDistribution <-
-max(0, amountValuationNet + amountDebtRepayment) %>% currency
cash_check <-
!amountValuationGross + amountCostSale + amountDebtRepayment + amountEquityDistribution == 0
if (cash_check) {
stop("Cash waterfall does not tie")
}
value_df <-
tibble(
pctCapRate = pct_cap_rate,
pctCostSale = pct_sale_cost,
amountNetOperatingIncome = noi,
amountDebtBalance = debt,
amountValuationGross,
amountCostSale,
amountValuationNet,
amountDebtRepayment,
amountEquityDistribution
)
if (!return_wide) {
value_df <-
value_df %>%
dplyr::select(-amountValuationNet) %>%
gather(
item,
value,
-c(
pctCapRate,
amountNetOperatingIncome,
amountDebtBalance,
pctCostSale
)
) %>%
mutate(value = value %>% currency(digits = 2)) %>%
suppressWarnings()
}
return(value_df)
}
ebtida_multiple_value <-
function(ebitda_multiple = 10,
ebitda = "$27,500,000",
cost_of_sale = "5%",
debt_balance = "$350,000,000",
return_wide = T) {
ebitda <-
ebitda %>%
parse_for_currency_value
debt <-
debt_balance %>%
parse_for_currency_value()
multipleEBITDA <-
ebitda_multiple %>%
parse_multiple()
pct_sale_cost <-
cost_of_sale %>%
parse_for_percentage()
amountValuationGross <-
ebitda * multipleEBITDA
amountCostSale <-
-((pct_sale_cost %>% as.numeric) * amountValuationGross)
amountValuationNet <-
amountValuationGross + amountCostSale
amountDebtRepayment <-
-min(amountValuationNet, debt)
amountEquityDistribution <-
-max(0, amountValuationNet + amountDebtRepayment) %>% currency
cash_check <-
!amountValuationGross + amountCostSale + amountDebtRepayment + amountEquityDistribution == 0
if (cash_check) {
stop("Cash waterfall does not tie")
}
value_df <-
tibble(
multipleEBITDA,
pctCostSale = pct_sale_cost,
amountEBITDA = ebitda,
amountDebtBalance = debt,
amountValuationGross,
amountCostSale,
amountValuationNet,
amountDebtRepayment,
amountEquityDistribution
)
if (!return_wide) {
value_df <-
value_df %>%
dplyr::select(-amountValuationNet) %>%
gather(item,
value,
-c(
multipleEBITDA,
amountEBITDA,
amountDebtBalance,
pctCostSale
)) %>%
mutate(value = value %>% currency(digits = 2)) %>%
suppressWarnings()
}
return(value_df)
}
#' Residual value, capitalization rate method
#'
#' This function calculates \href{https://en.wikipedia.org/wiki/Residual_value}{residual values}
#' based upon the specified inputs. See \code{\link{calculate_residual_valuation_ebitda_multiples}} for
#' EBITDA multiple method.
#'
#' @param cap_rates vector of capitalization rates in percent or character form
#' @param net_operating_income vector of net operating income in numeric or character numeric/currency form
#' @param cost_of_sale vector of cost of sale in percent or character percent form
#' @param debt_balance vector of anticipated debt balance at sale in numeric or character numeric/currency form
#' @param return_wide \code{TRUE} return wide or \code{FALSE}
#' @import readr dplyr purrr formattable
#' @return a \code{tibble}
#' @family calculation
#' @family residual value calculation
#' @export
#'
#' @examples
#' calculate_residual_valuation_cap_rates(cap_rates = c(.05, .0525, .06, .2),
#' net_operating_income = "$27,500,000", cost_of_sale = "5%",debt_balance = "$350,000,000", return_wide = T)
calculate_residual_valuation_cap_rates <-
function(cap_rates = c(.05, .0525, .06, .2),
net_operating_income = "$27,500,000",
cost_of_sale = "5%",
debt_balance = "$350,000,000",
return_wide = T) {
scenario_matrix <-
expand.grid(
cap_rate = cap_rates,
noi = net_operating_income,
cost_sale = cost_of_sale,
debt = debt_balance,
stringsAsFactors = F
) %>%
as_tibble
scenario_df <-
1:nrow(scenario_matrix) %>%
future_map_dfr(function(x) {
cap_rate_valuation(
cap_rate = scenario_matrix$cap_rate[[x]],
net_operating_income = scenario_matrix$noi[[x]],
cost_of_sale = scenario_matrix$cost_sale[[x]],
debt_balance = scenario_matrix$debt[[x]],
return_wide = T
)
}) %>%
mutate(idScenario = 1:n()) %>%
dplyr::select(idScenario, everything())
scenario_df <-
scenario_df %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^pct")) %>% names,
funs(. %>% percent(digits = 2))) %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^amount")) %>% names,
funs(. %>% currency(digits = 2)))
if (!return_wide) {
scenario_df <-
scenario_df %>%
dplyr::select(-amountValuationNet) %>%
gather(
item,
value,
-c(
idScenario,
pctCapRate,
amountNetOperatingIncome,
amountDebtBalance,
pctCostSale
)
) %>%
mutate(value = value %>% currency(digits = 2)) %>%
suppressWarnings()
}
return(scenario_df)
}
#' Residual value, EBITDA multiple method
#'
#' This function calculates \href{https://en.wikipedia.org/wiki/Residual_value}{residual values}
#' based upon the specified inputs. See \code{\link{calculate_residual_valuation_cap_rates}} for
#' capitalization rate method.
#'
#' @param ebitda_multiples vector of EBITDA multiples in numeric or character
#' @param ebitda vector of EBITDA in numeric or character numeric/currency form
#' @param cost_of_sale vector of cost of sale in percent or character percent form
#' @param debt_balance vector of anticipated debt balance at sale in numeric or character numeric/currency form
#' @param return_wide return data in wide or long form
#' @import readr dplyr purrr formattable
#' @return a \code{tibble}
#' @export
#' @family calculation
#' @family residual value calculation
#' @examples
#' calculate_residual_valuation_ebitda_multiples(ebitda_multiples = c(5, 10, 15, 20), ebitda = "$27,500,000", cost_of_sale = "5%", debt_balance = "$350,000,000", return_wide = T)
calculate_residual_valuation_ebitda_multiples <-
function(ebitda_multiples = c(5, 10, 15, 20),
ebitda = "$27,500,000",
cost_of_sale = "5%",
debt_balance = "$350,000,000",
return_wide = T) {
scenario_matrix <-
expand.grid(
ebitda_multiple = ebitda_multiples,
ebitda = ebitda,
cost_sale = cost_of_sale,
debt = debt_balance,
stringsAsFactors = F
) %>%
as_tibble
scenario_df <-
1:nrow(scenario_matrix) %>%
future_map_dfr(function(x) {
ebtida_multiple_value(
ebitda_multiple = scenario_matrix$ebitda_multiple[[x]],
ebitda = scenario_matrix$ebitda[[x]],
cost_of_sale = scenario_matrix$cost_sale[[x]],
debt_balance = scenario_matrix$debt[[x]],
return_wide = T
)
}) %>%
mutate(idScenario = 1:n()) %>%
dplyr::select(idScenario, everything())
scenario_df <-
scenario_df %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^pct")) %>% names,
funs(. %>% percent(digits = 2))) %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^amount")) %>% names,
funs(. %>% currency(digits = 2)))
if (!return_wide) {
scenario_df <-
scenario_df %>%
dplyr::select(-amountValuationNet) %>%
gather(item,
value,
-c(
multipleEBITDA,
amountEBITDA,
amountDebtBalance,
pctCostSale
)) %>%
mutate(value = value %>% currency(digits = 2)) %>%
suppressWarnings()
}
return(scenario_df)
}
post_money_valuation <-
function(pre_money_valuation = "$45,000,000",
percent_sold = "10%") {
options(scipen = 999999)
pre_money <-
pre_money_valuation %>%
parse_for_currency_value()
pct_sold <-
percent_sold %>%
parse_for_percentage() %>%
as.numeric
new_capital <-
(pct_sold * pre_money) %>% currency
total_val <-
pre_money + new_capital
valuation_data <-
tibble(valuationPreMoney = pre_money,
amountCapitalInvestment = new_capital) %>%
mutate(
pctOwnershipExistingShareholders = (pre_money / total_val) %>% percent,
pctOwnershipNewInvestment = (new_capital / total_val) %>% percent
)
return(valuation_data)
}
#' Post money valuation splits
#'
#' This function calculates the the post-money
#' ownership splits of an entity for specified inputs.
#'
#' @param pre_money_valuation vector of of valuations in numeric or character
#' @param percent_sold vector of of amount of business sold in percent or character percent form
#' @param return_wide Return data in wide or long form
#' @import readr dplyr purrr formattable tidyr
#' @return a \code{tibble()}
#' @family calculation
#' @family venture capital
#' @export
#'
#' @examples
#' calculate_valuation_post_money(pre_money_valuation = "$45,000,000", percent_sold = "10%", return_wide = T)
calculate_valuation_post_money <-
function(pre_money_valuation = "$45,000,000",
percent_sold = "10%",
return_wide = T) {
scenario_matrix <-
expand.grid(
pre_money_valuation = pre_money_valuation,
percent_sold = percent_sold,
stringsAsFactors = F
) %>%
as_tibble
scenario_df <-
1:nrow(scenario_matrix) %>%
future_map_dfr(function(x) {
post_money_valuation(
pre_money_valuation = scenario_matrix$pre_money_valuation[[x]],
percent_sold = scenario_matrix$percent_sold[[x]]
)
}) %>%
mutate(idScenario = 1:n()) %>%
dplyr::select(idScenario, everything())
scenario_df <-
scenario_df %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^pct")) %>% names,
funs(. %>% percent(digits = 2))) %>%
mutate_at(.vars =
scenario_df %>% dplyr::select(dplyr::matches("^amount|valuation")) %>% names,
funs(. %>% currency(digits = 2)))
if (!return_wide) {
scenario_df <-
scenario_df %>%
dplyr::select(-dplyr::matches("pct")) %>%
gather(item, value, -c(idScenario)) %>%
mutate(value = value %>% currency(digits = 2)) %>%
suppressWarnings()
}
return(scenario_df)
}
#' Share proceeds
#'
#' This function calculates
#' distributable proceeds from
#' a share sale based upon specified inputs.
#'
#' @param price numeric price
#' @param shares share count
#'
#' @return numeric \code{vector}
#' @export
#' @family calculation
#' @importFrom formattable currency
#' @examples
#' calculate_share_proceeds(price = 9, shares = 150000)
calculate_share_proceeds <-
function(price = 10, shares = 1000000) {
proceeds <-
(price * shares) %>% currency()
return(proceeds)
}
calculate_basis <-
function(purchase_price = "$10,000,000",
capitalized_acquisition_costs = "$300,0000",
capital_investment = "$1,200,000") {
options(scipen = 999)
if (purchase_price %>% is_null) {
stop("Please enter a purchase price")
}
amountPurchasePrice <-
purchase_price %>%
parse_for_currency_value()
if (!capitalized_acquisition_costs %>% is_null) {
amountCapitalizedCosts <-
capitalized_acquisition_costs %>%
parse_for_currency_value()
} else {
amountCapitalizedCosts <-
0
}
if (!capitalized_acquisition_costs %>% is_null) {
amountCapitalInvestment <-
capital_investment %>%
parse_for_currency_value()
} else {
capital_investment <-
0
}
basis_df <-
tibble(amountPurchasePrice,
amountCapitalInvestment,
amountCapitalizedCosts) %>%
mutate(amountBasis = amountPurchasePrice + amountCapitalInvestment + amountCapitalizedCosts)
return(basis_df)
}
calculate_capitalization <-
function(purchase_price = "$9,700,000",
capitalized_acquisition_costs = "$300,000",
capital_investment = "$0",
loan_to_cost = .7,
borrow_capital_investment = T,
borrow_capitalized_costs = F,
leverage_threshold = .95) {
if (loan_to_cost %>% is_null()) {
stop("Please enter a loan to cost even if it is zero")
}
if (leverage_threshold %>% is_null()) {
leverage_threshold <-
1
}
pct_ltc <-
loan_to_cost %>% parse_for_percentage()
if (pct_ltc > leverage_threshold) {
leverage_message <-
"\nDon't be a reckless idiot, remember what happend to Lehman Brothers???\nDon't know Lehman Brothers, Google it\n" %>%
paste0(
pct_ltc,
' is unprudent leverage\nA more reasonable amount of leverage is: ',
leverage_threshold %>% formattable::percent(),
'\nChange your leverage assumptions and try again'
)
stop(leverage_message)
}
basis_df <-
calculate_basis(
purchase_price = purchase_price,
capitalized_acquisition_costs = capitalized_acquisition_costs,
capital_investment = capital_investment
)
if (borrow_capitalized_costs) {
debt_basis <-
basis_df %>% mutate(amountDebtBasis = amountPurchasePrice + amountCapitalizedCosts) %>%
.$amountDebtBasis
} else {
debt_basis <-
basis_df$amountPurchasePrice
}
loan_proceeds <-
debt_basis * pct_ltc
if (borrow_capital_investment) {
loan_proceeds <-
loan_proceeds + basis_df$amountCapitalInvestment
}
capital_stack_df <-
basis_df %>%
mutate(
amountLoanProceeds = -loan_proceeds,
amountEquity = -(amountBasis + amountLoanProceeds)
)
return(capital_stack_df)
}
get_data_monthly_periods <-
function(start_date = "2016-06-01",
term_years = 25,
term_months = 0) {
periods <-
term_years * 12 + term_months
periods <-
0:periods
start_date <-
start_date %>% lubridate::ymd %>% as.Date()
get_end_of_period <-
function(period = 0) {
if (period == 0) {
period_date <-
start_date %m+% months(period) %>%
as.character() %>%
as.Date()
}
if (period == 1) {
period_date <-
start_date %m+% months(0) %>%
timeDate::timeLastDayInMonth %>%
as.character() %>%
as.Date()
}
if (period > 1) {
period_date <-
start_date %m+% months(period - 1) %>%
timeDate::timeLastDayInMonth %>%
as.character() %>%
as.Date()
}
period_df <-
tibble(idPeriod = period, datePeriod = period_date)
return(period_df)
}
all_periods <-
periods %>%
future_map(function(x) {
get_end_of_period(period = x)
}) %>%
compact %>%
bind_rows()
all_periods <-
all_periods %>%
mutate(yearPeriod = ifelse(idPeriod == 0, 0, (idPeriod %/% 12) + 1)) %>%
dplyr::select(idPeriod, yearPeriod, everything())
return(all_periods)
}
pmt <-
function (r, n, pv, fv, type = 0) {
if (type != 0 && type != 1) {
print("Error: type should be 0 or 1!")
}
else {
pmt <- (pv + fv / (1 + r) ^ n) * r / (1 - 1 / (1 + r) ^ n) * (-1) *
(1 + r) ^ (-1 * type)
return(pmt)
}
}
#' Loan payment calculation
#'
#' Calculate loan payment information
#' based upon specified parameters.
#'
#' @param loan_start_date date loan starts in year, month, date form
#' @param amount_initial_draw amount of initial draw
#' @param is_interest_only \code{TRUE} has interest only periods
#' @param interest_only_periods count of interest only periods
#' @param interest_rate interest rate in character or numeric form
#' @param is_actual_360 \code{TRUE} interest calculated on actual/360 basis
#' @param amortization_years amortization in years
#' @param amortization_months amortization additional months
#' @param term_years term of the loan in years
#' @param term_months term of the loan additional months
#' @param pct_loan_fee loan fee in percent
#' @param balloon_year year loan baloons
#' @param override_monthly_interest \code{TRUE} override
#' @param interest_reserve_period periods of interest reserve
#' @param balloon_month month loan baloons
#' @param return_annual_summary \code{TRUE} returns annual summary
#'
#' @return a \code{tibble}
#' @export
#' @family leveraged finance calculation
#' @family calculation
#' @examples
#' calculate_loan_payment(loan_start_date = "2016-06-01", amount_initial_draw = 3000, is_interest_only = F, interest_only_periods = 24,
#' interest_rate = "10%", is_actual_360 = TRUE, amortization_years = 10, amortization_months = 0,
#' term_years = 10, term_months = 0, pct_loan_fee = 0, balloon_year = 10,
#' override_monthly_interest = FALSE, interest_reserve_period = 0, balloon_month = 0,
#' return_annual_summary = F)
calculate_loan_payment <-
function(loan_start_date = "2016-06-01",
amount_initial_draw = 3000,
is_interest_only = F,
interest_only_periods = 24,
interest_rate = "10%",
is_actual_360 = TRUE,
amortization_years = 10,
amortization_months = 0,
term_years = 10,
term_months = 0,
pct_loan_fee = 0,
balloon_year = 10,
override_monthly_interest = FALSE,
interest_reserve_period = 0,
balloon_month = 0,
return_annual_summary = F) {
options(scipen = 99999)
options(digits = 10)
interest_rate <-
interest_rate %>%
parse_for_percentage()
if (is_actual_360 == T) {
daily_interest <-
interest_rate / 360
net_rate <-
interest_rate / 360 * 365
} else {
daily_interest <-
interest_rate / 365
net_rate <-
interest_rate
}
amortization_periods <-
(amortization_years * 12) + amortization_months
loan_periods <-
(balloon_year * 12) + balloon_month
loan_period_df <-
get_data_monthly_periods(start_date = loan_start_date,
term_years = term_years,
term_months = term_months)
loan_period_df <-
loan_period_df %>%
mutate(
isIO = ifelse(is_interest_only == T &
idPeriod <= interest_only_periods, T, F),
isActiveLoan = ifelse(idPeriod <= loan_periods, T, F),
amountInitialDraw = ifelse(idPeriod == 0, amount_initial_draw, 0),
amountLoanFee = amountInitialDraw * pct_loan_fee
)
loan_period_df <-
loan_period_df %>%
dplyr::filter(isActiveLoan == T)
periods <-
loan_period_df$idPeriod
all_payment_data <-
tibble()
for (period in periods) {
period_index <-
period + 1
datePeriod <-
loan_period_df$datePeriod[period_index]
drawInitial <-
loan_period_df$amountInitialDraw[period_index]
drawAdditional <-
0 # loan_period_df$amountAdditionalDraw[period_index] -- layer in eventully
is_interest_only <-
loan_period_df$isIO[period_index]
periodFee <-
loan_period_df$amountLoanFee[period_index]
if (period == 0) {
month_df <-
tibble(
idPeriod = period,
dateStartPeriod = datePeriod,
dateEndPeriod = datePeriod,
isIO = is_interest_only,
balanceInitial = 0 %>% currency(digits = 2),
amountInitialDraw = drawInitial %>% currency(digits = 2),
amountAdditionalDraw = drawAdditional %>% currency(digits = 2),
paymentInterest = 0 %>% currency(digits = 2),
paymentPrincipal = 0 %>% currency(digits = 2),
amountRepayment = 0 %>% currency(digits = 2),
amountLoanFee = periodFee %>% currency(digits = 2)
) %>%
mutate(balanceEnd = amountInitialDraw + amountAdditionalDraw)
}
if (period > 0) {
initial_balance <-
all_payment_data$balanceEnd[period_index - 1]
total_balance <-
initial_balance + drawInitial + drawAdditional
balance_basis <-
(all_payment_data$amountInitialDraw %>% sum()) +
all_payment_data$amountAdditionalDraw %>% sum()
start_month <-
timeDate::timeFirstDayInMonth(datePeriod) %>% as.Date()
days <-
(datePeriod - start_month) + 1
month_days <-
as.numeric(days, units = 'days')
if (override_monthly_interest == T) {
monthly_interest <-
net_rate / 12
} else {
monthly_interest <-
daily_interest * month_days
}
paymentInterest <-
monthly_interest * (total_balance)
if (interest_reserve_period > 0 &
period <= interest_reserve_period) {
paymentInterest <-
0
}
if (is_interest_only == T) {
paymentTotal <-
paymentInterest
} else {
paymentTotal <-
pmt(
r = monthly_interest,
pv = balance_basis,
fv = 0,
n = amortization_periods
)
}
paymentPrincipal <-
abs(paymentTotal) - paymentInterest
if (period == loan_periods) {
amountRepayment <-
initial_balance + drawInitial + drawAdditional - paymentPrincipal
} else {
amountRepayment <-
0
}
month_df <-
tibble(
idPeriod = period,
dateStartPeriod = start_month,
dateEndPeriod = datePeriod,
isIO = is_interest_only,
balanceInitial = initial_balance %>% currency(digits = 2),
amountInitialDraw = drawInitial %>% currency(digits = 2),
amountAdditionalDraw = drawAdditional %>% currency(digits = 2),
paymentInterest = -paymentInterest %>% currency(digits = 2),
paymentPrincipal = -paymentPrincipal %>% currency(digits = 2),
amountRepayment = -amountRepayment %>% currency(digits = 2),
amountLoanFee = periodFee %>% currency(digits = 2)
) %>%
mutate(
balanceEnd =
balanceInitial + amountInitialDraw + amountAdditionalDraw +
paymentPrincipal + amountRepayment
)
}
all_payment_data <-
month_df %>%
bind_rows(all_payment_data) %>%
arrange((idPeriod))
}
all_payment_data <-
all_payment_data %>%
mutate(
balanceInitial = balanceInitial %>% currency(digits = 2),
amountInitialDraw = amountInitialDraw %>% currency(digits = 2),
amountAdditionalDraw = amountAdditionalDraw %>% currency(digits = 2),
paymentInterest = paymentInterest %>% currency(digits = 2),
paymentPrincipal = paymentPrincipal %>% currency(digits = 2),
amountRepayment = amountRepayment %>% currency(digits = 2),
amountLoanFee = amountLoanFee %>% currency(digits = 2),
balanceEnd = balanceEnd %>% currency(digits = 2)
) %>%
mutate(yearPeriod = ifelse(idPeriod > 0,
((idPeriod - 1) %/% 12) + 1,
0)) %>%
dplyr::select(yearPeriod, everything())
if (return_annual_summary == T) {
all_payment_data <-
all_payment_data %>%
group_by(yearPeriod) %>%
summarise(
dateStartPeriod = min(dateStartPeriod),
dateEndPeriod = max(dateEndPeriod),
amountInitialDraw = sum(amountInitialDraw),
amountAdditionalDraw = sum(amountAdditionalDraw),
paymentInterest = sum(paymentInterest),
paymentPrincipal = sum(paymentPrincipal),
amountRepayment = sum(amountRepayment),
amountLoanFee = sum(amountLoanFee),
cfLoan = (
amountInitialDraw + amountAdditionalDraw + paymentInterest + paymentPrincipal + amountRepayment + amountLoanFee
) %>% currency(digits = 2)
) %>%
mutate(
balanceEnd = cumsum(amountInitialDraw) + cumsum(paymentPrincipal) + cumsum(amountRepayment)
) %>%
ungroup
}
return(all_payment_data)
}
calculate_average_payment <-
function(amount_initial_draw = 3000,
is_interest_only = F,
interest_only_periods = 24,
interest_rate = "10%",
is_actual_360 = T,
amortization_years = 10,
amortization_months = 0,
term_years = 10,
term_months = 0,
pct_loan_fee = 0,
balloon_year = 10,
override_monthly_interest = F,
interest_reserve_period = 0,
balloon_month = 0) {
library(lubridate)
first_of_the_month <-
(ceiling_date((Sys.Date() %m+% months(0)), "month") - days(1)) + 1
pmt_df <-
calculate_loan_payment(
loan_start_date = first_of_the_month,
amount_initial_draw = amount_initial_draw,
is_interest_only = is_interest_only,
interest_only_periods = interest_only_periods,
interest_rate = interest_rate,
is_actual_360 = is_actual_360,
amortization_years = amortization_years,
amortization_months = amortization_months,
term_years = term_years,
term_months = term_months,
pct_loan_fee = pct_loan_fee,
balloon_year = balloon_year,
balloon_month = balloon_month,
return_annual_summary = F
)
pmt_df <-
pmt_df %>%
dplyr::filter(!idPeriod == 0) %>%
group_by(yearPeriod) %>%
summarise(
paymentPrincipal = sum(paymentPrincipal, na.rm = T),
paymentInterest = sum(paymentInterest, na.rm = T)
) %>%
ungroup %>%
summarise(
meanPrincipal = mean(paymentPrincipal, na.rm = T) %>% formattable::currency(),
meanInterest = mean(paymentInterest, na.rm = T) %>% formattable::currency()
) %>%
mutate(meanPayment = meanPrincipal + meanInterest)
return(pmt_df)
}
calculate_leverage_metric <-
function(purchase_price = "$9,700,000",
capitalized_acquisition_costs = "$300,000",
capital_investment = "0",
revenue = "$1,500,000",
expenses = "$115,000",
loan_to_cost = .7,
borrow_capital_investment = F,
borrow_capitalized_costs = T,
leverage_threshold = .95,
is_interest_only = TRUE,
interest_only_periods = 12,
interest_rate = "5%",
is_actual_360 = TRUE,
amortization_years = 30,
amortization_months = 0,
term_years = 10,
term_months = 0,
pct_loan_fee = 0,
balloon_year = 10,
balloon_month = 0,
return_message = F) {
basis_df <-
calculate_capitalization(
purchase_price = purchase_price,
capitalized_acquisition_costs = capitalized_acquisition_costs,
capital_investment = capital_investment,
loan_to_cost = loan_to_cost,
borrow_capital_investment = borrow_capital_investment,
borrow_capitalized_costs = borrow_capitalized_costs
)
revenue_amount <-
revenue %>%
parse_for_currency_value()
expense_amount <-
expenses %>%
parse_for_currency_value()
if (expense_amount > 0) {
expense_amount <-
-expense_amount
}
operating_income <-
revenue_amount + expense_amount
interest_rate <-
interest_rate %>%
parse_for_percentage()
pct_loan_fee <-
pct_loan_fee %>%
parse_for_percentage()
average_pmt <-
calculate_average_payment(
amount_initial_draw = basis_df$amountLoanProceeds %>% abs(),
is_interest_only = is_interest_only,
interest_only_periods = interest_only_periods,
interest_rate = interest_rate,
is_actual_360 = is_actual_360,
amortization_years = amortization_years,
amortization_months = amortization_months,
term_years = term_years,
term_months = term_months,
pct_loan_fee = pct_loan_fee,
balloon_year = balloon_year,
override_monthly_interest = override_monthly_interest,
interest_reserve_period = interest_reserve_period
)
data <-
basis_df %>%
mutate(
amountRevenue = revenue_amount,
amountExpense = expense_amount,
amountEBITDA_NOI = operating_income
) %>%
bind_cols(average_pmt) %>%
mutate(
amountLNCFMean = amountEBITDA_NOI + meanPayment,
pctLeverage = (-amountLoanProceeds / amountBasis) %>% formattable::percent(),
pctMarginEBITDA_NOI = (amountEBITDA_NOI / amountRevenue) %>% formattable::percent(),
pctReturnOnCost = (amountEBITDA_NOI / amountBasis) %>% formattable::percent(),
pctDebtYieldInitial = -(amountEBITDA_NOI / amountLoanProceeds) %>% formattable::percent(),
ratioDSCRMean = (amountEBITDA_NOI / -meanPayment) %>% as.numeric() %>% formattable::digits(3),
pctCashOnCashMean = (amountLNCFMean / -amountEquity) %>% formattable::percent(),
pctReturnOnEquity = ((amountEBITDA_NOI + meanInterest) / -amountEquity) %>% formattable::percent(),
rule72Multiple2x = (72 / (pctCashOnCashMean * 100)) %>% as.numeric()
)
if (return_message) {
metric_message <-
"Basis: " %>%
paste0(
data$amountBasis,
'\n',
'Leverage: ',
data$pctLeverage,
'\n',
'Interest Rate: ',
interest_rate,
'\n',
'Amortization: ',
((amortization_years * 12) + amortization_months),
' periods\n',
'Return on Cost: ',
data$pctReturnOnCost,
'\nCash on Cash: ',
data$pctCashOnCashMean,
"\nReturn on Equity: ",
data$pctReturnOnEquity,
"\nRule of 72: Equity doubles in ",
data$rule72Multiple2x,
' years\n'
)
metric_message %>% cat(fill = T)
}
return(data)
}
#' Leveraged return metrics
#'
#' This function returns data with the outputs
#' from a "back-of-the-envelope" leveraged math calculation
#' that can be used for a quick and dirty underwriting of a
#' leveragd cash flow stream.
#'
#' @param purchase_price vector of purchase prices
#' @param capitalized_acquisition_costs vector of capitalized acquisition costs
#' @param capital_investment vector of capital investment
#' @param revenue vector of revenue amounts
#' @param expenses vector of expenses
#' @param loan_to_cost vector of loan to cost
#' @param interest_rate vector nterest rates
#' @param borrow_capital_investment \code{TRUE} borrow capital investment
#' @param borrow_capitalized_costs \code{TRUE} borrow capitalized costs
#' @param leverage_threshold maximum leverage allowed
#' @param is_interest_only \code{TRUE} has interest only periods
#' @param interest_only_periods count of interest only periods
#' @param is_actual_360 \code{TRUE} is interest calculated on actual 360 basis
#' @param is_actual_360 \code{TRUE} interest calculated on actual/360 basis
#' @param amortization_years amortization in years
#' @param amortization_months amortization additional months
#' @param term_years term of the loan in years
#' @param term_months term of the loan additional months
#' @param pct_loan_fee loan fee in percent
#' @param return_wide \code{TRUE} return data in wide form
#' @param return_message \code{TRUE} return a message after data import
#'
#' @return a \code{tibble}
#' @export
#' @import readr dplyr lubridate stringr purrr tidyr formattable
#' @family calculation
#' @family leveraged finance calculation
#' @examples
#' calculate_leverage_metrics(purchase_price = c("13,000,000", "9,000,000"),
#' capitalized_acquisition_costs = "300,000",
#' capital_investment = "$100,000", revenue = "$1,200,000", expenses = "$400,000",
#' loan_to_cost = .75,interest_rate = .045,borrow_capital_investment = TRUE,
#' borrow_capitalized_costs = TRUE, leverage_threshold = .8, is_interest_only = FALSE,
#' interest_only_periods = 0, is_actual_360 = TRUE, amortization_years = 30,
#' amortization_months = 0, term_years = 10, term_months = 0, pct_loan_fee = 0, return_wide = TRUE,
#' return_message = TRUE)
calculate_leverage_metrics <-
function(purchase_price = 0,
capitalized_acquisition_costs = 0,
capital_investment = 0,
revenue = 0,
expenses = 0,
loan_to_cost = 0,
interest_rate = 0,
borrow_capital_investment = F,
borrow_capitalized_costs = T,
leverage_threshold = .95,
is_interest_only = FALSE,
interest_only_periods = 0,
is_actual_360 = TRUE,
amortization_years = 30,
amortization_months = 0,
term_years = 30,
term_months = 0,
pct_loan_fee = 0,
return_wide = T,
return_message = T) {
variable_matrix <-
expand.grid(
purchase_price = purchase_price,
capitalized_acquisition_costs = capitalized_acquisition_costs,
capital_investment = capital_investment,
revenue = revenue,
expenses = expenses,
loan_to_cost = loan_to_cost,
borrow_capital_investment = borrow_capital_investment,
borrow_capitalized_costs = borrow_capitalized_costs,
leverage_threshold = leverage_threshold,
is_interest_only = is_interest_only,
interest_only_periods = interest_only_periods,
interest_rate = interest_rate,
is_actual_360 = is_actual_360,
amortization_years = amortization_years,
amortization_months = amortization_months,
term_years = term_years,
term_months = term_months,
pct_loan_fee = pct_loan_fee,
stringsAsFactors = F
) %>%
as_tibble()
all_data <-
1:nrow(variable_matrix) %>%
future_map_dfr(function(x) {
calculate_leverage_metric(
purchase_price = variable_matrix$purchase_price[[x]],
capitalized_acquisition_costs = variable_matrix$capitalized_acquisition_costs[[x]],
capital_investment = variable_matrix$capital_investment[[x]],
revenue = variable_matrix$revenue[[x]],
expenses = variable_matrix$expenses[[x]],
loan_to_cost = variable_matrix$loan_to_cost[[x]],
borrow_capital_investment = variable_matrix$borrow_capital_investment[[x]],
borrow_capitalized_costs = variable_matrix$borrow_capitalized_costs[[x]],
leverage_threshold = variable_matrix$leverage_threshold[[x]],
is_interest_only = variable_matrix$is_interest_only[[x]],
interest_only_periods = variable_matrix$interest_only_periods[[x]],
is_actual_360 = variable_matrix$is_actual_360[[x]],
amortization_years = variable_matrix$amortization_years[[x]],
amortization_months = variable_matrix$amortization_months[[x]],
term_years = variable_matrix$term_years[[x]],
term_months = variable_matrix$term_months[[x]],
pct_loan_fee = variable_matrix$pct_loan_fee[[x]],
balloon_year = variable_matrix$term_years[[x]],
balloon_month = variable_matrix$term_months[[x]],
return_message = return_message
) %>%
mutate(idScenario = x) %>%
dplyr::select(idScenario, everything())
})
if (!return_wide) {
all_data <-
all_data %>%
gather(item, value, -c(idScenario))
} else {
all_data <-
all_data %>%
mutate_at(
.vars =
all_data %>% dplyr::select(dplyr::matches("^amount[A-Z]|^mean[A-Z]")) %>% names(),
funs(. %>% formattable::currency(digits = 0))
) %>%
mutate_at(.vars =
all_data %>% dplyr::select(dplyr::matches("^pct[A-Z]")) %>% names(),
funs(. %>% formattable::percent(digits = 2))) %>%
mutate_at(.vars =
all_data %>% dplyr::select(dplyr::matches("^ratio[A-Z]|^rule")) %>% names(),
funs(. %>% formattable::comma(digits = 4)))
}
return(all_data)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.