STA/OPR 9750 Mini-Project #04: Monte Carlo-Informed Selection of CUNY Retirement Plans

\[\newcommand{\P}{\mathbb{P}} \newcommand{\E}{\mathbb{E}}\]

Due Dates

  • Released to Students: 2024-11-14
  • Initial Submission: 2024-12-04 11:45pm ET on GitHub and Brightspace
  • Peer Feedback:
    • Peer Feedback Assigned: 2024-12-05 on GitHub
    • Peer Feedback Due: 2024-12-11 11:45pm ET on GitHub

Introduction

Welcome to Mini-Project #04! In this project, you will use R to make an important personal financial decision. New faculty hired at CUNY have 30 days to choose one of two retirement plans.1 This is an important and early choice faculty have to make, as it is essentially permanent and cannot be changed. Financial forecasting is quite difficult and it is far from clear which plan is the better long-term choice. In this mini-project, you will use historical financial data and a bootstrap inference strategy to estimate the probability that one plan is better than the other.

In this project, you will:

  • Use a password-protected API to acquire financial data
  • Use resampling inference to estimate complex probability distributions
  • See how the optimal financial decision varies as a function of market returns
  • Investigate how demographic and actuarial assumptions, as well as individual risk-tolerances, change the optimal decision.

As always, there isn’t a single “right” answer to the questions posed herein. The optimal retirement plan will depend on your starting salary, your projections of future salary growth, your current age and expected age of retirement, stock market returns, inflation, etc.

Also note that this mini-project is intended to be the least difficult of the course; Note that, compared to previous projects, the scope of this project is relatively smaller: in light of this, and the more advanced skills you have spent the past 3 months developing, this mini-project should be the least difficult of the course. At this point in the course, you should be spending the majority of your out-of-class hours on your Course Project.

