Parameters
Default
Description
cash_flow_array *
--
Yearly cash-flow vector to be discounted.
discount_rate *
--
Discount rate information used by the selected method. For spot methods, provide the curve date. For method curve, provide the output of curve_build.
method
flat
- flat = flat annual rate,
- vector = VT-style vector such as rate1, dur1, rate2,
- spot-uk = UK (Bank of England) nominal spot curve selected by year and month,
- spot-cia = CIA (Fiera Capital) nominal spot curve selected by exact date,
- curve = output of curve_build.
payment_timing
0.5
Timing of the payment within each year, expressed between 0 for payment at start of year and 1 for payment at end of year.
PV_CF(A2#, 0.04)
Discounts the yearly cash-flow vector in A2# using a flat 4% annual effective rate and the default mid-year timing.
PV_CF(A2#, {0.03,10,0.04}, "vector")
Discounts the vector using VT-style discounting: 3% for 10 years, then 4% thereafter.
PV_CF(A2#, DATE(2024,9,30), "spot-uk", 0.25)
Uses the UK nominal spot curve for September 2024 (matched by year and month) and derives year-by-year discount rates from that curve.
PV_CF(A2#, DATE(2024,9,30), "spot-cia", 0.75)
Uses the Canadian nominal spot curve from worksheet spot-cia in Aubin Stat.xlsm for the exact date and derives year-by-year discount rates from that curve.
PV_CF(A2#, curve_build(...), "curve")
Uses a curve object returned by curve_build and derives year-by-year discount rates at durations i + payment_timing.
flat
The first supplied rate is applied to all policy years.
vector
The discounting follows the same term-structure convention as the add-in VT function.
spot-uk
The function reads UK nominal spot rates (Bank of England) for the requested year and month and converts them into discount factors by payment timing and duration.
spot-cia
The function reads CIA (Fiera Capital) nominal spot rates from worksheet spot-cia in Aubin Stat.xlsm for the exact date and converts them into discount factors by payment timing and duration.
curve
The function reads durations and spot rates from a curve_build payload (2-row or transposed 2-column) and derives discount factors by payment timing and duration.
Payment timing
With the default value 0.5, each yearly cash flow is discounted using a mid-year timing assumption.
Validation
The function returns an Excel number error if method is not flat, vector, spot_uk/spot-uk, spot_cia/spot-cia, or curve, if payment_timing is outside 0 to 1, or if any resolved discount rate is less than or equal to -100%.