Portfolio Project Part 2
barkersbaseball
investmentportfoliospreadsheet-guideline.docxGUIDELINE FOR PART 2- SPREADSHEET
Investment Portfolio Spreadsheet
-Scenario- Please plug in the types of securities you have selected in part 1 on the Excel Sheet
-Mild recession
-Normal Growth
-Rate of Return on your portfolio
-Expected Return
-Realized return
-Deviation from Expected Return
For Example: A SIMPLE SCENARIO
|
Return on portfolio |
|
|
Stocks |
25955.59 |
|
Bonds |
21236.93 |
|
Cash and cash equivalents |
4115.09 |
|
|
|
|
Total Dollar Value |
51307.61 |
|
Percentage gain/loss |
3% |
|
|
|
|
|
|
|
|
|
|
My Initial Investment was an amount of $50,000.00. I chose to invest in 6 different securities. At the end of a one |
|
|
|||||
|
Year period, the total value of my portfolio is $51,307.61 which represents a 3% gain. This gain meets my |
|
|
|||||
|
Requirement for a minimum 3% return on my investment. I plan on rebalancing my portfolio at the end of every year in order to gain maximum return |
|||||||
|
Dollar Gain/Loss Per Share |
|||||||
|
after 1 year |
|||||||
|
Number of shares |
|||||||
|
Stock Value At End of Year
|
OR, YOU CAN FOLLOW A DETAILED WRTIE- UP LIKE THIS: AN EXAMPLE ONLY- THEN PLUG THE DATA IN EXCEL.
|
Stocks are 60% of Portfolio. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Name of Stock |
Bed, Bath, and Beyond |
Under Armour |
PNC, Inc. |
Ford Motor Co. |
Honda Motor Co. |
Coca-Cola Bottling Co. |
Verizon |
|
Proportion of 100% |
12.50% |
12.50% |
12.50% |
12.50% |
12.50% |
12.50% |
12.50% |
|
Dollar Value Invested |
$1,250.00 |
$1,250.00 |
$1,250.00 |
$1,250.00 |
$1,250.00 |
$1,250.00 |
$1,250.00 |
|
Price: |
|
|
|
|
|
|
|
|
Date: |
|
|
|
|
|
|
|
|
May 1,2013 |
$68.81 |
$28.18 |
$67.17 |
$13.38 |
$39.19 |
$59.75 |
$52.40 |
|
June 1, 2013 |
$68.25 |
$31.00 |
$71.64 |
$15.68 |
$37.57 |
$59.79 |
$48.48 |
|
July 1, 2013 |
$71.76 |
$30.04 |
$73.49 |
$15.74 |
$37.54 |
$61.22 |
$50.36 |
|
August 1, 2013 |
$76.60 |
$34.75 |
$77.65 |
$17.19 |
$36.95 |
$63.94 |
$50.01 |
|
September 1, 2013 |
$73.74 |
$36.32 |
$72.48 |
$16.34 |
$35.94 |
$63.88 |
$47.38 |
|
October 1, 2013 |
$77.84 |
$41.20 |
$72.67 |
$17.19 |
$38.22 |
$62.22 |
$46.99 |
|
November 1, 2013 |
$76.91 |
$40.13 |
$73.37 |
$16.68 |
$40.10 |
$63.58 |
$50.49 |
|
December 1, 2013 |
$78.03 |
$40.59 |
$77.50 |
$17.06 |
$42.36 |
$67.55 |
$49.26 |
|
January 1, 2014 |
$80.03 |
$43.49 |
$76.60 |
$15.44 |
$41.35 |
$73.19 |
$49.14 |
|
February 1, 2014 |
$63.85 |
$54.06 |
$79.88 |
$14.96 |
$37.51 |
$68.28 |
$46.41 |
|
March 1, 2014 |
$67.82 |
$56.58 |
$81.78 |
$15.39 |
$36.05 |
$75.64 |
$47.31 |
|
April 1, 2014 |
$69.83 |
$58.00 |
$87.26 |
$16.32 |
$35.20 |
$85.01 |
$47.75 |
|
|
|
|
|
|
|
|
|
|
Dollar Gain/Loss Per Share after 1 year |
$1.02 |
$29.82 |
$20.09 |
$2.94 |
$3.99 |
$25.26 |
$4.65 |
|
Number of Shares |
18.17 |
44.36 |
18.61 |
93.42 |
31.90 |
20.92 |
23.85 |
|
Stocks Value at End of Year |
$1,268.81 |
$2,572.88 |
$1,623.91 |
$1,524.61 |
$1,122.88 |
$1,778.41 |
$1,138.60 |
|
|
|
|
|
|
|
|
|
|
Total Dollar Amount Invested in Stocks |
$10,000.00 |
|
|
|
|
|
|
|
Total Value of Stocks At End of Year |
$12,626.42 |
|
|
|
|
|
|
|
Dollar Gain At End of Year |
$2,626.42 |
|
|
|
|
|
|
|
Gain/Loss as a Percentage |
26.26% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Bonds are 30% of Portfolio |
||
|
|
|
|
|
Name: |
20-year T-Bond |
20-year T-Bond |
|
Purchase Date: |
November 15, 2012 |
November 15, 2013 |
|
Coupon Rate: |
3.00 |
2.875 |
|
Maturity Date: |
May 15, 1942 |
May 15, 1943 |
|
Purchase Price: |
$92.13 |
$89.16 |
|
Yield to Maturity: |
3.439 |
3.47 |
|
Current Yield: |
3.256 |
3.224 |
|
Rating: |
AAA |
AAA |
|
Quantity Purchased: |
27 |
28 |
|
Payment Frequency: |
Semi Annual |
Semi Annual |
|
Interest Payment Amount: |
$2.74 |
$2.56 |
|
Cash Equivalents are 10% of Portfolio |
||
|
|
|
|
|
Name: |
USAA Certificate of Deposit |
|
|
Purchased Date: |
May 1, 2013 |
|
|
Interest Rate: |
0.71% |
|
|
Maturity Date: |
May 1, 2014 |
|
|
Purchased Price: |
$1,682.68 |
|
|
Interest Payment at Maturity: |
$11.95 |
|
|
Total Dollar Value in 1 Year: |
$1,694.63 |
|
|
|
|
|
|
Return On Portfolio |
||
|
Stocks |
$12,626.42 |
|
|
Bonds |
$5,276.94 |
|
|
Cash Equivalents |
$1,694.63 |
|
|
Total Dollar Value |
$19,596.99 |
|
|
Percentage Gain/Loss |
1.17% |
The initial investment was $16,667.67. This was the amount the young investor received after a car accident. There are no constraints on the stocks and the investor decides to be aggressive for the stock. The investor invested mostly in the stock since she is young and has time to recoup any loss. Although the portfolio meets the requirement of returning at least 1%, this investor will need to invest in more aggressive securities in order to meet long term goals.
YOU CAN PLUG THE AMOUNT AND THE SECURITIES SELECTED, THE TIME FRAME ETC IN AN EXCEL SHEET SHOWING THE REQUIRED RATE OF RETURN, EXPECTED RATE OF RETURN AND REALIZED RATE OF RETURN.
1
