Herby Builds a Cash Flow

1. Some Events in Both Personal and Business Life are Measured by the Cost of Something Needed, Rather than by Profit • Example - Herby Housing needs a place for his family to live while he goes to school at SIU. Herby is debating whether to rent an apartment or buy a house(he has military benefits and his wife Hanna will be working). Herby finds that the cost of renting will be $650/month. Herby does some house shopping and finds he can get a house in DeSoto for $28,000. Herby expects to take 5 years for school and wants to know what his housing is really going to cost?

2. Herby Builds a Cash Flow Herby assumes rent will go up $20/year with inflation The NPV of this cash flow will surely be negative - does that mean no go? $650 Deposit Refund 0 1 2 3 4 5 6 7 8 54 55 56 57 58 59 60 61 $650 Rent $650 Deposit $650 Rent $730 Rent Cash Flow for Renting Scenario

3. Herby Studies Buying • The house will cost Herby $28,000. (Herby will finance that) • Herby needs a 10% down payment $2,800 • Herby discovers that there are “Closing Costs” when you buy a house • Appraisal fee $250 • Flood Determination Letter $150 • Credit Report $25 • Dead Registration $15 • Mortgage Registration $15 • Private Mortgage Insurance $280

4. Herby’s first time home buyers adventure continues • Herby will need to get Home Owners Insurance $600/year • Herby discovers that banks also like to charge their little “fees” for starting up a loan • Herby can get a local loan from Union Shafters Bank • Union Shafters will simply recover closing costs • Union Shafters will charge 8% annual interest compounded monthly over 15 years • Herby could also get a loan over the internet from Inter Your Pocket Mortgage Lenders • 5% of face amount loan initiation - covers all closing costs • 1.5 points (rolled into the mortgage) • 6.25% annual interest compounded monthly over 30 years

5. Herby Compares Loans • Union Shafters • Closing Costs $735 • Down Payment $2,800 • Needs $3,535 now • Mortgage Payments • Loan Amount $28,000 - $2,800 (down payment) = $25,200 • Loan over 15 years at 8% interest - How do I get the Payments?

6. Enter Our Super Hero • I need to convert a present value amount into an annuity • A/P * Present Loan = Annuity of Loan Payments • cancellation of units checks out • What is the value of n? • 15 years * 12 months/year = 180 • What is the value of i?

7. Oh NO You Don’tWe is smart students. We know that interest rate did not match the compounding period. • Annual interest is 8% • But it is compounded monthly • Get the monthly rate • 8%/12 = 0.00667 • Plug and Crank • { 1.00667180 * 0.00667}/{1.00667180 -1} = 0.009559 • $25,200 * 0.009559 = $240.88/month

8. Now Check Out the Internet Bank • Loan Amount $25,200 • 5% initiation fee $25,200*0.05 = $1,260 • Whats this point business • Lenders discount interest rate on the loan for an up front payment of a percentage of the loan amount. A point is a catchy way of saying what percent of the loan amount they will charge (they often roll it into the loan) • $25,200 * 0.015 = $378 • Loan amount is $25,578

9. Get Our Monthly Payments • What is n (30 year loan) n= 360 • Watch out for i compounding period mismatch trick • 6.25%/12 = 0.0052083 • Plug and Crunch • A/P 0.0052083, 360 = 0.006157 • $25,578 * 0.006157 = $157.49

10. Some Initial Statistics • Action Up Front Cost Monthly Cost • Rent $1,300 $650 • Buy with US $3,535 $240.88 • Buy with IYP $4,060 $157.49 • Buying is looking really good right now except for those scary up front costs. • Which loan should Herby get if he does buy the house?

11. Building some cash flows Resell the House 0 1 2 3 4 5 6 7 8 9 ………………………... 69 months (assumed takes 9 months to sell house) Interest is tax deductible but the loans have different interest rates Loans have different terms so one loan will be more paid-off when the house sells (they will build “equity” faster)

12. The tax deduction and equity problem • The handy magic numbers are designed to sweep cash in standard positions into the pot. • We can always get questions they really weren’t designed to answer • This case - how much is interest and how much is building equity • Enter another answer - the spreadsheet

13. Setting Up Our Spreadsheet A speadsheet is a series of cells into which we type words, numbers or formulas. It will calculate the values for us automatically (it’s a nice little calculator). Because we can copy formula’s around it can help us avoid key punch errors or redo our homework quickly if we find we have made just one little mistake. I often use convention of coloring cell yellow where I want someone to put in a number.

14. Putting in a Formula I entered formula =c3/100/12 Each cell in the spreadsheet has a name. I can tell my formula to look in a specific cell for a value. This is how I can build formulas that refer to information I can change. (Remember I have two bank loans to work with). In Excel I have to start a formula with = The / sign means divide just like on a calculator.

15. Copy Formula Trick I entered 1 in cell a7 I entered =a7+1 into cell a8 I will click on that cell and copy it to the cells below. When I copy the formula each cell will refer to the one above it. (ie- cell a12 will say = a11 + 1)

