Transcript New Review - College of Engineering | SIU

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?
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?
0
1
2 3
4
5 6
7
8
54 55 56 57 58 59 60
61
$650
Rent
$650
Deposit
$650 Deposit
Refund
$650 Rent
$730 Rent
Cash Flow for Renting Scenario
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
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
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?
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?
Oh NO You Don’t
We 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
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
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
Some Initial Statistics
• Action
Up Front Cost
– Rent
$1,300
– Buy with US $3,535
– Buy with IYP $4,060
Monthly Cost
$650
$240.88
$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?
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)
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
Setting Up Our Spreadsheet
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
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.
Putting in a Formula
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
I entered formula
=c3/100/12
In Excel I have to start a formula with =
The / sign means divide just like on a
calculator.
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).
Copy Formula Trick
Mortgage Calculation
Interest Rate/Year
Monthly Interest
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
Month
1
2
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)
Developing the Spreadsheet
Mortgage Calculation
You can
see what
happened
when I
copied the
formula
Interest Rate/Year
Monthly Interest
Month
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
25127.12
72.88
168
240.88
25200
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
I entered = b7*c4
I entered =c7-d7
I entered =b7-e7
More Formula Copying
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
We’ll make
next periods
principle
equal to what
was left
from the time
before.
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
25127.12
72.88
168
240.88
25200
1
240.88
2 = f7
240.88
3
240.88
4
240.88
5
240.88
6
240.88
7
240.88
8
240.88
9
240.88
10
240.88
11
240.88
12
240.88
13
240.88
14
240.88
15
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.
Copy the Formulas
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment
72.88
168
240.88
25200
73.36586667
167.5141333
240.88
25127.12
73.85497244
167.0250276
240.88
25053.75413
74.34733893
166.5326611
240.88
24979.89916
74.84298785
166.0370121
240.88
24905.55182
75.34194111
165.5380589
240.88
24830.70883
75.84422071
165.0357793
240.88
24755.36689
76.34984885
164.5301511
240.88
24679.52267
76.85884784
164.0211522
240.88
24603.17282
77.37124016
163.5087598
240.88
24526.31398
77.88704843
162.9929516
240.88
24448.94274
78.40629542
162.4737046
240.88
24371.05569
78.92900406
161.9509959
240.88
24292.64939
79.45519742
161.4248026
240.88
24213.72039
79.98489873
160.8951013
240.88
24134.26519
New Principle Equity
25127.12
25053.75413
24979.89916
24905.55182
24830.70883
24755.36689
24679.52267
24603.17282
24526.31398
24448.94274
24371.05569
24292.64939
24213.72039
24134.26519
24054.28029
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.
Magic at the End
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
8
0.006666667
Outstanding Principle Monthly Payment Interest Amount Principle Payment
25200
240.88
168
72.88
25127.12
240.88
167.5141333
73.36586667
25053.75413
240.88
167.0250276
73.85497244
24979.89916
240.88
166.5326611
74.34733893
24905.55182
240.88
166.0370121
74.84298785
24830.70883
240.88
165.5380589
75.34194111
24755.36689
240.88
165.0357793
75.84422071
24679.52267
240.88
164.5301511
76.34984885
24603.17282
240.88
164.0211522
76.85884784
24526.31398
240.88
163.5087598
77.37124016
24448.94274
240.88
162.9929516
77.88704843
24371.05569
240.88
162.4737046
78.40629542
24292.64939
240.88
161.9509959
78.92900406
24213.72039
240.88
161.4248026
79.45519742
24134.26519
240.88
160.8951013
79.98489873
24054.28029
240.88
160.3618686
80.51813139
23973.76216
240.88
159.8250811
81.05491893
23892.70724
240.88
159.2847149
81.59528506
23811.11196
240.88
158.7407464
82.13925363
23728.9727
240.88
158.1931513
82.68684865
23646.28585
240.88
157.6419057
83.23809431
23563.04776
240.88
157.0869851
83.79301494
23479.25474
240.88
156.528365
84.35163504
23394.90311
240.88
155.9660207
84.91397927
23309.98913
240.88
155.3999275
85.48007247
23224.50906
240.88
154.8300604
86.04993962
23138.45912
240.88
154.2563941
86.62360588
23051.83551
240.88
153.6789034
87.20109659
22964.63442
240.88
153.0975628
87.78243723
22876.85198
240.88
152.5123465
88.36765348
22788.48432
240.88
151.9232288
88.95677117
22699.52755
240.88
151.3301837
89.54981631
22609.97774
240.88
150.7331849
90.14681508
22519.83092
240.88
150.1322061
90.74779385
22429.08313
240.88
149.5272209
91.35277914
22337.73035
240.88
148.9182023
91.96179767
22245.76855
240.88
148.3051237
92.57487632
22153.19368
240.88
147.6879578
93.19204216
22060.00163
240.88
147.0666776
93.81332245
21966.18831
240.88
146.4412554
94.43874459
21871.74957
240.88
145.8116638
95.06833623
21776.68123
240.88
145.1778749
95.70212513
21680.9791
240.88
144.5398607
96.3401393
21584.63897
240.88
143.8975931
96.9824069
21487.65656
240.88
143.2510437
97.62895628
21390.0276
240.88
142.600184
98.27981598
21291.74779
240.88
141.9449852
98.93501476
21192.81277
240.88
141.2854185
99.59458152
21093.21819
240.88
140.6214546
100.2585454
20992.95964
240.88
139.9530643
100.9269357
20892.03271
240.88
139.2802181
101.5997819
20790.43293
240.88
138.6028862
102.2771138
20688.15581
240.88
137.9210388
102.9589612
20585.19685
240.88
137.2346457
103.6453543
20481.5515
240.88
136.5436767
104.3363233
20377.21517
240.88
135.8481012
105.0318988
20272.18328
240.88
135.1478885
105.7321115
20166.45116
240.88
134.4430078
106.4369922
20060.01417
240.88
133.7334278
107.1465722
19952.8676
240.88
133.0191173
107.8608827
19845.00672
240.88
132.3000448
108.5799552
19736.42676
240.88
131.5761784
109.3038216
19627.12294
240.88
130.8474863
110.0325137
19517.09043
240.88
130.1139362
110.7660638
19406.32436
240.88
129.3754957
111.5045043
19294.81986
240.88
128.6321324
112.2478676
New Principle
25127.12
25053.75413
24979.89916
24905.55182
24830.70883
24755.36689
24679.52267
24603.17282
24526.31398
24448.94274
24371.05569
24292.64939
24213.72039
24134.26519
24054.28029
23973.76216
23892.70724
23811.11196
23728.9727
23646.28585
23563.04776
23479.25474
23394.90311
23309.98913
23224.50906
23138.45912
23051.83551
22964.63442
22876.85198
22788.48432
22699.52755
22609.97774
22519.83092
22429.08313
22337.73035
22245.76855
22153.19368
22060.00163
21966.18831
21871.74957
21776.68123
21680.9791
21584.63897
21487.65656
21390.0276
21291.74779
21192.81277
21093.21819
20992.95964
20892.03271
20790.43293
20688.15581
20585.19685
20481.5515
20377.21517
20272.18328
20166.45116
20060.01417
19952.8676
19845.00672
19736.42676
19627.12294
19517.09043
19406.32436
19294.81986
19182.57199
Equity
72.88
146.2459
220.1008
294.4482
369.2912
444.6331
520.4773
596.8272
673.686
751.0573
828.9443
907.3506
986.2796
1065.735
1145.72
1226.238
1307.293
1388.888
1471.027
1553.714
1636.952
1720.745
1805.097
1890.011
1975.491
2061.541
2148.164
2235.366
2323.148
2411.516
2500.472
2590.022
2680.169
2770.917
2862.27
2954.231
3046.806
3139.998
3233.812
3328.25
3423.319
3519.021
3615.361
3712.343
3809.972
3908.252
4007.187
4106.782
4207.04
4307.967
4409.567
4511.844
4614.803
4718.449
4822.785
4927.817
5033.549
5139.986
5247.132
5354.993
5463.573
5572.877
5682.91
5793.676
5905.18
6017.428
In year #1 Herby will pay $1983.21 in
potentially tax deductible interest
Year 1 Interest
1983.209
Year 2 Interest
1907.9
Year 3 Interest
1826.339
Year 4 Interest
1738.01
Year 5 Interest
1642.349
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.
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
Cash Flow for Buying with Local
Loan
$6,358.68
$297.48
0
1 - 12
$240.88 per month
$3,535
13 - 24
$286.19
25 - 36
$273.95
37 - 48
$246.35
$260.70
49 -
60
61 - 69
$174.63
73
Can do the Same Thing for the
Internet Loan
Mortgage Calculation
Interest Rate/Year
Monthly Interest
Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
6.25
0.005208333
Outstanding Principle Monthly Payment Interest Amount Principle Payment New Principle Equity
24.27125 25553.72875 24.27125
133.21875
157.49
25578
24.39766276 25529.33109 48.66891
133.0923372
157.49
25553.72875
24.52473392 25504.80635 73.19365
132.9652661
157.49
25529.33109
24.65246691 25480.15389 97.84611
132.8375331
157.49
25504.80635
24.78086517 25455.37302 122.627
132.7091348
157.49
25480.15389
24.90993218 25430.46309 147.5369
132.5800678
157.49
25455.37302
25.03967141 25405.42342 172.5766
132.4503286
157.49
25430.46309
25.17008637 25380.25333 197.7467
132.3199136
157.49
25405.42342
25.30118057 25354.95215 223.0478
132.1888194
157.49
25380.25333
25.43295755 25329.51919 248.4808
132.0570425
157.49
25354.95215
25.56542087 25303.95377 274.0462 Year 1 Interest
131.9245791
157.49
25329.51919
25.6985741 25278.2552 299.7448 1590.135
131.7914259
157.49
25303.95377
238.5203
25.83242084 25252.42278 325.5772
131.6575792
157.49
25278.2552
25.9669647 25226.45581 351.5442
131.5230353
157.49
25252.42278
26.10220931 25200.3536 377.6464
131.3877907
157.49
25226.45581
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
Cash Flow for Internet Loan
$1630.95
$238.52
0
1 - 12
$157.49 per month
$4,060
13 - 24
$235.64
25 - 36
$232.55
37 - 48
$229.27
49 -
$225.79
60
61 - 69
$166.92
73
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)
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
$3,535-$4,060=
-$525
$238.52 $297.48 =
-$58.96
-$50.56 -$41.40 -$31.43 -$20.56 -$7.71
$1630.95 - $6358.68 =
-$4,727.73
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?
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
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.
Lets identify our cash flow
elements
What an element is depends on where the pot is (remember the annuity problem)
-$50.56 -$41.40 -$31.43 -$20.56 -$7.71
We have a
Present Value that
needs no magic number
Spreadsheet
Interest Rate
Cash Flow n
-525
83.39
-58.96
-50.56
-41.4
-31.43
-20.56
-7.71
-4727.73
290.56
0
69
13
25
37
49
61
73
69
2
0.001667
Magic #
1
65.12911
0.978584
0.959223
0.940244
0.921642
0.903407
0.885533
0.891451
sum
% per year
% per month
Multiplied
-525
5431.117
-57.6973
-48.4983
-38.9261
-28.9672
-18.574
-6.82746
-4214.54
492.0845
Another Case
Interest Rate
Cash Flow n
-525
83.39
-58.96
-50.56
-41.4
-31.43
-20.56
-7.71
-4727.73
290.56
0
69
13
25
37
49
61
73
69
8
0.006667
Magic #
1
55.16279
0.917246
0.84695
0.782041
0.722107
0.666765
0.615665
0.632248
sum
% per year
% per month
Multiplied
-525
4600.025
-54.0809
-42.8218
-32.3765
-22.6958
-13.7087
-4.74678
-2989.1
915.4964
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.
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.
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.
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.
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.
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.
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)
The Insurance Costs
• Insurance $280 + $600 = $880
• 12 payments = $880/12 = $73.33
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
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)
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
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
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
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.
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)
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
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
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)
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
Figuring Year II Escrow
• Taxes will be $924.21
• Insurance will be $904
• Total Payout estimated for end of year II
– $1,828.21
• Billy Banker Would Like twice that in
account
– about $3,657
• Billy Banker has $855.20 in there now
Setting Next Years Escrow
• $3657-855.20 = $2802
• $2802/12 months = $233.50 for Escrow
• Mortgage Principle and Interest is
– $157.49
– Add Escrow
– $390.99 next years mortgage payment
• During Year #1 pay $377.49
• During Year #2 pay $390.99
Estimating Escrow for Later
Years
• Escrow account has now built up the
bankers interest free extra money - now
they’ll just have Herby pay as he goes
• Assume PMI (Private Mortgage Insurance)
stays same - Home owners goes up 4% per
year
• Assume taxes up 5% per year
Taxes and Insurance
• Year #3 homeowners insurance
–
–
–
–
–
Year #2 was $624
Year #3 $624*(1.04) = $649
Year #4 $649*(1.04) = $675
Year #5 $675*(1.04) = $702
Year #6 (until house sells) $702*1.04 = $730
• PMI stays the same at $280
• Taxes will go up 5% each year
Taxes and Escrow
•
•
•
•
•
Year 2 taxes were $924.21
Year 3 taxes $924.21*1.05 = $970.42
Year 4 taxes $970.42*1.05 = $1,018.94
Year 5 taxes $1,018.94*1.05 = $1,069.89
Year 6 taxes $1069.89*1.05 = $1123.38
Future Years Escrow
•
•
•
•
Year 3 $280 + $649 + $970.42 = $1899.42
Year 4 $280 + $675 + $1,018.94 = $1973.94
Year 5 $280 + $702 + $1,069.89 = $2051.89
Year 6 $280 + $730 + $1,123.38 = $2133.88
Important Inflation Features
• Note that the Taxes and Insurance Payments
are just going up by 3.9% over-all each year
• Many Times in Inflation Scenarios you see
a cost that grows and compounds with
inflation
– If the costs grow at a steady rate through each
compounding period you have something like
an annuity with inflation
• can’t use P/A or A/P because payment amount
changes
The Geometric Gradient
• In some engineering econ problems we see
what would be an annuity except that it
grows at a steady rate each compounding
period.
– Some equipment maintenance expenses behave
this way
– Most common source is inflation in the cash
flow (ie - its an annuity with inflation)
• The tax and insurance expense is behaving this way
Special Features for Special
Problems
• We’ve met P/A
– They really don’t do anything for us that can’t be done
with a large number of different P/F values
– We got P/A because it let us treat an obnoxious series of
numbers as one cash flow element that can be dealt
with all at once
• Now we have an annuity almost except its
growing
– Without help it’s a large number of P/F problems
Another Super Hero
• P/Ag,i,n
– Looks very similar to our old friend P/A only
this one has 3 numbers
– The first two numbers look like interest rates
• Actually what you have is a rate of inflation
or cost escalation, and an interest rate, and a
number of payments or compounding
periods
Super Hero Formula
• Look in the front of the book “Geometric
Series Present Worth”
• In our case we won’t be able to use the hero
because Herby’s escrow payments are made
monthly, while the growth is yearly
• Why introduce the Geometric Series Present
Worth this way?
– Example illustrates how inflation commonly produces
these growing annuities
– Also I don’t like inflation in engineering cash flow
analysis and so I’m not putting out a lot of emphasis
Back from the Detour
• Annual Escrow Payments
– Year #3 $1,899.42/12 = $158.29
– Year #4 $1973.94/12 = $164.50
– Year #5 $2051.89/12 = $170.99
– Year #6 $2133.88/12 = $177.78
Adjusting the Mortgage
Payments
• Basic Loan will be for $25,200
• The Bank also charges points
– (an up front premium in exchange for a lower
interest rate - or a good way for bankers to make their
loan look like a better deal and still make the same
money)
– $378
• But Herby gets credit for last years taxes
(since he’ll end up paying them)
– $880.20
Herby’s Loan with Tax
Adjustment
• $25,200 + $378 - $880.2 = $24,698
• A/P is 0.006157 for 360 payments with 6.25%
annual interest (divided by 12 for monthly
compounding)
• $24,698*0.006157 = $152.06
• Mortgage Payments
–
–
–
–
–
–
Year #1 - $152.06 + $220 = $372.06
Year #2 - $152.06 + $233.50 = $385.56
Year #3 - $152.06 + $158.29 = $310.35
Year #4 - $152.06 + $164.50 = $316.56
Year #5 - $152.06 + $170.99 = $323.05
Year #6 - $152.06 + $177.78 = $329.84
Building the Home Buy Cash
Flow
$132
$230.31 $139 $227.52
$146
$2,800
Down Payment
$372.06/mo.
$1,260
Loan Initiation Fees
$600
Homeowners Insurance
Bank also charged
$378 in “Points that
rolled into loan
$385.56/mo.
$310.56
$224.55
$221.39 $218.03 $161.18
$153
$161
$169
$316.56
$323.05
$329.84
Maintenance Costs
• Home owners have regular repair costs
• DeSoto house needs some initial repairs
– about $5,000 for materials plus some personal
“sweat equity”
• Herby could get a personal loan from the bank for 5
years at 8% interest with a $200 loan application fee
• Herby’s could just use his credit card at 15% interest
• There are routine things that break down Herby figures about $75/month
Herby’s Big Kicker
• Herby is getting the house pretty cheap but
• The roof will probably fail in 4 years
– This will likely cost about $4,200
– Herby is concerned about whether he will be
able to get additional money on loans at a
critical time like that.
How Should Herby Deal With
This?
• $75/month maintenance is just an annuity in
the cash flow.
• The loan vs. credit card choice is another of
those spreadsheet comparison jobs
– Figure the cash flow from each
– Pick a preferred alternative
– Subtract one alternative from the other to define a cash
flow of costs and benefits from choosing your favorite
– Discount cash flow back and see if your preferred
alternative saved you enough.
The Roof
• Herby sees a big expense coming and can’t
risk ability to get credit when it hits
• Answer is a business device called a sinking
fund
– Save up money just like Fursee Foresight
– Money is saved up as a series of regular savings
at the end of each compounding period - ie. Its
an annuity.
The Sinking Fund
• Discounted Cash Flow was developed in
Mining
– Got its name from the practice of saving money
to sink a new mine shaft
• Trick here is that we are trying to find an
annuity that will reach a set amount of
money at some time in the future
– P/A and A/P deal with present values
– F/A converts and annuity to a future value
Enter a New Super Hero
• A/F
• Check to make sure she can do the job
– A/F * Future Cost of Re-roofing
• Need to know n
– re-roof in 4 years (but we’re on a monthly
schedule)
• 4*12 = 48
• Need to know i
– What ever Herby can get on his savings
Herby Builds a Sinking Fund for
His Roof
• Herby will put money into a money market
account at 5% interest (compounded
monthly)
• Using the formula
– Just F/A flipped
– ( i / {[1 + i ]n -1}) = A/F
• for i= 0.05 /12 = 0.004167
• and n=48
• A/F = 0.018863
Herby’s Monthly Cost for the
Roof
• 0.018863 * $4,200 = $79.22
• Herby now has monthly maintenance costs
of
– $75/month routine maintenance
– $79.22/month sinking fund to replace the roof
– Herby still needs to deal with the $5,000 in
initial repairs
Herby’s Personal Loan Choice
• $5,000 in initial repairs
• The Bank Loan Option
– $200 loan application fee
– 8% compounded monthly
– 5 year amortization
• Convert a present loan amount into an
annuity of payments in the future
– A/P * Present Loan Amount
check
A Bank Loan for Herby
• A/P
– n = 60 = 5 years * 12 months per year
– i = 8% per year/ 12 months per year = 0.00667
– A/P0,00667, 60 = 0.020278
• Monthly cost for initial repairs
– 0.020278 * $5000 = $101.39
• Also an initial fee of $200
Herby’s Credit Card
• Credit cards usually do not charge a fixed
monthly payment (except for some minimum
usually around $10 to $15)
– Instead they charge a fixed percentage of the
outstanding balance
• will produce a declining monthly payment like a
reverse geometric gradient going down
• We have no super hero for that
• We do have a spreadsheet
Working with Credit Cards
Payoff of a Credit Card
Annual Percentage Rate
Days per year for periodic rate
Daily Periodic Rate
Minimum Payment %
Days in Month Monthly Rate Balance
Minimum Monthly
Payment Interest
15
362
0.000414
2
Principle
Paid
New
Balance
Credit Cards usually compound interest monthly, but they use a “Daily Periodic
Rate” so that months with more days have a higher monthly rate.
Credit Cards frequently play games with the rate. They quote an annual rate,
charge interest for each day, but divide the annual rate by a number of days
smaller than 365.
Credit cards usually charge 2% or 3% of your outstanding balance as a minimum
payment.
Developing the Spreadsheet
Payoff of a Credit Card
Annual Percentage Rate
Days per year for periodic rate
Daily Periodic Rate
Minimum Payment %
Days in Month
31
28
31
30
31
30
31
31
30
31
30
31
Monthly Rate Balance
0.0128453
0.01160221
0.0128453
0.01243094
0.0128453
0.01243094
0.0128453
0.0128453
0.01243094
0.0128453
0.01243094
0.0128453
Minimum Monthly
Payment Interest
15
362
0.000414
2
Principle
Paid
New
Balance
The Monthly Rate is Calculated by
taking the number of days in the
billing cycle (month) * the daily
periodic rate.
A Spreadsheet for Credit Cards
Payoff of a Credit Card
Annual Percentage Rate
Days per year for periodic rate
Daily Periodic Rate
Minimum Payment %
Minimum Payment
Days in Month
31
28
31
30
31
30
31
31
30
15
362
0.000414
2
15
Minimum Monthly Principle New
Monthly Rate Balance Payment Interest
Paid
Balance
0.0128453
5000
100
0.01160221
0.0128453
0.01243094
0.0128453
0.01243094
0.0128453
The minimum payment cell contains an if
0.0128453
=if(c11*f$6/100>f$7, c11*f$6/100, f$7)
0.01243094
Credit Cards Charge a fixed % of the
outstanding balance or a minimum monthly
payment.
statement
The Payoff
Payoff of a Credit Card
Annual Percentage Rate
Days per year for periodic rate
Daily Periodic Rate
Minimum Payment %
Minimum Payment
Days in Month
31
28
31
30
31
30
31
31
30
15
362
0.000414
2
15
Minimum Monthly Principle New
Monthly Rate Balance Payment Interest
Paid
Balance
0.0128453
5000
100 64.22652 35.77348 4964.227
0.01160221 4964.227
Interest is the average daily balance * the
0.0128453
monthly rate.
0.01243094
Principle paid is of course the monthly
0.0128453
0.01243094
payment minus the interest.
0.0128453
New balance is the old balance minus the payment
0.0128453
against the principle.
0.01243094
Or Is It
Days in Month
31
28
31
30
31
30
31
31
30
31
30
31
31
28
31
30
31
30
31
31
30
31
30
Monthly Rate Balance
0.0128453
5000
0.01160221 4964.227
0.0128453 4922.538
0.01243094 4887.319
0.0128453 4850.326
0.01243094 4815.624
0.0128453 4779.174
0.0128453 4744.98
0.01243094 4711.032
0.0128453 4675.373
0.01243094 4641.923
0.0128453 4606.788
0.0128453 4573.827
0.01160221 4541.103
0.0128453 4502.968
0.01243094 4470.75
0.0128453 4436.911
0.01243094 4405.166
0.0128453 4371.823
0.0128453 4340.544
0.01243094 4309.489
0.0128453 4276.87
0.01243094 4246.271
Minimum
Payment
100
99.28453
98.45076
97.74637
97.00653
96.31247
95.58348
94.89961
94.22063
93.50747
92.83845
92.13575
91.47655
90.82206
90.05936
89.41501
88.73822
88.10333
87.43647
86.81089
86.18978
85.5374
84.92541
Monthly
Interest
64.22652
57.596
63.2315
60.75396
62.30392
59.86273
61.38994
60.95072
58.56255
60.05659
57.70346
59.17559
58.7522
52.68683
57.84199
55.57563
56.99347
54.76035
56.1574
55.75561
53.571
54.9377
52.78513
Principle
Paid
35.77348
41.68853
35.21926
36.99241
34.70261
36.44975
34.19354
33.94889
35.65808
33.45088
35.13499
32.96017
32.72435
38.13523
32.21737
33.83938
31.74475
33.34297
31.27907
31.05528
32.61878
30.59971
32.14028
New
Balance
4964.227 Year #1
4922.538
4887.319
4850.326
4815.624
4779.174
4744.98
4711.032
4675.373
4641.923
4606.788
4573.827
4541.103 Year #2
4502.968
4470.75
4436.911
4405.166
4371.823
4340.544
4309.489
4276.87
4246.271
4214.13
Credit Card
Monthly Payments
Decline over time
But so does your
payment against
principle - unlike
loans which pay
down faster over
time.
In Fact
30
31
30
31
31
28
31
30
31
30
31
31
30
31
30
0.01243094
0.0128453
0.01243094
0.0128453
0.0128453
0.01160221
0.0128453
0.01243094
0.0128453
0.01243094
0.0128453
0.0128453
0.01243094
0.0128453
0.01243094
202.8063
190.3274
177.7722
164.9821
152.1013
139.0551
125.6685
112.2827
98.6785
84.94606
71.00202
56.91406
42.64514
28.17526
13.53718
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
2.521073
2.444813
2.209876
2.119245
1.953788
1.613347
1.61425
1.39578
1.267555
1.055959
0.912042
0.731078
0.530119
0.36192
0.16828
12.47893
12.55519
12.79012
12.88075
13.04621
13.38665
13.38575
13.60422
13.73244
13.94404
14.08796
14.26892
14.46988
14.63808
14.83172
190.3274
177.7722
164.9821
152.1013
139.0551 Year #28
125.6685
112.2827
98.6785
84.94606
71.00202
56.91406
42.64514
28.17526
13.53718
-1.29454 27 Years 11 months
If Herby pays minimum payments on his credit card it will take
him 27 years 11 months to pay off the credit card.
The Game Afoot
• Why are credit cards so hard to pay-off
– The minimum payments are generally set to be
competitive with signature loans at banks
– But the modest payment hides a high interest
rate and results in slow payment of the debt
• The Declining Payment - Why?
– Official Answer - to better service the customer
by minimizing demands made upon him
Its A TRAP
• By directing a high percentage of your
payments to interest and then slowly
declining those payments - credit card debt
takes a long time to pay-off
• The Credit Card Company’s bet you a life
time of debt that if you ever get a good
credit card debt - you won’t be able to avoid
using your card again for 25 years - You'll
recharge the debt.
Other Credit Card Games
• Credit Cards have “Cash Advance Fees”
– Credit Card Checks or getting cash from an ATM is
considered a “Cash Advance”
– Cash advances are usually 3% of the amount advanced
(or a minimum fee of $5 or $10)
• some cards do have a maximum fee also
• If Herby gets a cash advance on his credit card he
will pay
– $5,000 * 0.03 = $150
• so much for getting out of that loan initiation fee
More Games
• If Herby Charges supplies at Lowe’s or
Home Depot he does not pay a cash
advance fee.
– But the credit card company charges a couple
percent to the merchant (hidden in higher
prices)
• Reason some gas stations have a higher price for
credit cards than cash
• Reason that not every store takes credit cards
Cash Advance vs. Purchase
• In addition to cash advance fee credit cards charge
a higher interest rate for cash advances than
purchases
– purchases are often 9.9% to 18.9%
– cash advances are typically 18.9 to 24%
• If you get a cash advance on your credit card - all
your payments will go to cover purchases (lower
rate) until the low rate stuff is paid off (never if you
fall in the declining payment trap)
– About the only way to get a cash advance off your back
is to pay-off the entire card.
The Promotional Offer
• Many credit cards offer promotional interest
rates
– They last around 3 to 12 months depending on
the card - And then they jump to a higher
interest rate
• The idea is to get you to run up a balance that you
won’t be able to dig out of
• Often they will charge a cash advance fee
with a promotional interest rate check
The Grace Period
• Most cards offer a grace period
– If you pay-off your balance in full each month there is
no interest BUT
• Many credit cards are shortening grace period
from 25 days to 20
– They wait about a week to 10 days after your “statement date” to
send the bill
– Takes about 3 or 4 days in the mail
– They warn you in fine print that it may take 3 to 5 days to credit
your payment
– Takes you about 3 or 4 days to get payment to their office
– Result - you may have only a day or two to have the money in
your checking account ready to pay them.
Bankers Grace
• If your payment doesn’t make it on time (I
wonder how much hustle they put into processing your
payment if its close - I wonder how you’d prove it)
– Credit Cards Charge a late fee (usually $29) in
addition to interest
– If they can catch you several times, most will
raise the interest rate that they charge on your
account (after all you’re a bad credit risk
because you don’t pay on time)
Billing Cycle Tricks
• Some credit cards change the way the calculate the
“average daily balance”
– They do the average daily balance as a two cycle
average (ie the average over two months instead of each
billing cycle)
• Results
– If you try to take a promotional offer and then pay off the balance
before the rate goes up - they get to zap you even after the balance
is paid.
– They can continue to charge you interest on purchases after they
are paid off
– You have to keep your credit card paid off for many months
straight to stop monthly interest charges.
Back to Herby Choosing a
Personal Loan or Credit Card
• Assume that Herby will buy the supplies at
Lowe’s so he will not be on a credit card
cash advance.
• How do we compare two financial
alternatives to the same problem when both
will cost you money?
The Cash Flow Comparison
Trick
I have a hunch Herby would rather have the bank loan to fix up his house so I
will get the cash flow from choosing the bank loan instead of the credit card
We’ll assume Herby would pay off the
credit card when the house sold. By
having the bank loan instead of the credit
card, Herby keeps money he would
otherwise have to use to pay off the credit
card
$3,017.62 - $0.
Herby pays
a $200
loan
application
fee
$0 - $200
Bank loan payments stay the
same while the credit card payments
go down with time.
They start at -$1.39 and end at -$36.87
After the bank loan is paid off
Herby would still have credit card
bills so now Herby is saving money
by having the bank loan instead of
the credit card.
Savings start at $64.05 and decline to
$60.35 when the house sells
Getting Ready for NPV
Since none of these cash flows is an annuity, I have 70 cash flow elements to
sweep back one at a time with a P/F. - You can bet I’m planning to use my
spreadsheet for that one.
We’ll assume Herby would pay off the
credit card when the house sold. By
having the bank loan instead of the credit
card, Herby keeps money he would
otherwise have to use to pay off the credit
card
$3,017.62 - $0.
Herby pays
a $200
loan
application
fee
$0 - $200
Bank loan payments stay the
same while the credit card payments
go down with time.
They start at -$1.39 and end at -$36.87
After the bank loan is paid off
Herby would still have credit card
bills so now Herby is saving money
by having the bank loan instead of
the credit card.
Savings start at $64.05 and decline to
$60.35 when the house sells
t Card
Big Cash Flows with a Spread
Sheet
I just stuck my cash flow right
along side my credit card payoff
schedule
15
362
0.000414
2
15
Principle
Paid
35.77348
41.68853
35.21926
36.99241
34.70261
36.44975
34.19354
33.94889
35.65808
33.45088
35.13499
Annual %
Monthly i
New
Balance
4964.227 Year #1
4922.538
4887.319
4850.326
4815.624
4779.174
4744.98
4711.032
4675.373
4641.923
4606.788
2
0.001667
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
-7.88253
-8.55155
NPV
1790.521
P/F
0
1
2
3
4
5
6
7
8
9
10
11
1
0.998336
0.996675
0.995017
0.993361
0.991708
0.990058
0.988411
0.986766
0.985124
0.983485
0.981849
cash*P/F
-200
-1.38769
-2.09847
-2.92459
-3.61944
-4.34713
-5.02705
-5.73923
-6.4045
-7.06272
-7.75235
-8.39633
What Interest Rate to Use
• We’ve looked at what peoples marginal rate
of interest may be
• We’ve looked at feeling around with NPVs
using different interest rates to see what
happens
• Another tool is the IRR
– Internal Rate of Return
– It is the interest rate that makes NPV zero
The IRR
• The IRR is popular because it tells you what
interest rate the investment makes
– Can make complicated cash flow into an interest rate
like is posted at a bank
• Very simple flows have a formula for IRR
but most cash flow IRRs are computed
iteritivly until the NPV is zero
– The way financial calculators do it
– Excel has an IRR function that works same way.
– Where going to do manual iteration this time
Lets Try 2%
dit Card
15
362
0.000414
2
15
Principle
Paid
35.77348
41.68853
35.21926
36.99241
34.70261
36.44975
34.19354
33.94889
35.65808
33.45088
35.13499
Annual %
Monthly i
New
Balance
4964.227 Year #1
4922.538
4887.319
4850.326
4815.624
4779.174
4744.98
4711.032
4675.373
4641.923
4606.788
2
0.001667
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
-7.88253
-8.55155
NPV
1790.521
P/F
0
1
2
3
4
5
6
7
8
9
10
11
1
0.998336
0.996675
0.995017
0.993361
0.991708
0.990058
0.988411
0.986766
0.985124
0.983485
0.981849
cash*P/F
-200
-1.38769
-2.09847
-2.92459
-3.61944
-4.34713
-5.02705
-5.73923
-6.4045
-7.06272
-7.75235
-8.39633
NPV is still
Positive - the
interest rate is
higher.
Lets Try 6%
Annual %
Monthly i
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
6
0.005
NPV
1291.104
P/F
0
1
2
3
4
5
6
7
1
0.995025
0.990075
0.985149
0.980248
0.975371
0.970518
0.96569
cash*P/F
-200
-1.38308
-2.08457
-2.89559
-3.57165
-4.27551
-4.92783
-5.6073
Still Positive We’re making
over 6%
Lets Try 12%
Annual %
Monthly i
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
Interest Rate is
Still higher.
12
0.01
NPV
743.9639
P/F
0
1
2
3
4
5
6
7
8
9
1
0.990099
0.980296
0.97059
0.96098
0.951466
0.942045
0.932718
0.923483
0.91434
cash*P/F
-200
-1.37624
-2.06398
-2.8528
-3.50145
-4.17072
-4.78326
-5.41585
-5.99377
-6.55524
Lets Try 30%
Annual %
Monthly i
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
-7.88253
30
0.025
NPV
-46.3627
P/F
0
1
2
3
4
5
6
7
8
9
10
1
0.97561
0.951814
0.928599
0.905951
0.883854
0.862297
0.841265
0.820747
0.800728
0.781198
cash*P/F
-200
-1.3561
-2.00402
-2.72938
-3.30094
-3.87435
-4.37833
-4.88482
-5.32697
-5.74072
-6.15782
So its gone negative.
The interest rate is
less than 30%, but
probably not much.
Lets Try 28%
Annual %
Monthly i
28
0.023333
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
-7.88253
NPV
2.001101
P/F
0
1
2
3
4
5
6
7
8
9
10
1
0.977199
0.954917
0.933144
0.911867
0.891075
0.870758
0.850903
0.831501
0.812542
0.794015
cash*P/F
-200
-1.35831
-2.01055
-2.74273
-3.3225
-3.90601
-4.42129
-4.94079
-5.39677
-5.82542
-6.25885
We’re Close. It’s a
little above 28%.
I’ll Call this Close Enough
Annual %
Monthly i
28.0774
0.023398
cash flow n
-200
-1.39
-2.10547
-2.93924
-3.64363
-4.38347
-5.07753
-5.80652
-6.49039
-7.16937
-7.88253
-8.55155
NPV
0.00219
P/F
0
1
2
3
4
5
6
7
8
9
10
11
1
0.977137
0.954797
0.932968
0.911637
0.890795
0.870428
0.850528
0.831082
0.812081
0.793515
0.775373
cash*P/F
-200
-1.35822
-2.0103
-2.74222
-3.32166
-3.90477
-4.41962
-4.93861
-5.39405
-5.82211
-6.25491
-6.63064
Conclusion
• Picking the bank loan instead of a credit
card to pay for his home repairs is like
Herby investing at around 28.1% interest
– I doubt Herby has many opportunities for that
kind of return
• You can see how wise choices about needed
expenses can help you to accumulate wealth
• Do not confuse this to mean that spending
money for anything makes you wealthy.
Interpreting IRR
• The IRR represents the rate of interest paid
by the project (or the selection of the
preferred cost scenario over its alternative)
• The IRR is compared to the Rate of Return
that the investor requires (or the interest rate
for the investor’s other opportunities)
– If the rate is greater or equal to the target rate
then GO FOR IT
– If not spit in the pot and walk away
Herby’s Home Buy Cash Flow
$132
$2,800
Down Payment
$372.06/mo.
$1,260
Loan Initiation Fees
$600
Homeowners Insurance
$200 Home improvement
loan initiation.
Bank also charged
$378 in “Points that
rolled into loan
$230.31 $139 $227.52
$146
$385.56/mo.
$310.56
$224.55
$221.39 $218.03 $161.18
$153
$161
$169
$316.56
$75/month for Home Repairs
$101.39/month Bank Loan for Repairs
$79.22/month Sinking Fund for Roof
$323.05
$329.84
One More Issue
• Herby will sell the house when he graduates
• Herby hopes to have built some equity
– Herby’s has $22,792.47 left on his home loan
– Herby’s house should have grown in value
• according to tax assessment records the home value
has increased 27.63% since he bought it
• $28,000 + 1.2763 = $35,736
• Herby has also made some home
improvements since he got the house
Home Improvements and Equity
• Many home maintenance items are needed
to keep a salable house but add little to the
selling value
– There are guides on what kinds of things add
value
• example new Kitchen usually returns value
• finishing a basement often does not
– Many items add part, but not all of their cost to
the homes value
Herby’s Home Improvements
• Herby bought $5,000 in goods and put a lot
of sweat into installing them.
– Lets assume Herby gets his cash out, but not his sweat
– Value increases $5,000
• Herby has just reroofed
– Herby may get 50% out of that
– Value increases $2,100
• Herby’s new home value
– $35,736 + $7,100 = $42,836
Herby’s Equity
• Home Value is $42,836
• Buyers will usually try to hack at the price
– In a sellers market you can get what you ask (if
its reasonable)
– In a buyers market you often have to be
dickered down (Southern Illinois is a buyers
market)
– Lets assume Herby will get $40,000
• Herby’s Equity
– $40,000 - $22,792.47 = $17,207.53
Not So Fast
• It costs money to sell a house
– Real Estate Brokers Commission
• 7% of selling price $2,800
–
–
–
–
Title Insurance $400
Dead Preparation $200
Mortgage Release Recording $15
Real Estate Stamps (about 75cent/$100)
• $40,000/$100 = 400 * 0.75 = $300
Taxing Questions
• Property Taxes are in arears - Buyer will get
a credit
– Next years taxes will be about 5% higher than
last years
– $1,123.38 * 1.05 = $1,179.58
– Herby sold part way through the year so he
only covers 9 months of the 12
• $1,179.58 * 9/12 = $884.66
Herby’s Windfall
• Sellers Costs
– $2800 + $400 + $200 + $15 + $300 + $884.66
+$22,792.47 = $27,392.13
• Home Sells for $40,000
– $40,000 - $27,392.13 = $12,607.87 Cleared
• Herby can also cash in that Escrow Account
– Account will have $3,657 - $2,051.89 + $177.78*9 =
$3205.13
• Herby can also cancel his homeowners
insurance for last 3 months of year
– $730 * 0.25 = $182.50
Herby’s Homey Cash Flow
$15,995.50
from sale of
home
$132
$2,800
Down Payment
$372.06/mo.
$1,260
Loan Initiation Fees
$600
Homeowners Insurance
$200 Home improvement
loan initiation.
Bank also charged
$378 in “Points that
rolled into loan
$230.31 $139 $227.52
$146
$385.56/mo.
$310.56
$224.55
$221.39 $218.03 $161.18
$153
$161
$169
$316.56
$75/month for Home Repairs
$101.39/month Bank Loan for Repairs
$79.22/month Sinking Fund for Roof
$323.05
$329.84
Now How Do We Decide
Whether to Buy or Rent?
• That’s Right - The old subtract one
alternative from the other trick
• Our Thought would probably be that Herby
Should Buy Rather than Rent.
– Lets try to cash flow
• Hand Out the Cash Flow
Look At Flow
Month #
Renting
Initial Cost
-1300
1
-650
2
-650
3
-650
4
-650
5
-650
6
-650
7
-650
8
-650
9
-650
10
-650
11
-650
12
-670
Comparative Cash Flow if Herby
Buys Instead of Rents
Buying
Note that the cash
Buying
Over Rent
flow of the
-4860
-3560
Preferred
-627.67
22.33
Alternative is just
-627.67
22.33
Preferred - Alternate.
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
22.33
-627.67
42.33
Look at the Cumulative Cash
Position
Initial Cost
Month #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Payback Period
39
Renting
-1300
-650
-650
-650
-650
-650
-650
-650
-650
-650
-650
-650
-670
-670
-670
-670
-670
-670
-670
-670
-670
-670
-670
-670
-690
-690
-690
-690
-690
-690
-690
-690
-690
-690
-690
-690
-710
-710
-710
-710
Comparative Cash Flow if Herby
Buys Instead of Rents
Buying
Cash
Buying
Over Rent Position
-4860
-3560
-3560
-627.67
22.33 -3537.67
-627.67
22.33 -3515.34
-627.67
22.33 -3493.01
-627.67
22.33 -3470.68
-627.67
22.33 -3448.35
-627.67
22.33 -3426.02
-627.67
22.33 -3403.69
-627.67
22.33 -3381.36
-627.67
22.33 -3359.03
-627.67
22.33
-3336.7
-627.67
22.33 -3314.37
-627.67
42.33 -3272.04
-278.86
391.14
-2880.9
-641.17
28.83 -2852.07
-641.17
28.83 -2823.24
-641.17
28.83 -2794.41
-641.17
28.83 -2765.58
-641.17
28.83 -2736.75
-641.17
28.83 -2707.92
-641.17
28.83 -2679.09
-641.17
28.83 -2650.26
-641.17
28.83 -2621.43
-641.17
28.83
-2592.6
-641.17
48.83 -2543.77
-199.65
490.35 -2053.42
-566.17
123.83 -1929.59
-566.17
123.83 -1805.76
-566.17
123.83 -1681.93
-566.17
123.83
-1558.1
-566.17
123.83 -1434.27
-566.17
123.83 -1310.44
-566.17
123.83 -1186.61
-566.17
123.83 -1062.78
-566.17
123.83
-938.95
-566.17
123.83
-815.12
-566.17
143.83
-671.29
-201.62
508.38
-162.91
-572.17
137.83
-25.08
-572.17
137.83
112.75
What are the Chances of Getting Your
Rate of Return from a Cash Flow that
never pays back?
Payback Period can be a quick check for
a looser proposition.
Point at Which the Cumulative
Cash Position Goes Positive is
Called the Payback Period