-
Notifications
You must be signed in to change notification settings - Fork 180
/
Copy pathexcel-financial-math-functions.R
190 lines (163 loc) · 6.62 KB
/
excel-financial-math-functions.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
#' Excel Financial Math Functions
#'
#' @description
#' __Excel financial math functions__ are designed to easily calculate Net Present Value ([NPV()]),
#' Future Value of cashflow ([FV()]), Present Value of future cashflow ([PV()]), and more.
#'
#' These functions are designed to help users coming from an __Excel background__.
#' Most functions replicate the behavior of Excel:
#' - Names are similar to Excel function names
#' - By default, missing values are ignored (same as in Excel)
#'
#'
#'
#' @param cashflow Cash flow values. When one value is provided, it's assumed constant cash flow.
#' @param rate One or more rate. When one rate is provided it's assumed constant rate.
#' @param nper Number of periods. When `nper`` is provided, the cashflow values and rate are assumed constant.
#' @param pv Present value. Initial investments (cash inflows) are typically a negative value.
#' @param fv Future value. Cash outflows are typically a positive value.
#' @param pmt Number of payments per period.
#' @param type Should payments (`pmt`) occur at the beginning (`type = 0`) or
#' the end (`type = 1`) of each period.
#'
#' @return
#' - Summary functions return a single value
#'
#' @details
#' __Net Present Value (NPV)__
#' Net present value (NPV) is the difference between the present value of cash inflows and
#' the present value of cash outflows over a period of time. NPV is used in capital budgeting
#' and investment planning to analyze the profitability of a projected investment or project.
#' For more information, see [Investopedia NPV](https://www.investopedia.com/terms/n/npv.asp).
#'
#' __Internal Rate of Return (IRR)__
#' The internal rate of return (IRR) is a metric used in capital budgeting to estimate the
#' profitability of potential investments. The internal rate of return is a discount rate
#' that makes the net present value (NPV) of all cash flows from a particular project equal
#' to zero. IRR calculations rely on the same formula as NPV does.
#' For more information, see [Investopedia IRR](https://www.investopedia.com/terms/i/irr.asp).
#'
#' __Future Value (FV)__
#' Future value (FV) is the value of a current asset at a future date based on an assumed
#' rate of growth. The future value (FV) is important to investors and financial planners
#' as they use it to estimate how much an investment made today will be worth in the future.
#' Knowing the future value enables investors to make sound investment decisions based on
#' their anticipated needs. However, external economic factors, such as inflation, can adversely
#' affect the future value of the asset by eroding its value.
#' For more information, see [Investopedia FV](https://www.investopedia.com/terms/f/futurevalue.asp).
#'
#' __Present Value (PV)__
#' Present value (PV) is the current value of a future sum of money or stream of cash flows given a
#' specified rate of return. Future cash flows are discounted at the discount rate, and the higher
#' the discount rate, the lower the present value of the future cash flows. Determining the
#' appropriate discount rate is the key to properly valuing future cash flows, whether they be earnings
#' or obligations. For more information, see [Investopedia PV](https://www.investopedia.com/terms/p/presentvalue.asp).
#'
#' __Payment (PMT)__
#' The Payment [PMT()] function calculates the payment for a loan based on constant payments and a constant interest rate.
#'
#' __Rate (RATE)__
#' Returns the interest rate per period of a loan or an investment.
#' For example, use 6%/4 for quarterly payments at 6% APR.
#'
#' @examples
#'
#' NPV(c(-1000, 250, 350, 450, 450), rate = 0.05)
#'
#' IRR(c(-1000, 250, 350, 450, 450))
#'
#' FV(rate = 0.05, nper = 5, pv = -100, pmt = 0, type = 0)
#'
#' PV(rate = 0.05, nper = 5, fv = -100, pmt = 0, type = 0)
#'
#' PMT(nper = 20, rate = 0.05, pv = -100, fv = 0, type = 0)
#'
#' RATE(nper = 20, pmt = 8, pv = -100, fv = 0, type = 0)
#'
#' @name excel_financial_math_functions
#' @rdname excel_financial_math_functions
#' @export
NPV <- function(cashflow, rate, nper = NULL) {
validate_numericish(cashflow)
validate_numericish(rate)
# Case when cash outflow (initial investment) is present
period <- 1:length(cashflow)
if (cashflow[[1]] < 0) {
# Cash outflow
period <- period - 1
}
# Case when nper is present
if (!is.null(nper)) {
if (length(cashflow) > 1)
warning("NPV(): Using nper with more than one cashflow value. Only first value being used. You probably want to use nper = NULL.", call. = FALSE)
period <- 1:nper
cashflow <- cashflow[[1]]
rate <- rate[[1]]
}
# Perform NPV
npv <- tibble::tibble(
cashflow = cashflow,
rate = rate,
period = period
) %>%
dplyr::summarise(npv = sum(cashflow / (1 + rate)^period )) %>%
dplyr::pull(npv)
return(npv)
}
#' @rdname excel_financial_math_functions
#' @export
IRR <- function(cashflow) {
# Case when cash outflow (initial investment) is not present
if (cashflow[[1]] > 0) {
stop("IRR(): cashflow[1] is positive. Initial investment must be a negative cashflow to calculate IRR.")
}
# Setup IRR function to optimize
starting_value <- 0.10
irr_fun <- function(r, x){
( sum(x / (1 + r)^{0:(length(x)-1)}) )^2
}
# Optimize IRR
result <- stats::optim(
par = starting_value,
fn = irr_fun,
x = cashflow,
method = "Brent",
lower = -1000000,
upper = 1000000)
return(result$par)
}
#' @rdname excel_financial_math_functions
#' @export
FV <- function(rate, nper, pv = 0, pmt = 0, type = 0) {
(-1 * pv * (1 + rate)^nper) + (-1 * ( pmt / rate * ((1 + rate)^nper - 1) ) * (1 + rate)^type)
}
#' @rdname excel_financial_math_functions
#' @export
PV <- function(rate, nper, fv = 0, pmt = 0, type = 0) {
(-1 * fv / (1 + rate)^nper) + (-1 * (pmt / rate * (1 - 1 / (1 + rate)^nper))*(1 + rate)^type)
}
#' @rdname excel_financial_math_functions
#' @export
PMT <- function(rate, nper, pv, fv = 0, type = 0) {
( pv + fv / (1 + rate)^nper ) * rate/ (1 - 1 / (1 + rate)^nper) * (-1) * (1 + rate)^(-1 * type)
}
#' @rdname excel_financial_math_functions
#' @export
RATE <- function(nper, pmt, pv, fv = 0, type = 0) {
# Setup rate function to optimize
rate_fun <- function(r, nper, pmt, pv, fv, type){
FV(rate = r, nper = nper, pv = pv, pmt = pmt, type = type) - fv
}
# Find rate
result <- stats::uniroot(
f = rate_fun,
nper = nper,
pmt = pmt,
pv = pv,
fv = fv,
type = type,
lower = 1e-6,
upper = 1e6,
)
return(result$root)
}