16. Developing the Spreadsheet You can see what happened when I copied the formula I entered = b7*c4 I entered =c7-d7 I entered =b7-e7

17. More Formula Copying = f7 We’ll make next periods principle equal to what was left from the time before. Lets Change formula in cell d7. Right now it says = b7*c4 In Excel, putting a dollar sign in front of part of a cell name makes it stay the same when copied. In this case I want the 4 part of c4 to stay the same = b7 * c$4 I entered =c7 in cell c8 and then copied. Thus each cell simply copies the one above it. What if I want to refer to the same cell each time and copy a formula? My interest cell multiplies the outstanding principle by c4 the monthly interest rate and I want to keep the same interest.

18. Copy the Formulas Equity is the difference between what the house is worth and what the unpaid load is. Enter formula =b$7-f7 and copy the formula Notice that with time the amount of money going to Principle increases and interest decreases as the debt is paid-off.

19. Magic at the End In year #1 Herby will pay $1983.21 in potentially tax deductible interest In year #2 Herby has $1907.90 in deductions In year #3 $1826.34 In year #4 $1738.01 In year #5 $1642.35 In year #6 (before the house is resold) $1164.23 When the house resells - Herby will have $6358.68 in equity The question of whether Herby gets a deduction depends on whether his itemized deductions exceed the standard deduction.

20. Tax Assumption • Lets assume Herby can deduct his interest, but that he is only in the 15% tax bracket • The money Herby saves on his taxes may be a positive flow into his pocket as a refund check • Year #1 $1983 * 0.15 = $297.48 • Year #2 $1907.9 * 0.15 = $286.19 • Year #3 $1826.33 *0.15 = $273.95 • Year #4 $1738.01 * 0.15 = $260.70 • Year #5 $1642.35 * 0.15 = $246.35

21. Cash Flow for Buying with Local Loan $6,358.68 $246.35 $297.48 $286.19 $273.95 $260.70 $174.63 0 1 - 12 13 - 24 25 - 36 37 - 48 49 - 60 61 - 69 73 $240.88 per month $3,535

22. Can do the Same Thing for the Internet Loan Note that with the spreadsheet I just retyped the 3 numbers in yellow and it did my whole interest, tax, and equity problem for me instantly

23. Cash Flow for Internet Loan $1630.95 $238.52 $235.64 $232.55 $229.27 $225.79 $166.92 0 1 - 12 13 - 24 25 - 36 37 - 48 49 - 60 61 - 69 73 $157.49 per month $4,060

24. Herby Must Now Compare • Herby is comparing two alternatives that will both cost him money • One often used technique is to subtract one alternative from the other and look at the incremental value of choosing one alternative over the other • This then becomes a question of how much you gain (or loose by choosing one alternative over the other)

25. -$50.56 -$41.40 -$31.43 -$20.56 -$7.71 Application Need to first decide which alternative we think we want to choose. - Oh yes the loan with the lower payments. $240.88 - $157.49 = 83.39 / month $238.52 - $297.48 = -$58.96 $1630.95 - $6358.68 = -$4,727.73 $3,535-$4,060= -$525

26. Now What Should Herby Do? • He has a cash flow that represents the value of choosing the loan with the lower payments and interest rate • Naturally he could discount it back to his decision point (when he goes to bank or signs on the internet) • But What Rate?

27. Herby’s Interest Rate Dilemma • Herby hopes to save some money by picking the lower monthly payments and interest rate • What will he do with the money he saves? • May very well use it for school. Herby may be looking at student loans for what ever he and wifey can’t get together • Herby’s incremental cost of money may be what student loans would cost • Herby may be playing the market on the side • What could Herby get on the market if he were to invest

28. More on Herby’s Dilemma • Herby might not know what the heck his cost or value of money is • Lets suppose Herby is clueless • With spreadsheet Herby can try different interest rates and see what the resulting flow is.

29. -$50.56 -$41.40 -$31.43 -$20.56 -$7.71 Lets identify our cash flow elements What an element is depends on where the pot is (remember the annuity problem) We have a Present Value that needs no magic number

30. Spreadsheet

31. Another Case

32. Observations • Notice that even where something is going to cost you money that subtracting one alternative from another will show an NPV for the value of picking one alternative instead of the other. • If somethings going to cost you you usually have choices. This technique allows you to measure the value of one choice vs. the other • If you have only one choice you can still get the NPV of what it will cost you.

33. More Observations • In this case one of the reasons for choosing the spreadsheet was we were unsure of what interest rate to use. • Many activities and needs cost money. Freeing up money from the activity brings other opportunities • They may be to eliminate debt or the need for debt • They may be to invest.

34. A Personal Life Interest Rate • Herby may have several things that cost him money (look for where your additional margin of dollars go and what interest rate or forgone interest rate opportunities there are) • School - if you can’t pay as you go you have student loans • Credit Cards - if you have needs you are going to charge (or a credit card debt from previous needs) • If you have investment opportunities • If you could put money into CDs or a money market account • If you have an interest bearing checking account it may have a rate • The home loan itself has an interest rate - and most allow extra payment directly against principle.

