(a)
To determine:
To set the spreadsheet for calculating the relative strength of the banking sector compared to the broad market.
Introduction:
Relative strength helps to measure the extent to which a security has underperformed or it has outperformed its own industry or the entire market. Relative strength is a ratio of price of security to industry price index.
Answer to Problem 24PS
The spreadsheet is obtained and given in explanation.
Explanation of Solution
Given Information:
The data given in the website. The initial value of the sector index and S&P 500 Index is equal to 100. Each week's
The data was downloaded from the given website.
Week | SPY rate | FSRBX rate |
2013.01 | 1.64 | 1.75 |
2013.02 | 0.17 | 0.55 |
2013.03 | -0.82 | -0.92 |
2013.04 | 0.85 | 0.93 |
2013.05 | 0.59 | 0.92 |
2013.06 | -0.21 | -0.08 |
2013.07 | 0.12 | 1.37 |
2013.08 | 1.19 | 1.80 |
2013.09 | -3.27 | -2.73 |
2013.10 | -1.00 | -0.58 |
2013.11 | -0.03 | -0.87 |
2013.12 | 3.25 | 1.09 |
2013.13 | -0.23 | -0.21 |
2013.14 | -0.47 | -3.37 |
2013.15 | 0.47 | 0.00 |
2013.16 | -2.97 | -1.55 |
2013.17 | -0.91 | -1.75 |
2013.18 | 0.07 | 0.40 |
2013.19 | -0.20 | 1.11 |
2013.20 | 2.77 | 2.50 |
2013.21 | 0.11 | 0.04 |
2013.22 | 1.21 | 0.38 |
2013.23 | -0.27 | -0.89 |
2013.24 | 0.19 | 0.52 |
2013.25 | -0.85 | -0.17 |
2013.26 | -1.02 | -1.16 |
2013.27 | -0.92 | 0.04 |
2013.28 | -1.57 | 0.04 |
2013.29 | 1.72 | 0.95 |
2013.30 | -3.60 | -1.97 |
2013.31 | 0.32 | 0.83 |
2013.32 | 3.07 | 3.47 |
2013.33 | 0.88 | 1.05 |
2013.34 | 0.59 | 0.58 |
2013.35 | 0.85 | 0.54 |
2013.36 | 0.50 | 0.21 |
2013.37 | -1.49 | -1.72 |
2013.38 | 1.96 | 2.08 |
2013.39 | -1.00 | 0.16 |
2013.40 | -1.11 | -0.37 |
2013.41 | -1.13 | -1.51 |
2013.42 | 2.91 | 3.28 |
2013.43 | 3.61 | 2.73 |
2013.44 | 1.29 | 1.60 |
2013.45 | -0.86 | -2.16 |
2013.46 | 0.79 | 1.18 |
2013.47 | 0.76 | 0.19 |
2013.48 | 0.06 | -0.19 |
2013.49 | 0.57 | 0.04 |
2013.50 | 1.11 | 1.83 |
2013.51 | 0.08 | -0.15 |
2013.52 | -2.01 | -2.22 |
2014.01 | -0.17 | -1.56 |
2014.02 | -1.24 | 0.16 |
2014.03 | 0.56 | -0.28 |
2014.04 | 2.38 | 2.74 |
2014.05 | 0.45 | -0.39 |
2014.06 | -0.32 | -2.45 |
2014.07 | 0.87 | 0.36 |
2014.08 | 1.07 | 1.63 |
2014.09 | -1.91 | -1.87 |
2014.10 | -1.15 | -1.23 |
2014.11 | -1.18 | -2.58 |
2014.12 | 0.25 | 0.29 |
2014.13 | 0.48 | 0.66 |
2014.14 | -3.26 | -2.29 |
2014.15 | 1.24 | 0.75 |
2014.16 | 0.16 | 1.54 |
2014.17 | 1.15 | 0.78 |
2014.18 | -1.17 | -1.30 |
2014.19 | 2.93 | 3.13 |
2014.20 | 0.96 | -0.52 |
2014.21 | -0.09 | -0.36 |
2014.22 | 0.05 | -0.04 |
2014.23 | 1.37 | 1.65 |
2014.24 | -1.96 | -1.07 |
2014.25 | 0.46 | 0.80 |
2014.26 | 1.49 | 1.19 |
2014.27 | 1.26 | 1.65 |
2014.28 | 0.57 | 0.35 |
2014.29 | 0.16 | -0.92 |
2014.30 | -0.69 | -1.24 |
2014.31 | 0.14 | 0.04 |
2014.32 | -0.47 | 0.39 |
2014.33 | -1.39 | -1.92 |
2014.34 | 1.24 | 1.00 |
2014.35 | 1.91 | 0.63 |
2014.36 | -0.46 | 0.08 |
2014.37 | -1.67 | -2.36 |
2014.38 | 1.32 | -0.08 |
2014.39 | -2.79 | -1.73 |
2014.40 | -0.78 | 0.04 |
2014.41 | -0.45 | 1.02 |
2014.42 | 1.41 | 2.23 |
2014.43 | 1.93 | 1.03 |
2014.44 | 1.35 | 3.02 |
2014.45 | 1.10 | -0.19 |
2014.46 | 1.60 | 1.72 |
2014.47 | -0.22 | -0.68 |
2014.48 | -0.41 | -0.38 |
2014.49 | 0.55 | 0.72 |
2014.50 | 0.31 | 0.19 |
2014.51 | -1.77 | -1.73 |
2014.52 | 3.15 | 2.41 |
2015.01 | 0.19 | -0.52 |
2015.02 | -2.10 | -2.89 |
2015.03 | 2.04 | 2.59 |
2015.04 | -1.76 | -1.81 |
2015.05 | 0.29 | 0.81 |
2015.06 | 1.72 | 1.71 |
2015.07 | 0.47 | 1.35 |
2015.08 | -0.50 | -1.07 |
2015.09 | -0.13 | 0.07 |
2015.1 | 1.99 | 2.13 |
2015.11 | -0.32 | -0.51 |
2015.12 | -0.29 | -0.66 |
2015.13 | -0.23 | -0.04 |
2015.14 | -0.64 | -0.41 |
2015.15 | 1.90 | 1.37 |
2015.16 | 0.24 | 3.11 |
2015.17 | 0.80 | 0.57 |
2015.18 | -2.48 | -2.76 |
2015.19 | -1.66 | -1.49 |
2015.2 | 1.01 | 0.59 |
2015.21 | 0.48 | 1.06 |
2015.22 | -2.83 | -0.80 |
2015.23 | -0.12 | -1.72 |
2015.24 | -0.17 | -1.60 |
2015.25 | 2.29 | 2.08 |
2015.26 | -0.53 | -0.11 |
2015.27 | -2.44 | -1.30 |
2015.28 | 0.34 | 2.67 |
2015.29 | 3.25 | 2.31 |
2015.3 | 0.17 | 0.72 |
2015.31 | -0.93 | -1.99 |
2015.32 | 2.89 | 1.88 |
2015.33 | -0.67 | -1.14 |
2015.34 | 1.23 | 0.54 |
2015.35 | -0.86 | -0.54 |
2015.36 | 1.73 | 1.80 |
2015.37 | -0.38 | -0.25 |
2015.38 | 1.61 | 0.78 |
2015.39 | 1.08 | 0.67 |
2015.4 | 1.20 | 0.87 |
2015.41 | 0.15 | -1.21 |
2015.42 | 0.78 | 0.28 |
2015.43 | -1.00 | -1.01 |
2015.44 | 1.25 | 1.62 |
2015.45 | 1.58 | 0.28 |
2015.46 | -0.05 | -0.62 |
2015.47 | -0.09 | -0.49 |
2015.48 | 0.85 | 1.12 |
2015.49 | 0.65 | 2.04 |
2015.5 | -1.11 | 0.03 |
2015.51 | 0.62 | 0.34 |
2015.52 | -0.76 | -0.74 |
2016.01 | 1.92 | 0.34 |
2016.02 | -0.29 | -0.65 |
2016.03 | -0.48 | -0.27 |
2016.04 | 1.88 | 1.78 |
2016.05 | -0.60 | -0.20 |
2016.06 | 1.24 | 1.82 |
2016.07 | -0.29 | -0.50 |
2016.08 | -4.57 | -4.10 |
2016.09 | 1.52 | 0.35 |
2016.1 | -1.21 | -1.77 |
2016.11 | 3.51 | 3.45 |
2016.12 | -0.97 | -2.21 |
2016.13 | 1.58 | -0.31 |
2016.14 | 0.74 | -0.24 |
2016.15 | 2.27 | 3.50 |
2016.16 | 0.61 | -0.30 |
2016.17 | 0.93 | 0.95 |
2016.18 | -0.03 | 0.34 |
2016.19 | 1.16 | 0.60 |
2016.2 | -0.61 | -0.97 |
2016.21 | 1.57 | 0.84 |
2016.22 | -1.97 | -2.20 |
2016.23 | 1.78 | 1.47 |
2016.24 | -1.64 | -2.55 |
2016.25 | -0.08 | -0.41 |
2016.26 | 1.70 | 1.18 |
2016.27 | 1.22 | 0.24 |
2016.28 | -0.87 | -3.31 |
2016.29 | -5.46 | -5.40 |
2016.3 | -0.90 | -4.03 |
2016.31 | 0.63 | 5.80 |
2016.32 | 0.00 | 3.71 |
2016.33 | 2.51 | 0.07 |
2016.34 | -0.50 | -1.49 |
2016.35 | -1.03 | -2.52 |
2016.36 | 1.94 | 1.66 |
2016.37 | 2.55 | 2.50 |
2016.38 | 0.40 | -1.74 |
2016.39 | 2.14 | 4.61 |
2016.4 | 0.31 | -2.58 |
2016.41 | -4.26 | -7.57 |
2016.42 | 2.63 | 2.98 |
2016.43 | -1.57 | -6.13 |
2016.44 | -4.01 | -2.14 |
2016.45 | 0.45 | -1.90 |
2016.46 | -1.14 | -2.72 |
2016.47 | 3.15 | 5.34 |
2016.48 | 1.52 | -0.24 |
2016.49 | -2.48 | -6.53 |
2016.5 | 1.18 | 1.08 |
2016.51 | -0.56 | -1.15 |
2016.52 | -4.06 | -6.69 |
2017.01 | -0.82 | 0.56 |
2017.02 | -5.77 | -7.23 |
2017.03 | 0.75 | 10.91 |
2017.04 | 4.91 | 7.74 |
2017.05 | -4.67 | -6.93 |
2017.06 | 1.56 | -1.07 |
2017.07 | 0.35 | 0.45 |
2017.08 | -1.32 | -5.12 |
2017.09 | -3.08 | -6.53 |
2017.1 | -0.07 | 1.77 |
2017.11 | 2.41 | 11.69 |
2017.12 | -0.43 | -7.26 |
2017.13 | 4.09 | 3.60 |
2017.14 | -2.56 | -4.91 |
2017.15 | 3.82 | 4.78 |
2017.16 | 0.81 | 2.42 |
2017.17 | 1.37 | 1.95 |
2017.18 | -1.85 | -4.99 |
2017.19 | 2.70 | 1.13 |
2017.2 | -3.52 | -4.13 |
2017.21 | 1.96 | 0.15 |
2017.22 | -2.89 | -7.05 |
2017.23 | -0.11 | -4.31 |
2017.24 | -2.87 | -4.07 |
2017.25 | -3.08 | -4.19 |
2017.26 | -0.96 | -4.67 |
2017.27 | -1.95 | -6.75 |
2017.28 | 1.72 | 12.69 |
2017.29 | -0.40 | 1.30 |
2017.3 | 0.55 | 7.63 |
2017.31 | 2.54 | 1.03 |
2017.32 | 0.62 | -1.55 |
2017.33 | -0.40 | -3.05 |
2017.34 | -0.66 | 3.25 |
2017.35 | -3.40 | 3.86 |
2017.36 | 1.35 | 2.30 |
2017.37 | -0.99 | 10.94 |
2017.38 | -2.63 | -10.46 |
2017.39 | -8.70 | -5.66 |
2017.4 | -19.79 | -18.66 |
2017.41 | 5.33 | 9.99 |
2017.42 | -6.63 | -7.68 |
2017.43 | 11.25 | 13.61 |
2017.44 | -3.07 | -8.20 |
2017.45 | -7.71 | -6.40 |
2017.46 | -8.19 | -20.03 |
2017.47 | 13.29 | 25.04 |
2017.48 | -2.39 | -0.34 |
2017.49 | 1.20 | -6.79 |
2017.5 | -0.09 | 2.69 |
2017.51 | -1.17 | -2.55 |
2017.52 | 6.66 | -0.29 |
The graph of the relative strength for S&P 500 and FB is plotted as shown below.
Using the price movements and their changing speed, the relative strength of the fund against the S&P 500 can be measured.
(b)
To determine:
To determine the instances where the relative strength ratio exceeds by minimum 5% from its 5 weeks prior value and to determine the number of weeks immediately following the substantial increase the banking sector outperforms S&P500 and how many weeks banking sector underperforms S&P500.
Introduction:
Relative strength helps to measure the extent to which a security has underperformed or it has outperformed its own industry or the entire market. Relative strength is a ratio of price of security to industry price index.
Answer to Problem 24PS
As per the data, 16 times the relative strength increases more than 5%.
Explanation of Solution
Given Information:
The data is available in the given website.
Of the 255 total instances, there are 16 times when the relative strength increases by greater than 5% over the interval of 5 weeks.
Here, it is seen that 10 times the FSRBX underperformed the S&P 500 following the increase of more than 5%.
There are 6 times that FSRBX outperformed S&P 500 following the increase of more than 5%.
(c)
Requirement 3 To Determine:
To determine the instances where the relative strength ratio decreases by minimum 5% from its 5 weeks prior value and to determine the number of weeks immediately following the substantial decrease the banking sector outperforms S&P 500 and how many weeks banking sector underperforms S&P 500.
Introduction:
Relative strength helps to measure the extent to which a security has underperformed or it has outperformed its own industry or the entire market. Relative strength is a ratio of price of security to industry price index.
Answer to Problem 24PS
As per the data, 31 times the relative strength moves decrease by greater than 5%.
Explanation of Solution
Given Information:
The data is available in the given website.
Of the 255 total instances, there are 31 times when the relative strength decreases by greater than 5% over the interval of 5 weeks.
Here it is seen that 17 times the FSRBX underperformed S&P 500 following the decrease of more than 5%.
There are 14 times that FSRBX outperformed S&P 500 following the decrease of more than 5%.
(d)
Requirement 4 To Determine:
To determine how well the relative strength rule functions in identifying the selling or buying opportunities.
Introduction:
Relative strength helps to measure the extent to which a security has underperformed or it has outperformed its own industry or the entire market. Relative strength is a ratio of price of security to industry price index.
Answer to Problem 24PS
The data above gives the bullish sign and the bearish sign.
Explanation of Solution
Given Information:
The data is available in the given website.
We can see the relative strength depicted in second part of requirement as bullish sign. Here the FB fund is likely to underperform the S&P 500 index than to perform better than the index according to this signal.
We can see the relative strength depicted in third part of requirement as bearish sign. Here the FB fund did not give a better performance than the index but there is no statistical difference succeeding the considerable change in the relative strength. There is random successive performance.
Want to see more full solutions like this?
Chapter 12 Solutions
EBK INVESTMENTS
- Please refer to the formulas in the lesson and on the following website. Pay particular attention to the examples. Scroll to the bottom to review a table with a list of various formula arrangements that may be helpful in completing this assignment. 1) If you deposit $4,000 today in a bank account and the interest is compounded annually at 10 percent, what will be the value of this investment: a. five years from now? b. ten years from now? c. fifteen years from now? d. twenty years from now? 2) If a business manager deposits $12,000 in a savings account at the end of each year for twenty years, what will be the value of her investment: a. at a compounded rate of 12 percent? b. at a compounded rate of 18 percent? 3) The chief financial officer of a home health agency needs to determine the present value of a $60,000 investment received at the end of year 15. What is the present value if the discount rate is 5%? 4) After completing her residency, an obstetrician plans to invest $9,000…arrow_forwardSuppose that you have $1,000,000 available for investment for a period of four years. After investigating the local banks, you have compiled the following table for comparison. Bank Interest Rate Compounding Scotia Bank 10% Annually National Commercial Bank 9% Semi-annually First Caribbean International Bank 6.8% Quarterly Using the Future Value concept (based on calculation), in which bank should you deposit your funds to maximize return and why?arrow_forwardIf a cell in an Excel spreadsheet uses the following formula to determine the future value of an investment: =FV(0.0745/12, 12*30,-145) How much money is being invested each month? (Express your answer rounded correctly to the nearest cent!) %$4 What is the APR? (Express your answer correctly rounded to the nearest hundredth of a percent!) % If you haven't answered the question correctly in 3 attempts, you can get a hint.arrow_forward
- Suppose that you initially invested 10,000 in the Stivers mutual fund and 5,000 in the Trippi mutual fund. The value of each investment at the end of each subsequent year is provided in the table: Which of the two mutual funds performed better over this time period?arrow_forward8. You are evaluating five different investments, all of which involve an upfront outlay of cash. Each investment will provide a single cash payment back to you in the future. Details of each investment appears here: 2. Calculate the IRR of each investment. State your answer to the nearest basis point (i.e., the nearest 1/100th of 1%, such as 3.76%). Review Only Click the icon to see the Worked Solution (Calculator Use). Click the icon to see the Worked Solution (Spreadsheet Use). The yield for investment A is %. (Round to two decimal places.) The yield for investment B is %. (Round to two decimal places.) The yield for investment C is %. (Round to two decimal places.) The yield for investment D is %. (Round to two decimal places.) The yield for investment E is %. (Round to two decimal places.) 2: Data Table Initial Future End of Investment Investment Value Year A $1,800 $6,387 14 B $9,500 $14,353 11 $600 $3,091 17 D $3,500 $4,505 3 E $5,800 $12,092 12 (Click on the icon located on the…arrow_forwardPercentages need to be entered in decimal format, for instance 3% would be entered as .03 in cell B12.) Set up an amortization schedule for a $60,000 loan to be repaid in equal installments at the end of each of the next 20 years at an interest rate of 20%. What is the annual payment? After you input the data for each scenario, click on the Graph tab (second tab on the worksheet) and look at the Principal and Interest portions of the payments throughout the years. What do you notice about the amount of Principal and Interest over the years (which amount is higher in the early years, and which amount is higher in the later years) of the loan? What do you notice about the difference in Principal and Interest in the 10% scenarios compared to 20% scenarios?arrow_forward
- Model each investment as an equation, and then arrange the investments in ascending order (from least to greatest value) based on their values after three years. Put from least to greatest in value after three years.$2,100 in a money market account that yields 12% per year$2,800 in a term CD that yields interest of 4% per year$2,520 in a savings account that yields interest of 5% per yeararrow_forwardSolve step by step using excel: Upon starting your new job after college, you've been confronted with selecting the investments for your 401(k) retirement plan. You have four choices for investing your money: A money market fund that has historically returned about 1% per year. A long-term bond fund that has earned an average annual return of 4.5%. A conservative common-stock fund that has earned 6.5% per year. An aggressive common-stock fund that has earned 9.0% per year. If you were to contribute $5,500 per year for the next 35 years, how much would you accumulate in each of the above funds? Now, change your worksheet so that it allows for less than annual investments (monthly, biweekly, etc.). The annual investment will be the same, but it will be made in smaller, more frequent, amounts. Set up a scenario analysis that shows your accumulated value in each fund if you were to invest quarterly, monthly, biweekly, and weekly. Create a scenario summary of your results. What relationship…arrow_forwardAn investor is told that the following cash flow profile has a present value of $11,000 assuming that the money received at each time period is placed into an account where it earns 18% annually. For what value of X will this be true? Year Cash Flow Click here to access the TVM Factor Table calculator. $ 1 2 3 4 5 6 7 $1,300 $1,600 X X $3,000 $1,600 $400 Carry all interim calculations to 5 decimal places and then round your final answer to a whole number. The tolerance is ±5.arrow_forward
- You invest money in a fund that paid 9% simple interest paid annually. If it is worth $21,200.00 in 11 months, a) what is the present value? $ b) What is the rate of return? (answer as a percent 2 decimal places)arrow_forwardAn account invested in a money market fund grew from $73,265.45 to 73,554.71 in a month. What was the interest rate to the nearest tenth? What was the interest rate? (Do not round until the final answer. Then round to the nearest tenth as needed.)arrow_forwardSuppose you invested $50,000 into an account at a private bank that attracts annual interest of 20% using this formula A= Pe^rt . How long would it take to ensure it grows to become $88,000?arrow_forward
- Essentials of Business Analytics (MindTap Course ...StatisticsISBN:9781305627734Author:Jeffrey D. Camm, James J. Cochran, Michael J. Fry, Jeffrey W. Ohlmann, David R. AndersonPublisher:Cengage Learning