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. BoE continuously compounded annual percentage quotes are converted to annual effective decimal rates using exp(q / 100) - 1 before discount factors are calculated for each 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%.