35. Notes about NPV • The NPV of picking the internet loan over the local loan is positive for any positive rate of interest • (just cash flow total was positive) • The higher the interest the more discounted the home equity at the end is and the greater the savings on monthly payments and interest expenses.

36. Conclusion • Herby should pick the internet loan • Notice that this kind of calculation can be done to decide when to refinance a house or to choose between various borrowing options. • We’ve now decided which loan is best for the house - but not whether Herby should buy or rent.

37. The Home Loan Payments Game • We found that Herby’s mortgage payments will be pretty small compared to rent payments • The payments consider • Principle (paying off part of the debt each month) • Interest (paying investors each month for the gratification they are giving up by keeping money in Herby’s House) • Banks Charge Other Fees as part of the payments.

38. The Escrow Costs • There is that homeowners insurance payment $600 per year including a chunk right up front • There is the private mortgage insurance $280 per year (first year was covered in closing) • There is property tax • The bank doesn’t trust you to save this money so they charge it to you each month • They stash it in an escrow account (where they usually make the interest)

39. The Insurance Costs • Insurance $280 + $600 = $880 • 12 payments = $880/12 = $73.33

40. The Property Tax Game • Illinois (and most states) try to baffle people with convoluted formulas to keep them too confused to fuss • In Illinois You first compute “Fair Market Value” • Many communities estimate low • You think your getting a deal so your less likely to fuss • If the state ever wishes to use eminent domain to buy you out they have a basis for a lower value • If property taxes are ever frozen, they can keep the tax rate and jack up the property value

41. Herby’s Property Tax • DeSoto values Herby’s house at $27,000 • Next you get the “Assessed Valuation” • By law in Illinois - this is 1/3rd of fair market value • $9,000 • (People really think they’re getting a deal when the realize they are only paying taxes on this little amount - never mind the tax rate is about 3 times as high as in states that don’t do this step)

42. Figuring Herby’s Property Tax • Apply the tax rate to the “Assessed Valuation” • Tax rate about 9.78% • rates can be high is Southern Illinois because of a weak industrial base • They can be high in Chicago suburbs because suburban school districts and governments are very very good at spending money • Apply and let dry • $9,000 * 0.0978 = $880.20 per year

43. The Escrow Account • Insurance monthly payments were $73.33 • Taxes $880.2/ 12 = $73.35 • The Total = $146.78 • Wrong • Banks usually charge you 1.5 times the estimated amount to accumulate money in the escrow account • Official reason - so if rates go up there is money there • Unofficial reason - since they get to collect the interest on the account it gives them more of your money to make extra money on. • $220/month

44. More on Escrow • Banks adjust escrow payments each year based on actual tax and insurance rates • They don’t charge 1.5 times forever but they do get the amount up to about twice the actual experience before they back off on overcharging you • Escrow payments can drastically alter what you thought your mortgage was • 30 year internet mortgage is $157.49 + $220 = $377.49/month

45. Herby Looks at Later Year Mortgage Payments • In order to know what escrow payments will be the second year - Herby has to know how much tax and insurance will take out of his escrow account. • On the insurance - Herby will pay that for the first year (or 6 months) when he gets the loan. • He’ll accumulate escrow for a year and then pay it again.

46. Herby’s Insurance • Herby has Private Mortgage Insurance • Usually stays the same - in fact lenders risk is declining as you get more paid off • $280 • Herby has homeowners insurance • say goes up about with inflation 4%/year • $600*1.04 = $624 • Herby’s escrow account will pay out $904 for insurance at the end of the first year (note this won’t be a Herby cash flow item because Herby makes monthly escrow and the banker worries about the insurance premiums)

47. Herby’s Taxes and Escrow • Taxes are paid “in arrears” • That means that in 2001 you pay property tax for year 2000 • This can be a problem for buying because you will get a tax bill for when you didn’t own the house • Solved by giving you a “credit” at closing (adjusts your loan amount) • may adjust exact mortgage amount but we already found which loan was best

48. Payments Out of Herby’s Escrow • Taxes at end of year will be the previous years amount $880.20 • Insurance at the end of the year $904 • Money out of account • $1,784.20 • Money into account • $220 * 12 = $2640 • Balance in account $855.20

49. Billy Banker Reviews Next Years Tax and Insurance Cost • Insurance was $904.00 • Taxes • $880.20 this year • Each year the State estimates the increase in property value around the state • When real estate sells have to fill out a report form to the government • (Also used by appraisers) • State Issues a multiplier • say (1.05)

50. The State Multiplier Strikes Again • Periodically the county also reappraise all the property (usually do a roving system so it won’t be all at once) • County makes any changes they see, supervisor of assessments reviews, then multiply by state multiplier • Get a new assessed valuation • Last year $9,000*1.05 = $9450 • Next Years Tax $9,450*0.0978 = $924.21