This mini-project completes our whirlwind tour of several different forms of data-driven writing:

  • Data Analysis Report (MP#01)
  • Data-Based Support for “Sales Pitch” (MP#02)
  • Data-Based Evaluation of Third-Party Assertion (“Fact Check”) (MP#03)
  • Data-Driven Decision Support (this project)

There are, of course, other ways that data can be used to generate and communicate insights, but hopefully this “hit parade” has exposed you to many of the ways that you can use data to evaluate complex qualitative and quantitative claims outside of a binary classroom “correct/incorrect” structure. The tools of quantitative analysis and communication you have developed in this course can be used in essentially infinite contexts– we have only scratched the surface–and I’m excited to see what you do in the remainder of this course, in your remaining time at Baruch, and in your future careers.

Please note that you have the option of creating an interactive shiny retirement forecasting tool as extra credit, but this is not required. See details below.

Background

Please Note: Nothing in this document constitutes an official NYS, NYC, or CUNY statement about the retirement plans. This document omits several subtleties in the interest of pedagogical simplicity. If you are a potential or new CUNY employee who has stumbled across this document, please speak to your personal financial advisor, your Human Resources department, or your union representative for up-to-date and accurate retirement benefit information.

CUNY Retirement Plans

CUNY offers two retirement plans, the traditional defined-benefit Teachers Retirement System (TRS) plan and the newer defined-contribution Optional Retirement Plan (ORP).2

For this project, you may ignore the effect of taxes as both plans offer pre-tax retirement savings, so whichever plan has the greater (nominal, pre-tax) income will also yield the greater (post-tax) take-home amount.

Teachers Retirement System

The TRS plan is a traditional pension plan: after retirement, the employer (CUNY) continues to pay employees a fraction of their salary until death. This type of plan is called a “defined-benefit” because the retirement pay (the benefit) is fixed a priori and the employer takes the market risk. If the market underperforms expectations, CUNY has to “pony up” and make up the gap; if the market overperforms expectations, CUNY pockets the excess balance.

At CUNY, the TRS is administered as follows:

  1. Employees pay a fixed percentage of their paycheck into the pension fund. For CUNY employees joining after March 31, 2012–which you may assume for this project–the so-called “Tier VI” contribution rates are based on the employee’s annual salary and increase as follows:

    • $45,000 or less: 3%
    • $45,001 to $55,000: 3.5%
    • $55,001 to $75,000: 4.5%
    • $75,001 to $100,000: 5.75%
    • $100,001 or more: 6%
  2. The retirement benefit is calculated based on the Final Average Salary of the employee: following 2024 law changes, the FAS is computed based on the final three years salary. (Previously, FAS was computed based on 5 years: since salaries tend to increase monotonically over time, this is a major win for TRS participants.)

    If \(N\) is the number of years served, the annual retirement benefit is:

    • \(1.67\% * \text{FAS} * N\) if \(N \leq 20\)
    • \(1.75\% * \text{FAS} * N\) if \(N = 20\)
    • \((35\% + 2\% * N) * \text{FAS}\) if \(N \geq 20\)3

    In each case, the benefit is paid out equally over 12 months.

  3. The benefit is increased annually by 50% of the CPI, rounded up to the nearest tenth of a percent: e.g., a CPI of 2.9% gives an inflation adjustment of 1.5%. The benefit is capped below at 1% and above at 3%, so a CPI of 10% leads to a 3% inflation adjustment while a CPI of 0% leads to a 1% inflation adjustment.

    The inflation adjustement is effective each September and the CPI used is the aggregate monthly CPI of the previous 12 months; so the September 2024 adjustment depends on the CPI from September 2023 to August 2024.

Optional Retirement Plan

The ORP plan is more similar to a 401(k) plan offered by a private employer. The employee and the employer both make contributions to a retirement account which is then invested in the employee’s choice of mutual funds. Those investments grow “tax-free” until the employee begins to withdraw them upon retirement. If the employee does not use up the funds, they are passed down to that employee’s spouse, children, or other heirs; if the employee uses the funds too quickly and zeros out the account balance, no additional retirement funds are available. Though the employee hopefully still has Social Security retirement benefits and other savings to cover living expenses. This type of plan is called a defined-contribution plan as only the contributions to the retirement account are fixed by contract: the final balance depends on market factors outside of the employee’s control.

At retirement, the employee has access to those funds and can choose to withdraw them at any rate desired. A general rule of thumb is withdrawing 4% of the value per year, e.g., this Schwab discussion; you can assume a constant withdrawal rate in your analysis. Note that unwithdrawn funds continue to experience market returns.

The funds available in a ORP account depend strongly on the investments chosen. For this analysis, you can assume that the ORP participants invest in a Fidelity Freedom Fund with the following asset allocation:[^6]

  • Age 25 to Age 49:
    • 54% US Equities
    • 36% International Equities
    • 10% Bonds
  • Age 50 to Age 59:
    • 47% US Equities
    • 32% International Equities
    • 21% Bonds
  • Age 60 to Age 74:
    • 34% US Equities
    • 23% International Equities
    • 43% Bonds
  • Age 75 or older:
    • 19% US Equities
    • 13% International Equities
    • 62% Bonds
    • 6% Short-Term Debt

Under the ORP, both the employee and the employer make monthly contributions to the employee’s ORP account. These returns are calculated as a percentage of the employee’s annual salary. Specifically, the employee contributes at the same rate as the TRS:

  • $45,000 or less: 3%
  • $45,001 to $55,000: 3.5%
  • $55,001 to $75,000: 4.5%
  • $75,001 to $100,000: 5.75%
  • $100,001 or more: 6%

The employer contribution is fixed at:

  • 8% for the first seven years of employment at CUNY.
  • 10% for all years thereafter.

You may assume that the contributions are immediately invested according to the asset allocations above.

[^6] This is simplified from the actual rates used by Fidelity, but it’s close to accurate.

The Power of Compound Interest

Retirement in the US would scarcely be possible without the magic of compounded growth. When you invest early, the money you put in your retirement savings grows, as do the earning you make along the way. This creates a virtuous cycle, resembling exponential growth, making it possible to retire with (hopefully far) more money than you originally put in the account.

Compound interest works as follows: let \(X_t\) be the amount of money in the account at the start of month \(t\), let \(C_t\) be the contributions during month \(t\), and let \(r_t\) be the fund return during month \(t\). We then have the recurrence relation:

\[X_{t+1} = X_t(1+r_t) + C_t\]

Repeating this, we get:

\[\begin{align*} X_{t+2} &= X_{t+1}(1+r_{t+1}) + C_{t+1} \\ &= X_{t}(1+r_t)(1+r_{t+1}) + C_t(1+r_{t+1}) + C_{t+1} \\ X_{t+3} &= X_{t}(1+r_t)(1+r_{t+2})(1+r_{t+3}) + C_t(1+r_{t+1})(1+r_{t+2}) + C_{t+1}(1+r_{t+2}) + C_{t+2} \end{align*}\]

and so on. In R, you might calculate two years of compounded growth as follows:

RETIREMENT <- data.frame(
    r = rnorm(24, mean=0.5) / 100, # Monthly returns
    C = rep(100, 24),            # Monthly savings: 100 per month
    period = 1:24                # Period ID (# of months)
)

RETIREMENT |> 
    mutate(net_total_return = order_by(desc(period), 
                                       cumprod(1 + lead(r, default=0)))) |>
    summarize(future_value = sum(C * net_total_return))
  future_value
1     2532.232

(It is a good exercise to try to understand the calculation above.)

Here, we combine a series of monthly returns, column r, with periodic contributions, column C, to find the final future value of the account. Because the returns have a small positive mean (0.5% per month, equivalent to about 6.2% annual), the final value of the account is likely more than the total contributions.

Data Sources - AlphaVantage & FRED

For this project, we will use data from two economic and financial data sources:

  • AlphaVantage: a commercial stock market data provider
  • FRED: the Federal Reserve Economic Data repository maintained by the Federal Reserve Bank of St. Louis

FRED is free to access, but AlphaVantage is a commercial service requiring subscription. For this mini-project, the free tier of AlphaVantage will suffice.

Task 1 - Register for AlphaVantage API Key

Create your AlphaVantage free API key at https://www.alphavantage.co/support/#api-key.

Do Not Include This Key in Your Submission.4 It is your personal account and linked to whatever email you use to create your account.

I recommend creating a new file on your computer and storing your key there. You can read it into R using the readLines function and use it as needed. Just make sure not to print it.

Once you store your AlphaVantage key in a plain text file, make sure to add that file to your .gitignore to make sure you don’t accidentally include it in your git history.

Documentation for the AlphaVantage API can be found at https://www.alphavantage.co/documentation/.

Task 2 - Register for FRED API Key

Create your FRED free API key at https://fredaccount.stlouisfed.org/login/secure/.

Do Not Include This Key in Your Submission. It is your personal account and linked to whatever email you use to create your account.

I recommend creating a new file on your computer and storing your key there. You can read it into R using the readLines function and use it as needed. Just make sure not to print it.

Once you store your FRED key in a plain text file, make sure to add that file to your .gitignore to make sure you don’t accidentally include it in your git history.

Documentation for the FRED API is available at https://fred.stlouisfed.org/docs/api/fred/.

Don’t Use Specialized R Packages To Access Data Sources

For this project, you may not use any R packages that wrap AlphaVantage or FRED. A learning objective of this mini-project is accessing data via an API, and you won’t practice that skill if you simply use a package like quantmod or alphavantager.

You should interact with the APIs directly using httr2 or equivalent packages.

You will need to download data from FRED and/or AlphaVantage to complete this assignment.

Bootstrap Resampling

To complete this project, you will use a boostrap resampling strategy. The details of how bootstrap sampling can be applied to this type of financial analysis are given in more detail below. Here, we simply review the basic principles of the bootstrap.

Classically, statistics proceeds by assuming some sort of parametric model for the population from which we have samples. Parametric models describe a family of not-dissimilar distributions, indexed by a small number of parameters; e.g., the two parameters of the normal distribution, mean and variance. While this leads to quite elegant mathematical theory, it is unclear how useful this theory is in practice: we rarely know with certainty that a particular parametric family is an accurate model for a phenomenon of interest. Asymptotic results like the Central Limit Theorem give us some sense that we don’t need to know the true distribution family exactly to get accurate answers, but the era of computers gives us an alternate approach.

If we have a large sample size, the empirical CDF of our data is guaranteed to converge to the true CDF of the population. Since essentially all quantities of interest can be computed using the CDF, this guarantees that, with enough data, we can compute any quantity of interest using the empirical CDF only and get an accurate answer. The bootstrap principle, and the associated broader field of resampling inference, uses this concept to compute variances of complex statistical procedures without using parametric models.

Formally, let our data come from some distribution \(\P\) and let \(\P_n\) be the empirical (sample) distribution. The core statistical theorems tell us that \(\P_n \to \P\) in various ways:

  • Law of Large Numbers: \(\E_{\P_n}[f(X)] \to \E_{\P}[f(X)]\)
  • Glivenko-Cantelli: \(\sup |\P_n(X \leq x) - \P(X \leq x)| \to 0\)

Bootstrap sampling relies on a similar principle:

\[ \text{Var}_{\P_n}[f(X_1, \dots, X_n)] \to \text{Var}_{\P}[f(X_1, \dots, X_n)]\]

Here, \(\text{Var}_{\P}[f(X_1, \dots, X_n)]\) is the sampling variance of the estimator \(f\) under the true distribution, and \(\text{Var}_{\P_n}[f(X_1, \dots, X_n)]\) is the variance of that same estimator under the sample distribution. What exactly does this last claim mean? Essentially, if we repeat \(f\) on our sample data many times, the variance will approximate the “true” sampling variance.

But if we simply repeat \(f\) on the same data, we get the same answer each time - so where is the variance? This is where the magic of the bootstrap comes in. Instead of using the sample data as is, we resample it in the following manner.

  • Given data \(\mathcal{D} = \{x_1, \dots, x_n\}\), create a new sample \(\mathcal{D}^\#_1 = \{x^\#_1, x^\#_2, \dots, x^\#_n\}\) by drawing from the original data with replacement. (If you draw without replacement, you just get back the original data in full every time, possibly permuted.) This new sample \(\mathcal{D}^\#_1\) is the same size as \(\mathcal{D}\), so we can apply \(f\) to it unchanged. \(\mathcal{D}^\#_1\) is called a boostrap sample.
  • Compute \(f\) on the bootstrap sample to get \(f^\#_1\).
  • Repeat this process many times to get bootstrap samples of \(f\): \(f^\#_1, \dots, f^\#_B\) for some large \(B\) (typically around 200-500).
  • The variance of \(f^\#_1, \dots, f^\#_B\) is an approximation of the sampling variance of \(f\) under the data-generating process.

This is all a bit heady, but the key point to keep in mind is this: as we get more data, our sample is a good estimate of the true distribution, so variance from resampling our data from our sample approximates variance from resampling our sample from the population.

Let’s put this into practice: suppose we have \(250\) samples from an unknown distribution and we want to estimate the median of the distribution using the sample median. Computing the variance of the sample median is rather tricky, but asymptotically it approaches \(1/(4n f_X(m_X)^2)\) where \(m_X\) is the true median. This formula is essentially useless however if we don’t assume a parametric model for \(f_X\), so we instead resort to bootstrapping.

To demonstrate this, let’s suppose \(X\) comes from a non-central \(\chi^2\) distribution with \(\pi\) degrees of freedom and non-centrality parameter \(e^2\). (This is a very weird distribution!)

set.seed(100)
DATA <- rchisq(250, df=pi, ncp = exp(2))
SAMPLE_MEDIAN <- median(DATA)

The median of this distribution is too complex for Wikipedia, but we can compute it empirically using a very large sample:

TRUE_MEDIAN <- median(rchisq(5e7, df=pi, ncp = exp(2)))

So our sample median (10.74) is a bit off from the true median (9.58) but not catastrophically so. How can we estimate the variance? By bootstrapping!

We can implement a bootstrap in dplyr as follows:

B <- 500 # Number of boostrap samples to create
n <- length(DATA) # Original data size

expand_grid(B = 1:B, 
            n = 1:n) |>
    # Notice here we sample _with replacement_ from DATA
    mutate(x = sample(DATA, n(), replace = TRUE)) |>
    group_by(B) |>
    summarize(f_boot = median(x)) |>
    summarize(var_f = var(f_boot)) |>
    pull(var_f)
[1] 0.1970951

We can compare this to the CLT-asymptotic variance:

1/(4 * n * dchisq(TRUE_MEDIAN, df=pi, ncp = exp(2))^2)
[1] 0.21209

And, since we’re in simulation land, we also have the true variance:

var(replicate(10000, {
    median(rchisq(250, df=pi, ncp = exp(2)))
}))
[1] 0.2100952

The bootstrap variance is a little bit too high, but it’s surprisingly good! And we didn’t have to know anything about the true distribution of the data! I’d call that a win.

In general, we can estimate the variability of any function of data this way: i) create bootstrap samples by resampling the original data with replacement, ii) apply the function of interest to each of the bootstrap samples, and iii) take the bootstrap variance as an approximation to the “true” variance.

This strategy is particularly useful for this project, as we don’t want to make any modeling assumptions on the returns of the stock market (a famously hard problem!) other than assuming the future looks something like the past.

Bootstrapping is one form of a general computational paradigm known as Monte Carlo, wherein computers are given randomized inputs and told to evaluate complex functions on those inputs. The various randomized outputs are then analyzed to give insight into the behavior of the complex function. Monte Carlo methods are popular because:

  1. Computers are cheap, but thinking is hard.
  2. They can treat the function of interest in a “black box” manner, making them suitable for use with complex computer simulation models.
  3. They do not require the use of statistical asymptotics or, when performed with resampling, any distributional assumptions at all.

The name “Monte Carlo” is a reference to the famous casino of the same name in Monaco. While it is hard to evaluate odds of success in complex games of chance, by playing the game many times over and collecting results, it is straightforward to accurately compute the house edge. In the (computational) Monte Carlo approach, we replace the roulette wheel with a computer’s random number generator, but the same principles apply.

Bootstrapping Complex Data

In the example above, we had IID scalar data so a simple call to sample(replace=TRUE) was sufficient to implement a proper bootstrap. When dealing with multivariate data, e.g. \((x, y)\) pairs in a regression model, we need to “jointly” resample pairs or else we will break the underlying correlation.5 Thankfully, dplyr provides us with a slice_sample function which can be used to do esentially the same thing.

For example, suppose we are interested in assessing the uncertainty associated with the Kendall correlation of two variables. This is a rather non-linear function of the data and classical tools like Fisher’s transform for standard (Pearson) correlation can’t be easily applied. Bootstrapping works perfectly well without much additional effort, as we show below.

We generate \((x, y)\) pairs with a visible, but not precisely linear, relationship.

x <- rchisq(100, df=3, ncp=2)
y <- x * sin(2 * x) + 15 * log(x)
plot(x, y)

The Kendall correlation is easily computed:

cor(x, y, method="kendall")
[1] 0.820202

To put a confidence interval on this, we can use a bootstrap with \(B=400\) replicates:

stopifnot(length(x) == length(y))
n_samp <- length(x)
n_boot <- 400

data.frame(x = x, y = y) |>
    slice_sample(n = n_samp * n_boot, 
                 replace=TRUE) |>
    mutate(resample_id = rep(1:n_boot, times=n_samp)) |>
    group_by(resample_id) |>
    summarize(kendall_cor = cor(x, y, method="kendall")) |>
    summarize(var(kendall_cor))
# A tibble: 1 × 1
  `var(kendall_cor)`
               <dbl>
1            0.00113

We can again compare this to the “true” sampling variance since we have access to the data-generating model.

var(replicate(5000, {
    x <- rchisq(100, df=3, ncp=2)
    y <- x * sin(2 * x) + 15 * log(x)
    cor(x, y, method="kendall")
}))
[1] 0.001457306

Again - really close! And we don’t need to know anything about the joint distribution of \((x, y)\) to apply the bootstrap.

You will need to use this “row-resampling” bootstrap to maintain the underlying economic relationships between the various investment returns and macroeconomic quantities you consider in this exercise.6

Mini-Project Objectives

In this project, you will use historical economic data and a bootstrap resampling strategy to see the distribution of retirement outcomes under different (resampled) economic histories. You will collect relevant economic data including:

  • Rate of Inflation
  • Rate of Wage Growth
  • US Equity Market Returns
  • International Equity Market Returns
  • Bond Returns
  • Short Term Debt Returns

You will resample these to create artificial histories and apply the TRS and ORP rules to each strategy to determine i) how much wealth you have going into retirement; ii) your probability of running out of funds before death; and iii) the amount of funds (if any) you leave to your heirs.

The optimal strategy may depend on various parameters including: i) your age when you start saving; ii) your age when you retire; iii) the age at which you shuffle off this mortal coil; and iv) your starting salary.

