JL function

Actuarial Excel Addin

The JL function returns the value of a joint lifetime annuity on two lives, with payments ceasing on first death. By default it returns a scalar annuity factor. When the optional argument cash_flow is set to a non-zero value, the function instead returns a spilled yearly cash-flow vector, with each yearly amount multiplied by cash_flow.

See also:JSCF_GetCF_SumByYearCF_SumByRow CF_SumRangePV_CF

* : Required parameter

Parameters
Default
Description
interest *
--
Interest rate, either flat or in VT-style vector form rate1, dur1, rate2, dur2, ....
age1 *
--
Age of the first annuitant.
lx1 *
--
Mortality table for the first annuitant, usually supplied by LX() or LX_H().
age2 *
--
Age of the second annuitant.
lx2 *
--
Mortality table for the second annuitant.
deferred
0
Deferment period, in years, before the first payment.
guarantee
0
Certain period of the annuity, in years, starting after the deferment period.
years paid
120
Payment period, in years, starting after deferment. If omitted, the function uses the full lifetime horizon.
indexation
0
Indexation rate, either flat or vector-based.
first age index
age1 + 1 + deferred
Age of the first annuitant at which indexation starts.
fraction first indexation
1
Fraction, between 0 and 1, applied to the first indexation.
frequency
12
Number of payments per year.
due
TRUE
TRUE for payments at the beginning of each period, FALSE for payments at the end.
qx-pre1
TRUE
TRUE to apply mortality discount on the first annuitant during deferment, FALSE otherwise.
qx-pre2
TRUE
TRUE to apply mortality discount on the second annuitant during deferment, FALSE otherwise.
continuation_rate
0
Rate of pension continuation after first death.
guarantee rate
1
Rate of payment of the guaranteed pension if both annuitants are dead.
cash_flow
0
If 0, returns the annuity factor. If non-zero, returns the yearly cash-flow vector and multiplies each yearly amount by cash_flow.

Example
Result
JL({0.03}, 65, B2#, 62, C2#)
Returns the scalar joint-life annuity factor for lives aged 65 and 62.
JL({0.03}, 65, B2#, 62, C2#, cash_flow=1)
Returns a spilled yearly vector of unit joint-life cash flows.
JL({0.03}, 65, B2#, 62, C2#, guarantee=5, continuation_rate=0.6, cash_flow=2500)
Returns yearly expected cash flows for a pension amount of 2500, scaled directly in the output vector.

Notes
Description
Two output modes
The function returns a scalar when cash_flow=0 and a spilled vector when cash_flow<>0.
Scaling
In cash-flow mode, the yearly vector is already multiplied by the supplied cash_flow value.
Mortality discount
If qx-pre1 or qx-pre2 applies during deferment, that adjustment is reflected in both the scalar result and the yearly cash-flow vector.
Helper functions
The spilled yearly output can be passed directly to sumcashflowrange, sumcashflows, averagecashflows, and PV_CF.
Actuarial Excel Addin: JL function

JL function

Actuarial Excel Addin

The JL function returns the value at Age1/Age2 of a joint and life annuity ceasing (or reducing ...) on the first death of $1 of pension per year starting in Deferred years, discounted with interest and mortality and payable:

See also JS to calculate a single life annuity or an annuity ceasing on second death.

* : Required parameter

Parameters
Default
Description
Interest *
--
Interest rate(s): single rate or vector of rates
Age1 *
--
Age of annuitant at valuation, fraction allowed
LX1 *
--
LX or LX_aea function: annuitant's mortality
Age2 *
--
Age of co-annuitant at valuation date, fraction allowed
LX2 *
--
LX or LX_aea function: co-annuitant's mortality
Deferred
0
Deferment of first annuity payment (in years), fraction allowed
Guarantee
0
Payments guaranteed (in years), fraction allowed
Years paid
--
Period of payments (in years), by default, ends at age 120, fraction allowed
Indexation
0 %
Indexation rate(s): single rate or vector of rates
First age index
--
First age annuity is indexed (annuitant's age), by default, starts at Age1 + Deferred + 1, fraction allowed
Fraction first indexation
1
Fraction, between 0 and 1, applied to the first indexation
Frequency
12
Number of payments per year, must be an integer, e.g.: 1, 12, 24, 26, 52
Due
True
False: first payment further deferred by 1/Frequency (in years)
Qx-pre 1
True
True: Mortality of annuitant during deferment period
Qx-pre 2
True
True: Mortality of co-annuitant during deferment period
Continuarion rate
0
Portion of pension continuing after first death (between 0 and 1).
Guarantee rate
1
Portion of pension guaranteed (between 0 and 1).