EE590 Project Proposal
SAMPLE PROPOSAL
2
Proposed Work
1. Problem Statement
For federal employees, retirement planning and investment strategies differ slightly from those working in
the private sector. In the private sector, employers offer a 401(k)-retirement account. In the public sector,
employers offer a Thrift Savings Plan (TSP). While different in name, both have employer defined
investment funds with varying levels of risk and reward associated with them.
With the TSP, there are five funds that a portfolio can be diversified between (G, F, C, S, and I). There is
also an option to for an employee to utilize the Lifecycle Fund (L Fund). A comparison matrix of the
funds, as published on the TSP website, can be seen in Table 1 [1]. The lifecycle fund utilizes a
professional service that determines the fund allocation based on target date and risk allocation. With this
fund, individual risk preferences are not considered. All individuals that elect to allocate their investment
in this fund are grouped based on time horizon and individuals in the group all have the same investment
strategy.
Table 1: TSP Published Fund Comparison Matrix [1]
G Fund F Fund* C Fund* S Fund* I Fund* L Funds**
Description of
Investments
Government
securities
(specially
issued to the
TPS)
Government,
corporate,
and
mortgage-
backed bonds
Stocks of
large and
medium-
sized U.S.
companies
Stocks of
small to
medium-
sized U.S.
companies
(not included
in the C
fund)
International
stocks of
more than 20
developed
countries
Invested in the
G, F, C, S, and
I Funds
Objective of
Fund
Interest
income
without risk
and loss of
principal
To match the
performance
of the
Bloomberg
Barclays U.S.
Aggregate
Bond Index
To match the
performance
of the
Standard &
Poor’s 500
(S&P 500)
Index
To match the
performance
of the Dow
Jones U.S.
Completion
TSM Index
To match
performance
of the MSCI
EAFE
(Europe,
Australasia,
Far East)
Index
To provide
professionally
diversified
portfolios
based on
various time
horizons, using
the G, F, C, S,
and I Funds
Volatility Low Low to
moderate
Moderate Moderate to
high –
historically
more volatile
than C Fund
Moderate to
high –
historically
more volatile
than C Fund
Asset
allocation
shifts as time
horizon
approaches to
reduce
volatility
*The F, C, S, and I Funds are also have earnings from securities lending income and from temporary
investments in G Fund securities. These amounts represent a very small portion of total earnings.
**Each of the L Funds is invested in the individual TSP funds (G, F, C, S, and I).
3
This study focuses on optimizing the retirement plan of a federal employee utilizing personal investment
preferences. Individuals have different risk tolerances, diversification preferences, and opinions on
manager/fund performance. With the Lifecycle fund, these preferences are not considered. Using
historical data for the TSP funds and the Benchmark Indices, an interactive model will be developed
where a user can define their personal investment preferences and receive an investment strategy that
maximizes portfolio return per unit of risk.
This is an important study because it considers personal risk preferences for federal retirement investment
strategies. Currently, there are limited resources and funds available for TSP holders. Developing a tool
that not only provides an investment strategy, but also allows an individual to adjust their investment
preferences as they age encourages one to better optimize their portfolio.
2. Approach
For each of the TSP funds, the daily stock price is available from 2005 to 2020. In order to capture an
entire economic cycle in the analysis, historical data from Jan 1, 2005 to Dec 31, 2014 (10-year
timeframe) will be used for analysis and model creation. Historical data from Jan 1, 2015 to Dec 31, 2017
(3-year timeframe) will be used to validate the model.
There are two main components to the approach to this problem: analysis of manager/fund performance,
and portfolio optimization. The model will be developed in Excel.
Manager/Fund Performance:
There are five funds that will be analyzed in this study (G, F, C, S, and I). Each fund in the TSP is
modelled to match the performance of a market style/index. To determine the manager performance of the
TSP funds, each fund will be compared to its respective benchmark. Table 1 identifies each TSP’s
benchmark index under the “Objective Fund” row of the table.
To examine portfolio performance compared to the benchmark, beta and alpha, two metrics commonly
used to examine fund performance need to be calculated.
Beta Calculation:
To determine fund volatility, beta (β), will be calculated for each TSP fund.
=
( , )
( )
Beta will indicate how much the fund deviates from the overall market, where the overall market has a
beta equal to 1.0 [2]. If beta is greater than 1, the fund tends to have a higher risk and a higher reward; a
beta less than 1 equates to less risk and a lower reward [2]. By determining the beta for each fund, the
systematic risk for the fund will be determined and one can then calculate the alpha for each fund.
Alpha Calculation:
Alpha, α, is a metric used to evaluate fund performance compared to its benchmark. Derived from the
CAPM model, the formula for alpha is listed below [3]:
= ? ? ( ? )
4

