Asset/Liabilities

AnalysisBy: James Regan, Kelsey Hopper, Nolan

Dowd, Brett Edelbeck, Patrick BrodesserAdvisors: Tom McCallum and Albert Cohen

Overview

Changes We’ve Made MERS Overview Assets and Liabilities Predictions Future Groups Comments, Concerns, Questions

Changes We’ve made!

Introduced more historical data into model Strengthens our analysis and shows us

previously unseen information

New form of testing, using time series analysis

Explored several more options and looked at patterns/trends to help our predictions

MERS DATA

Using the appendix(MERS) and pension data, we matched most of the asset/liability values in the MERS Report. (See Excel doc)

To find amount of benefits paid out(payroll), used formula: Benefit Multiplier x FAC x Years of Service +(80% max FAC)

Difficulties matching valuation assets and payroll to MERS report

Asset and Liabilities Predictions

Predicted both assets and liabilities using a non-linear regression model

Gathered estimates from both models and took their ratio(assets/liabilities) to obtain a predicted funded ratio

1978 1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010 2012 2014 2016 2018 20200

10,000,00020,000,00030,000,00040,000,00050,000,00060,000,00070,000,00080,000,00090,000,000

100,000,000110,000,000120,000,000130,000,000140,000,000150,000,000160,000,000170,000,000

Assets vs. Liabilities(1979-2011)

Act. Acrr. Liabilities

Polynomial (Act. Acrr. Liabilities)

Valuation Assets

Polynomial (Valuation Assets)

Equations for Lines• assets= -6,421.306year^3 + 38,440,308.951year^2 - 76,701,416,415.023year + 51,012,118,586,785.500

• R² = 0.995

• liabilities = -4,701.535year^3 + 28,227,540.619year^2 - 56,485,383,617.588year + 37,672,974,716,973.600

• R² = 0.999

1996 1998 2000 2002 2004 2006 2008 2010 2012 2014 2016 2018 202050,000,000

65,000,000

80,000,000

95,000,000

110,000,000

125,000,000

140,000,000

155,000,000

170,000,000

185,000,000

200,000,000

215,000,000

Assets vs. Liabilities(1997-2011) w/ order3 assets and simple liabilities trend

Act. Acrr. Liabilities

Linear (Act. Acrr. Liabili-ties)

Valuation Assets

Polynomial (Valuation Assets)

Equations for Lines• assets = 5705.9year^3 - 3E+07year^2 + 7E+10year - 5E+13

• R² = 0.9842

• liabilities = 6E+06year - 1E+10

•R² = 0.9976

1978 1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010 2012

-2,000,000

0

2,000,000

4,000,000

6,000,000

8,000,000

10,000,000

12,000,000

14,000,000

Yearly Changes(1979-2010)

L(t+1)-L(t)

A(t+1)-A(t)

1978 1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010 2012 20140.4

0.5

0.6

0.7

0.8

0.9

1

1.1

Realized Assets/Liabilities Ratio(1979-2011)

100% Funded Line

1978 1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010 2012 2014 2016 2018 20200.00

0.10

0.20

0.30

0.40

0.50

0.60

0.70

0.80

0.90

1.00

Predicted assets/liability ratio(1979-2018)

y = 0.0001x3 - 0.004x2 + 0.0177x + 0.8222R² = 0.9693 percentfunded = 0.0001year^3 - 0.004year^2 +

0.0177year + 0.8222

R² = 0.9693

Things Future Groups Could Do!

“It’s the future, the possibilities are endless” Look at investment data from MERS to better predict an

asset return Monte Carlo simulation

Read closer into inflection points to deduce causal effects Inflection point- where a graph switches from increasing to

decreasing

Find a way to incorporate mortality tables Would need to be given more information on individual

employees per division Mortality tables would show when certain liabilities’ would

be freed up.

Comments/Concerns/Questions

????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????