The attached spreadsheet can be used to solve Cramer’s Rule Problems for a two equation system with two variables.
Link to spreadsheet:
Author: economicmemos
Finance Data
Attached is a spreadsheet with financial data.
The first dataset involves data on sales growth for 35 companies in a growth fund and 35 companies in a value fund. Fund number one is the the growth fund. Fund number two is the value fund. The data is year over year for the most recent quarter, usually the third quarter of 2017.
Go here for this sales growth data:
Using Excel to Calculate Mortgage Qualification Amounts
The attached spreadsheet shows how to use an Excel spreadsheet to calculate the amount of mortgage a person with student or consumer debt might qualify for. One scenario estimates the impact of increasing maturity of student loan from 10 years to 20 years on increase in potential mortgage and cost to student borrower.
Examples of solving for mortgage qualification amounts in Excel.
Using contingency tables to measure systematic risk.
Contingency Tables Versus Beta
Question: Below are two contingency tables describing the relationship between daily stock prices and daily changes in the S&P 500 for two companies. One of the companies is a high-beta firm. The other company a low-beta firm.
| Movements in Stock Price Versus S&P Company One | |||
| Stock Close<Prior Low | Stock Between Prior Low and High | Stock Close>Prior High | |
| S&P Close < Prior Low | 12 | 18 | 4 |
| S&P Between Prior Low & High | 26 | 80 | 28 |
| S&P > Prior High | 5 | 31 | 48 |
| Movements in Stock Price Versus S&P Company Two | |||
| Stock Close<Prior Low | Stock Between Prior Low and High | Stock Close>Prior High | |
| S&P Close < Prior Low | 10 | 21 | 3 |
| S&P Between Prior Low & High | 19 | 68 | 47 |
| S&P > Prior High | 14 | 48 | 22 |
The rows of the contingency table were formed by comparing the S&P close to the value of the low or high of the S&P on the previous market day.
The columns of the contingency table were formed by comparing the closing stock price to the low or high of the stock price on the previous day.
What table depicts the high beta stock? What table depicts the low beta stock? Defend your answer?
Use Stata to calculate Kendalls Tau B and Spearman’s rank correlation coefficient for the two firms.
How do these non-parametric statistics differ for these two firms?
Discuss implications of these results for future research.
Analysis:
The count of observations on the diagonal of the contingency tables starting from the top right cell to the bottom left cell of the table represent the number of instances where stock prices move in tandem with the market.
The proportion of observations on this diagonal is 55.6% for company one compared to 39.7% for company 2.
Beta is the most common measure of the movement of stock prices with the market so it is relatively clear that company one has a higher beta than company two.
In fact, company one is Apple and company two is Duke Power. According to yahoo finance, the beta for Apple is 1.40 and the beta for Duke Power is 0.01. The values of the proportions of observations on the diagonal of the contingency table appear to correspond to yahoo finance beta estimates. (Sample size of two is admittedly very small.)
Kendall’s Tau and Spearman’s rho are two statistics commonly used to measure the association between row and column variables of a contingency table. The table below lists these two statistics for these two companies
| Comparison of Non-Parametric Measures of Association Between Stock Price Movements and Market Movements | |||
| Apple | Duke | Ratio Apple to Duke | |
| Kendall’s
Tau |
0.3672 | 0.0247 | 14.9 |
| Spearman’s
Rho |
0.3980 | 0.0658 | 6.0 |
Concluding Thoughts: Financial analysts use beta to measure the price of a stock. The preliminary results presented here indicate that contingency tables and non-parametric statistics can be used to measure the association between movements company stock price and the overall market. More research will be available on this topic shortly.
Authors Note: I am planning the launch of a monthly financial and economics newsletter. Please follow this blog to obtain advanced notice of this product.
Proposal One: Increased Financial Assistance for First-Year Students
There is little doubt that failure to reduce college debt levels will dramatically worsen the economic situation of many people who borrow for college. However, free college or debt-free college is not a viable economic program. This proposal targeting aid towards first-year students is part of an economically viable program to reduce burdens caused by college debt.
Description of program: The federal government would provide all accredited four-year and two-year universities with funds to cover 50 percent of the tuition of the average four-year state university for all first-year students with household income less than a threshold. The subsidy would be phased out for students above the threshold. Schools and states could differ on the income threshold defining the group of students obtaining a subsidy. Schools that wish to participate in this program would be required to match 25 percent of the federal contribution.
Comment One: The program is far less costly and far less ambitious than the programs offered by Senator Sanders and Secretary Clinton. The total estimated cost of Senator Sanders’ proposal is $70 billion a year, with around two thirds of the costs of the program borne by the federal government and one third borne by state governments.
Comment Two: The program described here allows for greater control at the state level than the program advocated by Hillary Clinton. Hillary Clinton’s proposal would provide grants for tuition assistance only in states that fund public universities at a high level. Her proposal also includes penalties for universities that fail to meet certain goals including reducing costs, earnings of graduates, and reduced tuition. These conditions are problematic because state differ in their resources and priorities. Moreover, this approach would favor state university systems that emphasized research over teaching
This program is designed to allow schools and states to select a sustainable subsidy level. The subsidies proposed by Sanders and Clinton will prove to be unsustainable during economic downturns.
Comment Three: The four-year exclusively-for-public university benefit would have a devastating impact on enrollment in all private universities including historically black colleges. Many of these universities have excellent track records for educating students and preparing students for careers. This benefit is available at both private and public universities but the total cost of the program is much lower because it targets first-year students only.
Comment Four: First-year students with no academic track record past high school are more likely to drop out of college than students further along in their academic career. The possibility of failing academically and incurring debt at the age of 18 may deter some students from entering college even though education past high school is increasingly necessary for career advancement. Targeting financial aid for first-year students is the most effective way to enroll students who might otherwise be deterred from every trying college.
Comment Five: The government pays interest on some student loans until the student leaves school and starts repayment. On other student loans, the cost of interest is borne by the student as soon as the loan is issued. Cumulative interest on student debt is highest when debt is issued years prior to the initiation of repayment. Cumulative interest on student loans are best reduced through subsidies that reduce loans incurred by first-year students.
Comment Six: Students who drop out of college after one year of school have lower salaries than students who complete more college. The lower salaries of students who only complete one year of college suggests that a program targeting first-year students will be more progressive than a program targeting all students throughout their entire career.
The lower salaries for students who incur first-year debt and leave school will also result in higher default and delinquency rates. Programs that reduce loans for first-year students will result in a larger improvement in default or delinquency rates than financial aid programs targeting all students.
Comment Seven: The requirement that schools publish the percent of first-year students incurring debt will provide valuable information to students on the cost of their education prior to students incurring too much debt. This requirement will create an incentive for colleges to expand assistance for first-year students, which as shown in the comments here results in substantive benefits for the borrowers and the government lender.
Concluding Comment: The increased financial assistance in this proposal is far smaller than the increased assistance offered under the more expansive proposals presented by Secretary Clinton and Senator Sanders. A totally free college program provides extensive assistance to many people who do not need help. Around 30 percent of graduates from four-year institutions finish school with no debt and many student borrowers have sustainable debt levels
The principle, that subsidies should target people who would not otherwise do the socially desirable action is central to proper public policy. One should not target tax subsidies for fuel efficient cars if people were eager to buy the car without the subsidy. Similarly, financial assistance should target students who could not enter or complete school without taking out large amount of debt.
Other initiatives including other forms of targeted financial assistance, improvements in loan forgiveness programs, initiatives to improve on-time graduation rates and improved information on school quality and costs will reduce college debt burdens in a way that is fairer to taxpayers than free college for all.
Timing of Drug Purchases and Out-of-Pocket Costs for HDHPs
Timing of Drug Purchases and Out-of-Pocket Costs for HDHPs
Background: A lot of analysts advocate high deductible health plans (HDHPs) for two reason — low premiums and access to tax deductible savings accounts. These advantages are real.
Less attention has been spent on differences between how HDPPs and standard plans differ in their reimbursement of prescription medicine. Most standard deductible health plans with low deductibles pay a share of prescription drug costs prior to the deductible even being met. By contrast, most high-deductible health plans do not reimburse any health care costs until the entire deductible is met.
Moreover, many insurance companies that sponsor both standard and high-deductible health plans have less generous benefits for certain drugs under the high-deductible health plan.
Drugs can be very expensive especially when a generic alternative does not exist. The prices of some commonly used drugs presented below was obtained from a major reputable insurer which offered both a standard deductible and HDHP.
| Prices of Some Drugs | |
| Atorvastatin | $30 |
| Cialis | $923 |
| Trulicity | $1,994 |
| Metopropol | $3.60 |
| Welchol | $1,753 |
| Janumet | $1,177 |
| Jardiance | $1,273 |
Prices are for 90-day supplies.
This insurer typically required consumers to pay around 25 percent of the cost of the non-generic drug. However, I noted that the insurer paid 25 percent of the cost for a 90-day supply of Cialis 5-mg daily use under the standard plan and 0 percent under the HDHP.
Most consumers evaluate insurance plans based on broad reimbursement levers, deductibles, out-of-pocket expense limits, and coinsurance rates. However, increasingly narrow policy rules including the reimbursement rate for a drug or whether a procedure is medically necessary can have a large impact on out-of-pocket costs and financial exposure.
There is another complexity associated with the use of high-deductible health plans and reimbursement for prescription drugs. The complexity is the result of the higher coinsurance rate for prescription drugs than for most hospital services. In some plans the coinsurance rate (the share paid by the customer) is 25 percent for prescription drugs and only 5 percent for hospital visits. This feature combined with rules governing deductibles can result in the timing of medical services impacting the total amount paid by the customer.
A person who pays $X for a car and $Y for a vacation will pay a total of $X +$Y for both items regardless of when the purchases occur. However, the amount of out-of-pocket expenses incurred by a person purchasing prescription drugs and a visit to the ER or a hospital can vary quite a bit depending upon when the purchases occur.
The purpose of this post is to show that the timing of drug purchases and visits to the hospital can have a nontrivial impact on out-of-pocket health costs when reimbursement is guided by the rules of a high-deductible health plan with a high coinsurance rate for drugs and a low coinsurance rate for hospital stays.
Examples:
Let’s illustrate the timing effect with a couple of simple examples.
Situation One: An individual-only health insurance policy has a $1,500 deductible and a catastrophic limit of $5,000. The plan has a 5 percent coinsurance rate on hospital visits and a 25 percent coinsurance rate on prescription drug purchases.
How much does a person insured by this plan pay if she purchases $1,500 of prescription drugs prior to December 2018 and then has a $1,500 ER visit in the middle of December?
How much does a person insured by this plan pay if she has a $,1500 ER visit in early January 2018 followed by $1,500 in purchases of prescription drugs though out the year?
Assessment of Situation One:
The person who starts off the year with prescription drug expenses pays $1,500 on the prescription drugs and $175 (0.05 x $1,500) for the ER visit.
The person who starts off the year with an ER visit will pay $1,500 for the visit to the ER and $375 (0.25*$1,500) for the drugs.
The difference is around 19 percent of the lower amount.
The lesson here is that you should consider scheduling your ER visits for early in the year. (Just kidding. It is obviously very hard to schedule your ER visit.)
Situation Two: A family has household coverage with $5,000 deductible. The catastrophic limit on the plan is $13,500. The family incurs two types of expenses during the year — $5,000 of drug costs and a $20,000 operation.
How much does the person who starts off the year with prescription drug expenses and ends with the operation pay in total out-of-pocket expenses?
How much does the person who starts off the year with the operation and ends with prescription drugs pay in total out-of-pocket expenses?
Assessment of Situation Two:
The person purchasing drugs prior to the operation pays $5,000 out-of-pocket for drugs and $1,000 for (0.05*$20,000) for the operation.
The person with the early operation pays $5,750 for the operation ($5,000+0.05*$15,000) and $1,250 (0.25*$5,000) for the drugs for a total of $7,000. The difference as a percent of the smaller amount rounds to 16.7 percent.
A Note: The two examples presented here result in the person who has the major-medical event later in the year incur more expenses than the person who starts the year with a major- medical event. This is not always the case when the major-medical event is very large. I hope to address this post in a separate post.
Implications: I am the first to acknowledge that disparities resulting from timing of drug purchases under a HDHP are not the largest factor impacting health insurance markets. As many have noted crappy insurance beats no insurance every single time.
However, the issue raised here is non-trivial for a few reasons. First, a few hundred dollars or a thousand dollars is a non-trivial amount for a household struggling with student debt or just generally living pay check to pay check. Second, the lack of transparency on this issue reduces the credibility of the insurance industry and the economists and financial advisors who work for it. Third, based on the calculations demonstrated here some people may underutilize pharmaceutical drugs and/or delay needed medical services. Fourth, based on the findings presented here policymakers should consider changing the rules governing eligibility for health savings accounts.
Other readings on advantages of different plan types can be found here.
Health Insurance Math – Problem One
http://www.dailymathproblem.com/2013/12/health-insurance-policy-math-post.html
High Deductibles Versus High Out-of-Pocket Limits
http://www.dailymathproblem.com/2013/12/high-deductibles-versus-high-out-of.html
Health Plan Comparisons:
http://www.dailymathproblem.com/2013/12/health-plan-comparisons.html
My sense from these papers is that lower deductible plans with high catastrophic limits and higher coinsurance rates would benefit both the industry and the consumer. The decision to only provide tax preferences for holders of high deductibles was a very bad one.
I would be happy to write the definitive study on this topic but funding and data are both needed.
Buy or Rent a House?
Discussing Buy Versus Rent Calculators
Realtor groups have created a number of on-line calculators that attempt to provide an objective view of the advantages of buying a home versus renting a home. The link to one such calculator is presented below.
http://www.realtor.com/mortgage/tools/rent-or-buy-calculator/
First, I describe the calculator. Then I critique it.
Description of Calculator:
The simple version of the buy/rent calculator at www.realtor.com allows one to put in an address and get financial estimates for renting or buying. The more advanced and interesting version allows a consumer to select assumptions on costs of buying and cost of renting.
The key assumptions on the cost of buying involve home price, down payment mortgage term, buying costs, selling costs, house appreciation, real estate taxes and miscellaneous homeowner fees.
The most important renting costs include the initial rent and the yearly appreciation in rent.
Other key assumptions include the exemption of $500,000 on capital gains in housing, an investment return, and an inflation rate.
Based on the inputted assumptions the model provides an estimate of the amount of time that it takes for buying a home to be cheaper than renting a home.
The model at www.realtor.com assumes that buying costs are 4.0 percent of the purchase price and selling closing costs are 6.0 percent of the final sales price. Due to transaction costs associated with home purchases, renting will be less expensive than buying for people who stay in a house for a short period of time. The output of the model is the number of years it takes for buying to be less
Comments on the calculator at www.realtor.com
Comment One: Often realtors and bankers persuade young homebuyers to use available cash for a down payment rather than immediately retire consumer or student debt. The model does not have an option to explicitly consider the impact of credit card debt or student debt on the buy versus rent outcome. The model does require input on the assumption of investment returns. One way to model the impact of keeping debt is to increase the investment return assumption so that it equals cost of credit cards and student loans. It would be useful if the model allowed for separate assumptions on investment return and the cost of existing debt.
Comment Two: I modified one example to consider the breakeven point of a transaction with a 15-year FRM at current interest rates. I found that buying was preferable to renting after a 6-year period for the 15-year FRM compared to 8 years for the 30-year FRM. Essay Four provides more information on mortgage choice and lifetime savings.
Comment Three: The model cannot be easily modified to allow for interest rate uncertainty associated with adjustable rate mortgages.
Comment Four: The model requires an assumption of average annual growth in house appreciation over the entire period and does not consider issues related to the uncertainty of future house appreciation. House prices do not appreciate in a steady or reliable fashion. The realtor’s model would have severely overestimated the value of buying a home during the 2004 to 2009 time period and would have underestimate returns from purchasing in 2011 or 2012. The argument that housing prices would continue to rise was made quite strenuously in 2007 and was used to motivate unrealistic price appreciation assumptions in the breakeven analysis.
The house price appreciation assumption is usually based on what the analyst expects will occur. An alternative approach would involve basing this parameter on the certainty equivalent. A certainty equivalent is the guaranteed return that someone would accept rather than take a risk on a higher but uncertain return.
Comment Five. Many people are forced to move because of a new job or divorce. The rent versus buy calculator does not allow for economic costs associate with moving when house prices fall and house equity turns negative. Nor does the buy versus rent calculator consider economic costs associated with negative equity that make it difficult for a home buyer to refinance should interest rates fall.
The more relevant question not answerable from this calculator is it better for a person to buy now or reduce debt and buy in a couple of years.
Comment Six: Often realtors will expect home sellers to put additional investments into the property prior to selling the home. (Most recently in many neighborhoods realtors are pushing home sellers to install granite kitchen tops.) The model does not include an option to consider likely upgrade costs. It may be able to correct for this problem by reducing the price appreciation assumption in the model. However, the need for upgrades appears to differ widely across properties.
Comment Seven:: The buy-sell calculator can also be used to evaluate mortgage properties financed with FHA loans. The FHA loan program is geared for relatively small mortgages. The program has a loan limit that varies across counties and can change over time. The FHA loan program allows for down payments as low as 3.5% FHA loan costs include mandatory mortgage insurance premiums, part of which is paid up front. Due to the insurance premiums the cost of the FHA loan is often one percent point higher than the cost of conventional loans. Most often, the number of years it takes for a home buyer to break even on an FHA loan program will be substantially higher than the number of years it takes to break even on a transaction financed with a conventional loan. Not surprisingly, the use of real estate break- even calculators is usually illustrated with conventional loan examples rather than FHA loan examples.
Comment Eight: The assumption regarding the rate of appreciation of rents has a major impact on the buy versus rent decision. A larger percent of people are choosing to rent rather than buy consequently more rents are continuing to rise often at a rate that exceeds the increase in the value of the home. In some markets it may be legitimate to assume a higher increase in rents than home prices. This alternative assumption might persuade more people to buy rather than rent.
Comment Nine: Realtors often argue that a house purchase should occur now rather than later because macroeconomic conditions are about to change. Over the last three or four years realtors have argued that people should buy because the FED is about to raise interest rates. An increase in interest rates induced by Fed policy would increase the cost of interest on a home but might also lower house prices.
The Fed will eventually raise interest rates but even Nobel Prize winning economists are confused about when this will happen. Potential homebuyers should not rely upon the interest rate forecasts of realtors when determining whether or not or buy or rent a home.
Concluding thoughts on the Limitations of Buy Versus Debt Calculators: My comments suggest that for a wide variety of reasons buy versus debt calculators often overstate the case for buying rather than renting a home. The approach relies on subjective assumptions on a wide variety of economic variables. Assumptions on the most crucial variable – the future growth of housing prices have been grossly inaccurate in the past.
The one factor that favors buying over renting in the current environment is that stock prices are currently at historic highs and long term interest rates are at historic lows. I suspect that based on the current market conditions returns on real estate will outpace returns on financial assets in the near future. Hence an assumption of a low future return on financial assets might be justified at this time.
The buy versus rent calculator does not accurately measure the benefits of delaying a home purchase until consumer debt and student loans are substantially reduced or eliminated. Nor does the model allow for active consideration of costs, which might be incurred if a young worker with little initial house equity is forced to sell a home in order to take advantage of a new job opportunity. Usually younger households will be much better off by delaying the home purchase and using all available funds to retire student loans and consumer debt.
Two Ways to Calculate a Portfolio PE Ratio
Two Ways to Calculate a Portfolio PE Ratio
Question: The table below contains data on the market cap and the earnings for four high-tech firms.
| Market Cap and Earnings for Four Tech Firms | ||
| Market Cap
($ B) |
Earnings
($ B) |
|
| AAPL | 892.16 | 46.65 |
| MSFT | 585.37 | 21.2 |
| AMZN | 475.37 | 1.92 |
| TWTR | 13.11 | -0.44797 |
In this post, I am asking you to use two methods to calculate the PE ratio of this four-stock portfolio and to confirm that both methods provide the same answer.
Method One:
Calculate the PE ratio of this portfolio by taking the sum of the market cap numbers for the four stocks and dividing by the sum of the earnings of the four stocks.
Method Two:
Calculate the ratio of (market cap minus earnings) divided by market cap for the four stocks.
Calculate a weighted average of the values (MC-E)/MC for the four stocks with the ratio weighted by MC. Give the name to this weighted average the letter f.
Calculate 1/(1-f).
Show that the PE ratio from method one is identical to 1/(1-f).
Analysis:
The straight forward way to calculate the PE ratio by taking the ratio of the sum of the market caps to the sum of the earnings is presented below.
| Portfolio PE Ratio – Method One | |||
| Market Cap
($ B) |
Earnings
($ B) |
||
| AAPL | 892.16 | 46.65 | |
| MSFT | 585.37 | 21.2 | |
| AMZN | 475.37 | 1.92 | |
| TWTR | 13.11 | -0.44797 | |
| Total | 1966.0 | 69.3 | 28.4 |
This four-firm portfolio has a PE ratio of 28.4.
The PE ration calculation for method two is presented below.
| Portfolio PE Ratio — Method Two | ||||
| Market Cap | Earnings | (MC-E)/MC | Weight | |
| AAPL | 892.16 | 46.65 | 0.9477 | 0.4538 |
| MSFT | 585.37 | 21.2 | 0.9638 | 0.2977 |
| AMZN | 475.37 | 1.92 | 0.9960 | 0.2418 |
| TWTR | 13.11 | -0.44797 | 1.0342 | 0.0067 |
| 1966.01 | 1.0000 | |||
| f | 0.9647 | |||
| 1/(1-f) | 28.4 | |||
The second method for calculating a PE ratio gives the same result as a the first – 28.4.
Implications: The PE ratio of a portfolio can be expressed as function of the weighted average of the ratio of the difference between market cap and earnings of the firm to market cap of the firm. This is a very useful result.
PE ratios of firms are frequently not useful.
First, the PE ratio can become very large when earnings are very small. This means it is misleading to look at a weighted average of PE ratios because one firm can have a a very large impact. In our current example, the PE ratio of Amazon is 248 and the weighted average PE ratio for the four stocks is 77.
Second, PE ratios have no economic meaning when earnings are negative.
The PE ratio of a firm with negative earnings would reduce the weighted average of PE ratios in a portfolio. By contrast, (MC-E)/MC will be larger than 1 if E is less than 0.
A firm with slightly negative earnings would have a negative PE ratio with a larger absolute value than a firm with very large losses. This ranking of firms is incorrect because larger losses should be associated with lower relative valuations. By contrast, (MC-E)/MC will always rise when E falls.
By contrast, the ratio of the difference between market cap and earnings over market cap is inversely related to the valuation of a firm. When earnings are negative this ratio is greater than one. When earnings are zero the ratio equals one. When earnings are very small the ratio approaches one and is not an outlier. The ratio of the difference between the market cap and earnings to market cap is intuitively defined for all earnings and not impacted by outliers.
In my next post, I will show that statistical tests based on samples of the ratio of the difference between the market cap and earnings to market cap are more useful than statistical tests based on PE ratios.
Measuring Returns for Different Investment-Consumption Patterns
Measuring Returns for Different Investment-Consumption Patterns
Question: An investment advisor tells his client to invest $1,000 per month in VFIAX (Vanguard S&P fund) for five years. The person will then live off the proceeds in this fund for 36 consecutive months.
Calculate the return on assets from this investment/consumption plan for two different start dates – January 1, 2002 and January 1, 2003.
What is the NPV of investment returns from this investment strategy/ consumption plan on the same start dates?
What should investors who are planning to save for five years and spend for three years learn from this example?
Mutual funds and ETFs tend to advertise holding period returns based on specific purchase dates and specific sale dates. These returns are based on the price of securities on two dates only. What does the example presented here tell you about the usefulness of two-period return statistics reported by mutual funds?
Methodological Note: The shares purchased each month are $1,000/PVFIAX where PVFIAX is the price of the ETF. I sum over 60 months to get the total shares purchased, which I will denote TSHARES. The formula for cash inflow for the 36 months are (1/36)*TSHARE*PVFIAX.
The cash inflow/outflow column and the date column are inputted into the XIRR function in Excel to give the IRR of the inflows/outflows on these particular dates. The XNPV function gives net present value of the cash flows.
Analysis:
The value of VFIAX reached its pre financial crisis high in 10/2007 and reached its crisis trough in 02/2009. Hindsight is 20/20 but it appears as though diversification prior to the downturn would have been beneficial.
What follows are return calculations for the two scenarios.
Results are in the table below.
| Returns for Two Investment/Consumption Scenarios | |||
| Invest Period | Consumption Period | IRR | NPV |
| 2002/2006 | 2007/2009 | 12.04 | $15,766 |
| 2003/2007 | 2008/2010 | 2.98*e-9 | $801 |
Observations:
- The person who stopped saving in December 2006 did fairly well despite the financial crisis.The IRR for this investor was 12.04 %. The NPV of the investments was $15,766. (NPV calculation assumes a5 percent cost of capital.)
- The person who stopped investing in December 2007 realized a return only slightly higher than 0 percent.The NPV of this person’s investment was around $800.
Discussion of Investment Strategy:
In my view, a 100 percent VFIAX strategy is unwise for an investor with this type of investment and consumption period.
How to fix this problem is a more difficult question. It is important to note that the strategy of putting 100 percent of funds in VFIA for an investor with a start date of January 1 2009 or January 1, 2010 did quite well.
529 plans offer life-cycle funds that drift towards a more conservative investment as the person nears the date where he must spend money. Lifecycle funds would have done reasonably well for both of the scenarios considered here. However, the life-cycle approach creates miserable results when the market does poorly in the first few years of the investment period and then rebounds.
My view on how to solve this problem is evolving. A 60/40 (stock/bond) portfolio would have done well in these time periods but I don’t believe that it will work in the next crash. Interest rates are now very low and I expect in the next crisis bonds and stocks will crash together. Perhaps allocating some resources into an inflation-indexed bond fund would help balance returns during the next crisis.
The trend in investment is toward investment in passively managed funds like the ones offered by Vanguard. This is at best a partial solution. Investors need help in allocating money across several passively managed funds. This includes advice on initial allocations and reallocation over time.
I believe there is a need for an actively managed fund that invests exclusively in passively managed funds and reallocated assets across funds as market conditions change.
Note on traditional holding period statistics: The value of VFIAX in January 2002 was 17.9. In December of 2010 the value of VFIAX was 39.5. The return for this 7.9 year holding period was at 10.5%.
| Holding Period Calculation | |
| Jan-03 | 17.9 |
| Dec-10 | 39.5 |
| Holding Period in Years | 7.92 |
| ROR | 10.5% |
However a person who started investing in January 2003 and started spending in January 2008 earned squat!
The mutual funds can legally and honestly report great eight-year or ten-year holding return but their clients aren’t doing particularly well.
Such a surprise!
Expected Profit and Risk with Random Transaction Dates
Profit and risk when there are four random purchase dates and four random sale dates
Question: In 2013 a person buys QQQ the high tech ETF) on one of four randomly selected dates determined by when the broker arranges a meeting. I
The person who bought the QQQ shares in 2014 got fired in 2015. As soon as the person was fired he realized he needed cash so he called his broker and said “SELL QQQ” The firing is a random event independent of the market and out of control of the person, which occurred on one of four dates.
The four potential purchase and four potential sales dates for the QQQ transactions are presented below.
| Information on Potential Purchases and Sales of QQQ | ||||
| Potential Purchase Date | Purchase Price QQQ | Quantity purchased $25,000/Price | Potential Sale Date | Sale Price |
| 20-May-14 | 88.0 | 284.1 | 5-Jan-15 | 101.4 |
| 7-Jul-14 | 95.1 | 262.9 | 8-Aug-15 | 110.5 |
| 7-Aug-14 | 94.2 | 265.4 | 24-Aug-15 | 98.5 |
| 10-Sep-14 | 100.1 | 249.8 | 5-Nov-15 | 114.7 |
The person spends $25,000 on the purchase of QQQ in 2014 and sells all shares in 2015.
Assume no dividends are paid.
What are all possible profit outcomes from the purchase and sale of the QQQ securities?
What is the expected profit?
What is the variance of profit?
Analysis: The number of share purchased is $25,000 divided by the purchase price; hence the purchase price determines the number of shares purchased.
| Tabulation of Number of Shares Purchased | ||
| Potential Purchase Date | Purchase Price QQQ | Number of shares purchased |
| 20-May-14 | 88.0 | 284.1 |
| 7-Jul-14 | 95.1 | 262.9 |
| 7-Aug-14 | 94.2 | 265.4 |
| 10-Sep-14 | 100.1 | 249.8 |
Revenue received after the sale is price at time of sale times the number of shares owned.
Profit after the sale is revenue minus the $25,000 initial investment.
There are four possible purchase dates and four possible sale dates. The purchase and sale dates are independent so there are a total of 16 possible equally likely combinations of sale and purchase dates. The probability of each purchase/sale combination is 0.0625 (0.25*0.25).
The profit calculation for the 16 purchase-sale combinations is presented in the table below.
| Potential Profit Calculation for Four Purchase Dates and Four Sale Dates | ||||||
| Comb # | Probability | Purchase Date | Sale Date | Number of Shares Owned | Sale Price | Profit |
| 1 | 0.0625 | 20-May-14 | 5-Jan-15 | 284.1 | 101.4 | $3,807 |
| 2 | 0.0625 | 20-May-14 | 8-Aug-15 | 284.1 | 100.5 | $3,552 |
| 3 | 0.0625 | 20-May-14 | 24-Aug-15 | 284.1 | 98.5 | $2,984 |
| 4 | 0.0625 | 20-May-14 | 5-Nov-15 | 284.1 | 114.7 | $7,586 |
| 5 | 0.0625 | 7-Jul-14 | 5-Jan-15 | 262.9 | 101.4 | $1,656 |
| 6 | 0.0625 | 7-Jul-14 | 8-Aug-15 | 262.9 | 100.5 | $1,420 |
| 7 | 0.0625 | 7-Jul-14 | 24-Aug-15 | 262.9 | 98.5 | $894 |
| 8 | 0.0625 | 7-Jul-14 | 5-Nov-15 | 262.9 | 114.7 | $5,152 |
| 9 | 0.0625 | 7-Aug-14 | 5-Jan-15 | 265.4 | 101.4 | $1,911 |
| 10 | 0.0625 | 7-Aug-14 | 8-Aug-15 | 265.4 | 100.5 | $1,672 |
| 11 | 0.0625 | 7-Aug-14 | 24-Aug-15 | 265.4 | 98.5 | $1,141 |
| 12 | 0.0625 | 7-Aug-14 | 5-Nov-15 | 265.4 | 114.7 | $5,441 |
| 13 | 0.0625 | 10-Sep-14 | 5-Jan-15 | 249.8 | 101.4 | $325 |
| 14 | 0.0625 | 10-Sep-14 | 8-Aug-15 | 249.8 | 100.5 | $100 |
| 15 | 0.0625 | 10-Sep-14 | 24-Aug-15 | 249.8 | 98.5 | -$400 |
| 16 | 0.0625 | 10-Sep-14 | 5-Nov-15 | 249.8 | 114.7 | $3,646 |
| Min | -$400 | |||||
| Max | $7,586 | |||||
| Range | $7,986 | |||||
The minimum profit is -$400. The maximum profit is $7,985.
The expected profit is obtained by taking the dot product or the sumproduct of the probability vector with the profit vector. The variance was obtained from the computational formula.
Var (Profit) = E(profit2) – E(Profit)2
For a discussion of these calculations see the previous post.
http://dailymathproblem.blogspot.com/2015/11/expected-value-and-variance-of-share.html
The expected value and variance or profit from the purchase of QQQ on one of four dates in 2014 and the sale of QQQ on one of four dates in 2015 are presented below.
| Expected Profit and Variance of Profit Calculations | |
| E(PROFIT) | 2555.4 |
| E(PROFIT2) | 11036765.0 |
| E(PROFIT2)-E(PROFIT)2 | 4506556.2 |
| E(PROFIT-E(PROFIT))2 | 4506556.2 |
Financial Discussion:
The purchaser of QQQ or any stock that buys randomly and is forced to sell because of random events unrelated to the market bears substantial risk compared to an investor with enough liquid assets who will not need to sell in an emergency. Investors would be wise to consider the level of the market and their ability to hold through downturns prior to selling. The experts say that stock market returns beat returns on other securities over the long haul. But this investor was only able to hold for a year.
Outcomes could have been worse. The broker put the investor in QQQ a relatively diversified ETF that focuses on tech stocks. Had the broker put his client in one particular stock (say IBM) and the investor was forced to sell he would have realized a large loss.