3 assignments
usa94
WorkshopSix-TimeValueofMoneyFormulasCheatSheet.docxDISCOUNTED CASH FLOW VALUATION
Useful Formulas - Mathematical
The general form is: FV = C0×(1 + r)
where r is the interest rate per period
C0 (also called PV) is the value at period 0
FV (also called Ct+T) is the value at period t+T
PV = FV / (1 + r) = C1 / (1 + r)
Compounding solves for the value at the end of the investment duration (FV), and discounting solves for the value at the beginning of the investment duration (PV).
PV of future amount in t periods at r is: PV = FV [1 / (1 + r)T]
Finding the number of periods: T = ln(FV / PV) / ln(1 + r)
Finding the needed interest rate: r = (FV / PV)1/T – 1
Rule of 72 – the time to double your money (FV / PV = 2.00) is approximately (72 / r%) periods.
For multiple compounding time periods: FV = C0 × (1 + r/m)m×T
Effective Annual Interest Rate EAR = [1 + (quoted rate)/m]m – 1
where m is the number of periods per year (semi-annual = 2, monthly = 12)
For continuous compounding: FV = C0 × erT
where e is a constant, transcendental number equal to approximately 2.718
The EAR of a continuously compounded investment is: EAR = er – 1
Perpetuity – Series of level cash flows forever: PV = C / r
Growing Perpetuity - A stream of cash flows that grows at a constant rate forever: PV = C1 / (r-g)
The present value of an ordinary annuity of $C per period for T periods: PV = C[1 – 1/(1 + r)T] / r
Finding the future value of an ordinary annuity: FV = C[(1 + r)T– 1] / r
DISCOUNTED CASH FLOW VALUATION
Useful Functions – Excel Syntax
The variables used most often in Excel are:
rate = The discount / interest rate to be applied
nper = The number of periods
pmt = The regular, level, repeating payment in each period (the annuity payments)
fv = The future value (a single lump sum at the end of periods)
pv = The present value (a single lump sum at the beginning of periods)
In addition, sometimes these variables are needed:
type = Determines if the calculations are based on payments occurring at the beginning
(an annuity due) or the end (ordinary annuity) of the periods.
Set type to "1" to specify an annuity due, or "0" to specify an ordinary annuity.
If not used, the function assumes a zero value and an ordinary annuity.
guess = A guess at what the rate will be calculated as. Helps Excel with the iterative calculations necessary for some situations, but is optional and usually not required.
values = A list of cash flows to be evaluated. Should always be entered in order, and there can't be any blank cells in the series (zeros are OK).
All Excel functions start with an equal sign “=” or a plus sign “+”.
Brackets [like this] indicate an optional variable.
To determine the Present Value of a single lump sum or annuity:
=PV(rate, nper, pmt, [fv], [type])
To determine the Future Value of a single lump sum or annuity:
=FV(rate, nper, pmt, [pv], [type])
To determine the number of periods between a present and future value:
=NPER(rate, pmt, pv, [fv], [type])
To determine the rate required to reach a future value from a given present value:
=RATE(rate, pmt, pv, [fv], [type], [guess])
To determine an annuity payment required to equate to given present and future values:
=PMT(rate, nper, pv, [fv], [type])
To determine the Internal Rate of Return (IRR) of a given set of cash flows:
=IRR(values, [guess])
Note the cash flow values must be entered in order as a string, starting with Year 0
To determine the Net Present Value (NPV) of a given set of cash flows:
= NPV(rate, value1, [value2], [value3]…)
Cash flow values must be entered in order, individually or as a string, starting with Year 1
T
T
r
C
r
C
r
C
C
NPV
)
1
(
)
1
(
)
1
(
2
2
1
1
0
+
+
+
+
+
+
+
-
=
L
ú
ú
û
ù
ê
ê
ë
é
÷
ø
ö
ç
è
æ
+
+
-
-
T
r
g
g
r
C
1
1
1
1
