Excel Functions in EAC ToolsExcel Functions in EAC Tools\Joint life functionsJoint life functions\Joint life short cut functionsJoint life short cut functions\PVJLPPA

PVJLPPA

PVJLPPA calculates the present value of a joint life annuity based on so-called PPA assumptions for interest (3 segment rates) and mortality (annuitant, non-annuitant, combined, applicable).

Syntax

PVJLPPA (AnnuityCode, Percentage, CurrentAgeX, CurrentAgeY, CommencementAge, InterestRate1, InterestRate2, InterestRate3, CalcYear, MortTableType, SexX, SexY, [YearsCertain], [Frequency], [COLA], [CalculationMethod], [NoPreRetMort], [StopAge], [AnnTiming], [YrsCertOption])

Parameter Name

Description

AnnuityCode

Type of joint life annuity to evaluate:

Annuity Code

Type of Calculation

Payment while
only
𝑥 is alive

Payment while
only
𝑦 is alive

Payment while
both
𝑥 and 𝑦 are alive

0

joint annuity

0

0

1

1

contingent joint & survivor annuity

1

Percentage

1

2

joint & survivor annuity

Percentage

Percentage

1

3

pop-up annuity

𝐵

Percentage

1

4

reversionary annuity

1

0

0

“x”

single life annuity to 𝑥

1

0

1

“y”

single life annuity to 𝑦

0

1

1

“xy”

joint annuity

0

0

1

For a pop-up annuity, 𝐵 is calculated as follows:

Percentage

The joint percentage payable to the survivor according to the AnnuityCode. May be either a percent or a number, e.g. 50% or .5.

Note: A value that is 10 or greater (i.e. 1000%) will be divided by 100, e.g. if you enter 10 it will be treated as 10%; if you enter 50 it will be treated as 50%.

CurrentAgeX

Current age of the primary annuitant 𝑥 (i.e. the age at the valuation date). May be provided as an integer, or as a fractional amount (e.g. years & months).

CurrentAgeY

Current age of the secondary annuitant 𝑦 (i.e. the age at the valuation date). May be provided as an integer, or as a fractional amount (e.g. years & months).

CommencementAge

Age of primary annuitant 𝑥 at benefit commencement.

·       If CommencementAge > CurrentAgeX then it will be valued as a deferred annuity.

·       If CommencementAge is missing or zero, then CommencementAge is assumed to be = CurrentAgeX and it will be valued as an immediate annuity

InterestRate1

Segment rate 1 -- to discount payments made during the first 5 years (years 1 to 5). Must be a single number.

Note: A value that is greater than 25% (i.e. 0.25) will be divided by 100; e.g. if you enter 2.75 it will be treated as .0275 or 2.75%.

InterestRate2

Segment rate 2 -- to discount payments made during the next 15 years (years 6 to 20).

InterestRate3

Segment rate 3 -- to discount payments made after the first 20 years (years 21 and later).

MortTableType

The type of static mortality table to use:

Type

Description

“ann”

annuitant table

“non”

non-annuitant table

“com” (or “small”)

optional combined table for small plans

“app”

applicable unisex table for distributions subject to §417(e)(3)

 

SexX

Primary annuitant sex: “m” or “f”; not used if type of table is “Applicable Table” because this is a unisex table.

SexY

Joint annuitant sex: “m” or “f”; not used if type of table is “Applicable Table” because this is a unisex table.

YearsCertain

Number of years guaranteed payments. May be an integer, or a factional amount (e.g. years & months).
Optional, default = 0

Frequency

Number of payments per year:
Optional, default = 12

Frequency

Description

1

annual

2

semi-annual

4

quarterly

6

bi-monthly

12

monthly

99

continuous

 

COLA

Periodic cost-of-living adjustment applied to the benefit amount.
Optional, default = 0
Rates can be defined using these COLA definitions
.

CalculationMethod

Method of calculation
Optional, default = 0

Method

Calculation
Approach

How to Handle

Fractional Ages

Frequency of Payments

0

discounted cash flow

interpolate on whole ages

Woolhouse

1

discounted cash flow

exact

exact

2

discounted cash flow

exact

Woolhouse

8

commutation functions

interpolate on 𝑙𝑥

Woolhouse

9

commutation functions

interpolate on 𝐷𝑥

Woolhouse

 

NoPreRetMort

The defines the application of pre-retirement mortality for a deferred annuity.
Optional, default = 0.

NoPreRetMort

Description

0

pre-ret mortality for 𝑥 and 𝑦

1

none

2

for 𝑥, none for 𝑦

 

StopAge

Age when the annuity benefit stops -- no payment at this age.
Optional, default = to end of the mortality table.

AnnTiming

Defines when the payments are made in the period.
Optional, default = 0.

AnnTiming

Description

0

beginning of period

1

end of period

0 to 1

fraction of period, e.g. .5 means paid in middle of the period

 

YrsCertOption

This is to select the method of calculation for the certain portion of the annuity when using CalculationMethod =0. It does not apply for other methods. This is used only if YearsCertain not a whole number.
Optional, default = 0.

YrsCertOption

Description

0

interpolate on whole numbers

1

exact certain period + deferred annuity