Mastering Binary Variables in Excel: A Guide to Econ 308 Assignment 02

Abstract

Assignment 02 Summer 22 – ECON 308Due 7/27 In the Sakai lesson “Pre-Course Excel_Stats” there are several sub-lessons. Of particular interest for this assignment is the sub lesson “Week 00 Part 7 Binary Variables” There are 4 answers required and only 4 answers accepted. You must do: Either Question 1 or 4 or 2; And either question 5 or 3 or 6; And Question 9 or 7; And Question 8 Each numbered question is weighted the same, but the parts of each numbered questions have various weights. The weights are shown below. Total Points Earned 0   points avail. points earned   points avail. points earned   points avail. points earned Question 1 25 0 Question 4 25 0 Question 2 25 0 1a 3 0 4a 4 0 2a 2 0 1b 3 0 4b 4 0 2b 2 0 1c 8 0 4c 8 0 2c 7 0 1d 4 0 4d 4 0 2d 4 0 1e 7 0 4e 5 0 2e 10 0   points avail. points earned             Question 8 25 0             8a 10 0             8b 15 0               points avail. points earned   points avail. points earned       Question 9 25 0 Question 7 25 0       9a 2 0 7a 4 0       9b 2 0 7b 3 0       9c 10 0 7c 4 0       9d 10 0 7d 14 0       9e 1 0               points avail. points earned   points avail. points earned   points avail. points earned Question 5 25 0 Question 3 25 0 Question 6 25 0 5a 4 0 3a 15 0 6ai 2 0 5b 3 0 3b 10 0 6aii 3 0 5c 4 0       6b 2 0 5d 14 0       6c 2 0             6d 7 0             6e 9 0 Each numbered question is unique in some ways. Here is a narrated guide of each numbered question This is pretty much a general nature question which relies on the idea of adverse selection in a lending process. This question is about the actual inflation rate, the real rate (defined in ex post), and the 1 year rate on government bonds. Yes this is a Fisher Equation question at hear. After identifying such rates you are asked about some impacts and policy. Question d relates to policy. Question e is something that is very practical. Question 3 is somewhat about the Fisher equation, but more directly associated with the aggregate supply and demand model. This question is about risk premiums on bond yields. There are some easy calculations to do and some implications of that idea. Question 5 is about the aggregate supply and demand model. Recent publications about how Economists handled the forecasting and analysis during the Covid Pandemic raised questions. There were many statements that “Economics is dead.” Well it isn’t. However, one problem for the current state of the world is the idea of “potential GDP”. This plays a big role in the aggregate supply and demand model. Here I am asking you if the numbers for potential GDP are reasonable as published. Some would say yes and some would say no. The issue is how we define potential GDP. Perhaps the economic development while Covid was here casts a shadow on things? Question 6 is again about the Fisher Equation. It is a good concept that can’t be ignored. Question 7 is really about the deposit/expansion or money/expansion chapter. We have an idea of the multiplier from that chapter, it is a big idea. However, it may have changed over time. Holistically this question seeks evidence of one of the reasons for that change. Excess reserves are a huge factor today. Another one is the public desire to hold cash which has vacillated in recent years. A banking balance sheet. Oh my. We begin in a world of the money or deposit expansion process (Chapter 17). The story is easy enough to follow there, reserves increase, the bank’s loans and deposits increase as well. There is a problem though. That idea, as explained, needs to be fleshed out because there is also a likely binding capital requirement. This is a big deal in banking today. This is a pretty straight forward and clear estimation regarding material in chapter 18. As complex as you wish to make it, but as easy as simple demand and supply. Here is a hint: “If I increase the supply of lemons the price of lemons falls.” Here is another hint: “If I decrease the supply of lemons, the price of lemons rises.” You are asked if the “FED” is doing what it says it was going to do. Interesting. For this question we consider a brief look at the lending process. In particular, we are put into the position of being a lending officer at a financial institution. You are provided hypothetical data. This is not real data but it is derived from real-world data. We are not going to forecast or explore a “yes/no” decision on loans – I wish we could but the statistical tool for that is hard to do and beyond a BS in business level. However, we are going to do something that takes us far along that trail. We are considering that Late Payments over the prior two years are a valid measure of credit-worthiness and that is what we explore. So our emphasis is on determining or explaining the number of late payments an individual has over the prior two years, and what drives, or impacts that number. Answer the following The average number of late payments for the sample is ______ The average number of late payments for females is ______ The average number of late payments for males is ________. Create a table showing for each income range, the average number of Late Payments for that income range. Your table should have a descriptive header and the rows and columns should be clearly labelled so a reader would know what they are. Estimate the Regression: LtPymt = B0 + B1*Cscore + B2*Income + B3*Age + B4*Female Write out your estimated equation algebraically. Explain the impact on late payments of the credit score, the income, the age, and gender. The “impact” would mean positive or negative and numerically a large or small impact. Answer this question in the context of adverse selection. Provide a layman’s definition of adverse selection first. Then explain, based on your regression model LtPymt = B0 + B1*Cscore + B2*Income + B3*Age + B4*Female whether or not applicants have an interest in “overstating” their income and whether or not loan officers have an incentive to incur some costs in verifying income measures. Estimate the Regression: LtPymt = B0 + B1*Cscore + B2*Income + B3*Age + B4*Female + B5*College + B6*Grad. Write out your estimated equation algebraically. Explain the impact on late payments of the credit score, the income, the age, gender, being a college graduate at the bachelors’ level, and being a holder of a graduate degree. Note, as explained in the supporting Sakai lesson – the interpretation of College and Grad are “compared” to being a high school graduate. If you were managing the lending operations for the financial institution and you had reason to fear victimization by moral hazard in the loans you have made and in future loans you were making; what additional questions would you ask on the loan application? Fully explain the logic of each question you propose to ask. Limit yourself to identifying two additional questions. Be sure to consider the “legality” of what you might ask. Data Items for Question 1 – Found in ASSGN02.xlsx in the tab “Question1”. KNT Simply a number showing an order of observations LtPymt The number of late payments an individual applicant had over the prior 24 months. Cscore The applicants credit score Income The applicant's reported income expressed in 10,000's. So an income of $50,000 is reported as 5.0. The "scaling" by 10,0000 does not impact the nature of our findings. It is done to make the numbers look nicer. IRange Designation of which income range an individual is in. Note the item is not in column order. Age The applicant's age reported in decades, i.e. divided by 10. This is done only to make the results "prettier." Female A binary variable, given a value of 1.0 for Females and 0.0 for males. A binary variable designating whether or not an individual has a college (bachelors') level degree as their most advanced degree. Equals 1.0 if the applicant has a degree and 0 otherwise. Grad A binary variable equal to 1 if the applicant has a graduate degree, 0 otherwise HS A binary variable equal to 1 if the applicant has a high school degree as the highest earned degree, 0 otherwise. Use the data in the tab Question02 of the file ASSGN02.xlsx. The data item 1 Year Inflation is calculated as the yearly change in the CPI expressed not in a percent format but as a whole number, i.e. the result is multiplied by 100. The value for 1987 is 1.365 Verify this number showing me the calculation that created it. I.e. write it out in a calculable form. Using the 1 year government bond rate, for each month in the file calculate the ex-post real inflation rate call this newly created variable 1YearRealRate. The value for 1986.01 is 3.76. Here report the average of 1YearRealRate _________. For each year in the table below show me the average values for 1 Year Inflation, DGS1, and 1YearRealRate. Values Based on Monthly Averages Year 1 Year Inflation DGS1 1YearRealRate 1986 1987 1996 1997 1998 1999 2007 2008 2009 2018 2019 2020 2021 2022 (First 5 months) We learned in the “macro part” of the course that one weapon that the Federal Reserve has in terms of impacting the economy is to lower interest rates. Identifying the “periods of crisis” in 2008-2010 and the years 2020 – 2022 do you see evidence of this effort? Fully explain. Considering the values of the 1 year real rate for the last 3 years in the table is it likely or not that people in the US are going to work more years and/or retire later than they would if the real interest rate was positive? Fully explain and justify. This question requires understanding the context of the real interest rate based on the Fisher Equation. In the previous question you calculated a real interest rate for a number of years and noted the difference across the years. Then you wrote about a real world implication of that finding. Now you are asked to put that finding in the context of the aggregate supply aggregate demand model discussed in the text and part of our classwork. Each option under this question require a graphic of a supply and demand model showing changes in one or more curves – you can make your own or you can “borrow one.” If you borrow one you need to cite its source or plagiarism, intended or unintended will have occurred. You have images in your text that might be appropriate, you have images on the web obtained via a google search that might be appropriate. You have a pencil and paper ! List three beneficial aspects (in terms of economic efficiency, stability, or growth) of the lower interest rates sought by government policy, they can be either on the aggregate demand or the aggregate supply (short or long term). Include for each aggregate supply or aggregate demand impact a diagram of a supply and demand model. Narrate the diagram, explaining what it is and how what you say is “shown” in the diagram. Fully explain and justify. List two harmful or non-beneficial aspects (in terms of economic efficiency, stability, or growth) of the lower interest rates sought by government policy, they can be either on the aggregate demand or the aggregate supply (short or long term). Include for each aggregate supply or aggregate demand impact a diagram of a supply and demand model. Narrate the diagram, explaining what it is and how what you say is “shown” in the diagram. Fully explain and justify We learned of the idea of a Risk Premium a concept based on the yield of government bonds which are safe relative to the yield on bonds that are not as safe. There is data in the tab “Question04” please read the file to see what the definitions are. Define the idea of a risk premium. Give an example of risk premium and show the calculation to determine it. While doing so differentiate between the risk of default and the risk of inflation on bond prices and yields. If you are advising your parents on their investment practices explain to your parents how they should be careful in buying those bonds from XYZ Insurance which are paying 5.89% instead of buying those 20 year bonds from the US Government that are paying 3.49%. Do so in terms they can understand. Include the phrase risk premium in your answer. Calculate the risk premiums between government and non-government bonds. Use the 20 year government bond rate compared to the AAA rate,...