Log inSign up for FREE
Library

5.7 Design your Portfolio

Last updated 10 months ago
16 questions
Note from the author:
OBJECTIVES & STANDARDS
Math Objectives
  • Write equations to assess investment performance
  • Utilize a spreadsheet as a tool to compare data and do repeat calculations
Common Core Math Standards
  • Link to all CCSS Math
  • CCSS.PRACTICE.MP4
  • CCSS.PRACTICE.MP5
  • CCSS.HSA.CED.A.1
  • CCSS.HSM
Personal Finance Objectives
  • Access historical stock price information and use it as one factor in forecasting future performance
  • Create a diversified investment portfolio using individual stocks and bond funds
  • Calculate rate of return
National Standards for Personal Financial Education
Investing
  • 1: A person’s investment risk tolerance depends on factors such as personality, financial resources, investment experiences, and life circumstances
  • 2b: Compare nominal annual rates of return over time on different types of investments, including cash flows and price changes
  • 3a: Discuss the advantages and disadvantages of investing in riskier assets
  • 3b: Investigate the long-run average rates of returns on small-company stocks, large-company stocks, corporate bonds, and Treasury bonds
DISTRIBUTION & PLANNING
Distribute to students
  • Student Activity Packet
OBJECTIVES & STANDARDS
Math Objectives
  • Write equations to assess investment performance
  • Utilize a spreadsheet as a tool to compare data and do repeat calculations
Common Core Math Standards
  • Link to all CCSS Math
  • CCSS.PRACTICE.MP4
  • CCSS.PRACTICE.MP5
  • CCSS.HSA.CED.A.1
  • CCSS.HSM
Personal Finance Objectives
  • Access historical stock price information and use it as one factor in forecasting future performance
  • Create a diversified investment portfolio using individual stocks and bond funds
  • Calculate rate of return
National Standards for Personal Financial Education
Investing
  • 1: A person’s investment risk tolerance depends on factors such as personality, financial resources, investment experiences, and life circumstances
  • 2b: Compare nominal annual rates of return over time on different types of investments, including cash flows and price changes
  • 3a: Discuss the advantages and disadvantages of investing in riskier assets
  • 3b: Investigate the long-run average rates of returns on small-company stocks, large-company stocks, corporate bonds, and Treasury bonds
DISTRIBUTION & PLANNING
Distribute to students
  • Student Activity Packet
Intro/Warm-Up
GRAPH: Diversify the portfolio
Toni is 24 years old, and they have a goal of retiring entirely by the age of 50. These are the 5-year historical returns for the 4 assets they are considering for their portfolio.

1

Which assets would you recommend, and in what proportion, to create a portfolio to meet Toni’s goal?

1

How risky is Toni’s portfolio? And how likely is it to meet Toni’s goal?

Design a Portfolio
DESIGN A PORTFOLIO:
The rest of this lesson will lead you through some steps to design your own investment portfolio using publicly traded stocks and bond funds. You will have $5000 fictional dollars to invest. You’ll want to keep in mind what you’ve learned so far about asset allocation, diversification, risk, and return.

INTERACTIVE: finviz S&P 500 Map
When you are creating an investment portfolio, you are probably going to consider adding some stocks. A good place to start is with the S&P 500, which are the 500 largest companies in the United States. In this interactive:
  • The size of the box represents the market capitalization (value of all the shares outstanding)
  • The percent is the growth or decline over the selected time period
  • And the color coding ranges from bright green (high performance) to bright red (low performance)
1

The map defaults to 1-day returns. Is today a “good day” or “bad day” in the market? How can you tell?

1

On the left side of the screen, you can change the time horizon of the performance data. Look at the map for a variety of time frames. When choosing stocks for your portfolio, would you care more about very recent returns (1 day, 1 week, 1 month) or longer-term performance (3, 6, or 12 months)? Explain.

10

Explore this interactive and find at least 10 stocks you would consider adding to your portfolio. Record their company name and ticker symbol on your paper (see image in show your work) and input the information you research in this spreadsheet. Make sure to share "anyone with the link" and past link in comments below.

ARTICLE: The Best Bond Index Funds to Buy From Vanguard and Fidelity
As you reviewed individual stock prices, you may have seen large differences between performance at different companies, or you may have seen returns change drastically depending on time horizon. One of the reasons people consider adding high grade bonds or bond funds to their portfolio is for some stability -- the swings tend to be much less dramatic. Both Vanguard and Fidelity have widely held bond funds, which you can read about here.
1

Summarize what these bond funds have in common.

1

Copy the ticker symbol for the Fidelity Short-Term Treasury Bond Index into the search on Yahoo Finance. Looking at the line graph for 1-month performance, what do you notice about the bond fund’s price per share?

1

Review this article and determine at least 3 bond funds you would consider adding to your portfolio. Record their fund name and ticker symbol on your notes paper (see drawing in show your work below) and input your Bond Fund and Ticket in linked spread sheet from question #5.