You may select values that seem appropriate to you for each of these or, if you are feeling ambitious, you can perform a sensitivity analysis to see how robust your final decision is to changes in each of these inputs.

After you simulate various “Monte Carlo” histories to assess the relative performance of the TRS and ORP retirement plans, you will make a data-driven decision recommendation to a potential CUNY employee. (In essence, you will play the role of a financial advisor.) Your final report should include a plan recommendation, as well as some notion of confidence or certainty associated with your recommendation.

Student Responsbilities

Recall our basic analytic workflow and table of student responsibilities:

  • Data Ingest and Cleaning: Given a single data source, read it into R and transform it to a reasonably useful standardized format.
  • Data Combination and Alignment: Combine multiple data sources to enable insights not possible from a single source.
  • Descriptive Statistical Analysis: Take a data table and compute informative summary statistics from both the entire population and relevant subgroups
  • Data Visualization: Generate insightful data visualizations to spur insights not attainable from point statistics
  • Inferential Statistical Analysis and Modeling: Develop relevant predictive models and statistical analyses to generate insights about the underlying population and not simply the data at hand.
Students’ Responsibilities in Mini-Project Analyses
Ingest and Cleaning Combination and Alignment Descriptive Statistical Analysis Visualization
Mini-Project #01
Mini-Project #02 ½
Mini-Project #03 ½
Mini-Project #04