? =
= ?
=
= , ?
For each of the TSP funds, alpha will be calculated. A positive alpha indicates the portfolio outperforms
the benchmark. A negative alpha indicates the portfolio underperformed the benchmark. Further, the
value of alpha is the percentage of over/underperformance compared to the benchmark [3]. The value of
alpha will be used when developing the portfolio optimization model.
Portfolio Optimization:
This study has identified multiple metrics to evaluate portfolio risk and compare fund performance to its
respective benchmark. Using the above metrics, along with conducting a mean variance analysis and
calculating the Sharpe ratio for the optimal portfolios, an informed decision regarding the investment
strategy for the TSP fund can be developed.
The TSP publishes the daily stock prices for each fund. Using the 2005-2014 price data, the following
information will be calculated for each of the five funds:
Compounded 10-Year Return [4]:
10 ? =
(
√∏(? + 1)
120
=1
120
? 1
)
× 100
Volatility [5]:
= √
∑ ( ? ?)2

=1


= × √#
Covariance [5]:
, =
∑ ( ? ?) × ( ? ?)

=1



Using the above information, Solver in Excel will be used to perform a mean variance optimization.
Multiple portfolio options, with varying volatility values, will be created with the goal being to maximize
the portfolio’s expected return. The model will take into consideration the portfolio diversification, equity
allocation, the portfolio’s weighted beta value, and the portfolio’s weighted alpha value. These values
address an individual’s risk tolerance, the portfolio diversification, and manager performance. A detailed
formulation for the Excel Solver problem is below where user defined values are indicated by XX:
5


Objective Function: Maximize Portfolio Expected Return
By Changing: Weights (% allocation) of each of the five funds
Subject To: Each weight should be greater than or equal to 0
The sum of the weights should be equal to 1
Minimum of XX fund(s) should be utilized to provide diversification
Equity Allocation should be XX%
Portfolio’s weighted beta should be greater than XX and less than XX
Portfolio’s weighted alpha should be greater than XX and less than XX
The portfolio volatility should be equal to Z, where Z= XX,…,XX%

The equations to solve portfolio expected return and variance are below:
Portfolio Expected Return [5]:
[] =∑
5
=1
= + + + +
Portfolio Variance [5]:
() = ∑∑,

=1

=1

After creating the portfolio options with different weightings, the efficient frontier will be plotted. The
efficient frontier is the Expected Portfolio Return versus Portfolio Volatility and it represents the optimal
portfolios [6].
After determining the set of optimal portfolios, the risk associated with each fund needs to be considered.
Instead of solely looking at historical returns, the Sharpe ratio will be calculated to consider the risk
associated with each fund and adjust the return accordingly. Calculating risk adjusted returns provides
information on fund performance per unit of risk associated with each fund. The equation for calculating
the Sharpe ratio is below [7]:
Sharpe Ratio:
? =
[] ?
√()

6

The Sharpe ratio indicates “how much excess return you receive for the extra volatility you endure for
holding a riskier asset” [7]. The higher the Sharpe ratio, the better the performance. The largest Sharpe
ratio among the portfolio options will be the optimal investment strategy.
Interactive Model:
The model created above provides the optimal portfolio for a user based on their personal investment
preferences. The anticipated user interface in the excel model will appear as below:
TSP Investment Model
User Inputs:
Investment Amount ($):
Equity Allocation (%):
Minimum # of Funds in Portfolio:
Weighted Beta Range:
Weighted Alpha Range:
Portfolio Volatility Range (%):
Number of Portfolio Options:
Risk Free Rate (%):

Run Solver

Model Outputs:
% Allocated to Fund G
% Allocated to Fund F
% Allocated to Fund C
% Allocated to Fund S
% Allocated to Fund I

Portfolio Expected Return (%):
Portfolio Volatility (%):