VIDEO: Learn to Create a Portfolio Spreadsheet
Watch the video on creating a custom portfolio in Google Sheets. Follow along and pause the video as needed
1

*If Absent, if in class follow teacher's directions: Create your own version of this portfolio spreadsheet and paste a link to the spreadsheet below. Be sure your sharing permissions allow your teacher to view it. Add a screenshot of your spreadsheet. Windows+Shift+S

Practice It
ACTIVITY: Create Your Own Portfolio
Through careful saving, hard work, luck, a generous gift, or some other means, you’ve got $5000 you wish to invest in a diversified investment portfolio of your own creation. Here are some guidelines:
  1. Make a copy of this template
  2. Fill your portfolio with individual company stocks or bond funds
  3. You must include at least 10 different investments
  4. Use Yahoo Finance or a similar site to look up share prices, and then round them to the nearest whole dollar for use in your spreadsheet
  5. Invest as close to $5000 as you can without going over or buying fractional shares
  6. Of course, don’t ACTUALLY buy any of these stocks or bond funds -- this is portfolio planning only
1

Paste a link to your spreadsheet below. Be sure your sharing permissions allow your teacher to view it. Add a screenshot of your spreadsheet. Windows+Shift+S

VIDEO: Learn to Generate a Pie Chart
Go back to the sample spreadsheet you created in the VIDEO: Learn to Create a Portfolio Spreadsheet section. Watch this new video on creating a pie chart based on data in Google Sheets. Follow along and pause the video as needed
1

Follow along to create this same pie chart on your own sample spreadsheet. Add a screenshot of your spreadsheet. Windows+Shift+S


2

Generate a pie chart for your portfolio. Copy/paste it below:Add a screenshot of your spreadsheet. Windows+Shift+S

ACTIVITY: Chart Your Growth (or Decline!)
We have no way to predict into the future how your specific portfolio will perform, as over a 10-year period changes in individual companies, the economy, technological advances, politics, consumer preferences, etc. can impact share prices. But, in this next section we’ll spin the Wheel of Returns and see how you fare! https://docs.google.com/spreadsheets/d/1aKhV0Wg1_Q5FQvIViTGsKPOYZawbKvgbcRJHPJxciRg/edit?usp=sharing
  1. In your Portfolio Spreadsheet, skip column H, and then label column I “Rate of Return”
2. Start at the top row and locate your first bond fund. Your teacher will spin the wheel,https://wheelofnames.com/3gu-d84, and then you should enter the rate of return as a decimal in column H.
  1. For example, if your first bond fund is in row 4, and your teacher says the rate of return is 6%, you will type 0.06 into cell H4.
3. Continue adding rates of return for each of your bond funds, using the numbers provided by the Wheel of Bond Fund Returns

4. At this point, you should NOT be filling in any rows for stocks

You may notice that your bond funds didn’t take many wild swings, but that might not be the case for stocks. To generate fictitious returns for your stock investments, your teacher will spin a new Wheel of Returns https://wheelofnames.com/3p2-qyr ,

5. Start at the top row and locate your first stock. Your teacher will spin the wheel, and you’ll enter the return as a decimal in column H.
  1. For example, if your first stock is in row 2, and your teacher says the rate of return is 5.5%, you will type 0.055 into cell H2.
6. Continue adding rates of return for each of your stocks, using the numbers provided by the Wheel of Stock Returns

7. Be sure to skip the rows that have your bond funds already completed
Now that you know all of your rates of return on individual assets, let’s figure out the value of each asset at some point in the future!
8. Label column J “Closing Value”

9. Write an equation in J2 that calculates the closing value of your row 2 asset using the Total Opening Value, Rate of Return, and a term of 10 years.

10. Use this equation to calculate the closing value for each of your assets

11. In cell J20, calculate the total closing value of your portfolio.

12. In cell I20, calculate the overall return on investment over the ten years and all assets.
25

If you were recreating this portfolio from scratch, what would you do differently? Make a copy of this Spreadsheet and: Screen shot and put your spreadsheet link in the messages below. *Make sure to share "anyone with the link"*

Exit Ticket
1

Which statement best compares the typical share price fluctuations between an individual stock and a high-grade bond fund?
1. Individual stock prices typically fluctuate more than high-grade bond funds do
2. Individual stock prices typically fluctuate less than high grade bond funds do
3. Individual stock prices and bond funds typically fluctuate almost identically
4. Individual stock prices and bond funds cannot be compared at all

1

Which two values do you need in order to calculate the return on investment for an individual investment or an entire portfolio of investments?
  1. The interest rate and the dividends
  2. The starting price and the closing price
  3. The interest rate and the closing price
  4. The duration of the investment and the starting price

1

What is one limitation of using historical return data to create an investment portfolio?
  1. Historical returns are only available for the past month, not any longer time frames
  2. Historical performance does not guarantee that future performance will match
  3. Historical performance data is difficult and costly to access
  4. Historical return isn’t meaningful due to inflation