In this mini-project, you are in charge of the whole pipeline, from deciding which data to use, to finding the data on AlphaVantage and/or FRED, to downloading it into R, to performing the resampled history analyses, to implementing the TRS and ORP savings rules, and to finally interpreting the results. The rubric below evaluates your work on all aspects of this project.

Rubric

STA/OPR 9750 Mini-Projects are evaluated using peer grading with meta-review by the course GTAs. Specifically, variants of the following rubric will be used for the mini-projects:

Mini-Project Grading Rubric
Course Element Excellent (9-10) Great (7-8) Good (5-6) Adequate (3-4) Needs Improvement (1-2) Extra Credit
Written Communication Report is well-written and flows naturally. Motivation for key steps is clearly explained to reader without excessive detail. Key findings are highlighted and appropriately given context. Report has no grammatical or writing issues. Writing is accessible and flows naturally. Key findings are highlighted, but lack suitable motivation and context. Report has no grammatical or writing issues. Key findings are present but insufficiently highlighted. Writing is intelligible, but has some grammatical errors. Key findings are obscured. Report exhibits significant weakness in written communication. Key points are difficult to discern. Report includes extra context beyond instructor provided information.
Project Skeleton Code completes all instructor-provided tasks correctly. Responses to open-ended tasks are particularly insightful and creative. Code completes all instructor-provided tasks satisfactorially. Response to one instructor provided task is skipped, incorrect, or otherwise incomplete. Responses to two instructor provided tasks are skipped, incorrect, or otherwise incomplete. Response to three or ore instructor provided tasks are skipped, incorrect, or otherwise incomplete. Report exhibits particularly creative insights drawn from thorough student-initiated analyses.
Formatting & Display

