# 33 spreadsheet Templates for Real Estate Finance and Investments_ by Rabia06

VIEWS: 3,449 PAGES: 214

• pg 1
```									                                                                                                      revised 10/9

This Excel Workbook contains 33 spreadsheet templates that replicate the calculations in selected exhibits
in Real Estate Finance and Investments , 13th ed. by William B. Brueggeman and and Jeffrey D. Fisher,
published by McGraw-Hill The tabs below indicate the chapter and identity of the template.
More information on the template is provide within the tab. Students can modify the templates to solve
several of the end-of-chapter problems in the chapters associated with the template.
Templates were developed by Jeffrey D. Fisher with the assistance of Anand Kumar.
These templates are intended only for educational purposes and not warranted for any other purpose.
The authors are not responsible for providing support on the use of the templates.
revised 10/9/06

culations in selected exhibits
n and and Jeffrey D. Fisher,

y the templates to solve

ed for any other purpose.
Chapter 4
Effective Cost of a Loan with Points

Spreadsheet Limitations: Prepayment assumed in 5 years

Input Assumptions

Loan amount                                    \$60,000
Initial Interest rate                           12.00%
Loan term                                            30 years
Points                                                3

Monthly payment                                 \$617.17
Annual payment                                \$7,406.01 (Assuming monthly payments)
Annual constant                                  12.34%
Effective cost (calculated below)                12.82%

Note: Do not change the payment and interest rate below unless you want to change from a standard fix

Year         Month       Beg. Balance     Payment          Rate     Interest   Principal
0
1             1       \$60,000.00        \$617.17     12.00%     600.00      17.17
2        59,982.83         617.17     12.00%     599.83      17.34
3        59,965.49         617.17     12.00%     599.65      17.51
4        59,947.98         617.17     12.00%     599.48      17.69
5        59,930.29         617.17     12.00%     599.30      17.86
6        59,912.43         617.17     12.00%     599.12      18.04
7        59,894.38         617.17     12.00%     598.94      18.22
8        59,876.16         617.17     12.00%     598.76      18.41
9        59,857.76         617.17     12.00%     598.58      18.59
10        59,839.17         617.17     12.00%     598.39      18.78
11        59,820.39         617.17     12.00%     598.20      18.96
12        59,801.43         617.17     12.00%     598.01      19.15
2             1        59,782.27         617.17     12.00%     597.82      19.34
2        59,762.93         617.17     12.00%     597.63      19.54
3        59,743.39         617.17     12.00%     597.43      19.73
4        59,723.66         617.17     12.00%     597.24      19.93
5        59,703.72         617.17     12.00%     597.04      20.13
6        59,683.59         617.17     12.00%     596.84      20.33
7        59,663.26         617.17     12.00%     596.63      20.53
8        59,642.73         617.17     12.00%     596.43      20.74
9        59,621.99         617.17     12.00%     596.22      20.95
10        59,601.04         617.17     12.00%     596.01      21.16
11        59,579.88         617.17     12.00%     595.80      21.37
12        59,558.51         617.17     12.00%     595.59      21.58
3             1        59,536.93         617.17     12.00%     595.37      21.80
2        59,515.13         617.17     12.00%     595.15      22.02
3        59,493.12         617.17     12.00%     594.93      22.24
4        59,470.88         617.17     12.00%     594.71      22.46
5        59,448.42         617.17     12.00%     594.48      22.68
6        59,425.74   617.17   12.00%   594.26   22.91
7        59,402.83   617.17   12.00%   594.03   23.14
8        59,379.69   617.17   12.00%   593.80   23.37
9        59,356.32   617.17   12.00%   593.56   23.60
10        59,332.71   617.17   12.00%   593.33   23.84
11        59,308.87   617.17   12.00%   593.09   24.08
12        59,284.79   617.17   12.00%   592.85   24.32
4    1        59,260.48   617.17   12.00%   592.60   24.56
2        59,235.91   617.17   12.00%   592.36   24.81
3        59,211.10   617.17   12.00%   592.11   25.06
4        59,186.05   617.17   12.00%   591.86   25.31
5        59,160.74   617.17   12.00%   591.61   25.56
6        59,135.18   617.17   12.00%   591.35   25.82
7        59,109.36   617.17   12.00%   591.09   26.07
8        59,083.29   617.17   12.00%   590.83   26.33
9        59,056.96   617.17   12.00%   590.57   26.60
10        59,030.36   617.17   12.00%   590.30   26.86
11        59,003.49   617.17   12.00%   590.03   27.13
12        58,976.36   617.17   12.00%   589.76   27.40
5    1        58,948.96   617.17   12.00%   589.49   27.68
2        58,921.28   617.17   12.00%   589.21   27.95
3        58,893.32   617.17   12.00%   588.93   28.23
4        58,865.09   617.17   12.00%   588.65   28.52
5        58,836.57   617.17   12.00%   588.37   28.80
6        58,807.77   617.17   12.00%   588.08   29.09
7        58,778.68   617.17   12.00%   587.79   29.38
8        58,749.30   617.17   12.00%   587.49   29.67
9        58,719.63   617.17   12.00%   587.20   29.97
10        58,689.66   617.17   12.00%   586.90   30.27
11        58,659.38   617.17   12.00%   586.59   30.57
12        58,628.81   617.17   12.00%   586.29   30.88

Effective Cost            12.82%
oan with Points

monthly payments)

u want to change from a standard fixed rate mortgage.

End Balance    Cash flow
(\$58,200)
59,982.83      617.17
59,965.49      617.17
59,947.98      617.17
59,930.29      617.17
59,912.43      617.17
59,894.38      617.17
59,876.16      617.17
59,857.76      617.17
59,839.17      617.17
59,820.39      617.17
59,801.43      617.17
59,782.27      617.17
59,762.93      617.17
59,743.39      617.17
59,723.66      617.17
59,703.72      617.17
59,683.59      617.17
59,663.26      617.17
59,642.73      617.17
59,621.99      617.17
59,601.04      617.17
59,579.88      617.17
59,558.51      617.17
59,536.93      617.17
59,515.13      617.17
59,493.12      617.17
59,470.88      617.17
59,448.42      617.17
59,425.74      617.17
59,402.83   617.17
59,379.69   617.17
59,356.32   617.17
59,332.71   617.17
59,308.87   617.17
59,284.79   617.17
59,260.48   617.17
59,235.91   617.17
59,211.10   617.17
59,186.05   617.17
59,160.74   617.17
59,135.18   617.17
59,109.36   617.17
59,083.29   617.17
59,056.96   617.17
59,030.36   617.17
59,003.49   617.17
58,976.36   617.17
58,948.96   617.17
58,921.28   617.17
58,893.32   617.17
58,865.09   617.17
58,836.57   617.17
58,807.77   617.17
58,778.68   617.17
58,749.30   617.17
58,719.63   617.17
58,689.66   617.17
58,659.38   617.17
58,628.81   617.17
58,597.93   59,215
Chapter 4
Effective Cost of a Loan with Points

Loan                 \$60,000
Term                       30 years
Rate                  12.00%
Points                 3.00%

Payment          \$    617.17 (using payment function)
Net Loan         \$ 58,200.00
Holding Period              5 years
Balance          \$ 58,597.93 (present value of remaining payments)

Eff Cost              12.82% (using rate function)
Cha
Effective Cost of a Consta

Input Assumptions

Loan amount                                            \$60,000
Initial Interest rate                                   12.00%
Loan term                                                    30 years
Points                                                        3

Monthly payment                                         \$617.17 (If a CPM)
Annual payment                                        \$7,406.01 (Assuming monthly payments)
Annual constant                                          15.33% (Based on first month)
Effective cost (calculated below)                        12.88%

Note: Do not change the payment and interest rate below unless you want to change from a standard fixed

Year                 Month          Beg. Balance   Payment     Rate     Interest   Principal
0
1                        1     \$60,000.00    \$766.67    12.00%    600.00        166.67
2      59,833.33    \$765.00    12.00%    598.33        166.67
3      59,666.67    \$763.33    12.00%    596.67        166.67
4      59,500.00    \$761.67    12.00%    595.00        166.67
5      59,333.33    \$760.00    12.00%    593.33        166.67
6      59,166.67    \$758.33    12.00%    591.67        166.67
7      59,000.00    \$756.67    12.00%    590.00        166.67
8      58,833.33    \$755.00    12.00%    588.33        166.67
9      58,666.67    \$753.33    12.00%    586.67        166.67
10      58,500.00    \$751.67    12.00%    585.00        166.67
11      58,333.33    \$750.00    12.00%    583.33        166.67
12      58,166.67    \$748.33    12.00%    581.67        166.67
2                        1      58,000.00    \$746.67    12.00%    580.00        166.67
2      57,833.33    \$745.00    12.00%    578.33        166.67
3      57,666.67    \$743.33    12.00%    576.67        166.67
4      57,500.00    \$741.67    12.00%    575.00        166.67
5      57,333.33    \$740.00    12.00%    573.33        166.67
6      57,166.67    \$738.33    12.00%    571.67        166.67
7      57,000.00    \$736.67    12.00%    570.00        166.67
8      56,833.33    \$735.00    12.00%    568.33        166.67
9      56,666.67    \$733.33    12.00%    566.67        166.67
10      56,500.00    \$731.67    12.00%    565.00        166.67
11      56,333.33    \$730.00    12.00%    563.33        166.67
12      56,166.67    \$728.33    12.00%    561.67        166.67
3                        1      56,000.00    \$726.67    12.00%    560.00        166.67
2      55,833.33    \$725.00    12.00%    558.33        166.67
3      55,666.67    \$723.33    12.00%    556.67        166.67
4      55,500.00    \$721.67    12.00%    555.00        166.67
5      55,333.33    \$720.00    12.00%    553.33        166.67
6      55,166.67    \$718.33    12.00%    551.67        166.67
7       55,000.00    \$716.67   12.00%   550.00   166.67
8       54,833.33    \$715.00   12.00%   548.33   166.67
9       54,666.67    \$713.33   12.00%   546.67   166.67
10       54,500.00    \$711.67   12.00%   545.00   166.67
11       54,333.33    \$710.00   12.00%   543.33   166.67
12       54,166.67    \$708.33   12.00%   541.67   166.67
4    1       54,000.00    \$706.67   12.00%   540.00   166.67
2       53,833.33    \$705.00   12.00%   538.33   166.67
3       53,666.67    \$703.33   12.00%   536.67   166.67
4       53,500.00    \$701.67   12.00%   535.00   166.67
5       53,333.33    \$700.00   12.00%   533.33   166.67
6       53,166.67    \$698.33   12.00%   531.67   166.67
7       53,000.00    \$696.67   12.00%   530.00   166.67
8       52,833.33    \$695.00   12.00%   528.33   166.67
9       52,666.67    \$693.33   12.00%   526.67   166.67
10       52,500.00    \$691.67   12.00%   525.00   166.67
11       52,333.33    \$690.00   12.00%   523.33   166.67
12       52,166.67    \$688.33   12.00%   521.67   166.67
5    1       52,000.00    \$686.67   12.00%   520.00   166.67
2       51,833.33    \$685.00   12.00%   518.33   166.67
3       51,666.67    \$683.33   12.00%   516.67   166.67
4       51,500.00    \$681.67   12.00%   515.00   166.67
5       51,333.33    \$680.00   12.00%   513.33   166.67
6       51,166.67    \$678.33   12.00%   511.67   166.67
7       51,000.00    \$676.67   12.00%   510.00   166.67
8       50,833.33    \$675.00   12.00%   508.33   166.67
9       50,666.67    \$673.33   12.00%   506.67   166.67
10       50,500.00    \$671.67   12.00%   505.00   166.67
11       50,333.33    \$670.00   12.00%   503.33   166.67
12       50,166.67    \$668.33   12.00%   501.67   166.67

Effective Cost             12.88%
Chapter 4
ive Cost of a Constant Amortization Loan with Points

ge from a standard fixed rate mortgage.

End Balance Cash flow
(\$58,200)
59,833.33    766.67
59,666.67    765.00
59,500.00    763.33                      Loan Payments: Constant Amort Mortgage vs. Constant Payment
Mortgage
59,333.33    761.67
59,166.67    760.00                   \$800
59,000.00    758.33
58,833.33    756.67
\$750
58,666.67    755.00
58,500.00    753.33
58,333.33    751.67                   \$700
58,166.67    750.00
Payment

58,000.00    748.33                   \$650
57,833.33    746.67
57,666.67    745.00
57,500.00    743.33                   \$600
57,333.33    741.67
57,166.67    740.00                   \$550                       Constant Amort Loan   Constant Payment Mortgage
57,000.00    738.33
56,833.33    736.67
\$500
56,666.67    735.00
1

2

3

4

56,500.00    733.33
56,333.33    731.67                                                           Year
56,166.67    730.00
56,000.00    728.33
55,833.33    726.67
55,666.67    725.00
55,500.00    723.33
55,333.33    721.67
55,166.67    720.00
55,000.00    718.33
54,833.33   716.67
54,666.67   715.00
54,500.00   713.33
54,333.33   711.67
54,166.67   710.00
54,000.00   708.33
53,833.33   706.67
53,666.67   705.00
53,500.00   703.33
53,333.33   701.67
53,166.67   700.00
53,000.00   698.33
52,833.33   696.67
52,666.67   695.00
52,500.00   693.33
52,333.33   691.67
52,166.67   690.00
52,000.00   688.33
51,833.33   686.67
51,666.67   685.00
51,500.00   683.33
51,333.33   681.67
51,166.67   680.00
51,000.00   678.33
50,833.33   676.67
50,666.67   675.00
50,500.00   673.33
50,333.33   671.67
50,166.67   670.00
50,000.00   50,668
onstant Payment

Constant Payment Mortgage
5
Chapter 4
Loan Balance on GPM

Spreadsheet Limitations: Projections for 7 years

Input Assumptions
Loan Amount            \$60,000
Interest Rate           12.00%
Loan Term                    30 years
Pmt Increase             7.50%
Inc. Years                    5
Points                        0

Lender's Yield (after 7 yrs)       12.00%

INITIAL PAYMENT CALCULATION:

(1)           (2)        (3)         (4)          (5)
Payment
Period       Payment    MPVIFA       MPVIF        (2x3x4)
1          1.00000   11.25508       1.00000      11.25508
2          1.07500   11.25508       0.88745      10.73743
3          1.15563   11.25508       0.78757      10.24360
4          1.24230   11.25508       0.69892       9.77247
5          1.33547   11.25508       0.62026       9.32302
6-25         1.43563   94.94655       0.55045      75.03072
Pmt factor =    126.36231
Initial Payment:
\$474.83 <====         \$60,000    126.36231
Loan Amt / Pmt Factor

LOAN AMORTIZATION SCHEDULE:

Month          Payment       Interest Principle      Balance CPM Payment
0               (\$60,000) <==Amount Dispersed       \$60,000
1                \$474.83 \$600.00 (\$125.17)          \$60,125    \$617.17
2                \$474.83 \$601.25 (\$126.43)          \$60,252    \$617.17
3                \$474.83 \$602.52 (\$127.69)          \$60,379    \$617.17
4                \$474.83 \$603.79 (\$128.97)          \$60,508    \$617.17
5                \$474.83 \$605.08 (\$130.26)          \$60,639    \$617.17
6                \$474.83 \$606.39 (\$131.56)          \$60,770    \$617.17
7                \$474.83 \$607.70 (\$132.88)          \$60,903    \$617.17
8                \$474.83 \$609.03 (\$134.20)          \$61,037    \$617.17
9                \$474.83 \$610.37 (\$135.55)          \$61,173    \$617.17
10                \$474.83 \$611.73 (\$136.90)          \$61,310    \$617.17
11   \$474.83   \$613.10   (\$138.27)   \$61,448   \$617.17
12   \$474.83   \$614.48   (\$139.65)   \$61,588   \$617.17   EOY 1
13   \$510.44   \$615.88   (\$105.44)   \$61,693   \$617.17
14   \$510.44   \$616.93   (\$106.49)   \$61,799   \$617.17
15   \$510.44   \$617.99   (\$107.56)   \$61,907   \$617.17
16   \$510.44   \$619.07   (\$108.63)   \$62,016   \$617.17
17   \$510.44   \$620.16   (\$109.72)   \$62,125   \$617.17
18   \$510.44   \$621.25   (\$110.82)   \$62,236   \$617.17
19   \$510.44   \$622.36   (\$111.92)   \$62,348   \$617.17
20   \$510.44   \$623.48   (\$113.04)   \$62,461   \$617.17
21   \$510.44   \$624.61   (\$114.17)   \$62,575   \$617.17
22   \$510.44   \$625.75   (\$115.32)   \$62,691   \$617.17
23   \$510.44   \$626.91   (\$116.47)   \$62,807   \$617.17
24   \$510.44   \$628.07   (\$117.63)   \$62,925   \$617.17   EOY 2
25   \$548.72   \$629.25    (\$80.53)   \$63,005   \$617.17
26   \$548.72   \$630.05    (\$81.33)   \$63,087   \$617.17
27   \$548.72   \$630.87    (\$82.15)   \$63,169   \$617.17
28   \$548.72   \$631.69    (\$82.97)   \$63,252   \$617.17
29   \$548.72   \$632.52    (\$83.80)   \$63,336   \$617.17
30   \$548.72   \$633.36    (\$84.64)   \$63,420   \$617.17
31   \$548.72   \$634.20    (\$85.48)   \$63,506   \$617.17
32   \$548.72   \$635.06    (\$86.34)   \$63,592   \$617.17
33   \$548.72   \$635.92    (\$87.20)   \$63,679   \$617.17
34   \$548.72   \$636.79    (\$88.07)   \$63,767   \$617.17
35   \$548.72   \$637.67    (\$88.95)   \$63,856   \$617.17
36   \$548.72   \$638.56    (\$89.84)   \$63,946   \$617.17   EOY 3
37   \$589.87   \$639.46    (\$49.59)   \$63,996   \$617.17
38   \$589.87   \$639.96    (\$50.08)   \$64,046   \$617.17
39   \$589.87   \$640.46    (\$50.58)   \$64,096   \$617.17
40   \$589.87   \$640.96    (\$51.09)   \$64,147   \$617.17
41   \$589.87   \$641.47    (\$51.60)   \$64,199   \$617.17
42   \$589.87   \$641.99    (\$52.12)   \$64,251   \$617.17
43   \$589.87   \$642.51    (\$52.64)   \$64,304   \$617.17
44   \$589.87   \$643.04    (\$53.16)   \$64,357   \$617.17
45   \$589.87   \$643.57    (\$53.70)   \$64,411   \$617.17
46   \$589.87   \$644.11    (\$54.23)   \$64,465   \$617.17
47   \$589.87   \$644.65    (\$54.77)   \$64,520   \$617.17
48   \$589.87   \$645.20    (\$55.32)   \$64,575   \$617.17   EOY 4
49   \$634.11   \$645.75    (\$11.63)   \$64,587   \$617.17
50   \$634.11   \$645.87    (\$11.75)   \$64,598   \$617.17
51   \$634.11   \$645.98    (\$11.87)   \$64,610   \$617.17
52   \$634.11   \$646.10    (\$11.99)   \$64,622   \$617.17
53   \$634.11   \$646.22    (\$12.11)   \$64,634   \$617.17
54   \$634.11   \$646.34    (\$12.23)   \$64,647   \$617.17
55   \$634.11   \$646.47    (\$12.35)   \$64,659   \$617.17
56   \$634.11   \$646.59    (\$12.47)   \$64,671   \$617.17
57   \$634.11   \$646.71    (\$12.60)   \$64,684   \$617.17
58   \$634.11   \$646.84    (\$12.73)   \$64,697   \$617.17
59   \$634.11   \$646.97    (\$12.85)   \$64,710   \$617.17
60   \$634.11   \$647.10    (\$12.98)   \$64,722   \$617.17   EOY 5
61   \$681.67   \$647.22     \$34.45    \$64,688   \$617.17
62      \$681.67   \$646.88   \$34.79   \$64,653   \$617.17
63      \$681.67   \$646.53   \$35.14   \$64,618   \$617.17
64      \$681.67   \$646.18   \$35.49   \$64,583   \$617.17
65      \$681.67   \$645.83   \$35.85   \$64,547   \$617.17
66      \$681.67   \$645.47   \$36.21   \$64,511   \$617.17
67      \$681.67   \$645.11   \$36.57   \$64,474   \$617.17
68      \$681.67   \$644.74   \$36.93   \$64,437   \$617.17
69      \$681.67   \$644.37   \$37.30   \$64,400   \$617.17
70      \$681.67   \$644.00   \$37.68   \$64,362   \$617.17
71      \$681.67   \$643.62   \$38.05   \$64,324   \$617.17
72      \$681.67   \$643.24   \$38.43   \$64,286   \$617.17   EOY 6
73      \$681.67   \$642.86   \$38.82   \$64,247   \$617.17
74      \$681.67   \$642.47   \$39.21   \$64,208   \$617.17
75      \$681.67   \$642.08   \$39.60   \$64,168   \$617.17
76      \$681.67   \$641.68   \$39.99   \$64,128   \$617.17
77      \$681.67   \$641.28   \$40.39   \$64,088   \$617.17
78      \$681.67   \$640.88   \$40.80   \$64,047   \$617.17
79      \$681.67   \$640.47   \$41.20   \$64,006   \$617.17
80      \$681.67   \$640.06   \$41.62   \$63,964   \$617.17
81      \$681.67   \$639.64   \$42.03   \$63,922   \$617.17
82      \$681.67   \$639.22   \$42.45   \$63,879   \$617.17
83      \$681.67   \$638.79   \$42.88   \$63,837   \$617.17
84   \$64,474.98   \$638.37   \$43.31   \$63,793   \$617.17   EOY 7
GPM vs CPM Payment

\$750
\$700
\$650
\$600
Payment

\$550
\$500
\$450
\$400
\$350
\$300
0
5

50

65
10
15
20
25
30
35
40
45

55
60

70
75
80
Month

GPM Loan Balance

\$66,000
\$65,000
\$64,000
\$63,000
Balance

\$62,000
\$61,000
\$60,000
\$59,000
\$58,000
\$57,000
0
5

45
10
15
20
25
30
35
40

50
55
60
65
70
75
80

Month
Chapter 5
Unrestricted ARM

Loan assumptions
Amount                    \$60,000
Initial rate                8.00%
Term                            30 years
Payments per year               12
Margin                      2.00%
Points                      2.00%

Loan schedule

Year           Index       Rate      Payment Balance Cash flow
(58,800)
1                    8.00%      440.26      59,499     5,283
2       10.00%      12.00%      614.24      59,255     7,371
3       13.00%      15.00%      752.26      59,106     9,027
4       15.00%      17.00%      846.20      58,991    10,154
5       10.00%      12.00%      617.60      58,639    66,050

Yield       12.98%

Yield is based on monthly cash flows below.

Month       Cash flow
0          (58,800)
1              440
2              440
3              440
4              440
5              440
6              440
7              440
8              440
9              440
10              440
11              440
12              440
13              614
14              614
15              614
16              614
17              614
18              614
19              614
20      614
21      614
22      614
23      614
24      614
25      752
26      752
27      752
28      752
29      752
30      752
31      752
32      752
33      752
34      752
35      752
36      752
37      846
38      846
39      846
40      846
41      846
42      846
43      846
44      846
45      846
46      846
47      846
48      846
49      618
50      618
51      618
52      618
53      618
54      618
55      618
56      618
57      618
58      618
59      618
60   59,257
Chapter 5
ARM with Interest Rate Cap

Loan assumptions
Amount                                   \$60,000
Initial rate                              11.00%
Annual rate cap                            2.00%
Lifetime rate cap                          5.00%
Term                                           30 years
Payments per year                              12
Margin                                     2.00%
Points                                     2.00%

Loan schedule
Unrestricted Restricted
Year              Index               Rate        Rate     Payment   Balance Cash flow
(58,800)
1                                11.00%     11.00%   571.39    59,730    6,857
2                  10.00%        12.00%     12.00%   616.63    59,485    7,400
3                  13.00%        15.00%     14.00%   708.37    59,301    8,500
4                  15.00%        17.00%     16.00%   801.65    59,159    9,620
5                  10.00%        12.00%     12.00%   619.37    58,807   66,239

Yield     13.38%

Yield is based on monthly cash flows below.

Month        Cash flow
0             (58,800)
1                 571
2                 571
3                 571
4                 571
5                 571
6                 571
7                 571
8                 571
9                 571
10                 571
11                 571
12                 571
13                 617
14                 617
15                 617
16                 617
17                 617
18                 617
19      617
20      617
21      617
22      617
23      617
24      617
25      708
26      708
27      708
28      708
29      708
30      708
31      708
32      708
33      708
34      708
35      708
36      708
37      802
38      802
39      802
40      802
41      802
42      802
43      802
44      802
45      802
46      802
47      802
48      802
49      619
50      619
51      619
52      619
53      619
54      619
55      619
56      619
57      619
58      619
59      619
60   59,426
Chapter 5
ARM with Payment Cap

Loan assumptions
Amount                       \$60,000
Initial rate                   9.00%
Annual Payment cap             7.50%
Term                               30 years
Payments per year                  12
Margin                         2.00%
Points                         2.00%

Loan schedule
Unrestricted Capped
Year          Index       Rate        Payment     Payment      Balance Cash flow
(58,800)
1                     9.00%          482.77   482.77     59,590    5,793
2       10.00%       12.00%          615.18    518.98    60,566    7,382
3       13.00%       15.00%          768.90    557.91    63,127    9,227
4       15.00%       17.00%          903.77    599.75    66,950   10,845
5       10.00%       12.00%          700.94    644.73    67,265   75,676

Yield     15.60%

Yield is based on monthly cash flows below.

Month        Cash flow
0     (58,800)
1         483
2         483
3         483
4         483
5         483
6         483
7         483
8         483
9         483
10         483
11         483
12         483
13         615
14         615
15         615
16         615
17         615
18         615
19         615
20         615
21      615
22      615
23      615
24      615
25      769
26      769
27      769
28      769
29      769
30      769
31      769
32      769
33      769
34      769
35      769
36      769
37      904
38      904
39      904
40      904
41      904
42      904
43      904
44      904
45      904
46      904
47      904
48      904
49      701
50      701
51      701
52      701
53      701
54      701
55      701
56      701
57      701
58      701
59      701
60   67,966
Chapter 5
Shared Appreciation Mortgage

Part A
Loan assumptions
Home price                      \$110,000
Home apprec.                     25.00%
Resale price                    \$137,500
Loan                            \$95,000
Term                                  30 years
Rate                              8.00%
Points                            0.00%
Participation                    30.00%

Payment                          \$697.08 (using payment function)
Net Loan                            95000
Holding Period                          5 years
Balance                       \$90,316.36 (present value of remaining payments)
Appreciation                       \$8,250
Total to lender               \$98,566.36

Eff Cost                             9.40% (using rate function)

Part B
Loan assumptions
Home price                      \$110,000
Home apprec.                     76.79%                            Home Appreciation is solved
Resale price                    \$194,467                           using Solver by setting Eff
Loan                            \$95,000                            cost (C45) as target cell and
Term                                  30 years                     Home Appreciation (C30) as
Rate                              8.00%                            changing cell
Points                            0.00%
Participation                    30.00%

Payment                          \$697.08 (using payment function)
Net Loan                            95000
Holding Period                          5 years
Balance                       \$90,316.36 (present value of remaining payments)
Appreciation                      \$25,340
Total to lender              \$115,656.47

Eff Cost                          12.00% (using rate function)

Part C - I
Loan assumptions
Home price                      \$110,000
Home apprec.                     25.00%
Resale price                    \$137,500
Loan                            \$95,000
Term                                  30 years
Rate                              8.00%
Points                            2.00%
Participation                    30.00%

Payment                          \$697.08 (using payment function)
Net Loan                            93100
Holding Period                          5 years
Balance                       \$90,316.36 (present value of remaining payments)
Appreciation                       \$8,250
Total to lender               \$98,566.36

Eff Cost                             9.90% (using rate function)

Part C - II
Loan assumptions
Home price                      \$110,000
Home apprec.                     66.33%                            Home Appreciation is solved
Resale price                    \$182,960                           using Solver by setting Eff
Loan                            \$95,000                            cost (C78) as target cell and
Term                                  30 years                     Home Appreciation (C72) as
Rate                              8.00%                            changing cell
Points                            2.00%
Participation                    30.00%

Payment                          \$697.08 (using payment function)
Net Loan                            93100
Holding Period                          5 years
Balance                       \$90,316.36 (present value of remaining payments)
Appreciation                      \$21,888
Total to lender              \$112,204.44

Eff Cost                          12.00% (using rate function)
preciation is solved
lver by setting Eff
5) as target cell and
preciation (C30) as
cell
preciation is solved
lver by setting Eff
8) as target cell and
preciation (C72) as
cell
Chapter 6
Incremental Cost

PART A

Holding period                     25

Loan                Points                 Loan Proceeds Rate     Term*    Payment
\$90,000              0.00%               \$90,000  8.50%       25     724.70
\$80,000              0.00%               \$80,000  8.00%       25     617.45
\$10,000                                  \$10,000              25     107.25

Incremental Cost               12.87%          *Should be the same term for both loans

PART B

Holding period                     25

Loan                Points           Loan Proceeds Rate     Term*    Payment
\$90,000              2.00%         \$88,200  8.50%       25     724.70
\$80,000              0.00%         \$80,000  8.00%       25     617.45
\$10,000                             \$8,200              25     107.25

Incremental Cost               15.77%          *Should be the same term for both loans

PART C

Holding period                         5

Loan                Points                 Loan Proceeds Rate     Term*    Payment
\$90,000              2.00%               \$88,200  8.50%       25     724.70
\$80,000              0.00%               \$80,000  8.00%       25     617.45
\$10,000                                   \$8,200              25     107.25

Incremental Cost               18.40%          *Should be the same term for both loans
Balance
\$90,000.00
\$80,000.00
10,000.00

the same term for both loans

Balance
\$90,000.00
\$80,000.00
10,000.00

the same term for both loans

Balance
\$90,000.00
\$80,000.00
10,000.00

the same term for both loans
Chapter 6
Combined Cost

PART A

Holding period                20
Loan 1
Loan           Points               Loan Proceeds Rate     Term*   Payment
\$220,000              0.00%            \$220,000 9.50%       20 2,050.69

Loan 2
Loan          Points             Loan Proceeds Rate      Term*    Payment
\$180,000              0.00%         \$180,000  9.00%       20 1,619.51
\$40,000              0.00%          \$40,000 13.00%       20    468.63
220000                              220000              20 2,088.14

Discount rate               9.00%
Net Benefit in taking Loan 1              \$4,162.20

PART B

Holding period                 5
Loan 1
Loan           Points               Loan Proceeds Rate     Term*   Payment
\$220,000              0.00%            \$220,000 9.50%       20 2,050.69

Loan 2
Loan          Points                Loan Proceeds Rate      Term*    Payment
\$180,000              0.00%            \$180,000  9.00%       20 1,619.51
\$40,000              0.00%             \$40,000 13.00%       20    468.63
220000                                 220000              20 2,088.14

Discount rate               9.00%
Net Benefit in taking Loan 1              \$1,804.02

PART C - I

Holding period                20
Loan 1
Loan           Points               Loan Proceeds Rate     Term*   Payment
\$220,000              0.00%            \$220,000 9.50%       20 2,050.69

Loan 2
Loan          Points                Loan Proceeds Rate      Term*    Payment
\$180,000              0.00%            \$180,000  9.00%       20 1,619.51
\$40,000              0.00%             \$40,000 13.00%       10    597.24
220000                                220000               20   2,216.75

Discount rate               9.00%
Net Benefit in taking Loan 1             (\$1,268.97)

PART C - II

Holding period                 5
Loan 1
Loan           Points               Loan Proceeds Rate     Term*   Payment
\$220,000              0.00%            \$220,000 9.50%       20 2,050.69

Loan 2
Loan          Points             Loan Proceeds Rate      Term*    Payment
\$180,000              0.00%         \$180,000  9.00%       20 1,619.51
\$40,000              0.00%          \$40,000 13.00%       10    597.24
220000                              220000              20 2,216.75

Discount rate               9.00%
Net Benefit in taking Loan 1             \$7,999.72
Balance
\$220,000.00

Balance
\$180,000.00
\$40,000.00
220000

Balance
\$220,000.00

Balance
\$180,000.00
\$40,000.00
220000

Balance
\$220,000.00

Balance
\$180,000.00
\$40,000.00
220000

Balance
\$220,000.00

Balance
\$180,000.00
\$40,000.00
220000
Chapter 7
Rent versus Own Analysis of a Personal Residence

Property information                                            Loan Information:
Loan-to-value ratio
Purchase price                                  \$150,000               Loan amount
Initial Rent                                     \$12,000               Interest rate
Rental growth rate                                 2.00%               Loan term (years)
Property growth rate                               2.00%               Payments (per year)
Insurance                                           \$500
Maintenance                                         \$500
Expense growth                                     2.00%
Marginal tax rate                                 28.00%
Property tax %                                     1.50%
Selling expenses                                   7.00%

Loan and equity calculations

Annual debt service (payment)                     \$9,580
Annual loan constant                               7.98%
Equity investment                                \$30,000

Summary loan schedule
End of year                                                       1                      2
Payment                                                     \$9,580                 \$9,580
Balance                                                    118,781                117,474
Interest                                                     8,361                  8,273
Principal                                                    1,219                  1,307

Property Data
0          1                     2
Property value                                  \$150,000    153,000               156,060
Rents                                                         12,000                12,240

BTCF (owner)
Year              1                      2

Property taxes                                                 2,250                 2,295
Insurance                                                        500                   510
Maintenance                                                      500                   510
Principal and Interest                                         9,580                 9,580
Cash Outflows before taxes                                    12,830                12,895

Tax deductions from owning

Property taxes                                               \$2,250                \$2,295
Interest                                                      8,361                 8,273
Total tax deductions                                         \$10,611               \$10,568
Tax savings                                                       2,971              2,959

Net cost of owning
Cash Outflows before taxes                                       12,830             12,895
Tax savings                                                        2,971             2,959
After tax cost                                                     9,859             9,936

Net cost of renting
Rents                                                           \$12,000            \$12,240

Net Cash Flow from Owning Before Sale
Cost of renting                                                 \$12,000             \$12,240
Cost of owning                                                    9,859              9,936
After Tax Cash Flow Own vs Rent                                  \$2,141             \$2,304

BT Cash Flow - Sale
1                   2
Sales price                                                    153,000             156,060
Selling Costs                                                   10,710              10,924
Mortgage balance                                               118,781             117,474
Benefit from sale (own - rent)                                  23,509              27,662

AT Cash Flow - Sale

Sales price                                                    \$153,000           \$156,060
Less selling costs                                               10,710             10,924
Less purchase price                                             150,000            150,000
Gain on sale                                                     (7,710)            (4,864)
Exclusion                                                             0                  0
Taxable gain                                                          0                  0
Tax                                                                   0                  0
After tax cash flow                                              23,509             27,662

Cash savings and IRR summary
Cash flows
Year Sold                 0           1                2
1          -30,000    \$25,650
2          -30,000       2,141           29,966
3          -30,000       2,141            2,304
4          -30,000       2,141            2,304
5          -30,000       2,141            2,304
ersonal Residence

Loan Information:
-value ratio                  80.00%
\$120,000
7.00%
30
12

3             4          5
\$9,580        \$9,580     \$9,580
116,072       114,569    112,958
8,179         8,077      7,969
1,402         1,503      1,612

3             4          5
159,181       162,365    165,612
12,485        12,734     12,989

3          4          5

2,341        2,388      2,435
520          531        541
520          531        541
9,580        9,580      9,580
12,962       13,029     13,098

\$2,341        \$2,388     \$2,435
8,179         8,077      7,969
\$10,520       \$10,465    \$10,404
2,946      2,930      2,913

12,962     13,029     13,098
2,946      2,930      2,913
10,016     10,099     10,185

\$12,485    \$12,734    \$12,989

Sale
\$12,485    \$12,734    \$12,989
10,016     10,099     10,185
\$2,469     \$2,635     \$2,804

3          4          5
159,181    162,365    165,612
11,143     11,366     11,593
116,072    114,569    112,958
31,966     36,430     41,061

\$159,181 \$162,365 \$165,612
11,143   11,366   11,593
150,000 150,000 150,000
(1,961)     999    4,019
0      999    4,019
0        0        0
0        0        0
31,966   36,430   41,061

mary
ATIRR
3          4          5
-14.50%
3.57%
34,435                            9.63%
2,469     39,065                12.34%
2,469      2,635     43,865     13.71%
Chapter 9
Effective Rents

Spreadsheet Limitations: Net Lease with free rent has begins payment year two.

Data Input Box:
Inerest Rate                             0.1 Adjustments
Year 1 Rents                                 Step Amount                       1
Net Lease with Steps                      10 Percent CPI Adj.                  1
Net with CPI Adjustment                   11 Yr 2 CPI                       0.02
Gross Lease                             17.5 CPI Growth                     0.01
Gross with Exp. Stop                    15.5 Yr 1 Expenses                     4
Gross with Exp. Stop &CPI               14.5 Expense Growth                  0.5
Year 2 Rents                                 Expense Stop                      4
Net Lease with Free Rent                14.5

NET LEASE with STEPS:
Year              1            2           3               4         5
Net Rent         \$          10.00   11.00         12.00         13.00     14.00
Average Rent                      \$ 12.00
Present Value                       44.77
Effective Rent                      11.81

NET LEASE with FREE RENT:
Year              1              2          3              4         5
Net Rent               0             14.50         15.50        16.50     17.50
Average Rent                         12.80
Present Value                        45.76
Effective Rent                       12.07

NET LEASE with CPI ADJUSTMENT:
Year              1            2          3                4         5
Expected CPI           -            2.00%        3.00%          4.00%     5.00%
Net Rent                  11.00      11.22        11.56          12.02     12.62
Average Rent                        11.68
Present Value                       44.00
Effective Rent                      11.61

GROSS LEASE:
Year              1             2             3            4         5
Gross Rent                  17.50    17.50             17.50    17.50     17.50
Less Expenses                4.00     4.50              5.00     5.50      6.00
Net Rent                    13.50    13.00             12.50    12.00     11.50
Average Rent                         12.50
Present Value                     47.74
Effective Rent                    12.59

GROSS LEASE with EXPENSE STOP:
Year              1          2          3         4       5
Gross Rent               15.50    15.50        15.50   15.50   15.50
Less Expenses             4.00     4.50         5.00    5.50    6.00
Plus Reimbursement        0.00     0.50         1.00    1.50    2.00
Net Rent                 11.50    11.50        11.50   11.50   11.50
Average Rent                      11.50
Present Value                     43.59
Effective Rent                    11.50

GROSS LEASE with EXPENSE STOP and CPI ADJUSTMENT:
Year            1           2         3          4        5
Expected CPI         -          2.00%        3.00%   4.00%     5.00%
Gross Rent             14.50     14.79        15.23   15.84     16.64
Less Expenses            4.00     4.50        5.00     5.50     6.00
Plus Reimbursement       0.00     0.50        1.00     1.50     2.00
Net Rent               10.50    10.79        11.23   11.84     12.64
Average Rent                    11.40
Present Value                   42.84
Effective Rent                  11.30
year two.

per year

per year

per year
Chapter 10
Mortgage-Equity Capitalization

Spreadsheet Limitations: Five year Holding Period; loan based on DCR and resale based on terminal cap ra

Data Input Box:
NOI                                            50000
Projected Increase in NOI                        0.03 per year
Operating Expenses                                0.3 of EGI
Loan:
Debt Coverage Ratio                            1.2
Interest                                      0.11
Term                                            20 years
Payments per year                               12

Holding Period                                     5 years
Terminal Cap Rate                               0.11
Before-tax Equity Yield                         0.12

Loan Constant                12.3863%
Loan Amount                   336,394         67.02% LTV
Loan Balance                  305,493          in year           5

Year            1          2            3        4        5
Net Operating Income                          50,000    51,500       53,045   54,636   56,275

Debt Service                                  41,667    41,667       41,667   41,667   41,667
Before-tax Cash Flow                           8,333     9,833       11,378   12,970   14,609

DCR                                             1.20       1.24        1.27     1.31     1.35

Cash Flows from Sale in Year                       5

Sales Price                                  526,943
Mortgage Balance                             305,493
Before-tax Cash Flow                         221,450

Present Value of Operations at                12.00%    39,910
Present value of Reversion at                 12.00%   125,657
Total Equity                                           165,567

Total Equity                                           165,567
Mortgage                                               336,394
Total Present Value                                    501,961

Check:
Cash Flow Summary
Year     0         1       2        3        4
BTCF                  (165,567)   8,333   9,833   11,378   12,970

BTIRR           12.00%
based on terminal cap rate.

6
57,964
5
236,059
Chapter 10
Highest and Best Use Analysis

Data Input Box:
Office             Retail         Apartment   Warehouse
NOI yr 1              \$500,000           \$600,000         \$400,000     \$400,000
Return or r            13.00%             12.00%           12.00%       10.00%
Growth or g             3.00%              4.00%            3.00%        2.00%
Building Costs        4,000,000          4,000,000        4,000,000   4,000,000

HIGHEST and BEST USE ANALYSIS:
Implied Property     Building    Implied Land
Use       Year 1 NOI    R           Value            Costs         Value
Office             \$500,000   10.00%    5,000,000         4,000,000     1,000,000
Retail              600,000    8.00%    7,500,000         4,000,000     3,500,000
Apartment           400,000    9.00%    4,444,444         4,000,000      444,444
Warehouse           400,000    8.00%    5,000,000         4,000,000     1,000,000
Warehouse
\$400,000
10.00%
2.00%
4,000,000

Implied Land
Value
1,000,000
3,500,000
444,444
1,000,000
Chapter 10
Oakwood Apartment Example

Building Name                     Oakwood
Address                           1234 Elm Street
City                              Anywhere
State                             USA
Total Units                                     95
Unit Size                                   1,100
Building Size (SF)                        104,500

Analysis Begin Date                       1/1/2000
Holding Period                                   5
Discount Rate                              11.00%
Terminal Rate                               9.00%
Selling Cost                                5.00%

Inputs              Apartment Unit
Name                               Two Bedroom
Units                                          95
Monthly Rent                           \$1,200.00
Lease Term (Yrs)                               1
Market Monthly Rent                    \$1,250.00
Market Rent Increase                       3.00%

Laundry Income/unit/year                  \$120.00
Laundry Income increase                     3.00%

Market Vacancy Rate                          5.00%
Credit Loss Rate                             1.00%

% of EGI         \$ Amount    \$ per Unit    Change %
Real Estate Taxes                           0.00%    \$87,000.00         \$0.00       2.50%
Office Expenses                             0.00%    \$20,000.00         \$0.00       3.00%
Insurance                                   0.00%         \$0.00      \$150.00        3.00%
Repairs & Maintenance                       0.00%         \$0.00      \$550.00        3.00%
Advertising                                 0.00%     \$8,000.00         \$0.00       3.00%
Management                                 12.00%         \$0.00         \$0.00       0.00%
Utilities                                   0.00%    \$45,000.00         \$0.00       3.00%
Miscellaneous Expenses                      0.00%    \$15,000.00         \$0.00       3.00%

Outputs
Year                      1               2            3            4
Income:
Current Rent                           \$1,368,000         \$0              \$0           \$0
Market Rent from lease renewals                 \$0 \$1,467,750      \$1,511,783   \$1,557,136
Laundry Income                            \$11,400     \$11,742         \$12,094      \$12,457
Potential Gross Income (PGI)    \$1,379,400 \$1,479,492     \$1,523,877     \$1,569,593
Less: Vacancy                      \$68,400    \$73,388        \$75,589        \$77,857
Less: Credit Loss                  \$13,794    \$14,795        \$15,239        \$15,696
Effective Gross Income (EGI)    \$1,297,206 \$1,391,310     \$1,433,049     \$1,476,040

Expenses:
Real Estate Taxes                 \$87,000     \$89,175        \$91,404       \$93,689
Office Expenses                   \$20,000     \$20,600        \$21,218       \$21,855
Insurance                         \$14,250     \$14,678        \$15,118       \$15,571
Repairs & Maintenance             \$52,250     \$53,818        \$55,432       \$57,095
Advertising                        \$8,000      \$8,240         \$8,487        \$8,742
Management                       \$155,665    \$166,957       \$171,966      \$177,125
Utilities                         \$45,000     \$46,350        \$47,741       \$49,173
Miscellaneous Expenses            \$15,000     \$15,450        \$15,914       \$16,391
Total Expenses                   \$397,165    \$415,267       \$427,279      \$439,641

Net Operating Income (NOI)       \$900,041    \$976,042     \$1,005,770     \$1,036,400

Expenses % of EGI                  30.62%      29.85%          29.82%       29.79%

Net Operating Income             \$900,041    \$976,042     \$1,005,770     \$1,036,400
PV Factors                        0.90090     0.81162        0.73119        0.65873
Present Value                    \$810,848    \$792,178       \$735,410       \$682,709

Resale                         \$12,227,545                              Sum PV NOI
Selling Cost                      \$611,377                              PV Resale
Net Resale                     \$11,616,168                              Value
PV Factor                          0.59345
PVResale                        \$6,893,630
Implied Change in Value
ample

5           6

\$0         \$0
\$1,603,850 \$1,651,966
\$12,831    \$13,216
\$1,616,681 \$1,665,181
\$80,193    \$82,598
\$16,167    \$16,652
\$1,520,322 \$1,565,931

\$96,032     \$98,433
\$22,510     \$23,185
\$16,039     \$16,520
\$58,808     \$60,572
\$9,004      \$9,274
\$182,439    \$187,912
\$50,648     \$52,167
\$16,883     \$17,389
\$452,361    \$465,452

\$1,067,960 \$1,100,479

29.75%      29.72%

\$1,067,960 \$1,100,479
0.59345
\$633,782

\$3,654,927
\$6,893,630
\$10,548,557

15.92%
Chapter 11
Lease Calculations: Given Lease Data, Solve for N

Spreadsheet Assumption: Vacancy rate does not take effect until the beginning of year 4.
Corresponds with Homework Problem 10-1

Input Data:
Current Market Rent                                           \$15.00   per s.f.
Gross square feet of building                                100,000   s.f.
Net rentable square feet of building                          96,000   s.f.
Projected Increase in Market Rent                              4.00%   per year
Management costs                                               5.00%   of Effective Gross Income
Estimated increase in CPI                                      4.00%   per year
Vacancy rate starting year 4                                   5.00%   per year

SUMMARY LEASE INFORMATION
Current
Tenant                  Sq. ft.           Rent                  Current
per s.f.                Rent

Tenant 1                                  70,000              \$14.00                  \$980,000
Tenant 2                                       0              \$14.00                         0
Tenant 3                                       0              \$14.00                         0
Tenant 4                                  10,000              \$14.50                   145,000
Tenant 5                                       0              \$15.00                         0
Tenant 6                                  16,000              \$15.00                   240,000

Total                                     96,000                                      1,365,000

Note: Expense stop excludes managemen

Summary of Expense Information
Dollars                     per s.f.

Property tax                                               \$148,800                         1.55
Insurance                                                    14,400                         0.15
Utilities                                                   120,000                         1.25
Janitorial                                                   76,800                         0.80
Maintenance                                                  67,200                         0.70

Subtotal (before mgt)                                        427,200                        4.45

Management                                                   \$68,250                        0.71

Total                                                      \$495,450                       \$5.16
PROJECTED BASE INCOME
Year        1                      2

Tenant 1                      980,000                    980,000
Tenant 2                            0                          0
Tenant 3                            0                          0
Tenant 4                      145,000                    145,000
Tenant 5                            0                          0
Tenant 6                      240,000                    240,000

Total base       1,365,000                 1,365,000

Tenant 1                         19,600                   39,592
Tenant 4                          2,900                    5,858
Tenant 6                              0                    4,800

Total CPI           22,500                   50,250

Total Base and CPI   1,387,500                 1,415,250

Expense Reinmursements
Tenant 1                      \$31,500                   \$39,445
Tenant 2                            0                         0
Tenant 3                            0                         0
Tenant 4                        2,000                     3,135
Tenant 5                            0                         0
Tenant 6                            0                     1,816
Total Reimbursements           33,500                    44,396

Potential Gross Income       1,421,000                 1,459,646
Vacancy                              0                         0
Effective Gross Income       1,421,000                 1,459,646

SUMMARY OF OPERATING EXPENSES

Reimbursable expenses
Property tax                  148,800                    148,800
Insurance                      14,400                     14,976
Utilities                     120,000                    126,000
Janitorial                     76,800                     79,104
Maintenance                    67,200                     69,216
Total before management       427,200                    438,096
per s.f.                          4.45                       4.56

Non reimbursable expenses
Management                     71,050                   72,982
Total expenses
Total Expenses                498,250                  511,078
PROJECTED NET OPERATING INCOME
Year                 1                2
Base income                        \$1,365,000           \$1,365,000
Plus CPI Adjustment                    22,500               50,250
Plus Reimbursements                   \$33,500              \$44,396
Total Potential Income             \$1,421,000           \$1,459,646
Less Vacancy                                0                    0
Effective Gross Income              1,421,000            1,459,646
Less operating expenses:
Reimbursable expenses               427,200                   438,096
Non reimbursable expenses            71,050                    72,982
NOI                                 \$922,750                  \$948,568

Asking price                       \$8,500,000
Capitalization rate                    10.86% (1st year NOI / Price)

Average compound increase in NOI                                  2.63%
er 11
Lease Data, Solve for NOI

ginning of year 4.

E INFORMATION

Remaining       Expense stop                      CPI adjustment
term (yrs)        per s.f.

3              \$4.00                          50.00%
3               4.00                          50.00%
3               4.00                          50.00%
4               4.25                          50.00%
5               4.45                          50.00%
5               4.45                          50.00%

e stop excludes management expenses

ormation

Level for 2 years then 10% increase, then level
increase                 4.00% per year
increase                 5.00% per year
increase                 3.00% per year
increase                 3.00% per year

(before management expenses)

5.00% of EGI
BASE INCOME
3         4              5         6

980,000    1,181,107    1,181,107   1,181,107
0            0            0           0
0            0            0           0
145,000      145,000      175,479     175,479
0            0            0           0
240,000      240,000      240,000     291,997

1,365,000   1,566,107    1,596,586   1,648,583

59,984             0     23,622         47,717
8,875        11,953          0          3,510
9,696        14,690     19,784              0

78,555        26,643     43,406         51,226

1,443,555   1,592,750    1,639,992   1,699,809

mursements
\$58,570            \$0     \$8,979        \$18,334
0             0          0              0
0             0          0              0
5,867         7,099          0          1,336
0             0          0              0
6,187         8,158     10,210              0
70,625        15,256     19,189         19,670

1,514,180   1,608,006    1,659,181   1,719,479
0      80,400       82,959      85,974
1,514,180   1,527,606    1,576,222   1,633,505

RATING EXPENSES

e expenses
163,680     163,680      163,680        163,680
15,575      16,198       16,846         17,520
132,300     138,915      145,861        153,154
81,477      83,921       86,439         89,032
71,292      73,431       75,634         77,903
464,325     476,146      488,460        501,289
4.84        4.96         5.09           5.22

able expenses
75,709        76,380     78,811         81,675
penses
540,034     552,526      567,271        582,964
PERATING INCOME
3             4              5         6
\$1,365,000      \$1,566,107   \$1,596,586   \$1,648,583
78,555          26,643       43,406       51,226
\$70,625         \$15,256      \$19,189      \$19,670
\$1,514,180      \$1,608,006   \$1,659,181   \$1,719,479
0          80,400       82,959       85,974
1,514,180       1,527,606    1,576,222    1,633,505

464,325        476,146       488,460      501,289
75,709         76,380        78,811       81,675
\$974,146       \$975,080    \$1,008,951   \$1,050,541

(years 1 to 6)
Chapter 11
Basic Investment Analysis

Spreadsheet Assumptions: 5 year holding period.

Data Input Box:
Purchase price           \$8,500,000
Resale Price             \$9,700,000

BEFORE TAX CASH FLOW FROM OPERATIONS:
Year                               0            1          2             3
Purchase price                           (\$8,500,000)
Net Operating Income
(NOI)
(from Ch11_Lease)                                            922,750   948,568   974,146
Sales Price
Cash flow                                (\$8,500,000)        922,750   948,568   974,146

IRR                                          13.46%

Discount rate                 14.00%

Present Value of Cash flows            \$8,336,061.59
NS:
4            5

975,080    1,008,951
9,700,000
975,080   10,708,951
Chapter 11
After Tax Investment Analysis

Spreadsheet Assumptions: 5 year holding period. Assumes passive losses used in year they are incurred. No PA
Also assumes land costs to be 15% of purchase price.

Data Input Box:
Purchase price                         8500000               Tax Considerations:
Building Value                         7225000               Depreciation (in years)
Ordinary income tax rate                   0.36              Capital gains tax rate
Loan-to-Value                               0.7              Depreciation recapture
Loan Interest                               0.1
Loan term                                    20 years         Building Square Footage:
Payments per year                            12               Gross
Resale Price                           9700000                Net
Holding Period                                5 years
Selling costs                                 0 of sale price
Equity discount rate                       0.13
Reinvestment rate                          0.06

Equity                               2,550,000
Loan                                 5,950,000
Annual Loan Payment                    689,025
Mortgage Balance                     5,343,245           year                          5

SUMMARY LOAN INFORMATION:
End of Year                   1         2              3
Payment                                689,025   689,025                         689,025
Mortgage Balance                     5,851,543 5,742,776                       5,622,620
Interest                               590,569   580,259                         568,869
Principal                               98,457   108,767                         120,156

TAXABLE INCOME & AFTER-TAX CASH FLOW FROM OPERATIONS:
Taxable Income (Loss):
Net Operating Income (NOI)
(from Ch11_Lease)                  922,750    948,568              974,146
Less: Interest                     590,569    580,259              568,869
Depreciation                  185,256    185,256              185,256
Taxable Income (Loss)              146,925    183,053              220,020
Tax (Savings) at 36%                52,893     65,899               79,207

Cash flow from operations:
Net Operating Income (NOI)           \$922,750      \$948,568                    \$974,146
Less Debt Service (DS)                689,025       689,025                     689,025
Before Tax Cash Flow                 \$233,725      \$259,542                    \$285,121
Less Tax                                 52,893         65,899                     79,207
After-tax Cash Flow                    \$180,832       \$193,643                   \$205,913

AFTER-TAX CASH CLOW FROM SALE IN YEAR 5:
Sales Price                             9,700,000
Sales costs                                     0
Mortgage Balance                        5,343,245
Before-tax cash flow                    4,356,755

Original Cost Basis                   8,500,000
Accumulated Depreciation                926,282
Adjusted Basis                        7,573,718

Capital Gain                          2,126,282
Depreciation recapture                  926,282
Price appreciation                    1,200,000

Tax on price appreciation               180,000
Tax on depreciation recapture           231,571
Total capital gain tax                                 411,571
After-Tax Cash Flow from Sale                        3,945,184

CASH FLOW SUMMARY
BTIRR on Equity
Year                       0              1                   2
Before-Tax Cash Flow                (\$2,550,000)      \$233,725                   \$259,542
After-Tax Cash Flow                 (\$2,550,000)      \$180,832                   \$193,643

Before-Tax IRR (BTIRR)                      20.08%
After-Tax IRR (ATIRR)                       15.75%

Effective Tax Rate                          21.57%

AFTER-TAX NET PRESENT VALUE                        Set purchase price (C10)
PV of ATCFo                                            \$696,943 equal to Cell D87 to make
PV of ATCFs                                           2,141,288 "circular" and solve for
Total Present Value                                   2,838,231 value
Less Original Equity Investment                      (2,550,000)
After-Tax Net Present Value                             288,231

Modified AT IRR (using reinvestment rate)               14.73% (also called Adjusted IRR)

BEFORE-TAX INVESTMENT ANALYSIS MEASURES
Price/Gross Square Foot                    \$85.00
Price/Net Square Foot                      \$88.54
Capitalization Rate                        10.86%
First Year Debt Coverage Ratio (DCR)          1.34
Modified IRR (6% reinvestment rate)        18.28%
NPV (18% discount rate)                    771,455
Profitablility Index (18% discount rate)      1.30
BT Equivalent Yield                         24.61%
Analysis

d in year they are incurred. No PAL limitations considered.

39
0.15
0.25

100000
96000

4            5
689,025      689,025
5,489,883    5,343,245
556,288      542,388
132,738      146,637

OPERATIONS:

975,080    1,008,951
556,288      542,388
185,256      185,256
233,536      281,306
84,073      101,270

\$975,080 \$1,008,951
689,025    689,025
\$286,054   \$319,925
84,073    101,270
\$201,981   \$218,655

3          4           5
\$285,121   \$286,054   \$4,676,680
\$205,913   \$201,981   \$4,163,839
Chapter 12 Participation Loan
Loan Analysis

Spreadsheet limitations: 5 year holding period. Assumes passive losses used in year they are incurred.
No passive activity loss limitations (PALL) considered. Set all data cells that do not apply to zero.

Data Input Box:
Purchase                                     1000000                     Tax Considerations:
NOI year 1                                    100000                     Building Value
Growth-NOI                                       0.03                    Depreciation
Loan-to-Value                                     0.7                    Tax rate
Loan Interest (Payment)                          0.08
Loan Interest (Accural)                          0.08
Loan Amortization Term                             15 years
Loan Due                                           15 years
Payments per year                                  12
Equity Participation                              0.5 of NOI
Partic. on Excess over                        100000
Equity Participation                             0.45 of sales gain
Land Lease Payment                                  0
Appreciation rate                                0.03
Lender's Equity Ownership                           0
Holding Period                                      5 years
Selling costs                                       0 of sale price

Equity                                     \$300,000
Loan                                        700,000
Annual Loan Payment                          80,275
Annual Accrual Amount                        80,275
Mortgage Balance                            551,364               year                        5

SUMMARY LOAN INFORMATION:
End of Year                                  1         2                           3
Payment                                     80,275     80,275                           80,275
Mortgage Balance                           674,815    647,540                          618,001
Interest                                    55,090     53,000                           50,736
Principal                                   25,185     27,275                           29,539

SUMMARY CASH FLOW INFORMATION:
Year                      1          2             3
NOI                                      100,000     103,000          106,090
Less Debt Service                         80,275      80,275           80,275
Less Land Lease Payment                        0            0               0
Before-tax Cash Flow                      19,725      22,725           25,815
Equity Participation                           0        1,500           3,045
Before-Tax Cash Flow                      19,725      21,225           22,770
NOI                                100,000        103,000             106,090
Less: Interest                      55,090         53,000              50,736
Depreciation                  32,727         32,727              32,727
Participation                      0          1,500               3,045
Less Land Lease Payment                  0              0                   0
Taxable Income                      12,183         15,773              19,582
Tax (Savings)                        3,411          4,417               5,483
After-Tax Cash Flow                 16,314         16,809              17,287

CASH FLOW FROM SALE:
Sale Price                                                        1,159,274
Sales costs                                                               0
Mortgage Balance                                                    551,364
Before-tax Cash Flow                                                607,910
Participation in Gain                                                71,673
Before-tax cash flow                                                536,237

Sale Price                                       1,159,274
Sales Costs                                              0
Participation                                       71,673

Original Cost Basis              1,000,000
Accumulated Depreciation           163,636
Adjusted Basis                                    836,364

Capital Gain                                      251,237
Tax from Sale                                                          70,346

After-Tax Cash Flow from Sale                                         465,891

EQUITY ANALYSIS:
Year                 0              1             2
BTCF after Participation          (300,000)         19,725             21,225
BTIRR on Equity                     18.36%

Year                  0             1              2
ATCF                              (300,000)        16,314              16,809
ATIRR on Equity                     14.07%
Effective Tax Rate                  23.37%

PROPERTY ANALYSIS:
Year                  0          1                2
NOI                              (1,000,000)    100,000               103,000
BTIRR on Property                    13.00%

Year                     0            1              2
ATCF-no loan                     (1,000,000)       81,164              83,324
ATIRR on Property                     9.98%
Lender's Yield:
Year                     0           1              2
Debt service                                           80,275            80,275
Loan balance
Participation                                               0             1,500
Loan amount                              -700000
Cash flows to lender                     -700000       80,275            81,775
Lender's IRR                              10.17%

Calculation of Lender's IRR:

Month                 Cash flow
0     -700000
1     6689.56
2     6689.56
3     6689.56
4     6689.56
5     6689.56
6     6689.56
7     6689.56
8     6689.56
9     6689.56
10     6689.56
11     6689.56
12     6689.56
13     6814.56
14     6814.56
15     6814.56
16     6814.56
17     6814.56
18     6814.56
19     6814.56
20     6814.56
21     6814.56
22     6814.56
23     6814.56
24     6814.56
25     6943.31
26     6943.31
27     6943.31
28     6943.31
29     6943.31
30     6943.31
31     6943.31
32     6943.31
33     6943.31
34     6943.31
35     6943.31
36   6943.31
37   7075.93
38   7075.93
39   7075.93
40   7075.93
41   7075.93
42   7075.93
43   7075.93
44   7075.93
45   7075.93
46   7075.93
47   7075.93
48   7075.93
49   7212.52
50   7212.52
51   7212.52
52   7212.52
53   7212.52
54   7212.52
55   7212.52
56   7212.52
57   7212.52
58   7212.52
59   7212.52
60 630249.67

IRR         10.17%
Loan

ar they are incurred.
ply to zero.

900000
27.5 years
0.28

4          5
80,275     80,275
586,010    551,364
48,284     45,629
31,991     34,646

4          5
109,273    112,551
80,275     80,275
0          0
28,998     32,276
4,636      6,275
24,362     26,001
109,273   112,551
48,284    45,629
32,727    32,727
4,636     6,275
0         0
23,625    27,919
6,615     7,817
17,747    18,183

3         4          5
22,770    24,362    562,238

3         4          5
17,287    17,747    484,074

3         4           5
106,090   109,273   1,271,825

3         4           5
85,548    87,840   1,179,128
3        4        5
80,275   80,275    80,275
551,364
3,045    4,636    77,949

83,320   84,911   709,587
Chapter 12
Sale Leaseback of Land

Spreadsheet limitations: 5 year holding period. Assumes passive losses used in year they are incurred.
No passive activity loss limitations (PALL) considered. Set all data cells that do not apply to zero.
Sale-Leaseback of Land : loan is calculated off of building value.

Data Input Box:
Asking Price                                        1000000                     Tax Considerations:
NOI year 1                                           100000                     Building Value
Growth-NOI                                              0.03                    Depreciation
Loan-to-Value                                            0.7                    Tax rate
Loan Interest (Payment)                                  0.1
Loan Interest (Accural)                                  0.1
Loan Amortization Term                                    15 years
Loan Due                                                  15 years
Payments per year                                         12
Equity Participation                                       0 of NOI
Partic. on Excess over                                     0
Equity Participation                                       0 of sales gain
Land Lease Payment                                     7800
Appreciation rate                                       0.03
Lender's Equity Ownership                                  0
Holding Period                                             5 years
Selling costs                                              0 of sale price

Equity                                             \$270,000
Loan                                                630,000
Annual Loan Payment                                  81,240
Annual Accrual Amount                                81,240
Mortgage Balance                                    512,295              year                            5

SUMMARY LOAN INFORMATION:
End of Year                       1           2                           3
Payment                                           81,240      81,240                           81,240
Mortgage Balance                                 610,900     589,800                          566,491
Interest                                          62,140      60,140                           57,931
Principal                                         19,100      21,100                           23,309

SUMMARY CASH FLOW INFORMATION:
Year                        1          2             3
NOI                                            100,000    103,000          106,090
Less Debt Service                               81,240     81,240           81,240
Less Land Lease Payment                          7,800       7,800           7,800
Before-tax Cash Flow                            10,960     13,960           17,050
Equity Participation                                 0           0               0
Before-Tax Cash Flow                            10,960     13,960           17,050

NOI                                                 100,000         103,000                   106,090
Less: Interest                             62,140          60,140             57,931
Depreciation                         32,727          32,727             32,727
Participation                             0               0                  0
Less Land Lease Payment                     7,800           7,800              7,800
Taxable Income                             (2,668)          2,332              7,632
Tax (Savings)                                (747)            653              2,137
After-Tax Cash Flow                        11,707          13,307             14,913

CASH FLOW FROM SALE:
Sale Price                                                               1,043,347
Sales costs                                                                      0
Mortgage Balance                                                           512,295
Before-tax Cash Flow                                                       531,052
Participation in Gain                                                            0
Before-tax cash flow                                                       531,052

Sale Price                                               1,043,347
Sales Costs                                                      0
Participation                                                    0

Original Cost Basis                       900,000
Accumulated Depreciation                  163,636
Adjusted Basis                                            736,364

Capital Gain                                              306,983
Tax from Sale                                                                 85,955

After-Tax Cash Flow from Sale                                                445,097

EQUITY ANALYSIS:
Year                    0               1             2
BTCF after Participation                 (270,000)         10,960             13,960
BTIRR on Equity                            19.16%

Year                  0               1             2
ATCF                                     (270,000)         11,707             13,307
ATIRR on Equity                            14.98%
Effective Tax Rate                         21.80%

PROPERTY ANALYSIS:
Year                   0          1                 2
NOI                                     (1,000,000)   100,000                103,000
BTIRR on Property                           11.24%

Year                       0              1             2
ATCF-no loan                            (1,000,000)        81,164             83,324
ATIRR on Property                            7.80%
Effective Cost of Sale-Leaseback of Land
Year                        0             1                 2

Land cash flows                                -100,000      7,800                   7,800

Effective Cost                                  10.25%

Calculation of Land purchaser's IRR:

Month                   Cash flow
0     -100000
1       650.00
2       650.00
3       650.00
4       650.00
5       650.00
6       650.00
7       650.00
8       650.00
9       650.00
10       650.00
11       650.00
12       650.00
13       650.00
14       650.00
15       650.00
16       650.00
17       650.00
18       650.00
19       650.00
20       650.00
21       650.00
22       650.00
23       650.00
24       650.00
25       650.00
26       650.00
27       650.00
28       650.00
29       650.00
30       650.00
31       650.00
32       650.00
33       650.00
34       650.00
35       650.00
36       650.00
37       650.00
38       650.00
39       650.00
40       650.00
41      650.00
42      650.00
43      650.00
44      650.00
45      650.00
46      650.00
47      650.00
48      650.00
49      650.00
50      650.00
51      650.00
52      650.00
53      650.00
54      650.00
55      650.00
56      650.00
57      650.00
58      650.00
59      650.00
60   116577.41

IRR           10.25%
are incurred.
ro.

900000
27.5 years
0.28

4          5
81,240     81,240
540,741    512,295
55,490     52,794
25,750     28,446

4          5
109,273    112,551
81,240     81,240
7,800      7,800
20,233     23,511
0          0
20,233     23,511

109,273    112,551
55,490    52,794
32,727    32,727
0         0
7,800     7,800
13,255    19,230
3,711     5,384
16,521    18,126

3         4         5
17,050    20,233    554,563

3         4         5
14,913    16,521    463,223

3         4          5
106,090   109,273   1,155,898

3         4          5
85,548    87,840   1,047,592
Land
3       4       5

7,800   7,800   123,727
Chapter 12
Convertible Loan

Spreadsheet limitations: 5 year holding period. Assumes passive losses used in year they are incurred.
No passive activity loss limitations (PALL) considered. Set all data cells that do not apply to zero.

Data Input Box:
Asking Price                                           1000000                     Tax Considerations:
NOI year 1                                              100000                     Building Value
Growth-NOI                                                 0.03                    Depreciation
Loan-to-Value                                                0.7                   Tax rate
Loan Interest (Payment)                                   0.085
Loan Interest (Accural)                                   0.085
Loan Amortization Term                                        30 years
Loan Due                                                      15 years
Payments per year                                             12
Equity Participation                                           0 of NOI
Partic. on Excess over                                         0
Equity Participation                                           0 of sales gain
Land Lease Payment                                             0
Appreciation rate                                          0.03
Lender's Equity Ownership                                  0.65
Holding Period                                                 5 years
Selling costs                                                  0 of sale price

Equity                                               \$300,000
Loan                                                  700,000
Annual Loan Payment                                    64,589
Annual Accrual Amount                                  64,589
Mortgage Balance                                      668,432               year                         5

SUMMARY LOAN INFORMATION:
End of Year                        1         2                             3
Payment                                             64,589     64,589                             64,589
Mortgage Balance                                   694,708    688,949                            682,680
Interest                                            59,297     58,829                             58,320
Principal                                            5,292       5,759                             6,269

SUMMARY CASH FLOW INFORMATION:
Year                         1          2                              3
NOI                                              100,000     103,000                             106,090
Less Debt Service                                 64,589      64,589                              64,589
Less Land Lease Payment                                0           0                                   0
Before-tax Cash Flow                              35,411      38,411                              41,501
Equity Participation                                   0           0                                   0
Before-Tax Cash Flow                              35,411      38,411                              41,501

NOI                                                    100,000         103,000                   106,090
Less: Interest                                          59,297          58,829                    58,320
Depreciation                             32,727          32,727         32,727
Participation                                 0               0              0
Less Land Lease Payment                             0               0              0
Taxable Income                                  7,976          11,443         15,043
Tax (Savings)                                   2,233           3,204          4,212
After-Tax Cash Flow                            33,178          35,207         37,289

CASH FLOW FROM SALE:
Sale Price (net of portion transferred to lender)                            405,746
Sales costs                                                                        0
Mortgage Balance                                                                   0
Before-tax Cash Flow                                                         405,746
Participation in Gain                                                              0
Before-tax cash flow                                                         405,746

Sale Price                                                   1,159,274
Sales Costs                                                          0
Participation                                                        0

Original Cost Basis                          1,000,000
Accumulated Depreciation                       163,636
Adjusted Basis                                                836,364

Capital Gain                                                  322,910
Tax from Sale                                                                 90,415

After-Tax Cash Flow from Sale                                                315,331

EQUITY ANALYSIS:
Year                        0          1            2
BTCF after Participation                      (300,000)      35,411           38,411
BTIRR on Equity                                 18.40%

Year                        0             1         2
ATCF                                          (300,000)        33,178         35,207
ATIRR on Equity                                 13.06%
Effective Tax Rate                              29.02%

PROPERTY ANALYSIS:
Year                        0         1             2
NOI                                         (1,000,000)   100,000            103,000
BTIRR on Property                               13.00%

Year                        0             1         2
ATCF-no loan                                (1,000,000)        81,164         83,324
ATIRR on Property                                9.66%
Lender's Yield:
Year                       0            1         2
Debt service                                                    64,589       64,589
Loan balance
Conversion
Loan amount                                      -700000
Cash flows to lender                             -700000       64,589        64,589
Lender's IRR                                      10.40%

Calculation of Lender's IRR:

Month                       Cash flow
0     -700000
1     5382.39
2     5382.39
3     5382.39
4     5382.39
5     5382.39
6     5382.39
7     5382.39
8     5382.39
9     5382.39
10     5382.39
11     5382.39
12     5382.39
13     5382.39
14     5382.39
15     5382.39
16     5382.39
17     5382.39
18     5382.39
19     5382.39
20     5382.39
21     5382.39
22     5382.39
23     5382.39
24     5382.39
25     5382.39
26     5382.39
27     5382.39
28     5382.39
29     5382.39
30     5382.39
31     5382.39
32     5382.39
33     5382.39
34     5382.39
35     5382.39
36     5382.39
37     5382.39
38   5382.39
39   5382.39
40   5382.39
41   5382.39
42   5382.39
43   5382.39
44   5382.39
45   5382.39
46   5382.39
47   5382.39
48   5382.39
49   5382.39
50   5382.39
51   5382.39
52   5382.39
53   5382.39
54   5382.39
55   5382.39
56   5382.39
57   5382.39
58   5382.39
59   5382.39
60 758910.54

IRR         10.40%
incurred.

900000
27.5 years
0.28

4         5
64,589    64,589
675,858   668,432
57,766    57,163
6,823     7,426

4         5
109,273   112,551
64,589    64,589
0         0
44,684    47,962
0         0
44,684    47,962

109,273   112,551
57,766    57,163
32,727    32,727
0         0
0         0
18,779    22,661
5,258     6,345
39,426    41,617

3         4         5
41,501    44,684   453,708

3         4         5
37,289    39,426   356,948

3         4         5
106,090   109,273 1,271,825

3         4         5
85,548    87,840 1,159,059
3        4         5
64,589   64,589    64,589
0
753,528

64,589   64,589   818,117
Chapter 12
Accrual Loan

Spreadsheet limitations: 5 year holding period. Assumes passive losses used in year they are incurred.
No passive activity loss limitations (PALL) considered. Set all data cells that do not apply to zero.
Capital Gain Tax Rate assumed same as Ordinary Income Tax Rate
Negative Amortization : requires a number greater than zero for "Loan Interest (Accural)".

Data Input Box:
Asking Price                         1000000                Tax Considerations:
NOI year 1                            100000                Building Value                  900000
Growth-NOI                               0.03               Depreciation                       27.5
Loan-to-Value                             0.7               Tax rate                           0.28
Loan Interest (Payment)                  0.08
Loan Interest (Accural)                   0.1
Loan Amortization Term                     30 years
Loan Due                                   15 years
Payments per year                          12
Appreciation rate                        0.03
Holding Period                              5 years
Selling costs                               0 of sale price

Equity                              \$300,000
Loan                                 700,000
Annual Loan Payment                   61,636
Annual Accrual Amount                 73,716
Mortgage Balance                     753,972               year                        5

SUMMARY LOAN INFORMATION:
End of Year                  1         2              3                             4
Payment                               61,636    61,636            61,636                    61,636
Mortgage Balance                     708,758   718,433           729,121                   740,928
Interest                              70,394    71,311            72,324                    73,444
Principal                              (8,758)   (9,675)         (10,688)                  (11,807)

SUMMARY CASH FLOW INFORMATION:
Year                     1          2            3                                4
NOI                                100,000    103,000         106,090                      109,273
Less Debt Service                   61,636     61,636           61,636                      61,636

Before-Tax Cash Flow                  38,364           41,364                   44,454      47,636

NOI                                  100,000          103,000                 106,090      109,273
Less: Interest                        70,394           71,311                  72,324       73,444
Depreciation                     32,727           32,727                  32,727       32,727
Taxable Income                        (3,121)          (1,039)                  1,038        3,102
Tax (Savings)                       (874)         (291)                   291       869
After-Tax Cash Flow               39,238        41,655                 44,163    46,768

CASH FLOW FROM SALE:
Sale Price (received by investor)                                 1,159,274
Sales costs                                                               0
Mortgage Balance                                                    753,972
Before-tax Cash Flow                                                405,302

Before-tax cash flow                                                  405,302

Sale Price                                   1,159,274
Sales Costs                                          0

Original Cost Basis             1,000,000
Accumulated Depreciation          163,636
Adjusted Basis                                 836,364

Capital Gain                                   322,910
Tax from Sale                                                          90,415

After-Tax Cash Flow from Sale                                         314,887

EQUITY ANALYSIS:
Year                    0           1               2                  3
BTCF after Participation         (300,000)     38,364                  41,364    44,454
BTIRR on Equity                    19.27%

Year                    0           1                  2               3
ATCF                             (300,000)     39,238                  41,655    44,163
ATIRR on Equity                    15.25%
Effective Tax Rate                 20.88%

PROPERTY ANALYSIS:
Year                  0               1            2                    3
NOI                         (1,000,000)        100,000          103,000         106,090
BTIRR on Property               13.00%

Year                  0              1                  2               3
ATCF-no loan                (1,000,000)        81,164                  83,324    85,548
ATIRR on Property                 9.66%

Lender's Yield:
Year                   0            1                  2               3
Debt service                                    61,636                 61,636    61,636
Loan balance
Loan amount                       -700000
Cash flows to lender           -\$700,000   \$61,636   \$61,636   \$61,636
Lender's IRR                     10.00%

Calculation of Lender's IRR:

Month             Cash flow
0      -700000
1      5136.35
2      5136.35
3      5136.35
4      5136.35
5      5136.35
6      5136.35
7      5136.35
8      5136.35
9      5136.35
10      5136.35
11      5136.35
12      5136.35
13      5136.35
14      5136.35
15      5136.35
16      5136.35
17      5136.35
18      5136.35
19      5136.35
20      5136.35
21      5136.35
22      5136.35
23      5136.35
24      5136.35
25      5136.35
26      5136.35
27      5136.35
28      5136.35
29      5136.35
30      5136.35
31      5136.35
32      5136.35
33      5136.35
34      5136.35
35      5136.35
36      5136.35
37      5136.35
38      5136.35
39      5136.35
40      5136.35
41      5136.35
42      5136.35
43      5136.35
44      5136.35
45     5136.35
46     5136.35
47     5136.35
48     5136.35
49     5136.35
50     5136.35
51     5136.35
52     5136.35
53     5136.35
54     5136.35
55     5136.35
56     5136.35
57     5136.35
58     5136.35
59     5136.35
60   759108.54

IRR           10.00%
ey are incurred.
o zero.

years

5
61,636
753,972
74,680
(13,044)

5
112,551
61,636

50,915

112,551
74,680
32,727
5,144
1,440
49,474

4         5
47,636    456,217

4         5
46,768    364,361

4          5
109,273   1,271,825

4          5
87,840   1,159,059

4         5
61,636      61,636
753,972
\$61,636   \$815,608
Chapter 13
Partioning the IRR and Calculating Expected Returns and Variance

Spreadsheet Assumptions: Use Exhibit 10-11 which is only in text.

Partitioning the IRR

Present Value of Before-tax Cash Flow from Operations (BFTCFo)
Year               Cash Flow                  Present Value
1                 214,025                     \$178,890
2                 239,960                      167,643
3                 266,413                      155,570
4                 284,764                      138,989
5                 321,796                      131,280
Total                                           \$772,373

Present Value on Before-tax Cash Flow from Sale (BTCFs)
Year                Cash Flow                    Present Value
5                   4,356,755                     1,777,383

PV of BTCFo                      \$772,373
PV of BTCFs                      1,777,383
Total Present Value             \$2,549,756

Ratio of Present Values
BTCFo to Total Present Value                                                   30%
BTCFs to Total Present Value                                                   70%

Calculating Variance of Returns
*Numbers from Exhibit 10-11
Office Building
Scenario                 Return (or R)     Probability (P)   (Return x Probability)
Pessimistic                               6.17%               25%           1.54%
Most Likely                              19.64%               50%           9.82%
Optimistic                               28.64%               25%           7.16%

Expected Return                         18.52%
Variance                          0.64%
Standard Deviation                         8.02%

Apartment Building
Scenario                  Return (or R)     Probability (P)  (Return x Probability)
Pessimistic                                 10%               25%          2.50%
Most Likely                   15%                50%          7.50%
Optimistic                    20%                25%          5.00%

Expected Return                       15.00%
Variance                        0.13%
Standard Deviation                       3.61%

Hotel
Scenario    Return (or R)     Probability (P)  (Return x Probability)
Pessimistic                  5.00%               25%         1.25%
Most Likely                    20%               50%        10.00%
Optimistic                     35%               25%         8.75%

Expected Return                       20.00%
Variance                        1.15%
Standard Deviation                      10.71%

SUMMARY
Property   Expected Return         Risk
Office        18.52%             8.02%
Apartment       15.00%             3.61%
Hotel         20.00%            10.71%
ted Returns and Variance

eturns

R - Expected R   P x (R-Expected R)2
-12.35%            0.3815%
1.12%            0.0062%
10.12%            0.2559%

18.52%
0.64%
8.02%

R - Expected R   P x (R-Expected R)2
-5.00%             0.0625%
-3.52%             0.0620%
1.48%             0.0055%

15.00%
0.13%
3.61%

R - Expected R   P x (R-Expected R)2
-13.52%            0.4571%
1.48%            0.0109%
16.48%            0.6788%

20.00%
1.15%
10.71%
Chapter 13
Risk and Leverage

Pessimistic
Year              0                1               2                 3
Purchase               -100,000
NOI                                         9,000            8,730            8,468
Resale
Total Cash Flow        -100,000             9,000            8,730            8,468

IRR                      4.33%

Most Likely
Year              0                1               2                 3
Purchase               -100,000
NOI                                         9,000            9,000            9,000
Resale
Total Cash Flow        -100,000             9,000            9,000            9,000

IRR                      7.27%

Optimistic
Year              0                1               2                 3
Purchase               -100,000
NOI                                         9,000            9,270            9,548
Resale
Total Cash Flow        -100,000             9,000            9,270            9,548

IRR                     10.21%

No Loan
Scenario        Return (or R)   Probability (P)    (Return x Probability)
Pessimistic               4.33%                30%          1.30%
Most Likely               7.27%                50%          3.64%
Optimistic               10.21%                20%          2.04%

Expected Return      6.98%
Variance      0.04%
Standard Deviation     2.06%

Loan to value           70.00%
Interest rate            6.00%
Loan term                    25 years

Pessimistic
Year              0                 1               2                 3
Purchase               -100,000
Loan                     70,000
NOI                                           9,000           8,730         8,468
Payment                                      -5,476          -5,476        -5,476
Resale
Loan balance
Total Cash Flow          -30,000             3,524            3,254            2,992

IRR                      -0.45%

Most Likely
Year              0                 1               2                 3
Purchase               -100,000
Loan                     70,000
NOI                                           9,000           9,000         9,000
Payment                                      -5,476          -5,476        -5,476
Resale
Loan balance
Total Cash Flow          -30,000             3,524            3,524            3,524

IRR                      10.22%

Optimistic
Year              0                 1               2                 3
Purchase               -100,000
Loan                     70,000
NOI                                           9,000           9,270         9,548
Payment                                      -5,476          -5,476        -5,476
Resale
Loan balance
Total Cash Flow          -30,000             3,524            3,794            4,072

IRR                      18.59%

Loan
Scenario        Return (or R)    Probability (P)    (Return x Probability)
Pessimistic               -0.45%                30%          -0.13%
Most Likely              10.22%                 50%           5.11%
Optimistic               18.59%                 20%           3.72%

Expected Return      8.69%
Variance      0.49%
Standard Deviation     6.99%
apter 13
nd Leverage

stic
4                    5              6

8,214                   7,968    7,729
77,286
8,214                  85,254

kely
4                    5              6

9,000                   9,000    9,000
90,000
9,000                  99,000

tic
4                    5              6

9,835                 10,130    10,433
104,335
9,835                114,464

R - Expected R    P x (R-Expected R) 2
-2.64%              0.02%
0.29%              0.00%
3.23%              0.02%

stic
4                     5              6

8,214                   7,968    7,729
-5,476                  -5,476
77,286
-62,808
2,738                   16,970

kely
4                     5              6

9,000                   9,000    9,000
-5,476                  -5,476
90,000
-62,808
3,524                   30,716

tic
4                     5              6

9,835                 10,130    10,433
-5,476                 -5,476
104,335
-62,808
4,359                  46,181

R - Expected R     P x (R-Expected R) 2
-7.43%               0.17%
3.24%               0.05%
11.62%                0.27%
Chapter 13
Retail Example

Input Assumptions
Building Name             Westgate Shopping Center

Purchase price            \$             8,500,000.00
State                     USA
Building Size (SF)        \$               100,000.00

Analysis Begin Date                            7/1/2000
Holding Period                                        5
Discount Rate                                   11.00%
Terminal Rate                                   10.50%
Selling Cost                                     3.00%

Tenant 1                              Tenant 2
Name                              Drug Store                           FoodStore4
Tenant Size                                      25,000                    60,000
Rent SF                                         \$12.00                      \$8.50
Lease Term (Yrs)                                     5                         14
Market Rent                                     \$12.00                      \$9.00
Market Rent Increase                             3.00%                      3.00%
Sales Volume/SF
Sales Annual Change
Breakpoint/SF
Overage %

MLA Lease Term                                      10                          5
MLA Tenant Improvement                            \$0.00                      \$0.00
MLA Lease Commissions                            0.00%                      0.00%
MLA TI Annual Change                             0.00%                      0.00%
Recoveries
Lease Only
Property Tax              Net                                  \$0.00 Net
Insurance                 Net                                  \$0.00 Net
Maintenance               Net                                  \$0.00 Net

Market Vacancy Rate                              5.00%

% of EGI                \$ Amount      \$ per SF
Management Fee                                   6.00%            \$0         \$0.00
Property Tax                                     0.00%       \$40,000         \$0.00
Insurance                                        0.00%            \$0         \$0.18
Maintenance                                      0.00%            \$0         \$0.75

Restaurant Sales Volume
\$250.00      \$257.50         \$0.00
Outputs
Year                            1                    2            3
Rental Income:
Drug Store Rent                                   \$300,000   \$300,000       \$300,000
Drug Store Renewal Rent                                  \$0         \$0             \$0
Drug Store Recoveries                               \$33,250    \$34,198        \$35,172
Food Store Rent                                   \$510,000   \$510,000       \$510,000
Food Store Renewal Rent                                  \$0         \$0             \$0
Food Store Recoveries                               \$79,800    \$82,074        \$84,413
Restaurant Rent                                    \$75,000    \$75,000             \$0
Restaurant Renewal Rent                                  \$0         \$0        \$84,872
Restaurant Recoveries                                \$6,650     \$6,840             \$0
Restaurant Overage Rent                              \$6,250     \$8,125             \$0
Vacant Space 1 Rent                                \$80,000    \$80,000        \$80,000
Vacant Space 1 Renewal Rent                              \$0         \$0             \$0
Vacant Space 1 Recoveries                            \$6,650     \$6,840         \$7,034
Vacant Space 2 Rent                                \$80,000    \$80,000        \$80,000
Vacant Space 2 Renewal Rent                              \$0         \$0             \$0
Vacant Space 2 Recoveries                                \$0     \$6,840         \$7,034
Total Income                                    \$1,177,600 \$1,189,915     \$1,188,526

Vacant Space Vacancy                               \$80,000         \$0             \$0
General Vacancy                                    \$58,880    \$59,496        \$59,426
Effective Gross Income                          \$1,038,720 \$1,130,419     \$1,129,100

Management Fee                                    \$62,323      \$67,825      \$67,746
Property Tax                                      \$40,000      \$41,000      \$42,025
Insurance                                         \$18,000      \$18,540      \$19,096
CAM                                               \$75,000      \$77,250      \$79,568
Total Expenses                                   \$195,323     \$204,615     \$208,435
NOI                                              \$843,397     \$925,804     \$920,665

Vacant Space TIs                                   \$50,000      \$50,000           \$0
In-Line Space TIs                                       \$0           \$0      \$26,523
Total TIs                                          \$50,000      \$50,000      \$26,523

Vacant Space Leasing Commissoins                   \$12,000      \$12,000           \$0
In-Line Space Leasing Commissions                       \$0           \$0      \$10,185
Total Leasing Commissions                          \$12,000      \$12,000      \$10,185

Cash Flow from Operations                        \$781,397     \$863,804     \$883,958

Cash flow from Resale
Resale                                          \$8,984,768
Selling Cost                                      \$269,543
Net Resale                                      \$8,715,225

Total Cash Flow
Year                           0                    1         2
Cash flow                                    (\$8,500,000)    \$781,397   \$863,804
IRR                         10.48%

Partitioning the IRR:
Using IRR as a discount rate:
PV of Cash flow from Operations                 \$3,204,921     37.70%
PV of Cash Flow from Resale                      5,295,079     62.30%
Total PV                                        \$8,500,000    100.00%
hapter 13
tail Example

ut Assumptions

Tenant 3                      Vacant Space
Restaurant   # Leases                      2 Total Space
5,000   Space per Lease          5,000       10,000
\$15.00    Rent SF                  \$16.00
2   Lease Term (Yrs)              3
\$16.00    Market Rent              \$16.00
3.00%   Market Rent Increase     3.00%
\$250.00   Period 2nd Lease              1
3.00%
\$225.00
5.00%

3
\$5.00                           \$10.00
4.00%                            5.00%
3.00%                            3.00%

\$0.00 Net                       \$0.00 Net                  \$0.00
\$0.00 Net                       \$0.00 Net                  \$0.00
\$0.00 Net                       \$0.00 Net                  \$0.00

Change %
0.00%
2.50%
3.00%
3.00%

\$0.00        \$0.00              \$0.00
4           5          6

\$300,000   \$300,000            \$0
\$0         \$0      \$347,782
\$36,175    \$37,206            \$0
\$510,000   \$510,000      \$510,000
\$0         \$0            \$0
\$86,820    \$89,295       \$91,841
\$0         \$0            \$0
\$87,418    \$90,041       \$92,742
\$0         \$0            \$0
\$0         \$0            \$0
\$0         \$0            \$0
\$87,418    \$90,041       \$92,742
\$0         \$0            \$0
\$80,000         \$0            \$0
\$0    \$90,041       \$92,742
\$7,235         \$0            \$0
\$1,195,066 \$1,206,623    \$1,227,849

\$0         \$0            \$0
\$59,753    \$60,331       \$61,392
\$1,135,312 \$1,146,292    \$1,166,457

\$68,119     \$68,778      \$69,987
\$43,076     \$44,153      \$45,256
\$19,669     \$20,259      \$20,867
\$81,955     \$84,413      \$86,946
\$212,818    \$217,602     \$223,056
\$922,494    \$928,690     \$943,401

\$0         \$0
\$27,318    \$28,138
\$27,318    \$28,138

\$0         \$0
\$10,490    \$10,805
\$10,490    \$10,805

\$884,686    \$889,747
3          4         5
\$883,958   \$884,686   \$9,604,972
Chapter 13
Industrial Example

Inputs
Building Name                     Worthington Distribution Center
Address
City                              Anywhere
State                             USA
Building Size (SF)                                        140,000

Analysis Begin Date                                         6/1/2000
Holding Period                                                     5
Discount Rate                                                10.50%
Terminal Cap Rate                                             9.75%
Selling Cost                                                  3.00%

Tenant 1
Name                                      Electric Supply
Tenant Size                                                  50,000
Rent SF                                                      \$6.00
Lease Term (Yrs)                                                 3
At Expiration                     Renew

Market Leasing Assumption
Rent Annual Change                                            3.00% Tenant Improvement Annual Change

Lease Term                 Renewal Prob.
New                                                                 5          70.0%
Renewal
Weighted

Market Rent
Market Rent 1 - Electric Supply                               \$7.10          \$355,136
Market Rent 1 - Sign Co.                                      \$7.43          \$315,618
Market Rent 1 - Computer Dist                                 \$7.48          \$355,520

% of EGI                     \$ Amount
Management Fee                                               5.00%                 \$0
Property Tax                                                 0.00%            \$23,000
Insurance                                                    0.00%                 \$0
Maintenance                                                  0.00%                 \$0
Roof Repair (year 1)                                        \$45,000
Outputs
Year                          1                  2
Income:
Electric Supply Rent                              \$300,000    \$300,000
Electric Supply Market Rent                              \$0          \$0
Sign Company Rent                                 \$276,250    \$276,250
Sign Company Market Rent                                 \$0          \$0
Computer Dist. Rent                               \$273,125    \$273,125
Computer Dist. Market Rent                               \$0          \$0
Total Income                                      \$849,375    \$849,375
Turnover Vacancy                                         \$0          \$0
Effective Gross Income                            \$849,375    \$849,375

Expenses:
Management Fee                                     \$42,469     \$42,469
Property Tax                                       \$23,000     \$23,575
Insurance                                          \$21,000     \$21,840
CAM                                                \$28,000     \$28,840
Total                                             \$114,469    \$116,724

Cash Flow:
NOI                                               \$734,906    \$732,651
Tenant Improvements                                      \$0          \$0
Leasing Commissions                                      \$0          \$0
Total TI and LC                                          \$0          \$0
Capital Costs                                       \$45,000           0
Cash Flow                                         \$689,906    \$732,651

PV Factors                                         0.90498     0.81898
Present Value of Cash Flow                        \$624,350    \$600,030

Resale Calculations:
Resale                                           \$9,137,730
Selling Cost                                       \$274,132
Net Resale                                       \$8,863,598

PV Factor                                          0.60700
PVResale                                         \$5,380,203
Chapter 13
Industrial Example

Inputs

Tenant 2                        Tenant 3
Sign Company                    Computer Dist.
42,500                          47,500
\$6.50                          \$5.75
2                               4
Vacate                           Market

nant Improvement Annual Change                          3.00%

Leasing           Tenant
Months Vacant Market Rent         Commissions      Improvements
10       \$7.00                3.00%              \$5.00
0       \$6.50                1.00%              \$2.00
3       \$6.65                1.60%              \$2.90

Start Date                       Market Turnover   Vacate Turnover     TI      LC
4.00         4.00             \$88,784          \$295,947   \$109,273 \$17,757
3.00         3.83             \$78,904          \$263,015   \$225,441 \$47,343
5.00         5.25             \$88,880          \$296,267   \$155,039 \$28,442

\$ per SF        Change %
\$0.00       0.00%
\$0.00       2.50%
\$0.15       4.00%
\$0.20       3.00%
Outputs
3              4          5             6
Income:
\$300,000          \$0            \$0            \$0
\$0    \$355,136      \$355,136     \$355,136
\$0          \$0            \$0            \$0
\$315,618    \$315,618      \$315,618     \$315,618
\$273,125    \$273,125            \$0            \$0
\$0          \$0      \$355,520     \$355,520
\$888,743    \$943,879    \$1,026,274    \$1,026,274
\$263,015          \$0       \$88,880            \$0
\$625,728    \$943,879      \$937,394    \$1,026,274

Expenses:
\$31,286     \$47,194      \$46,870       \$51,314
\$24,164     \$24,768      \$25,388       \$26,022
\$22,714     \$23,622      \$24,567       \$25,550
\$29,705     \$30,596      \$31,514       \$32,460
\$107,870    \$126,181     \$128,339      \$135,345

Cash Flow:
\$517,858    \$817,698     \$809,055      \$890,929
\$225,441    \$109,273     \$155,039
\$47,343     \$17,757      \$28,442
\$272,784    \$127,030     \$183,480
0           0            0
\$245,074    \$690,669     \$625,575

0.74116     0.67073      0.60700
\$181,640    \$463,256     \$379,724

Value:
PV Resale          \$5,380,203
PV Cash flows      \$2,248,999
Value              \$7,629,201
Implied Change in
Value      19.77%
Spreadsheet Limitations: Ten year holding period.

RETURN FOR PAST 5 YEARS

Data Input Box:
Purchase Price / Property Value              200000 Tax Considerations:
Land Value                                    40000 Depreciable Life (in years)               19
Building Value                               160000 Capital Gains Rate                      0.28
Operating Expenses (% of NOI)                    0.5 Marginal Tax Rate                      0.28
NOI Growth                                      0.04
Property Growth                                 0.03
Loan-to-Value                                   0.75
Loan Amount / Current Balance                150000
Interest Rate                                   0.11
Loan Term Remaining (in years)                    25
Payments per Year                                 12
Holding Period / Yrs. Since Purch.                 5
Selling Costs                                   0.06
Resale Value Year 5                          250000

Equity                           50000
Loan                            150000
Annual Loan Payment         17642.0354
Mortgage Balance              142,432 year 5

SUMMARY LOAN INFORMATION:
End of Year             1        2           3                          4          5
Payment                        17,642  17,642           17,642                17,642     17,642
Mortgage Balance              148,799 147,458         145,963                144,294    142,432
Interest                       16,441  16,302           16,146                15,973     15,780
Principal                       1,201   1,340            1,496                 1,669      1,862

CASH FLOW FROM OPERATIONS:
Year              1         2          3                              4           5
Rents                      39,000   40,560         42,182                     43,870      45,624
Less Operating Expenses      19500    20280           21091                     21935       22812
Net Operating Income        19500    20280           21091                     21935       22812
Less Debt Service           17642    17642           17642                     17642       17642
Before-Tax Cash Flow    \$    1,858 \$ 2,638 \$          3,449 \$                   4,293 \$     5,170

Net Operating Income            19,500       20,280                21,091     21,935      22,812
Less: Interest                  16,441       16,302                16,146     15,973      15,780
Depreciation               8,421        8,421                 8,421      8,421       8,421
Taxable Income (loss)           (5,362)      (4,443)               (3,476)    (2,460)     (1,389)
Tax                             (1,501)    (1,244)          (973)   (689)    (389)

Before-Tax Cash Flow             1,858    2,638            3,449   4,293   5,170
Less Tax                        (1,501)  (1,244)            (973)   (689)   (389)
After-Tax Cash Flow        \$     3,359 \$ 3,882 \$           4,423 \$ 4,982 \$ 5,559

CASH FLOW FROM SALE:
Sale Price (received by investor)                        250,000
Sales costs                                               15,000
Mortgage Balance                                         142,432
Before-tax Cash Flow                                      92,568

Sale Price                                250,000
Sales Costs                                15,000

Original Cost Basis            200,000
Accumulated Depreciation        42,105
Adjusted Basis                            157,895

Capital Gain                               77,105
Tax from Sale                                             21,589
After-Tax Cash Flow from Sale                             70,978

End of Year              0           1        2              3       4
Before-Tax Cash Flow           (50,000)     1,858          2,638    3,449   4,293
After-Tax Cash Flow            (50,000)     3,359          3,882    4,423   4,982

Before-Tax IRR                             18.26%
After-Tax IRR                              14.83%
Chapter 14 Exhibit 5
Return if Property Held additional 5 years

RETURN FOR NEXT 5 YEARS

Data Input Box for Yrs 6-10:
Current Value                            250000 Tax Considerations:
Original Cost Basis                      200000 Marginal Tax Rate                            0.28
Building Value (original)                160000 Capital Gains Rate                           0.28
Land Value (original)                     40000 Depreciable Life (in yrs)                      19
NOI Growth                                   0.03
Operating Expenses (% of NOI)                 0.5
Property Growth                              0.03
Resale Value                             289819
Selling Costs                                0.06
Loan Amount / Current Balance            142432
Interest Rate                                0.11
Loan Term Remaining (in years)                 20
Payments per Year                              12
Total Holding Period (in yrs)                  10
NO RENOVATION:
Equity                          57568
Loan                           142432
Annual Pmt                17641.99885
Mortg Bal                     106,727    in year 10

Yr of Ren.
6                  7            8                  9                 10
17,642        17,642      17,642                   17,642      17,642
140,355       138,038     135,452                  132,567     129,348
15,565        15,325      15,056                   14,757      14,423
2,077          2,317      2,586                    2,885       3,219

6                  7             8                 9              10
47,449        48,873      50,339                   51,849   53,405
23725         24436       25170                    25925    26702
23725         24436       25170                    25925    26702
17642         17642       17642                    17642    17642
\$               6,083 \$       6,794 \$     7,528 \$                  8,283 \$ 9,060

23,725         24,436     25,170                   25,925      26,702
15,565         15,325     15,056                   14,757      14,423
8,421          8,421      8,421                    8,421       8,421
(261)           691      1,692                    2,746       3,858
(73)              193       474               769      1,080

6,083             6,794      7,528              8,283     9,060
(73)              193        474                769     1,080
\$              6,156 \$           6,601    \$ 7,054   \$          7,514   \$ 7,980

Sale Price (received by investor)                            289,819
Sales costs                                                   17,389
Mortgage Balance                                             129,348
Before-tax Cash Flow                                         143,081

Sale Price                                289,819
Sales Costs                                17,389

Org. Cost Basis             200,000
Accum Dep                    84,211
Adj Basis                                 115,789

Capital Gain                              156,640
Tax from Sale                                                 43,859
After-Tax Cash Flow from Sale                                 99,222

CASH FLOW SUMMARY:
5                 5                  6               7           8               9       10
97,738                 (70,978)           6,083      6,794             7,528      8,283   152,142
76,537                 (70,978)           6,156      6,601             7,054      7,514   107,202

Before-Tax IRR                             23.06%
After-Tax IRR                              15.60%
If Renovated:
NOI Yr 1 After Renovation           45000
NOI Growth                            0.04
Terminal Cap Rate                      0.1
Renovation Costs                   200000
Loan Amount on Renovations         200000
Total New Loan Amount              337500
Interest Rate                         0.11
Loan Term (in years)                    15
Payments per Year                       12
Dep. Life of Renovation               31.5
Holding Period                           5
Years Since Purchase                     5

IF RENOVATED:
Additional Equity          4932
Loan                     337500
Annual Pmt             46032.18
Mortg Bal              278,477    in year 5

1                2          3           4          5
6                7          8           9         10
46,032     46,032     46,032      46,032    46,032
328,130    317,675    306,011     292,997   278,477
36,662     35,578     34,368      33,018    31,512
9,370     10,455     11,664      13,014    14,520

6               7            8           9        10      11
-               -            -           -         -
-               -            -           -         -
45000     46800        48672       50619     52644   54749
46032     46032        46032       46032     46032
\$            (1,032) \$    768     \$ 2,640     \$ 4,587   \$ 6,611

45,000     46,800     48,672      50,619    52,644
36,662     35,578     34,368      33,018    31,512
14,770     14,770     14,770      14,770    14,770
(6,432)    (3,548)      (466)      2,831     6,361
(1,801)      (993)       (131)         793     1,781

(1,032)    768   2,640   4,587                 6,611
(1,801)   (993)   (131)    793                 1,781
\$                769 \$ 1,761 \$ 2,770 \$ 3,794               \$ 4,830

Sale Price (received by investor)              547,494
Sales costs                                     32,850
Mortgage Balance                               278,477
Before-tax Cash Flow                           236,168

Sale Price                          547,494
Sales Costs                          32,850

Org. Cost Basis         400,000
Accum Dep               115,957
Adj Basis                           284,043

Capital Gain                  230,601
Tax from Sale                                   64,568
After-Tax Cash Flow from Sale                  171,599

5          6            7         8         9
ATCF assuming renovation               -           769        1,761     2,770     3,794
ATCF assuming no renovation            -         6,156        6,601     7,054     7,514
Incremental Cash Flow                (4,932)    (5,387)      (4,840)   (4,283)   (3,719)

IRR on Incremental Flows                        37.47%
10
176,430
107,202
69,227
Chapter 14
Marginal rate of return (MRR)

Spreadsheet Limitations: Ten year holding period.

Data Input Box
Current property value                                     250000
Current NOI                                                 47449
Original Building Value                                     160000
Original Land Value                                          40000
Years since purchased                                            5
Current Depreciable life                                        19

Financing information
Current loan balance                                       142432
Interest rate                                               11.00%
Remaining term                                                   20 YEARS

Tax rate                                                   28.00%

Growth rates for next 5 years:
NOI                                                          3.00%
Property Value                                               3.00%

Loan constant                                    12.39%
Payment                                         \$17,642

Equity                                         \$107,568

End of year                   1         2          3         4             5
Mortgate balance                      140355    138037     135452    132567        129348

Calulation of After Tax Cash Flow from Sale if Sold Today

Sale Price
Sale Costs                                        6.00%
Mortgage balance
Before tax cash flow

Sale price                                                            250000
Sale costs                                                             15000
Original cost basis                                        200000
Accumulated depreciation                                  42105
Adjusted basis                                                      157895

Capital gain                                                         77105

Capital gains tax rate                         28.00%

After tax cash flow from reversion

Calculation of After Tax Cash Flow from Operations if NOT sold
YEAR                    1          2           3        4         5

Rent                                 \$47,449   \$48,873   \$50,339    \$51,849   \$53,405
Expenses                              23,725    24,436    25,170     25,925    26,702
Net operating income                  23,725    24,436    25,170     25,925    26,702
Debt service                          17,642    17,642    17,642     17,642    17,642
Before tax C.F.                        6,083     6,794     7,528      8,283     9,060

Net Operating Income                  23,725    24,436    25,170     25,925    26,702
Interest                              15,565    15,325    15,056     14,757    14,423
Depreciaiton                           8,421     8,421     8,421      8,421     8,421
Taxable income                          -261       691     1,692      2,746     3,858
Tax                                      -73       193       474        769     1,080

Before tax C.F.                        6,083     6,794      7,528     8,283      9,060
Tax                                      -73       193        474       769      1,080
After tax cash flow                    6,156     6,601      7,054     7,514      7,980

Calculation of After Tax Cash Flow from Sale

Sale price                           \$257,500 \$265,225 \$273,182 \$281,377 \$289,819
Morggage balance                      140,355 138,037 135,452 132,567 129,348
Selling expenses                       15,450   15,914   16,391   16,883   17,389
B.T.C.F.                              101,695 111,274 121,339 131,928 143,081

Sale price                           257,500   265,225   273,182    281,377   289,819
Selling expenses                      15,450    15,914    16,391     16,883    17,389
Orig. Cost basis                     200,000   200,000   200,000    200,000   200,000
Accum Deprec.                         50,526    58,947    67,368     75,789    84,211
Adjusted basis                       149,474   141,053   132,632    124,211   115,789

Total taxable gain                    92,576   108,259   124,159    140,284   156,640

Capital gains tax                     25,921    30,312    34,765     39,280    43,859

ATCF - Rev.                           75,774    80,962    86,575     92,648    99,222
Cash Flow Summary
Year
Holding Period          0        1       2        3           4
1   -70,979   81,930
2   -70,979    6,156   87,563
3   -70,979    6,156    6,601   93,628
4   -70,979    6,156    6,601    7,054    100,162
5   -70,979    6,156    6,601    7,054      7,514
6   -70,979    6,156    6,601    7,054      7,514
7   -70,979    6,156    6,601    7,054      7,514
8   -70,979    6,156    6,601    7,054      7,514
9   -70,979    6,156    6,601    7,054      7,514
10   -70,979    6,156    6,601    7,054      7,514
Chapter 14
ginal rate of return (MRR)

6        7        8        9       10
125757   121750   117280   112292   106727

day

250000
15000
142432
92568
21589

70979

rom Operations if NOT sold
6         7       8         9         10

\$55,007   \$56,657   \$58,357   \$60,108   61911
27,503    28,329    29,178    30,054   30,955
27,503    28,329    29,178    30,054   30,955
17,642    17,642    17,642    17,642   17,642
9,861    10,687    11,536    12,412   13,313

27,503    28,329    29,178    30,054    30,955
14,051    13,635    13,172    12,654    12,077
8,421     8,421     8,421     8,421     8,421
5,032     6,272     7,586     8,978    10,457
1,409     1,756     2,124     2,514     2,928

9,861    10,687    11,536    12,412    13,313
1,409     1,756     2,124     2,514     2,928
8,453     8,930     9,412     9,898    10,385

sh Flow from Sale

\$298,513 \$307,468 \$316,693 \$326,193 \$335,979
125,757 121,750 117,280 112,292 106,727
17,911   18,448   19,002   19,572   20,159
154,845 167,270 180,411 194,330 209,093

298,513   307,468   316,693   326,193   335,979
17,911    18,448    19,002    19,572    20,159
200,000   200,000   200,000   200,000   200,000
92,632   101,053   109,474   117,895   126,316
107,368    98,947    90,526    82,105    73,684

173,234   190,073   207,165   224,516   242,136

48,505    53,220    58,006    62,865    67,798

106,340   114,050   122,405   131,465   141,295
Cash Flow Summary

5        6         7         8         9         10        IRR      MRR
15.43%   15.43%
15.49%   15.56%
15.54%   15.65%
15.57%   15.69%
107,202                                                     15.60%   15.71%
7,980   114,793                                           15.61%   15.69%
7,980     8,453   122,980                                 15.61%   15.65%
7,980     8,453     8,930   131,818                       15.61%   15.58%
7,980     8,453     8,930     9,412   141,363             15.60%   15.49%
7,980     8,453     8,930     9,412     9,898   151,680   15.58%   15.38%
Spreadsheet Limitations: Ten year holding period.

RETURN FOR PAST 5 YEARS

Data Input Box:
Purchase Price / Property Value             200000 Tax Considerations:
Land Value                                   40000 Depreciable Life (in years)
Building Value                              160000 Capital Gains Rate
Operating Expenses (% of NOI)                   0.5 Marginal Tax Rate
NOI Growth                                     0.04
Property Growth                                0.03
Loan-to-Value                                  0.75
Loan Amount / Current Balance               150000
Interest Rate                                  0.11
Loan Term Remaining (in years)                   25
Payments per Year                                12
Holding Period / Yrs. Since Purch.                5
Selling Costs                                  0.06
Resale Value Year 5                       250,000

Equity                       50,000
Loan                        150,000
Annual Loan Payment          17,642
Mortgage Balance            142,432 year 5

SUMMARY LOAN INFORMATION:
End of Year            1         2              3                          4
Payment                      17,642      17,642            17,642                 17,642
Mortgage Balance            148,799     147,458          145,963                 144,294
Interest                     16,441      16,302            16,146                 15,973
Principal                     1,201       1,340             1,496                  1,669

CASH FLOW FROM OPERATIONS:
Year             1         2              3                             4
Rents                    39,000       40,560            42,182                    43,870
Less Operating Expenses    19500        20280             21091                     21935
Net Operating Income      19500        20280             21091                     21935
Less Debt Service         17642        17642             17642                     17642
Before-Tax Cash Flow    \$ 1,858 \$       2,638 \$           3,449 \$                   4,293

Net Operating Income         19,500          20,280                21,091         21,935
Less: Interest               16,441          16,302                16,146         15,973
Depreciation            8,421           8,421                 8,421          8,421
Taxable Income (loss)        (5,362)         (4,443)               (3,476)        (2,460)
Tax                           (1,501)         (1,244)             (973)     (689)

Before-Tax Cash Flow           1,858           2,638             3,449     4,293
Less Tax                      (1,501)         (1,244)             (973)     (689)
After-Tax Cash Flow         \$ 3,359 \$          3,882 \$           4,423 \$   4,982

CASH FLOW FROM SALE:
Sale Price (received by investor)                              250,000
Sales costs                                                     15,000
Mortgage Balance                                               142,432
Before-tax Cash Flow                                            92,568

Sale Price                                   250,000
Sales Costs                                   15,000

Original Cost Basis      200,000
Accumulated Depreciation  42,105
Adjusted Basis                               157,895

Capital Gain                                  77,105
Tax from Sale                                                   21,589
After-Tax Cash Flow from Sale                                   70,978

CASH FLOW SUMMARY (Past 5 years)
End of Year             0            1             2                3
Before-Tax Cash Flow         (50,000)         1,858             2,638       3,449
After-Tax Cash Flow          (50,000)         3,359             3,882       4,423

Before-Tax IRR                                18.26%
After-Tax IRR                                 14.83%

AFTER TAX NET PRESENT VALUE:
Discount rate              13.00%
After-Tax Present Value from Operations                          17,988
After-Tax Present Value from Reversion                           38,524
Initial Loan Amount                                             150,000
Total Present Value                                \$           203,675

After-Tax Present Value from Operations                         15,150
After-Tax Present Value from Reversion                          38,524
Original Equity Investment                                      50,000
After-Tax Net Present Value                            \$       3,675
Chapter 14
Renovation versus no renovation

RETURN FOR NEXT 5 YEARS

Data Input Box for Yrs 6-10:
Current Value                                   250000
19   Original Cost Basis                             200000
0.28   Building Value (original)                       160000
0.28   Land Value (original)                            40000
NOI Growth                                         0.03
Operating Expenses (% of NOI)                       0.5
Property Growth                                    0.03
Resale Value                                    289819
Selling Costs                                      0.06
Loan Amount / Current Balance                   142432
Interest Rate                                      0.11
Loan Term Remaining (in years)                       20
Payments per Year                                    12
Total Holding Period (in yrs)                        10
NO RENOVATION:
Equity                              57,568
Loan                               142,432
Annual Pmt                          17,642
Mortg Bal                          106,727      in year

5                   6                      7              8
17,642                    17,642              17,642      17,642
142,432                   140,355             138,038     135,452
15,780                    15,565              15,325      15,056
1,862                     2,077               2,317       2,586

5                  6                      7              8
45,624                    47,449                48,873   50,339
22812                     23725                 24436    25170
22812                     23725                 24436    25170
17642                     17642                 17642    17642
\$ 5,170    \$                 6,083 \$               6,794 \$ 7,528

22,812                    23,725                24,436    25,170
15,780                    15,565                15,325    15,056
8,421                     8,421                 8,421     8,421
(1,389)                     (261)                  691     1,692
(389)                                  (73)             193        474

5,170                                6,083              6,794     7,528
(389)                                 (73)               193       474
\$ 5,559             \$                  6,156 \$            6,601   \$ 7,054

Sale Price (received by investor)
Sales costs
Mortgage Balance
Before-tax Cash Flow

Sale Price                                    289,819
Sales Costs                                    17,389

Org. Cost Basis                     200,000
Accum Dep                            84,211
Adj Basis                                     115,789

Capital Gain                                  156,640
Tax from Sale
After-Tax Cash Flow from Sale

CASH FLOW SUMMARY ( Next 5 years)
4         5                     5                   6          7
4,293    97,738                     (70,978)           6,083  6,794
4,982    76,537                     (70,978)           6,156  6,601

Before-Tax IRR                                 23.06%
After-Tax IRR                                  15.60%
14
no renovation

RN FOR NEXT 5 YEARS

nput Box for Yrs 6-10:
Tax Considerations:                                                           Data Input If Renovated:
Marginal Tax Rate                          0.28               NOI Yr 1 After Renovation             45000
Capital Gains Rate                         0.28               NOI Growth                              0.04
Depreciable Life (in yrs)                    19               Terminal Cap Rate                        0.1
Renovation Costs                     200000
Loan Amount on Renovations           200000
Total New Loan Amount                337500
Interest Rate                           0.11
Loan Term (in years)                      15
Payments per Year                         12
Dep. Life of Renovation                 31.5
Holding Period                             5
Years Since Purchase                       5

IF RENOVATED:
Additional Equity          4,932
Loan                     337,500
Annual Pmt                46,032
10                                                           Mortg Bal                278,477     in year

Yr of Ren.     1                  2                3          4
9                10                     6                  7                8          9
17,642     17,642                 46,032                   46,032     46,032     46,032
132,567    129,348                328,130                  317,675    306,011    292,997
14,757     14,423                 36,662                   35,578     34,368     33,018
2,885      3,219                  9,370                   10,455     11,664     13,014

9              10                        6                 7                 8          9
51,849   53,405                      -                -                 -          -
25925    26702                     -                -                 -          -
25925    26702                    45000                   46800       48672      50619
17642    17642                    46032                   46032       46032      46032
\$                 8,283 \$ 9,060                  \$ (1,032) \$                 768     \$ 2,640    \$ 4,587

25,925     26,702                 45,000                   46,800     48,672     50,619
14,757     14,423                 36,662                   35,578     34,368     33,018
8,421      8,421                 14,770                   14,770     14,770     14,770
2,746      3,858                 (6,432)                  (3,548)      (466)     2,831
769      1,080              (1,801)                    (993)      (131)         793

8,283     9,060              (1,032)                    768   2,640   4,587
769     1,080              (1,801)                   (993)   (131)    793
\$                7,514   \$ 7,980            \$    769 \$                 1,761 \$ 2,770 \$ 3,794

289,819                        Sale Price (received by investor)                547,494
17,389                        Sales costs                                       32,850
129,348                        Mortgage Balance                                 278,477
143,081                        Before-tax Cash Flow                             236,168

Sale Price                            547,494
Sales Costs                            32,850

Org. Cost Basis           400,000
Accum Dep                 115,957
Adj Basis                             284,043

Capital Gain                          230,601
43,859                     Tax from Sale                                     64,568
99,222                     After-Tax Cash Flow from Sale                    171,599

W SUMMARY ( Next 5 years)
8                 9         10                                            5          6
7,528     8,283   152,142 ATCF assuming renovation                 -           769
7,054     7,514   107,202 ATCF assuming no renovation              -         6,156
Incremental Cash Flow                  (4,932)    (5,387)

IRR on Incremental Flows                          37.47%
ovated:

5

5
10
46,032
278,477
31,512
14,520

10          11
-
-
52644       54749
46032
\$ 6,611

52,644
31,512
14,770
6,361
1,781

6,611
1,781
\$ 4,830

7         8         9          10
1,761     2,770     3,794    176,430
6,601     7,054     7,514    107,202
(4,840)   (4,283)   (3,719)    69,227
Lease versus

Spreadsheet Limitations: 15 years holding period. Assumes a interest-only loan. Make sure cells that do not ap
Opening costs are accured at time of purchase.

Data Input Box:
Sales Revenue                        \$1,500,000                 Ownership Data:
Cost of Goods Sold                       50.00%    of sales     Property Value Today      \$2,000,000
Additional Overhead                     200,000                 Time Since Purchase                 5
Up-Front/Opening Costs                2,500,000                 Purchase Price             1,800,000
Building Value             1,575,000
Lease Data:                                                     Land Value                   225,000
Annual Payment                          200,000                 Resale Value year 15       3,000,000
Lease Term                                   15   years         Loan-to-Value                 76.06%
Operating Expenses                       50.00%   of payment Loan Amount                   1,369,000
Interest rate                 10.00%
Tax Considerations:                                             Loan Term                          10
Corporate tax rate                       30.00%                 Equity Investment          430999.56
Depreciable Life (in yrs)                    32
*No value after 15 years.

OPERATING CASH FLOW:
Difference
Own          Lease          Own - Lease
Sales                                \$1,500,000    \$1,500,000             0
Less: Cost of Goods Sold                750,000       750,000             0
Gross Income                            750,000       750,000             0                      Same for own and le
Less: Overhead                          200,000       200,000             0                      difference.
Net Income (before real estate)         550,000       550,000             0

Operating Expenses                      100,000      100,000              0
Lease Payments                                0      200,000                  (200,000)
Mortgage Interest Payment               136,900            0                   136,900
Tax Depreciation                         50,000            0                    50,000
Taxable Income                          263,100      250,000                    13,100
Less: Tax                                78,930       75,000                     3,930
Income After-Tax                        184,170      175,000                     9,170

Plus: Depreciation                       50,000            0                    50,000
Less: Mortage Balance                         0            0                         0
After-Tax Cash Flow                    \$234,170     \$175,000                   \$59,170

CASH FLOW FROM SALE IF OWNED & SOLD AT END OF LEASE TERM:
Resale                               3,000,000
Less: Mortgage Balance               1,369,000
Before-Tax Cash Flow                      1,631,000
Resale                       3,000,000
Less: Adjusted Basis           800,000
Capital Gain                 2,200,000
Less: Tax                                  660,000
Additional cash flow from
After-Tax Cash Flow                       \$971,000 owning

SUMMARY OF AFTER-TAX CASH FLOW DIFFERENCES OF OWN vs. LEASE:
Outlay    Cash Flow   Reversion
Year                          0       1 to 14        15
Initial Investment         (496,000)
Operations                               59,170
Sale                                                     971,000
Total                     (\$496,000)   \$59,170          \$971,000

This is the return from
IRR on ATCF differences        14.10%                 owning;

SUMMARY A
Year              0           1                     2                3
Own                                  0     234,170                     234,170   234,170
Lease / Sale-Leaseback         496,000     175,000                     175,000   175,000
Differnce of Own vs. Lease    (496,000)     59,170                      59,170    59,170
IRR on Own vs. Lease            14.10%
Chapter 15
Lease versus Own and Sale-Leaseback Analysis

ake sure cells that do not apply are set to zero.
ase.

years

years

Same for own and lease, so no
difference.

Additional Cash Flow

CASH FLOW IF SOLD TODAY:
Resale                                 2,000,000
Less: Mortgage Balance                 1,369,000
Before-Tax Cash Flow                        631,000
Resale                       2,000,000
Less: Adjusted Basis         1,550,000
Capital Gain                   450,000
Less: Tax                                   135,000

After-Tax Cash Flow                      \$496,000

SUMMARY AFTER-TAX CASH FLOWS OF OPTIONS:
4             5         6             7          8         9         10        11
234,170 234,170    234,170       234,170   234,170   234,170   234,170   234,170
175,000 175,000    175,000       175,000   175,000   175,000   175,000   175,000
59,170  59,170     59,170        59,170    59,170    59,170    59,170    59,170
12        13        14         15
234,170   234,170   234,170   1,205,170
175,000   175,000   175,000     175,000
59,170    59,170    59,170   1,030,170
Chapter 16
Financing Proposed Projects with Construction Loan

Spreadsheet Limitations: Twelve month draw period, where first % draw is only for 4 months. All yellow color
are data input cells. Set all data input cells that do not apply equal to zero (i.e. if there is constant growth,
set the growth for "Yr2-Yr3" to zero.

Data Input Box:
Property Appreciation                  6.00%     per year         Holding Pd. (after con)
Selling Costs                          2.00%     of sale price    Permanent Financing
Gross Building Area                  120,000                      Loan Fee
Gross Leasable Area                  110,000                      Loan Fee Amort
Site Acq. & Closing                2,500,000                      Loan Amortization
On/Off Site Costs                    850,000                      Loan Term
Hard Costs                         7,055,500                      Interest Rate
Soft Costs                         1,576,786                      Payments per Year
Leasing Comissions                    45,300                      Construction Loan
Rent                                        15   per GLA          Loan Amount
Rent Growth                            6.00%     per year         Term
Overage Percentage                     5.00%     on gross sales % drawn first 4 mos.
Overage on Excess over                200.00     per GLA          % drawn last months
Average Sales                         200.27     per GLA          Interest Rate
Sales Growth Yr 2 - Yr 3                316%                      Loan Fee
Sales Growth                           4.00%     per year         Tax Considerations
Tenant Reinmbursement                    8.39    per GLA, yr 2    Marginal Tax Rate
Tenant Reinb. Growth                   4.00%     per year         Capital Gains Rate
Expenses                                 9.68    per GLA          Depreciation
Exp. Growth Yr 2 - Yr 3                2.59%                      Capital Improvements
Expenses Growth                        4.16%     per year         Capital Improvements
Vacancy Yr 2                          30.00%                      Tenant Improvements
Vacancy beginning Yr 3                 5.00%                      Tenant Improvements

Total Cost Breakdown
Site Acq. & Close                  2,500,000                 Total Project Costs
On/Off-Site Costs                    850,000                 Loan Amount
Hard Costs                         7,055,500                 Equity Needed
Soft Costs                         1,576,786

Total Project Costs             \$11,982,286                       Perm Loan Fee Amount

CONSTRUCTION LOAN DRAWS & REPAYMENT:

End of Month            Project Costs         Interest          Ending Balance
0
1                  \$ 1,557,938                     0 \$                 1,557,938
2                    1,557,938               15,579                    3,131,454
3               1,557,938          31,315              4,720,706
4               1,557,938          47,207              6,325,851
5                 259,656          63,259              6,648,766
6                 259,656          66,488              6,974,910
7                 259,656          69,749              7,304,315
8                 259,656          73,043              7,637,014
9                 259,656          76,370              7,973,041
10                 259,656          79,730              8,312,427
11                 259,656          83,124              8,655,208
12                 259,656          86,552              9,001,416
Total           \$ 8,309,000    \$    692,416
Yield to Lender

SUMMARY OF PERMANENT LOAN
Year of Loan               2              3               4
Payment                          1,104,844       1,104,844           1,104,844
Mortgage Balance                 8,682,752       8,616,260           8,541,335
Interest                         1,045,836       1,038,352           1,029,919
Principal                           59,008          66,492              74,925

SUMMARY OF DEPRECIABLE COSTS
On/Off Site Improvements       850,000
Hard Costs                   7,055,500
Soft Costs                   1,094,398 (Does not include leasing commissions,
Total Depreciable Costs      8,999,898 Permanent loan fees or construction loan

BEFORE AND AFTER-TAX CASH FLOWS:
Year                    0              1                2
Site Acq. and Closing            2,500,000
Site Improvements                                   850,000
Hard Costs                                        7,055,500
Soft Costs*                                         447,282
*(less interest & loan fees)
Permanent Loan Fee                 262,253
Construction Loan Fee              174,835
Construction Interest                               692,416
Total Constr. Outflow            2,937,088        9,045,198
Less : Total Draws                        0       8,741,760
Total Equity Needed            \$ 2,937,088 \$        303,438

OPERATING PERIOD CASH FLOWS
Year               1                2               3
Rent                                              1,650,000            1,749,000
Overage                                              29,997              124,788
Tenant Reimbursements                               923,000              959,920
PGI                                               2,602,997            2,833,708
Less : Vacancy                                      780,899              141,685
EGI                                             1,822,098                   2,692,022
Total Expenses                                  1,064,800                   1,092,402
Net Operating Income                              757,298                   1,599,620
Less : Debt Service                             1,104,844                   1,104,844
Before-Tax Cash Flow             (303,438)       (347,546)                    494,776

Net Operating Income                              757,298                   1,599,620
Less:
Interest                                  1,045,836                   1,038,352
Depreciation
Capital Imprvmnt                         257,140                       257,140
Tenant Imprvmnt                          257,140                       183,671
Amortization:
Constr Loan Fee          174,835
Perm Loan Fee                              26,225                       26,225
Leasing comm.                  0                0                            0
Taxable Income                   (174,835)        (829,043)                      94,231
Taxes                             (48,954)        (232,132)                      26,385

After-Tax Cash Flow              (254,484)        (115,414)                     468,391

AFTER-TAX CASH FLOW FROM SALE
Sale Price (received by investor)           16,035,002
Sales costs                                    320,700
Mortgage Balance                             8,361,773
Before-tax Cash Flow                         7,352,528

Original Cost Basis            11,982,286
Accum Dep & Amort               2,324,312
Adjusted Basis                                  9,657,974

Capital Gain                                    6,056,328
Tax from Sale                                                       1,695,772
After-Tax Cash Flow from Sale                                       5,656,756

BEFORE and AFTER-TAX CASH FLOW SUMMARY
Year                     0              1               2
Before-Tax Cash Flow            (2,937,088)       (303,438)            (347,546)
After-Tax Cash Flow             (2,937,088)       (254,484)            (115,414)

BTCF IRR                           20.95%
ATCF IRR                           17.35%

Note: The calculation of taxable income differs slightly from the book for the following reasons:
1) Leasing commissions were not amortized in the book as they should be as shown above.
2) The amount in the book for depreciation of tenant improvements in year 5 was wrong in the book.
pter 16
ects with Construction Loan

is only for 4 months. All yellow colored cell
zero (i.e. if there is constant growth,
o.

5 years

3.00%    of loan
10   years
25   years
10   years
0.12
12

\$    8,309,000
12 months
75.00%
25.00%
12.00%
2.00%

28.00%
28.00%

31.5    years - S/L
90.00%     of total
7    years - DDB
10.00%     of total

\$   11,982,286
8,741,760
3,240,526

\$      262,253

AYMENT:

Lender's Cash
Payoff           Flow
\$     174,835
0    (1,557,938)
0    (1,557,938)
0   (1,557,938)
0   (1,557,938)
0     (259,656)
0     (259,656)
0     (259,656)
0     (259,656)
0     (259,656)
0     (259,656)
0     (259,656)
8,741,760     8,741,760

Yield to Lender                   15.35%

AN
5            6
1,104,844    1,104,844
8,456,908    8,361,773
1,020,417    1,009,709
84,427       95,135

ER-TAX CASH FLOWS:
3                4           5           6

ASH FLOWS
4            5            6
1,853,940    1,965,176     2,083,087
129,779      134,970       140,369
998,317    1,038,250     1,079,780
2,982,036    3,138,396     3,303,236
149,102      156,920       165,162
2,832,934   2,981,476   3,138,074
1,137,847   1,185,183   1,234,487
1,695,087   1,796,294   1,903,587
1,104,844   1,104,844   1,104,844
590,243     691,450     798,743

1,695,087   1,796,294   1,903,587

1,029,919   1,020,417   1,009,709

257,140     257,140     257,140
131,194      93,710      66,936

26,225      26,225      26,225
0           0           0
250,609     398,802     543,576
70,170     111,665     152,201

520,073     579,785     646,541

AX CASH FLOW SUMMARY
3              4          5              6
494,776        590,243     691,450    8,151,271
468,391        520,073     579,785    6,303,298

the following reasons:
shown above.
as wrong in the book.
Chapter 17 E
Financing Land Develo

Type                Price            Number            Total
Cluster                        19,000             60           1,140,000
Standard                       45,600             87           3,967,200
Creek                          47,500             10             475,000
Totals           157           5,582,200

Available Financing
42%   of land acquisition                      1,000,000
100%   of improvement costs                     2,640,000
12%   Interest rate                            3,640,000 Total to be financed
3   points                                     448,109 Interest Carry (calc. below)
20%   faster than revenue                      4,088,109 Total Loan Amount

TARGET REVENUE:            \$4,651,833

DRAWS AND UNITS:
Draws
Month              Amount             Months         Cluster         Standard
0                  1,000,000           0
1                    600,000          1-3                      0            0
2                    600,000          4-6                      2            2
3                    600,000         7-12                      4            3
4                    300,000         13-18                     3            6
5                    300,000         19-24                     1            5
6                    300,000
Total                3,700,000                                  10         16

PRESENT VALUES, ACCELERATION, & RESALE PR

Month                Draw             Cluster       Standard          Creek
0                   1,019,200                 0                0            0
1                     655,200                 0                0            0
2                     655,200                 0                0            0
3                     655,200                 0                0            0
4                     218,400                 2                2            0
5                     218,400                 2                2            0
6                     218,400                 2                2            0
7                                             4                3            1
8                                             4                3            1
9                                             4                3            1
10                                             4                3            1
11                                             4                3            1
12                                             4                3            1
13                                             3                6            3
14                                        3             6        3
15                                        3             6        3
16                                        3             6        3
17                                        3             6        3
18                                        3             6        3
19                                        1             5        2
20                                        1             5        2
21                                        1             5        2
22                                        1             5        2
23                                        1             5        2
24                                        1             5        2
Total                  3,640,000          54            90       36
Present Value              3,569,554

PV Draws           3,569,554
PV Revenue         5,880,209
PV Rev / PV Draw    60.7046%
Acceleration          20.00%
Release Price       72.8455%

LOAN SCHEDULE AND LENDER'S IRR
Payments
Month                 Interest        Total     Principal     Interest
0                 0    1,019,200
1            10,192      665,392             0    10,192
2            16,846      672,046             0    16,846
3            23,566      678,766             0    23,566
4            30,354      248,754        94,116    30,354
5            31,900      250,300        94,116    31,900
6            33,462      251,862        94,116    33,462
7            35,040       35,040       189,617    35,040
8            33,494       33,494       189,617    33,494
9            31,933       31,933       189,617    31,933
10            30,356       30,356       189,617    30,356
11            28,763       28,763       189,617    28,763
12            27,155       27,155       189,617    27,155
13            25,530       25,530       344,632    25,530
14            22,339       22,339       344,632    22,339
15            19,116       19,116       344,632    19,116
16            15,861       15,861       344,632    15,861
17            12,573       12,573       344,632    12,573
18             9,253        9,253       344,632     9,253
19             5,899        5,899       249,131     5,899
20             3,467        3,467       249,131     3,467
21             1,010        1,010       102,005     1,010
22                 0            0             0         0
23                 0            0             0         0
24                 0            0             0         0
448,109    4,088,109     4,088,109 448,109
Lender's Yield                  15.69%

DEVELOPER'S CASH FLOW, NPV AND IRR

Quarter                     0              1              2             3
Inflow:
Sales                              0             0         387,600    780,900
Loan Draw                  1,019,200     1,965,600         655,200          0
Interest Draw                      0        50,604          95,717    100,466
Total Inflow               1,019,200     2,016,204       1,138,517    881,366

Outflows:
Site Purch.                2,400,000
Closing                      100,000
Loan Fee                     122,643
Loan Pmt.                                        0         282,349    568,850
Interest Cost                        0      50,604          95,717    100,466
Direct Costs                    19,200   1,965,600         655,200
Gen & Admin                                  26,250          26,250    26,250
Prop. Tax
Sales Exp.                                       0          19,380     39,045
Total Outflow              2,641,843     2,042,454       1,078,896    734,612

Net Cash                  (\$1,622,643)    (\$26,250)        \$59,621 \$146,755

Net Present Value                          40,056             15% discount rate
Internal Rate of Return                    16.64%
Chapter 17 Exhibits
Financing Land Development Projects*

Land and Development Costs
Land                      2,400,000                                              Site
Direct Costs              2,640,000                                              Site Closing
Commission                       5%                                              Site Improvements
Construction Interest
Const. Loan Fee
Release Prices                               Total Direct Costs
Cluster        Standard      Creek
13,840.64      33,217.53   34,601.60   Operating Expenses:
Total to be financed                             (calculated below)                         Selling Commissions
Interest Carry (calc. below)                                                                Property Taxes
Total Loan Amount                                                                           General & Admin.
Total Indirect Costs

Total Project Costs
- Loan Amount
Cumulative                                                Total Equity Required
Creek                Units
Percent Financed
0             0
0            12
1            60
3           132
2           180

180

ALUES, ACCELERATION, & RESALE PRICE:
Cumulative     Monthly     Cumulative   MPVIF @     PV
Units         Sales       Sales        0.12       Draws    Monthly Sales
0           0            0     1.00000    1019200             0
0           0            0      .99010 648712.87              0
0           0            0      .98030 642289.97              0
0           0            0      .97059 635930.66              0
4     129,200      129,200      .96098 209878.11         124159
8     129,200      258,400      .95147 207800.11         122929
12     129,200      387,600      .94205 205742.68         121712
20     260,300      647,900      .93272          0        242787
28     260,300      908,200      .92348          0        240383
36     260,300    1,168,500      .91434          0        238003
44     260,300    1,428,800      .90529          0        235646
52     260,300    1,689,100      .89632          0        233313
60     260,300    1,949,400      .88745          0        231003
72     473,100    2,422,500      .87866          0        415695
84     473,100     2,895,600    .86996            0      411579
96     473,100     3,368,700    .86135            0      407504
108     473,100     3,841,800    .85282            0      403470
120     473,100     4,314,900    .84438            0      399475
132     473,100     4,788,000    .83602            0      395520
140     342,000     5,130,000    .82774            0      283087
148     342,000     5,472,000    .81954            0      280284
156     342,000     5,814,000    .81143            0      277509
164     342,000     6,156,000    .80340            0      274762
172     342,000     6,498,000    .79544            0      272041
180     342,000     6,840,000    .78757            0      269348
180   6,840,000                           3,569,554    5,880,209
5,880,209

ENDER'S IRR
Payments
Total         Balance     Cash Flow
1,019,200      (896,557)
10,192     1,684,592      (655,200)
16,846     2,356,638      (655,200)
23,566     3,035,404      (655,200)
124,470     3,190,042      (124,284)
126,017     3,346,226      (124,284)
127,579     3,503,972      (124,284)
224,656     3,349,395       189,617
223,111     3,193,272       189,617
221,549     3,035,588       189,617
219,973     2,876,327       189,617
218,380     2,715,474       189,617
216,771     2,553,012       189,617
370,162     2,233,910       344,632
366,971     1,911,617       344,632
363,748     1,586,102       344,632
360,493     1,257,331       344,632
357,205       925,272       344,632
353,885       589,893       344,632
255,030       346,660       249,131
252,598       100,996       249,131
103,015             0       102,005
0             0             0
0             0             0
0             0             0
4,536,218
S CASH FLOW, NPV AND IRR

4             5           6              7            8

780,900    1,419,300   1,419,300      1,026,000   1,026,000
0
86,274       66,985      37,687         10,375           0
867,174    1,486,285   1,456,987      1,036,375   1,026,000

568,850    1,033,896   1,033,896       600,268           0
86,274       66,985      37,687        10,375           0

26,250      26,250         26,250      26,250      26,250
43,750                                             43,750
39,045       70,965      70,965        51,300      51,300
764,169    1,198,096   1,168,798       688,194     121,300

\$103,005    \$288,189    \$288,189       \$348,182    \$904,700

discount rate
Land and Development Costs:
2,400,000
Site Closing              50,000
Site Improvements      2,640,000
Construction Interest    448,109
Const. Loan Fee          122,643
Total Direct Costs  5,660,753

Operating Expenses:
Selling Commissions        279,110
Property Taxes              87,500
General & Admin.           210,000
Total Indirect Costs    576,610

Total Project Costs       6,237,363
- Loan Amount             4,088,109
Total Equity Required    2,149,253

Percent Financed            65.54%
Initial Investment
Investors Capital Inc.                      -\$45,000,000
Property Developers Inc.                     -\$5,000,000

Initial      Cash flow from
Year              Investment   Operations                                       Total Cash flow
0 -\$50,000,000                                                        -\$50,000,000
1                         \$1,000,000                                    \$1,000,000
2                         \$2,000,000                                    \$2,000,000
3                         \$5,000,000                                    \$5,000,000
4                         \$6,000,000                                    \$6,000,000
5                         \$6,500,000                                   \$81,500,000

IRR                                                                                         14.81%

Cash flow        Investors Capital Inc. Property Developers   Remaining
from             return from            Inc. return from      operating cash
Year              Operations       operations             operations            flow to be split

1             \$1,000,000              \$1,000,000                   \$0                   \$0
2             \$2,000,000              \$2,000,000                   \$0                   \$0
3             \$5,000,000              \$2,250,000             \$250,000           \$2,500,000
4             \$6,000,000              \$2,250,000             \$250,000           \$3,500,000
5             \$6,500,000              \$2,250,000             \$250,000           \$4,000,000

Investors      Property Developers
Year              Capital Inc.   Inc.
0           -\$45,000,000           -\$5,000,000
1             \$1,000,000                    \$0
2             \$2,000,000                    \$0
3             \$3,500,000            \$1,500,000
4             \$4,000,000            \$2,000,000
5            \$70,150,834           \$11,349,166

IRR               13.22%                  26.64%
Chapter 18

Property
Developers
Investors Capital    Inc. return     Remaining            Investors Capital Investors Capital
Inc. return from     from            operating cash       Inc. from sale for Inc. cash flow for
operations           operations      flow to be split     12% IRR            12% IRR
-\$45,000,000
\$1,000,000              \$0                   \$0                               \$1,000,000
\$2,000,000              \$0                   \$0                               \$2,000,000
\$2,250,000        \$250,000           \$2,500,000                               \$3,500,000
\$2,250,000        \$250,000           \$3,500,000                               \$4,000,000
\$2,250,000        \$250,000           \$4,000,000          \$61,801,668         \$66,051,668

12.00%

Investors Capital Investors Capital
Inc. cash flow    Inc. return of initial
Year                 from Operations investment
0
1                  \$1,000,000
2                  \$2,000,000
3                  \$3,500,000
4                  \$4,000,000
5                  \$4,250,000          \$45,000,000
Property           Remaining
Developers Inc.    cash flow     Investors          Property
return of initial  from sale to Capital Inc.        Developers
investment         be split      total cash flow    Inc.          Check
-\$45,000,000     -\$5,000,000       \$0
\$1,000,000              \$0       \$0
\$2,000,000              \$0       \$0
\$3,500,000      \$1,500,000       \$0
\$4,000,000      \$2,000,000       \$0
\$5,000,000    \$8,198,332      \$70,150,834    \$11,349,166        \$0

13.22%          26.64%

Investors Capital
Inc. additional
cash flow from    Total Cash
Sale              Flow
-\$45,000,000
\$1,000,000
\$2,000,000
\$3,500,000
\$4,000,000
\$16,801,668 \$66,051,668

12.00%
Initial Investment
Investors Capital Inc.                     -\$45,000,000
Property Developers Inc.                    -\$5,000,000

Initial        Cash flow from
Year         Investment         Operations                                Total Cash flow
0 -\$50,000,000                                                      -\$50,000,000
1                            \$1,000,000                                \$1,000,000
2                            \$2,000,000                                \$2,000,000
3                            \$5,000,000                                \$5,000,000
4                            \$6,000,000                                \$6,000,000
5                            \$6,500,000                               \$81,500,000

IRR                                                                                       14.81%

Cash flow      Investors Capital Inc. Property Developers      Remaining
from              return from         Inc. return from    operating cash
Year          Operations          operations            operations        flow to be split

1             \$1,000,000             \$1,000,000                    \$0                 \$0
2             \$2,000,000             \$2,000,000                    \$0                 \$0
3             \$5,000,000             \$2,250,000              \$250,000         \$2,500,000
4             \$6,000,000             \$2,250,000              \$250,000         \$3,500,000
5             \$6,500,000             \$2,250,000              \$250,000         \$4,000,000

Investors   Property Developers
Year         Capital Inc.         Inc.
0           -\$45,000,000           -\$5,000,000
1             \$1,000,000                    \$0
2             \$2,000,000                    \$0
3             \$3,500,000            \$1,500,000
4             \$4,000,000            \$2,000,000
5            \$66,051,493          \$15,448,507

IRR               12.00%                 32.94%
Chapter 18

Property
Developers
Investors Capital   Inc. return      Remaining       Investors Capital Investors Capital
Inc. return from      from       operating cash     Inc. from sale for Inc. cash flow for
operations       operations    flow to be split        12% IRR            12% IRR
-\$45,000,000
\$1,000,000            \$0                 \$0                              \$1,000,000
\$2,000,000            \$0                 \$0                              \$2,000,000
\$2,250,000      \$250,000         \$2,500,000                              \$3,500,000
\$2,250,000      \$250,000         \$3,500,000                              \$4,000,000
\$2,250,000      \$250,000         \$4,000,000          \$53,602,986        \$57,852,986

9.38%

Investors Capital Investors Capital
Inc. cash flow Inc. return of initial
Year           from Operations      investment
0
1                   \$1,000,000
2                   \$2,000,000
3                   \$3,500,000
4                   \$4,000,000
5                   \$4,250,000          \$45,000,000
Property       Remaining
Developers Inc.     cash flow     Investors     Property
return of initial from sale to   Capital Inc.  Developers
investment         be split  total cash flow    Inc.       Check
-\$45,000,000 -\$5,000,000        \$0
\$1,000,000          \$0        \$0
\$2,000,000          \$0        \$0
\$3,500,000  \$1,500,000        \$0
\$4,000,000  \$2,000,000        \$0
\$5,000,000 \$16,397,014      \$66,051,493 \$15,448,507        \$0

12.00%       32.94%

Investors Capital
Inc. additional
cash flow from    Total Cash
Sale          Flow
-\$45,000,000
\$1,000,000
\$2,000,000
\$3,500,000
\$4,000,000
\$8,602,986 \$57,852,986

9.38%
Chapter 18
Partnership Analysis

Data Input Box:
Land                             525,000                Income Considerations:
Improvements                 \$ 3,475,000                General Partner
Funding Required               4,180,000                Gen Partner Gain Allocation
Loan Amount                    3,000,000                Limited Partner
Interest Rate                     12.00%    per year    Lim Partner Gain Allocation
Loan Term                              25               PGI (year 2)
Payments per Year                      12               Vacancy & Loss
Holding Period                          5   years       Operating Expense (yr 2)
Syndication Expense              100,000                Income Growth
Points                            60,000                Resale Price
Points Term                           25                Selling Costs
Points Payment                     2,400    per year    Tax Considerations:
Organization Fee                  20,000                Depreciation
Org Fee Term                           5                General Partner
Org Fee Payment                    4,000    per year    Limited Partner

Total:                                            General Partner:
Equity                         1,180,000                Equity
Annual Loan Payment              379,161                Payment

SUMMARY LOAN INFORMATION:
End of Year                             2          3                               4
Payment                          379,161    379,161                         379,161
Mortgage Balance               2,979,750  2,956,931                       2,931,218
Interest                         358,910    356,342                         353,448
Principal                         20,250     22,819                          25,713

SUMMARY OF POINTS INFORMATION:
End of Year                            2         3                                 4
Payment                           2,400     2,400                             2,400
Balance                          57,600   55,200                             52,800

DISTRIBUTION OF BEFORE-TAX CASH FLOW:
Year                                   2          3                     4
Potential Gross Income          750,000     772500                 795675
Less : Vancancy & Loss            37500      38625                  39784
Effective Gross Income          712,500    733,875               755,891
Less : Operationg Expenses       249375     256856                 264562
Net Operating Income            463,125    477,019               491,329
Less : Debt Service             379,161    379,161               379,161
Before-Tax Cash Flow             83,964     97,858               112,169
Distribution:
General Partner BTCF                 4,198        4,893                       5,608
Limited Partner BTCF                79,766       92,965                     106,560

DISTRIBUTION OF TAXABLE INCOME:
Net Operating Income                463,125    477,019                      491,329
Less : Interest                     358,910    356,342                      353,448
Depreciation                 110,317    110,317                      110,317
Organization Fee               4,000      4,000                        4,000
Points                         2,400      2,400                        2,400
Taxable Income:                     (12,503)     3,959                       21,164
Distribution:
General Partner BTCF                   (625)         198                      1,058
Limited Partner BTCF                (11,878)       3,761                     20,106

DISTRIBUTION OF TAXABLE GAIN FROM SALE:
Sale Price                                           \$           5,000,000
Selling Costs                                                      250,000
Original Cost Basis                      \$ 4,100,000
Less : Accumulated Depreciation              551,587
Adjusted Basis                                                   3,548,413
Total Taxable Gain                                               1,201,587

Genral Partner's Gain                                                       \$120,159
Limited Partners' Gain                                                    \$1,081,429

CAPITAL ACCOUNTS PRIOR TO DISTRIBUTION OF CASH FLOW FROM SALE:
End of Year                                1         2                      3
Limited Partners
Equity                            1,121,000
Plus : Income                                        0                 3,761
Less : Loss                                   (11,878)                     0
Plus : Gain from Sale
Less : Cash Distribution                         (79,766)                   (92,965)
Total for Year                    1,121,000      (91,644)                   (89,204)
Balance                         \$ 1,121,000    1,029,356                    940,153

General Partner
Equity                              59,000
Plus : Income                                           0                       198
Less : Loss                                         (625)                         0
Plus : Gain from Sale
Less : Cash Distribution                         (4,198)                     (4,893)
Total for Year                      59,000       (4,823)                     (4,695)
Balance                         \$   59,000       54,177                      49,482
AFTER-TAX CASH FLOW & INTERNAL RATE OF RETURN:
End of Year                               1          2                     3
General Partner
Operation:
Before-Tax Cash Flow          \$     (59,000)      4,198                    4,893
Taxable Income                                     (625)                     198
Taxes                                              (175)                      55
After-Tax Cash Flow           \$     (59,000)      4,373                    4,837

Reversion:
Before-Tax Cash Flow
Capital Gains
Taxes
After-Tax Cash Flow
Total After-Tax Cash Flow     \$     (59,000) \$    4,373     \$              4,837

After-Tax Internal Rate of Return                22.24%

Limited Partners
Operation:
Before-Tax Cash Flow              (1,121,000)     79,766                  92,965
Taxable Income                                   (11,878)                  3,761
Taxes                                             (3,326)                  1,053
After-Tax Cash Flow               (1,121,000)     83,092                  91,912

Reversion:
Before-Tax Cash Flow
Capital Gains
Taxes
After-Tax Cash Flow
Total After-Tax Cash Flow     \$ (1,121,000) \$    83,092     \$             91,912

After-Tax Internal Rate of Return                13.15%
18
nalysis

5.00%
10.00%
95.00%
90.00%
750,000
5.00% (of PGI)
35.00% (of EGI)
3.00%
5,000,000
5.00%

31.5 years (S/L)
28.00%
28.00%

eral Partner:                                  Limited Partner:
59,000                Equity             1,121,000
18,958                Payment              360,203

5         6
379,161   379,161
2,902,244 2,869,596
350,187   346,512
28,974    32,648

:
5           6
2,400       2,400
50,400      48,000

OW:
5           6
819545      844132
40977       42207
778,568     801,925
272499      280674
506,069     521,251
379,161     379,161
126,909     142,091
6,345       7,105
120,563     134,986

506,069     521,251
350,187     346,512
110,317     110,317
4,000       4,000
2,400      50,400
39,165      10,021

1,958          501
37,207        9,520

ALE:

OF CASH FLOW FROM SALE:
4            5            6

20,106      37,207         9,520
0           0             0
1,081,429
(106,560)   (120,563)    (134,986)
(86,455)    (83,357)     955,963
853,698     770,341    1,726,304

1,058       1,958         501
0           0           0
120,159
(5,608)     (6,345)      (7,105)
(4,550)     (4,387)     113,555
44,931      40,544      154,100
RATE OF RETURN:
4             5              6

5,608         6,345          7,105
1,058         1,958            501
296           548            140
5,312         5,797          6,964

\$    154,100
120,159
33,644
120,455
\$     5,312   \$     5,797   \$    127,419

106,560       120,563        134,986
20,106        37,207          9,520
5,630        10,418          2,666
100,931       110,145        132,320

\$   1,726,304
1,081,429
302800
1,423,504
\$ 100,931     \$ 110,145     \$   1,555,825
Chapter 19
Mortgage Pass Through Security

Data Input Box:
Number of mortgages in initial pool                                     40
Average mortgage balance                                          \$25,000
Initial mortgage pool balance                                  \$1,000,000
Prepayment rate                                                    10.00%
Mortgage rate                                                      10.00%
Servicing and Guarantee Fee                                          0.5%
Coupon rate                                                         9.50%
Market interest rate                                                9.50%

Issuance of 100 Mortgage Pass Through Securities (MPT)

(a)           (b)                (c)            (d)            (e)
Total PrincipalGuarantee
Principal      Principal and      and Interest and
Pool         due to         Interest Pmts      Pmts to Issuer Service Fees
Year        Balance    Prepayment           to Issuer         (b)+(c)    (a)x(0.5%)
0    \$1,000,000
1        837,255        100,000           162,745        262,745           5,000
2        691,873         83,725           145,381        229,107           4,186
3        562,186         69,187           129,688        198,875           3,459
4        446,710         56,219           115,476        171,695           2,811
5        344,142         44,671           102,568        147,239           2,234
6        253,358         34,414            90,784        125,198           1,721
7        173,431         25,336            79,927        105,263           1,267
8        103,692         17,343            69,739         87,082             867
9         43,946         10,369            59,746         70,115             518
10              0              0            48,340         48,340             220

Discount rate     Price to Green    Value of MPT
9.50%          \$1,000,000      \$25,000
8.50%          \$1,033,908      \$25,848
10.50%            \$967,969      \$24,199
ty

ities (MPT)

(f)              (g)
Total            Pmt to
Payments         Individual
to Investors Investor
(d)-(e)          (f)/40
(\$25,000)
257,745            6,444
224,921            5,623
195,415            4,885
168,884            4,222
145,005            3,625
123,477            3,087
103,996            2,600
86,215            2,155
69,597            1,740
48,120            1,203
Chapte
CM

MORTGAGE POOL
Amount                      \$75,000 (ooo omitted)
Interest rate                11.00%
Term                              10 years

Annual Payment                \$12,735.11                   Prepayment rate             0.00%

LOAN SCHEDULE FOR MORTGAGE POOL

Year          Beg. Bal      Payment         Interest           Principal        End Bal

1     \$75,000.00    \$12,735.11       8,250.00            \$4,485.11      \$70,514.89
2      70,514.89    \$12,735.11       7,756.64            \$4,978.47      \$65,536.42
3      65,536.42    \$12,735.11       7,209.01            \$5,526.10      \$60,010.32
4      60,010.32    \$12,735.11       6,601.14            \$6,133.97      \$53,876.35
5      53,876.35    \$12,735.11       5,926.40            \$6,808.71      \$47,067.64
6      47,067.64    \$12,735.11       5,177.44            \$7,557.67      \$39,509.98
7      39,509.98    \$12,735.11       4,346.10            \$8,389.01      \$31,120.97
8      31,120.97    \$12,735.11       3,423.31            \$9,311.80      \$21,809.17
9      21,809.17    \$12,735.11       2,399.01           \$10,336.10      \$11,473.07
10      11,473.07    \$12,735.11       1,262.04           \$11,473.07           \$0.00

CLASS Z TRAUNCHE
Amount                          \$30,000
Rate                             11.00%

Total                                Cash
Year          Beg. Bal      Interest       Payment             End Bal            Flow

0                                                                         (\$30,000)
1     \$30,000.00      3,300.00           0.00           \$33,300.00            0.00
2      33,300.00      3,663.00           0.00            36,963.00            0.00
3      36,963.00      4,065.93          (0.00)           41,028.93           (0.00)
4      41,028.93      4,513.18          (0.00)           45,542.11           (0.00)
5      45,542.11      5,009.63       6,484.10            44,067.64        6,484.10
6      44,067.64      4,847.44      12,405.11            36,509.98       12,405.11
7      36,509.98      4,016.10      12,405.11            28,120.97       12,405.11
8      28,120.97      3,093.31      12,405.11            18,809.17       12,405.11
9      18,809.17      2,069.01      12,405.11             8,473.07       12,405.11
10       8,473.07        932.04       9,405.11                 0.00        9,405.11

IRR                       11.00%
P V at                       9.75%       \$32,611
TRAUNCH A

Amount                            \$27,000
Rate                                9.25%

Cash
Year          Beg. Bal      Interest       Principal      End Bal              Flow
0
1    \$27,000.00       2,497.50      \$7,785.11         \$19,214.89      10,282.61
2     19,214.89       1,777.38       8,641.47          10,573.42      10,418.85
3     10,573.42         978.04       9,592.03             981.39      10,570.07
4        981.39          90.78         981.39               0.00       1,072.17
5          0.00           0.00           0.00               0.00           0.00
6          0.00           0.00           0.00               0.00           0.00
7          0.00           0.00           0.00               0.00           0.00
8          0.00           0.00           0.00               0.00           0.00
9          0.00           0.00           0.00               0.00           0.00
10          0.00           0.00           0.00               0.00           0.00

P V at                   8.50%        \$27,376
YTM

CLASS B TRAUNCHE
Amount                           \$15,000
Rate                              10.00%
Cash
Year          Beg. Bal      Interest       Principal      End Bal              Flow
0
1    \$15,000.00      \$1,500.00          \$0.00         \$15,000.00       1,500.00
2     15,000.00       1,500.00           0.00          15,000.00       1,500.00
3     15,000.00       1,500.00           0.00          15,000.00       1,500.00
4     15,000.00       1,500.00       9,665.76           5,334.24      11,165.76
5      5,334.24         533.42       5,334.24               0.00       5,867.66
6          0.00           0.00           0.00               0.00           0.00
7          0.00           0.00           0.00               0.00           0.00
8          0.00           0.00           0.00               0.00           0.00
9          0.00           0.00           0.00               0.00           0.00
10          0.00           0.00           0.00               0.00           0.00

P V at                   9.50%         \$15,257
YTM

RESIDUAL CLASS

Total         Other
Year           in pool       Classes       Residual

0                                   (\$3,000.00)
1     \$12,735.11     \$11,782.61         952.50
2               \$12,735.11                   11,918.85         816.26
3               \$12,735.11                   12,070.07         665.03
4               \$12,735.11                   12,237.93         497.17
5               \$12,735.11                   12,351.76         383.34
6               \$12,735.11                   12,405.11         330.00
7               \$12,735.11                   12,405.11         330.00
8               \$12,735.11                   12,405.11         330.00
9               \$12,735.11                   12,405.11         330.00
10               \$12,735.11                    9,405.11       3,330.00

IRR                     20.19%

Beginning Balance of Class
A               B             Z             Total
Coupon                                        9.25%       10.00%          11.00%
End of Year
0                                 27000          15000            30000        72000
1                                 19215          15000            33300        67515
2                                 10573          15000            36963        62536
3                                   981          15000            41029        57010
4                                     0           5334            45542        50876
5                                     0              0            44068        44068
6                                     0              0            36510        36510
7                                     0              0            28121        28121
8                                     0              0            18809        18809
9                                     0              0             8473         8473

a) Weighted Average Coupon when issued (yr 0)                                          10.14%

b) See above schedules for each class.
Class A repaid in 3 years. Class B repaid in 5 years.

c) See above for WAC at end of each year.

Information and Graphs below are extra material for use by instructor

Weighted Average Coupon
(No prepayment)

11.20%

11.00%
Weighted Average Coupon

10.80%

10.60%

10.40%

10.20%
Weighted Average Coupo
10.20%

10.00%

9.80%

9.60%
0       1    2      3    4          5   6    7     8      9
Year

Prepayment IRR on Residual
Rate
0.00%        19.10%
5.00%        17.33%
10.00%        16.10%
15.00%        15.53%
20.00%        14.99%
25.00%        14.74%
30.00%        14.74%

IRR on Residual vs. Prepayment

20.00%

19.00%

18.00%
IRR

17.00%

16.00%

15.00%

14.00%
0.00%   5.00%   10.00%    15.00%    20.00%   25.00%   30.00%
Prepayment rate
Chapter 20
CMO

L

Prepayment

0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00

10% Price Increase

(\$33,000)
0.00
0.00
(0.00)
(0.00)
6,484.10
12,405.11
12,405.11
12,405.11
12,405.11
9,405.11

9.57%
10% Price Increase
(\$29,700)
10,282.61
10,418.85
10,570.07
1,072.17
0.00
0.00
0.00
0.00
0.00
0.00

Note: No Class C in this problem but is included to make template more genera
4.23%                      CLASS C TRAUNCHE
Amount                            \$0
Rate                           0.00%

Year         Beginning Interest Principal           Ending Balance
Balance
10% Price Increase            1      \$0.00      \$0.00            \$0.00      \$0.00
(\$16,500)            2        0.00      0.00             0.00       0.00
1,500.00             3        0.00      0.00             0.00       0.00
1,500.00             4        0.00      0.00             0.00       0.00
1,500.00             5        0.00      0.00             0.00       0.00
11,165.76             6        0.00      0.00             0.00       0.00
5,867.66             7        0.00      0.00             0.00       0.00
0.00            8        0.00      0.00             0.00       0.00
0.00            9        0.00      0.00             0.00       0.00
0.00           10        0.00      0.00             0.00       0.00
0.00
0.00   Do not delete Class C above. It is used for residual.

7.24%
WAC

10.14%
10.28%
10.46%
10.71%
10.90%
11.00%
11.00%
11.00%
11.00%
11.00%
9

ayment

30.00%
e template more general.
Chapter 20
Floater and Inverse Floater

Input Assumptions
Total amount of tranche                              \$20,000,000
Interest rate for tranche                                  6.00%
Total interest available                              \$1,200,000

Maximum
\$ Amount     Scale           Rate change         Rate limit
Floater (F)                           \$10,000,000          0.50                6.00%         12.00%
Inverse floater (IF)                    10,000,000         0.50               -6.00%          0.00%
Total                                   20,000,000         1.00

F / IF Ratio                                 1.00
Max Inverse Floater (IF) rate              12.00%

Libor                    (F) Rate     (IF) Rate         F Interest       IF Interest
0%          0.00%        12.00%                  \$0    \$1,200,000
1%          1.00%        11.00%             100,000      1,100,000
2%          2.00%        10.00%             200,000      1,000,000
3%          3.00%         9.00%             300,000        900,000
4%          4.00%         8.00%             400,000        800,000
5%          5.00%         7.00%             500,000        700,000
6%          6.00%         6.00%             600,000        600,000
7%          7.00%         5.00%             700,000        500,000
8%          8.00%         4.00%             800,000        400,000
9%          9.00%         3.00%             900,000        300,000
10%         10.00%         2.00%           1,000,000        200,000
11%         11.00%         1.00%           1,100,000        100,000
12%         12.00%         0.00%           1,200,000              0
13%         12.00%         0.00%           1,200,000              0
14%         12.00%         0.00%           1,200,000              0
15%         12.00%         0.00%           1,200,000              0
16%         12.00%         0.00%           1,200,000              0
17%         12.00%         0.00%           1,200,000              0
18%         12.00%         0.00%           1,200,000              0

Rate on Floater and Inverse Floater vs. LIBOR
14.00%

12.00%
10.00%

8.00%

Rate
6.00%

4.00%

2.00%

0.00%
0%   2%   4%   6%       8%        10%         12%   14%
LIBOR

Floater       Inverse Floater
Total Interest
\$1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000
1,200,000

er vs. LIBOR
14%   16%   18%
Chapter 20
IO/PO

MORTGAGE POOL
Amount                             \$1,000,000
Interest rate                          11.00%
TERM                                        10 years

Annual Payment                      169801.4271                   Prepayment

LOAN SCHEDULE FOR MORTGAGE POOL

Year          Beg. Bal         Payment         Interest          Principal

1       1000000            169801          110000                  59801
2        940199            169801          103422                  66380
3        873819            169801           96120                  73681
4        800138            169801           88015                  81786
5        718351            169801           79019                  90783
6        627569            169801           69033                 100769
7        526800            169801           57948                 111853
8        414946            169801           45644                 124157
9        290789            169801           31987                 137815
10        152974            169801           16827                 152974

Interest only and Principal only strips

Year          Interest          Principal

1       \$110,000                \$59,801
2        103,422                 66,380
3         96,120                 73,681
4         88,015                 81,786
5         79,019                 90,783
6         69,033                100,769
7         57,948                111,853
8         45,644                124,157
9         31,987                137,815
10         16,827                152,974

PV @ 11%          PV @ 11%
Assuming no prepayment             \$461,248           \$538,752
PV @ 10%         PV @ 10%
Assuming no prepayment            \$476,919          \$566,437

PV @12%          PV @12%
Assuming no prepayment            \$446,425          \$512,991

Assuming prepayment:

MORTGAGE POOL
AMOUNT                                  \$1,000,000
INTEREST RATE                               11.00%
TERM                                             10

ANNUAL PAYMENT                 169801.4271                     PREPAYMENT

LOAN SCHEDULE FOR MORTGAGE POOL

Year         Beg. Bal      Payment           Interest         Principal

1      1000000             169801         110000             259801
2       740199             133681          81422             200299
3       539900             104914          59389             153505
4       386395              81999          42503             116774
5       269620              63732          29658              87998
6       181623              49142          19978              65488
7       116135              37433          12775              47885
8        68249              27929           7507              34071
9        34178              19958           3760              23034
10        11144              12370           1226              11144

Interest only and Principal only strips

Year            Interest         Principal

1        \$110,000           \$259,801
2          81,422            200,299
3          59,389            153,505
4          42,503            116,774
5          29,658             87,998
6          19,978             65,488
7          12,775             47,885
8           7,507             34,071
9           3,760             23,034
10           1,226             11,144

PV @ 11%         PV @ 11%
\$276,200             \$723,800

Extra Calculations below

Present values at 8 percent with

PV of interest      PV of Principal
\$296,777              \$784,162

PV of Interest      PV of Principal
Prepayment
0.00%               \$361                   \$639
5.00%                311                    689
10.00%                271                    729
15.00%                238                    762
20.00%                211                    789
25.00%                189                    811
30.00%                171                    829

Assume \$275 paid for the IO and \$725 paid for the PO based on 15% prepa
What is the IRR for the IO and PO if prepayment is actually 20%?

Year            Interest           Principal
0              (\$275)               (\$725)
1            110,000              259,801
2             81,422              200,299
3             59,389              153,505
4             42,503              116,774
5             29,658               87,998
6             19,978               65,488
7             12,775               47,885
8              7,507               34,071
9              3,760               23,034
10              1,226               11,144

IRR               39974.02%            35811.78%

IRR of IO          IRR of PO
Prepayment
0.00%             30.00%                5.38%
5.00%             25.32%                6.57%
10.00%             20.62%                7.90%
15.00%             15.91%                9.37%
20.00%             11.19%               10.94%
25.00%              6.45%               12.59%
30.00%              1.70%               14.30%
IRR on IO and PO vs. Prepayment
35%

30%

25%

20%

IRR
15%

10%

5%

0%
0%   5%   10%    15%     20%
Prepayment rate
20

0.00%

GE POOL

End Bal       Prepayment

940199         0.00
873819         0.00
800138         0.00
718351         0.00
627569         0.00
526800         0.00
414946         0.00
290789         0.00
152974         0.00
0         0.00

Total PV
\$1,000,000
Total PV
\$1,043,356

Total PV
\$959,416

OL
(\$000 omitted)

years

20.00%

GE POOL

End Bal        Prepayment

740199        200000
539900        148040
386395        107980
269620         77279
181623         53924
116135         36325
68249         23227
34178         13650
11144          6836
0             0

Total PV
\$1,000,000

20.00% prepayment

Total PV
\$1,080,939

Total PV

\$1,000
\$1,000
\$1,000
\$1,000
\$1,000
\$1,000
\$1,000

the PO based on 15% prepayment.
nt is actually 20%?
vs. Prepayment

IO
PO

25%    30%
Chapter 20
Pool characteristics:
Mortgages     \$10,000,000                  Default property Price   \$8,000,000
Interest rate         10%
Maturity                 5

Assets                                                                   Liabilities

Commercial                                                          Senior Securities:
mortgages                  \$10,000,000                            Class A bonds

Subordinated
securities:
Class B bonds
Total
Net Worth (residual)

Total                        \$10,000,000                            Total

Panel A. No Default or Mortgage Payment

End of         Cash Inflow
Period         to Pool      Senior      Subordinated         Residual
1           \$1,000,000    \$480,000             \$300,000   \$220,000
2           \$1,000,000    \$480,000             \$300,000   \$220,000
3           \$1,000,000    \$480,000             \$300,000   \$220,000
4           \$1,000,000    \$480,000             \$300,000   \$220,000
5         \$11,000,000   \$6,480,000           \$3,300,000 \$1,220,000

IRR =            8.00%              10.00%             22.00%

Panel B. Default Occurs at Maturity and Sale of Property is 80%
of Outstanding Loan Balance

End of   Cash Inflow
Period   to Pool      Senior      Subordinated         Residual
1     \$1,000,000    \$480,000             \$300,000  \$220,000
2     \$1,000,000    \$480,000             \$300,000  \$220,000
3     \$1,000,000    \$480,000             \$300,000  \$220,000
4     \$1,000,000    \$480,000             \$300,000  \$220,000
5     \$9,000,000  \$6,480,000           \$2,520,000        \$0

IRR =          8.00%           5.33%           -4.92%
apter 20

Liabilities

Coupon
8%                          \$6,000,000

10%                         \$3,000,000
\$9,000,000
\$1,000,000

\$10,000,000

IRR Calculations
Senior       Subordinated     Residual
(\$6,000,000)     (\$3,000,000) (\$1,000,000)
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$6,480,000       \$3,300,000 \$1,220,000

8.00%           10.00%       22.00%
IRR Calculations
Senior       Subordinated     Residual
(\$6,000,000)     (\$3,000,000) (\$1,000,000)
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$480,000         \$300,000     \$220,000
\$6,480,000       \$2,520,000           \$0

8.00%            5.33%       -4.92%
Chapter 21
Efficient Frontier
1978 - 2000

Std. Dev.              Return
S&P                                                         7.41%              4.06%
NCREIF                                                      1.76%              2.32%
Correlation                                                -0.0521

Portfolio           Portfolio
% S&P % NCREIF                                   Variance           Stand. Dev        Return Return x 100
0.00% 100.00%                                        0.03%                 1.76%      2.32%       2.32
5.00%  95.00%                                        0.03%                 1.69%      2.41%       2.41
10.00%  90.00%                                        0.03%                 1.71%      2.50%       2.50
15.00%  85.00%                                        0.03%                 1.82%      2.58%       2.58
20.00%  80.00%                                        0.04%                 1.99%      2.67%       2.67
25.00%  75.00%                                        0.05%                 2.22%      2.76%       2.76
30.00%  70.00%                                        0.06%                 2.48%      2.84%       2.84
35.00%  65.00%                                        0.08%                 2.78%      2.93%       2.93
40.00%  60.00%                                        0.10%                 3.09%      3.02%       3.02
45.00%  55.00%                                        0.12%                 3.42%      3.10%       3.10
50.00%  50.00%                                        0.14%                 3.76%      3.19%       3.19
55.00%  45.00%                                        0.17%                 4.11%      3.28%       3.28
60.00%  40.00%                                        0.20%                 4.47%      3.36%       3.36
65.00%  35.00%                                        0.23%                 4.82%      3.45%       3.45
70.00%  30.00%                                        0.27%                 5.19%      3.54%       3.54
75.00%  25.00%                                        0.31%                 5.55%      3.62%       3.62
80.00%  20.00%                                        0.35%                 5.92%      3.71%       3.71
85.00%  15.00%                                        0.40%                 6.29%      3.80%       3.80
90.00%  10.00%                                        0.44%                 6.66%      3.88%       3.88
95.00%   5.00%                                        0.50%                 7.04%      3.97%       3.97
100.00%   0.00%                                        0.55%                 7.41%      4.06%       4.06

21-8
Exhibit 20-8 Portfolio Returns of NCREIF and S&P 500

4.50
4.50%

4.00%
4.00
Portfolio Return (percent)

3.50%

3.00%
3.50
2.50%

2.00%
3.00
1.50%
Portfolio Return (per
1.00%
2.50
0.50%

2.00
0.00%
1%
1%     2%
2%     3%
3%        4%
4%         5%
5%            6%
6%   7%
Portfolio standard deviation %
Portfolio standard deviation %
7%   8%

```
To top