fnb100f260909a
Overview
Dr. Jeff Hong uses an Excel housing-investment model to distinguish capital gains, growth rates, and holding-period returns across two scenarios: living in a house without rental income and subletting it for cash flow. He then explains why dividing a total return by 11 years or taking a simple arithmetic average can misrepresent annual performance when returns compound, and introduces the geometric average as the more appropriate measure.
Key takeaways
- A house's capital gain is its change in value measured in dollars, while its growth rate expresses that change as a percentage of the previous year's price.
- For an owner-occupied house with no rental income, holding-period return comes from capital gain alone; a sublet scenario adds rental cash flow to capital gain.
- Excel's F4 key locks cell references so formulas can be filled across columns without changing fixed inputs such as the initial investment.
- Dividing an 11-year total return by 11 does not produce a valid compounded annual growth rate because each year's return builds on the prior year's value.
- The arithmetic average of annual growth rates, about 12.21% in the class spreadsheet, is a simple summary but may overstate compounded performance; the geometric average accounts for compounding.
- Time-series values are linked across periods: each year's price depends on the previous year's price, which creates the snowball effect of compounding.
Chapters
- Dr. Jeff Hong spends the opening minutes setting up screen sharing and resolving access and login problems.
- Students are reminded to sign in because attendance is based on the class sign-in record.
- Hong reviews the prior September 2 class, which calculated holding-period returns for two house-investment scenarios.
- Scenario 1 is buying a house to live in; Scenario 2 includes rental income from subletting the house.
- Students reopen the Module 1 Excel spreadsheet to continue the housing-return exercise.
- Total capital gain is calculated as the ending house value minus the initial value.
- Year-over-year capital gain is the difference between adjacent annual house prices, and summing those yearly gains should match the total capital gain.
- Hong demonstrates entering a formula by starting with an equals sign and selecting cells with the mouse.
- Students use Excel cell references and fill formulas down the annual data range rather than retyping each calculation.
- The workbook contains 11 annual intervals from 2005 to 2016.
- The example tracks a house whose price reaches $500,000 at the end of 2016, 11 years after 2005.
- Hong connects rising house values to increased population and housing demand, while noting that low-demand areas may see prices fall.
- Capital gain is the dollar increase in the house value, while the growth rate expresses that increase as a percentage of the prior year's price.
- For the owner-occupied Scenario 1, rental cash flow is zero, so the return comes only from capital gain.
- The holding-period return is calculated as total capital gain divided by the initial investment.
- Hong uses Excel's F4 key to lock the initial-investment reference before filling the formula across columns.
- Scenario 2 adds positive rental cash flow from subletting to the property's capital gain.
- The total holding-period return combines rental cash flow and capital gain, then divides by the initial house investment.
- Absolute references such as locked cells for the initial price and cash flow allow the formula to be dragged across the spreadsheet without shifting key inputs.
- The annual growth rate compares each year's capital gain with the previous year's house price.
- For the 2016 calculation, the prior-year price is the base used to express the dollar gain as a percentage.
- Hong emphasizes that annual growth rates vary rather than remaining constant from year to year.
- Dividing the total 2005-to-2016 return by 11 produces a simple per-year figure, but it does not account for compounding.
- Hong contrasts changing house-price growth rates with a Certificate of Deposit, or CD, that pays a fixed annual rate.
- He uses a $10,000 investment and a 10% CD rate to introduce how investment value changes over multiple years.
- To calculate a simple average of annual growth rates, students sum the yearly observations and divide by the 11-year count.
- Hong shows that Excel's COUNT function can verify the number of observations and that AVERAGE can calculate the arithmetic mean.
- The worksheet's arithmetic average is about 12.21%, but Hong cautions that it is only a rule of thumb for time-series returns.
- Hong distinguishes cross-sectional data, such as students' heights measured at one time, from time-series data, such as yearly house prices.
- A time series may trend upward, trend downward, or show no clear trend; the housing example has an overall upward trend.
- Compounding links each year's value to the previous year's growth, creating a snowball effect as later gains build on a larger base.
- Hong identifies the geometric average as the appropriate measure for summarizing compounded growth and notes that a simple average can overstate performance.
- At a fixed 10% CD rate, $10,000 grows to $11,000 after one year, and the next year's interest applies to the larger balance.
- After the main lesson, Hong answers student questions about locking Excel references, Zoom attendance, and using Excel to solve course problems.
Transcript excerpt
0:11 Okay, now I guess you can sign in now. I'm sharing my screen. Okay. And we'll be admitting. So you guys are getting on with it. Now what is the screen that it's HDMI? Yeah.
1:13 Every time every time. All right. So I uh I don't know if you have access to uh uh I mean because last time we were working on let me see it's Monday Wednesday class so we met last Wednesday. Let me go
2:10 CC space. I don't know. For some reason, I can't even log into uh a lot of things. So, good. I don't know. Okay. So, this is your class. So, uh, if I remember correctly, uh, and
3:02 I've got like six classes to handle, so my memory isn't always, uh, perfect as to where we stopped in the last class, but if I remember correctly, We stopped right before the uh growth rates versus return, right? To just to trust your memory. So this was our 902 class September 2nd and So uh we did
3:55 we did basically um uh so I uh I don't have this file uh you know I I can download new um so we found, you know, uh we we found basically a holding period return uh we found holding period return right in both scenario one and scenario two. Now I I wonder if you remember what scenario one and scenario two is. Uh buy uh we want scenario one is a king square you buy your house and use it as your home
4:48 right scenario two is what pay scenario two there's cash flow So in um in scenario one we uh our photo return scenario to the full liquid 7%. Now we want to know how the uh anyone
5:54 Capital gain is the growth in house
This is the start of the transcript. Save this video free for the whole transcript, searchable and time-coded.
In this course
Related notes
Summary, takeaways, and chapters were generated by AI from the video's transcript and may contain errors. The video belongs to its creator, Jeff Hunk.