Tables and figures are full ‘publication-quality’.

Report includes at least one animated visualization designed to effectively communicate findings.

Tables have well-formatted column names, suitable numbers of digits, and attractive presentation.

Figures are ‘publication-quality’, with suitable axis labels, well-chosen structure, attractive color schemes, titles, subtitles, and captions, etc.

Tables are well-formatted, but still have room for improvement.

Figures are above ‘exploratory-quality’, but do not reach full ‘publication-quality’.

Tables lack significant ‘polish’ and need improvement in substance (filtering and down-selecting of presented data) or style.

Figures are suitable to support claims made, but are ‘exploratory-quality’, reflecting minimal effort to customize and ‘polish’ beyond ggplot2 defaults.

Unfiltered ‘data dump’ instead of curated table.

Baseline figures that do not fully support claims made.

Report includes interactive (not just animated) visual elements.
Code Quality

Code is (near) flawless.

Code passes all styler and lintr type analyses without issue.

Comments give context of the analysis, not simply defining functions used in a particular line. Code has well-chosen variable names and basic comments. Code executes properly, but is difficult to read. Code fails to execute properly. Code takes advantage of advanced Quarto features to improve presentation of results.
Data Preparation Data import is fully-automated and efficient, taking care to only download from web-sources if not available locally. Data is imported and prepared effectively, in an automated fashion with minimal hard-coding of URLs and file paths. Data is imported and prepared effectively, though source and destination file names are hard-coded. Data is imported in a manner likely to have errors. Data is hard-coded and not imported from an external source. Report uses additional data sources in a way that creates novel insights.