The user input values represent the constraints for the Solver. By developing a macro in Excel that runs
the solver to create all the portfolio options, the user only needs to input their desired requirements and
click the “Run Solver” button on the summary sheet in Excel. The user will not have to modify any of the
equations and will not have to run a solver multiple times in order to create the portfolio options used to
create the efficient frontier.
3. Model Validation
Historical data from Jan 1, 2005 to Dec 31, 2014 (10-year timeframe) was used to develop the investment
model. Historical data from Jan 1, 2015 to Dec 31, 2017 (3-year timeframe) will be used to validate the
model.
Testing Criteria:
7

To test the model, user criteria needs to be identified. The criteria will be plugged into the model using
the Jan 1, 2005 to Dec 31, 2014 historical data to determine the optimal investment strategy. The model
inputs will be:
Investment Amount ($): 100,000
Equity Allocation (%): 60

Minimum # of Funds in Portfolio: 3

Weighted Beta Range: 0.75 1
Weighted Alpha Range: 1 10
Portfolio Volatility Range (%): 0 15
Number of Portfolio Options: 15

Risk Free Rate (%): 2


The percent allocation for each TSP fund and the expected portfolio return and volatility obtained from
the model will be used to validate the model.
Model Validation:
From the optimization model, the percent allocation for each fund is identified. This investment strategy
is used to calculate the portfolio’s expected return and the portfolio’s volatility (standard deviation). To
validate the model’s investment strategy, the estimated portfolio return needs to be compared with the
actual portfolio return from the 2015-2017 time period. Using the Jan 1, 2015 to Dec 31, 2017 data, the
compounded 3-year return was calculated for each fund.
Compounded 3-year Return [4]:
3 ? =
(
√∏(? + 1)
36
=1
36
? 1
)
× 100
Using the compounded return for each fund, along with the weights determined in the model, the portfolio
return was calculated.
Portfolio Return [5]:
= + + + +
? ? ? ?
It is expected that the actual return will be within +/- one standard deviation of the expected return.
4. Deliverables and Expected Outcome
The final report will provide information on how the model was created, what variables were used, their
importance in developing an investment strategy, and each of their calculations. The report will cover the
model validation procedure, comparing the test case’s expected portfolio return to the portfolio’s actual
8

return during the validation period. It is expected that the actual return will be within +/- one standard
deviation of the expected return.
In addition to the report, the model created in Excel will be provided. This model will allow the various
constraints in the model to be adjusted based on investor preferences. The adjustable values will be, at a
minimum, the investment amount, portfolio’s equity allocation, the minimum number of funds in the
portfolio, the weighted beta range, the weighted alpha range, the portfolio volatility range, the number of
portfolio options, and the risk-free rate. The model will output the optimal percent allocation strategy for
the TSP portfolio, along with the expected portfolio return.
The results of this study could be utilized to determine whether investors should utilize the TSP’s
lifecycle fund or if they should define an investment strategy more specific to their goals. By creating an
interactive tool, investors can alter their investment preferences and determine the percent allocations for
the TSP funds. They could then compare these weights to those of the lifecycle fund to see how their
preferences vary. This will allow users to make a better-informed decision regarding their investments.

9

5. References
[1] “Fund Comparison Matrix.” TSP, Thrift Savings Plan, 2020,
www.tsp.gov/InvestmentFunds/FundsOverview/comparisonMatrix.html.
[2] Nickolas, Steven. “The Formula for Calculating Beta.” Investopedia, Investopedia, 5 Feb. 2020,
www.investopedia.com/ask/answers/070615/what-formula-calculating-beta.asp.
[3] “Alpha - Learn How to Calculate and Use Alpha in Investing.” Corporate Finance Institute, CFI
Education Inc., 26 Mar. 2020, corporatefinanceinstitute.com/resources/knowledge/finance/alpha/.
[4] Federal Retirement Thrift Investment board. Calculating Periodic Returns and Compound Annual
Returns. January 2006, http://www.tsp.gov/PDF/formspubs/oc05-16w.pdf.
[5] Linton, David. “Lecture 3: Portfolio Construction, Optimization, and Investor Utility.” FBE555-
Investment Analysis and Portfolio Management, University of Southern California, 16 September
2019, Los Angeles, CA. Lecture.
[6] Linton, David. “Lecture 4: Efficient Frontiers and Capital Allocation Line.” FBE555- Investment
Analysis and Portfolio Management, University of Southern California, 23 September 2019, Los
Angeles, CA. Lecture.
[7] Lioudis, Nick. “Understanding the Sharpe Ratio.” Investopedia, Investopedia, 12 Mar. 2020,
www.investopedia.com/articles/07/sharpe_ratio.asp.