Note that this rubric is designed with copious opportunities for extra credit if students go above and beyond the instructor-provided scaffolding. Students pursuing careers in data analytics are strongly encouraged to go beyond the strict ambit of the mini-projects to i) further refine their skills; ii) learn additional techniques that can be used in the final course project; and iii) develop a more impressive professional portfolio.

Because students are encouraged to use STA/OPR 9750 mini-projects as the basis for a professional portfolio, the basic skeleton of each project will be released under a fairly permissive usage license. Take advantage of it!

Submission Instructions

After completing the analysis, write up your findings, showing all of your code, using a dynamic quarto document and post it to your course repository. The qmd file should be named mp04.qmd so the rendered document can be found at docs/mp04.html in the student’s repository and served at the URL:

https://<GITHUB_ID>.github.io/STA9750-2024-FALL/mp04.html

Once you confirm this website works (substituting <GITHUB_ID> for the actual GitHub username provided to the professor in MP#00 of course), open a new issue at

https://github.com/<GITHUB_USERNAME>/STA9750-2024-FALL/issues/new .

Title the issue STA/OPR 9750 <GITHUB_USERNAME> MiniProject #03 and fill in the following text for the issue:

Hi @michaelweylandt!


https://<GITHUB_USERNAME>.github.io/STA9750-2024-FALL/mp04.html

Once the submission deadline passes, the instructor will tag classmates for peer feedback in this issue thread.

Additionally, a PDF export of this report should be submitted on Brightspace. To create a PDF from the uploaded report, simply use your browser’s ‘Print to PDF’ functionality.

NB: The analysis outline below specifies key tasks you need to perform within your write up. Your peer evaluators will check that you complete these. You are encouraged to do extra analysis, but the bolded Tasks are mandatory.

NB: Your final submission should look like a report, not simply a list of facts answering questions. Add introductions, conclusions, and your own commentary. You should be practicing both raw coding skills and written communication in all mini-projects. There is little value in data points stated without context or motivation.

Set-Up and Exploration

Data Acquisition

To begin your Monte Carlo analysis, you will need historical data covering (at a minimum) the following:

  • Wage growth
  • Inflation
  • US Equity Market total returns
  • International Equity Market total returns
  • Bond market total returns
  • Short-term debt returns7
Task 3: Data Acquisition

Identify and download historical data series for each of the above inputs to your Monte Carlo analysis. If necessary, “downsample” each series to a monthly frequency and join them together in a data.frame.

You must use at least one data series from AlphaVantage and one from FRED. You must use the APIs of each service to access this data and, as noted above, you need to use the “raw” API, relying only on the httr2 package (or similar) and not wrapper packages like quantmod or alphavantager.

Note that, for each of these quantities, there are many possibly-relevant data series: e.g., for inflation, you might compare CPI, core CPI, PCE, both nationally and, if available, in the NY metro area. You may select any series you feel best captures these for a potential CUNY employee. For the market returns, it may be easiest to identify a suitable index ETF and compute its (dividend-adjusted) returns as a proxy for market returns.

In any historically-based financial projection, there is a trade-off between having enough history to capture sufficient market cycles and having only relevant data in your training set. I’d recommend using around 20-30 years of data for this project.

Investigation and Visualization of Input Data

Task 4: Initial Analysis

After you have acquired your input data, perform some basic exploratory data analysis to identify key properties of your data. You may choose to measure the correlation among factors, long-term averages, variances, etc. Your analysis should include at least one table and one figure.

As part of your analysis, be sure to compute the long-run monthly average value of each series. You will use these in a later task.

Historical Comparison of TRS and ORP

Task 5: Historical Comparison

Now that you have acquired data, implement the TRS and ORP formulas above and compare the value of each of them for the first month of retirement. To do this, you may assume that your hypothetical employee:

  • Joined CUNY in the first month of the historical data
  • Retired from CUNY at the end of the final month of data

You will need to select a starting salary for your employee. Use historical data for wage growth and inflation and assume that the TRS and ORP parameters did not change over time. (That is, the employee contribution “brackets” are not inflation adjusted; the employee will have to make larger contributions as income rises over the span of a 20+ year career.)

Long-Term Average Analysis

The “first month of retirement” dollar value is interesting, but it arguably undersells a key strength of the TRS. The TRS guarantees income for life, while the ORP can be exhausted if the employee lives a very long time in retirement.

Task 6: Fixed-Rate Analysis

Modify your simulation from the previous section to project an employee’s pension benefit (TRS) or withdrawal amount (ORP) from retirement until death. (You will need to select an estimated death age.) In order to implement cost-of-living-adjustments (TRS) and future market returns (ORP), you can use the long-run averages you computed previously. This “fixed rate” assumption is rather limiting, but we will address it below.

As you compare the plans, be sure to consider:

  • Whether the employee runs out of funds before death and/or has funds to leave to heirs (ORP only)
  • Average monthly income (TRS vs ORP)
  • Maximum and minimum gap in monthly income between TRS and ORP

As noted above, you can ignore the effect of taxes throughout this analysis.

Bootstrap (Monte Carlo) Comparison

Now that you have implemented both the “while working” contributions and returns (ORP) only as well as the “while retired” benefits of both plans, we are finally ready to implement our Monte Carlo assessment.

Task 7: Monte Carlo Analysis

Using your historical data, generate several (at least 200) “bootstrap histories” suitable for a Monte Carlo analysis. Use bootstrap sampling, i.e. sampling with replacement, to generate values for both the “while working” and “while retired” periods of the model; you do not need to assume constant long-term average values for the retirement predictions any more.

Apply your calculations from the previous two tasks to each of your simulated bootstrap histories. Compare the distribution of TRS and ORP benefits that these histories generate. You may want to ask questions like the following:

  1. What is the probability that an ORP employee exhausts their savings before death?
  2. What is the probability that an ORP employee has a higher monthly income in retirement than a TRS employee?
  3. Is the 4% withdrawal rate actually a good idea or would you recommend a different withdrawal rate?

Report your findings to these or other questions of interest in tables or figures, as appropriate.

Deliverable: Data-Driven Decision Recommendation

Finally, write up your findings from Task 7 in the form of a “data-driven recommendation” to a potential CUNY employee. Here, you are playing the role of a financial advisor, so be sure to consider the employee’s current age and starting salary, expected lifetime, and risk tolerance. Be sure to suitably convey the uncertainty of your predictions and the limitations of the bootstrap-history approach used here.8 As you write this, think of what issues would matter most to you if you were making this decision and address them accordingly.

Extra Credit Opportunities

For extra credit, you may make an interactive version of your report, allowing your client to alter the parameters of your simulation and see how the predictions change.

Challenge Level: Basic (Up to 5 points Extra Credit)

Perform a “sensitivity analysis” by re-running your previous analysis under various different input parameters (starting salary, retirement age, death age, etc.) Then use some sort of interactive functionality to allow the reader to see how the results change.

The manipulateWidgets package may be useful here, but any sort of in-browser interactive display will suffice.

Note that, in this model, all the simulations are run by Quarto at Render time and the interactivity only controls which simulations are displayed.

Challenge Level: Moderate (Up to 10 points Extra Credit)

Use the shiny package to implement a reactive dashboard. shiny requires use of a server to perform calculations. The website shinyapps.io provides a free platform to host the “backend” of your shiny dashboard. This example may prove useful, but note that the analysis required for this project (historical resampling) is a bit more advanced than the parametric model used there.

Under the shiny model, a back-end server is running (and re-running) simulations in real-time in response to user input.

Challenge Level: Advanced (Up to 20 points Extra Credit)

Use the r-shinylive framework to create a fully dynamic in-browser simulation dashboard. This in-development technology allows users to modify and re-run all simulations in their browser, providing the highest level of flexibility. You can allow users to vary their starting salary, retirement age, choice of data series, number of Monte Carlo histories, dates of historical data used for resampling, etc.

Note that r-shinylive is a new technology and one that remains under active development. The instructor will not be able to provide support and assistance debugging it.


This work ©2024 by Michael Weylandt is licensed under a Creative Commons BY-NC-SA 4.0 license.

Footnotes

  1. While I don’t imagine many of you will have this exact decision to make, these forecasting and analysis strategies are readily applied to a variety of personal financial planning decisions you might face in future careers.↩︎

  2. CUNY employees have additional voluntary retirement savings plans, but these are non-exclusive and participation is voluntary, so we omit them from this analysis.↩︎

  3. If a TRS participant retires before the age of 63, their benefit is reduced, but you can ignore this wrinkle.↩︎

  4. If you accidentally include this file in your Git history, see these instructions for discussion of how to remove it. These are dangerous and advanced git “power tools”, so it will be far easier to use .gitignore defensively rather↩︎

  5. “Breaking” the correlation is not necessarily a bad thing. This essentially underlies the concept of permutation testing, but it’s not what we’re aiming to do here.↩︎

  6. Clearly, the bootstrap as we have described here looses any temporal structure in the data. The so-called block bootstrap can be used to bootstrap in a way that (approximately) respects temporal dependence, but you don’t need to implement this advanced variant in this project.↩︎

  7. For short-term debt, it may be easiest to pick a key short-term benchmark, e.g., the 2-year US Treasury yield. The world of “short-term debt” is rather wide and varied.↩︎

  8. As the SEC requires all advisors to disclaim: Past Performance is No Guarantee of Future Results.↩︎