33 spreadsheet Templates for Real Estate Finance and Investments_

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 Initial Interest rate Loan term Points Monthly payment Annual payment Annual constant Effective cost (calculated below) $60,000 12.00% 30 years 3 $617.17 $7,406.01 (Assuming monthly payments) 12.34% 12.82% Note: Do not change the payment and interest rate below unless you want to change from a standard fix Year 1 Month 0 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 Beg. Balance $60,000.00 59,982.83 59,965.49 59,947.98 59,930.29 59,912.43 59,894.38 59,876.16 59,857.76 59,839.17 59,820.39 59,801.43 59,782.27 59,762.93 59,743.39 59,723.66 59,703.72 59,683.59 59,663.26 59,642.73 59,621.99 59,601.04 59,579.88 59,558.51 59,536.93 59,515.13 59,493.12 59,470.88 59,448.42 Payment $617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 Rate 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% Interest 600.00 599.83 599.65 599.48 599.30 599.12 598.94 598.76 598.58 598.39 598.20 598.01 597.82 597.63 597.43 597.24 597.04 596.84 596.63 596.43 596.22 596.01 595.80 595.59 595.37 595.15 594.93 594.71 594.48 Principal 17.17 17.34 17.51 17.69 17.86 18.04 18.22 18.41 18.59 18.78 18.96 19.15 19.34 19.54 19.73 19.93 20.13 20.33 20.53 20.74 20.95 21.16 21.37 21.58 21.80 22.02 22.24 22.46 22.68 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 59,425.74 59,402.83 59,379.69 59,356.32 59,332.71 59,308.87 59,284.79 59,260.48 59,235.91 59,211.10 59,186.05 59,160.74 59,135.18 59,109.36 59,083.29 59,056.96 59,030.36 59,003.49 58,976.36 58,948.96 58,921.28 58,893.32 58,865.09 58,836.57 58,807.77 58,778.68 58,749.30 58,719.63 58,689.66 58,659.38 58,628.81 Effective Cost 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.82% 594.26 594.03 593.80 593.56 593.33 593.09 592.85 592.60 592.36 592.11 591.86 591.61 591.35 591.09 590.83 590.57 590.30 590.03 589.76 589.49 589.21 588.93 588.65 588.37 588.08 587.79 587.49 587.20 586.90 586.59 586.29 22.91 23.14 23.37 23.60 23.84 24.08 24.32 24.56 24.81 25.06 25.31 25.56 25.82 26.07 26.33 26.60 26.86 27.13 27.40 27.68 27.95 28.23 28.52 28.80 29.09 29.38 29.67 29.97 30.27 30.57 30.88 oan with Points monthly payments) u want to change from a standard fixed rate mortgage. End Balance 59,982.83 59,965.49 59,947.98 59,930.29 59,912.43 59,894.38 59,876.16 59,857.76 59,839.17 59,820.39 59,801.43 59,782.27 59,762.93 59,743.39 59,723.66 59,703.72 59,683.59 59,663.26 59,642.73 59,621.99 59,601.04 59,579.88 59,558.51 59,536.93 59,515.13 59,493.12 59,470.88 59,448.42 59,425.74 Cash flow ($58,200) 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 59,402.83 59,379.69 59,356.32 59,332.71 59,308.87 59,284.79 59,260.48 59,235.91 59,211.10 59,186.05 59,160.74 59,135.18 59,109.36 59,083.29 59,056.96 59,030.36 59,003.49 58,976.36 58,948.96 58,921.28 58,893.32 58,865.09 58,836.57 58,807.77 58,778.68 58,749.30 58,719.63 58,689.66 58,659.38 58,628.81 58,597.93 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 617.17 59,215 Chapter 4 Effective Cost of a Loan with Points Loan Term Rate Points Payment Net Loan Holding Period Balance Eff Cost $60,000 30 years 12.00% 3.00% $ 617.17 (using payment function) $ 58,200.00 5 years $ 58,597.93 (present value of remaining payments) 12.82% (using rate function) Cha Effective Cost of a Consta Input Assumptions Loan amount Initial Interest rate Loan term Points Monthly payment Annual payment Annual constant Effective cost (calculated below) $60,000 12.00% 30 years 3 $617.17 (If a CPM) $7,406.01 (Assuming monthly payments) 15.33% (Based on first month) 12.88% Note: Do not change the payment and interest rate below unless you want to change from a standard fixed Year 1 Month 0 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 Beg. Balance $60,000.00 59,833.33 59,666.67 59,500.00 59,333.33 59,166.67 59,000.00 58,833.33 58,666.67 58,500.00 58,333.33 58,166.67 58,000.00 57,833.33 57,666.67 57,500.00 57,333.33 57,166.67 57,000.00 56,833.33 56,666.67 56,500.00 56,333.33 56,166.67 56,000.00 55,833.33 55,666.67 55,500.00 55,333.33 Payment $766.67 $765.00 $763.33 $761.67 $760.00 $758.33 $756.67 $755.00 $753.33 $751.67 $750.00 $748.33 $746.67 $745.00 $743.33 $741.67 $740.00 $738.33 $736.67 $735.00 $733.33 $731.67 $730.00 $728.33 $726.67 $725.00 $723.33 $721.67 $720.00 Rate 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% Interest 600.00 598.33 596.67 595.00 593.33 591.67 590.00 588.33 586.67 585.00 583.33 581.67 580.00 578.33 576.67 575.00 573.33 571.67 570.00 568.33 566.67 565.00 563.33 561.67 560.00 558.33 556.67 555.00 553.33 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 55,166.67 55,000.00 54,833.33 54,666.67 54,500.00 54,333.33 54,166.67 54,000.00 53,833.33 53,666.67 53,500.00 53,333.33 53,166.67 53,000.00 52,833.33 52,666.67 52,500.00 52,333.33 52,166.67 52,000.00 51,833.33 51,666.67 51,500.00 51,333.33 51,166.67 51,000.00 50,833.33 50,666.67 50,500.00 50,333.33 50,166.67 Effective Cost $718.33 $716.67 $715.00 $713.33 $711.67 $710.00 $708.33 $706.67 $705.00 $703.33 $701.67 $700.00 $698.33 $696.67 $695.00 $693.33 $691.67 $690.00 $688.33 $686.67 $685.00 $683.33 $681.67 $680.00 $678.33 $676.67 $675.00 $673.33 $671.67 $670.00 $668.33 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.88% 551.67 550.00 548.33 546.67 545.00 543.33 541.67 540.00 538.33 536.67 535.00 533.33 531.67 530.00 528.33 526.67 525.00 523.33 521.67 520.00 518.33 516.67 515.00 513.33 511.67 510.00 508.33 506.67 505.00 503.33 501.67 Chapter 4 Effective Cost of a Constant Amortization Loan with Points g monthly payments) u want to change from a standard fixed rate mortgage. Principal 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 End Balance Cash flow ($58,200) 59,833.33 766.67 59,666.67 765.00 59,500.00 763.33 59,333.33 761.67 59,166.67 760.00 59,000.00 758.33 58,833.33 756.67 58,666.67 755.00 58,500.00 753.33 58,333.33 751.67 58,166.67 750.00 58,000.00 748.33 57,833.33 746.67 57,666.67 745.00 57,500.00 743.33 57,333.33 741.67 57,166.67 740.00 57,000.00 738.33 56,833.33 736.67 56,666.67 735.00 56,500.00 733.33 56,333.33 731.67 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 Loan Payments: Constant Amort Mortgage vs. Constant Payme Mortgage $800 $750 $700 Payment $650 $600 $550 Constant Amort Loan $500 2 1 3 Year 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 55,000.00 54,833.33 54,666.67 54,500.00 54,333.33 54,166.67 54,000.00 53,833.33 53,666.67 53,500.00 53,333.33 53,166.67 53,000.00 52,833.33 52,666.67 52,500.00 52,333.33 52,166.67 52,000.00 51,833.33 51,666.67 51,500.00 51,333.33 51,166.67 51,000.00 50,833.33 50,666.67 50,500.00 50,333.33 50,166.67 50,000.00 718.33 716.67 715.00 713.33 711.67 710.00 708.33 706.67 705.00 703.33 701.67 700.00 698.33 696.67 695.00 693.33 691.67 690.00 688.33 686.67 685.00 683.33 681.67 680.00 678.33 676.67 675.00 673.33 671.67 670.00 50,668 Amort Mortgage vs. Constant Payment Constant Amort Loan Constant Payment Mortgage 4 Year 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) Payment Period 1 2 3 4 5 6-25 Initial Payment: (2) Payment 1.00000 1.07500 1.15563 1.24230 1.33547 1.43563 (3) MPVIFA 11.25508 11.25508 11.25508 11.25508 11.25508 94.94655 (4) MPVIF 1.00000 0.88745 0.78757 0.69892 0.62026 0.55045 Pmt factor = (5) (2x3x4) 11.25508 10.73743 10.24360 9.77247 9.32302 75.03072 126.36231 $474.83 <==== $60,000 126.36231 Loan Amt / Pmt Factor LOAN AMORTIZATION SCHEDULE: Month 0 1 2 3 4 5 6 7 8 9 Payment Interest Principle ($60,000) <==Amount Dispersed $474.83 $600.00 ($125.17) $474.83 $601.25 ($126.43) $474.83 $602.52 ($127.69) $474.83 $603.79 ($128.97) $474.83 $605.08 ($130.26) $474.83 $606.39 ($131.56) $474.83 $607.70 ($132.88) $474.83 $609.03 ($134.20) $474.83 $610.37 ($135.55) Balance CPM Payment $60,000 $60,125 $617.17 $60,252 $617.17 $60,379 $617.17 $60,508 $617.17 $60,639 $617.17 $60,770 $617.17 $60,903 $617.17 $61,037 $617.17 $61,173 $617.17 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 $474.83 $474.83 $474.83 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $510.44 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $548.72 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $589.87 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $634.11 $611.73 $613.10 $614.48 $615.88 $616.93 $617.99 $619.07 $620.16 $621.25 $622.36 $623.48 $624.61 $625.75 $626.91 $628.07 $629.25 $630.05 $630.87 $631.69 $632.52 $633.36 $634.20 $635.06 $635.92 $636.79 $637.67 $638.56 $639.46 $639.96 $640.46 $640.96 $641.47 $641.99 $642.51 $643.04 $643.57 $644.11 $644.65 $645.20 $645.75 $645.87 $645.98 $646.10 $646.22 $646.34 $646.47 $646.59 $646.71 $646.84 $646.97 ($136.90) ($138.27) ($139.65) ($105.44) ($106.49) ($107.56) ($108.63) ($109.72) ($110.82) ($111.92) ($113.04) ($114.17) ($115.32) ($116.47) ($117.63) ($80.53) ($81.33) ($82.15) ($82.97) ($83.80) ($84.64) ($85.48) ($86.34) ($87.20) ($88.07) ($88.95) ($89.84) ($49.59) ($50.08) ($50.58) ($51.09) ($51.60) ($52.12) ($52.64) ($53.16) ($53.70) ($54.23) ($54.77) ($55.32) ($11.63) ($11.75) ($11.87) ($11.99) ($12.11) ($12.23) ($12.35) ($12.47) ($12.60) ($12.73) ($12.85) $61,310 $61,448 $61,588 $61,693 $61,799 $61,907 $62,016 $62,125 $62,236 $62,348 $62,461 $62,575 $62,691 $62,807 $62,925 $63,005 $63,087 $63,169 $63,252 $63,336 $63,420 $63,506 $63,592 $63,679 $63,767 $63,856 $63,946 $63,996 $64,046 $64,096 $64,147 $64,199 $64,251 $64,304 $64,357 $64,411 $64,465 $64,520 $64,575 $64,587 $64,598 $64,610 $64,622 $64,634 $64,647 $64,659 $64,671 $64,684 $64,697 $64,710 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 $634.11 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $681.67 $64,474.98 $647.10 $647.22 $646.88 $646.53 $646.18 $645.83 $645.47 $645.11 $644.74 $644.37 $644.00 $643.62 $643.24 $642.86 $642.47 $642.08 $641.68 $641.28 $640.88 $640.47 $640.06 $639.64 $639.22 $638.79 $638.37 ($12.98) $34.45 $34.79 $35.14 $35.49 $35.85 $36.21 $36.57 $36.93 $37.30 $37.68 $38.05 $38.43 $38.82 $39.21 $39.60 $39.99 $40.39 $40.80 $41.20 $41.62 $42.03 $42.45 $42.88 $43.31 $64,722 $64,688 $64,653 $64,618 $64,583 $64,547 $64,511 $64,474 $64,437 $64,400 $64,362 $64,324 $64,286 $64,247 $64,208 $64,168 $64,128 $64,088 $64,047 $64,006 $63,964 $63,922 $63,879 $63,837 $63,793 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 $617.17 GPM vs CPM Payment $750 $700 $650 $600 $550 $500 $450 $400 $350 $300 0 5 10 15 20 25 30 35 55 60 65 65 Month 70 70 40 45 50 Payment GPM Loan Balance $66,000 $65,000 $64,000 $63,000 $62,000 $61,000 $60,000 $59,000 $58,000 $57,000 0 5 10 15 20 25 30 35 40 45 50 55 Month 60 Balance EOY 1 EOY 2 EOY 3 EOY 4 EOY 5 EOY 6 EOY 7 70 70 75 80 75 80 Chapter 5 Unrestricted ARM Loan assumptions Amount Initial rate Term Payments per year Margin Points $60,000 8.00% 30 years 12 2.00% 2.00% Loan schedule Year 1 2 3 4 5 Index Rate 8.00% 12.00% 15.00% 17.00% 12.00% Payment Balance Cash flow 440.26 614.24 752.26 846.20 617.60 59,499 59,255 59,106 58,991 58,639 Yield (58,800) 5,283 7,371 9,027 10,154 66,050 12.98% 10.00% 13.00% 15.00% 10.00% Yield is based on monthly cash flows below. Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Cash flow (58,800) 440 440 440 440 440 440 440 440 440 440 440 440 614 614 614 614 614 614 614 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 614 614 614 614 614 752 752 752 752 752 752 752 752 752 752 752 752 846 846 846 846 846 846 846 846 846 846 846 846 618 618 618 618 618 618 618 618 618 618 618 59,257 Chapter 5 ARM with Interest Rate Cap Loan assumptions Amount Initial rate Annual rate cap Lifetime rate cap Term Payments per year Margin Points $60,000 11.00% 2.00% 5.00% 30 years 12 2.00% 2.00% Loan schedule Year 1 2 3 4 5 Index Unrestricted Restricted Rate Rate Payment 11.00% 12.00% 15.00% 17.00% 12.00% 11.00% 12.00% 14.00% 16.00% 12.00% 571.39 616.63 708.37 801.65 619.37 Balance Cash flow (58,800) 59,730 6,857 59,485 7,400 59,301 8,500 59,159 9,620 58,807 66,239 Yield 13.38% 10.00% 13.00% 15.00% 10.00% Yield is based on monthly cash flows below. Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Cash flow (58,800) 571 571 571 571 571 571 571 571 571 571 571 571 617 617 617 617 617 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 617 617 617 617 617 617 617 708 708 708 708 708 708 708 708 708 708 708 708 802 802 802 802 802 802 802 802 802 802 802 802 619 619 619 619 619 619 619 619 619 619 619 59,426 Chapter 5 ARM with Payment Cap Loan assumptions Amount Initial rate Annual Payment cap Term Payments per year Margin Points $60,000 9.00% 7.50% 30 years 12 2.00% 2.00% Loan schedule Year 1 2 3 4 5 Index Rate 9.00% 12.00% 15.00% 17.00% 12.00% Unrestricted Capped Payment Payment 482.77 615.18 768.90 903.77 700.94 482.77 518.98 557.91 599.75 644.73 Balance Cash flow (58,800) 59,590 5,793 60,566 7,382 63,127 9,227 66,950 10,845 67,265 75,676 Yield Yield is based on monthly cash flows below. 15.60% 10.00% 13.00% 15.00% 10.00% Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Cash flow (58,800) 483 483 483 483 483 483 483 483 483 483 483 483 615 615 615 615 615 615 615 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 615 615 615 615 615 769 769 769 769 769 769 769 769 769 769 769 769 904 904 904 904 904 904 904 904 904 904 904 904 701 701 701 701 701 701 701 701 701 701 701 67,966 Chapter 5 Shared Appreciation Mortgage Part A Loan assumptions Home price Home apprec. Resale price Loan Term Rate Points Participation Payment Net Loan Holding Period Balance Appreciation Total to lender Eff Cost $110,000 25.00% $137,500 $95,000 30 years 8.00% 0.00% 30.00% $697.08 (using payment function) 95000 5 years $90,316.36 (present value of remaining payments) $8,250 $98,566.36 9.40% (using rate function) Part B Loan assumptions Home price Home apprec. Resale price Loan Term Rate Points Participation Payment Net Loan Holding Period Balance Appreciation Total to lender Eff Cost $110,000 76.79% $194,467 $95,000 30 years 8.00% 0.00% 30.00% Home Appreciation is solved using Solver by setting Eff cost (C45) as target cell and Home Appreciation (C30) as changing cell $697.08 (using payment function) 95000 5 years $90,316.36 (present value of remaining payments) $25,340 $115,656.47 12.00% (using rate function) Part C - I Loan assumptions Home price Home apprec. Resale price Loan Term Rate Points Participation Payment Net Loan Holding Period Balance Appreciation Total to lender Eff Cost $110,000 25.00% $137,500 $95,000 30 years 8.00% 2.00% 30.00% $697.08 (using payment function) 93100 5 years $90,316.36 (present value of remaining payments) $8,250 $98,566.36 9.90% (using rate function) Part C - II Loan assumptions Home price Home apprec. Resale price Loan Term Rate Points Participation Payment Net Loan Holding Period Balance Appreciation Total to lender Eff Cost $110,000 66.33% $182,960 $95,000 30 years 8.00% 2.00% 30.00% Home Appreciation is solved using Solver by setting Eff cost (C78) as target cell and Home Appreciation (C72) as changing cell $697.08 (using payment function) 93100 5 years $90,316.36 (present value of remaining payments) $21,888 $112,204.44 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 Loan $90,000 $80,000 $10,000 Points 0.00% 0.00% 25 Loan Proceeds Rate Term* Payment $90,000 8.50% 25 724.70 $80,000 8.00% 25 617.45 $10,000 25 107.25 12.87% *Should be the same term for both loans Incremental Cost PART B Holding period Loan $90,000 $80,000 $10,000 Points 25 Loan Proceeds Rate Term* Payment 2.00% $88,200 8.50% 25 724.70 0.00% $80,000 8.00% 25 617.45 $8,200 25 107.25 15.77% *Should be the same term for both loans Incremental Cost PART C Holding period Loan $90,000 $80,000 $10,000 Points 2.00% 0.00% 5 Loan Proceeds Rate Term* Payment $88,200 8.50% 25 724.70 $80,000 8.00% 25 617.45 $8,200 25 107.25 18.40% *Should be the same term for both loans Incremental Cost 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 Loan 1 Loan Points $220,000 Loan 2 Loan Points $180,000 $40,000 220000 20 Loan Proceeds Rate Term* Payment $220,000 9.50% 20 2,050.69 0.00% Loan Proceeds Rate Term* Payment 0.00% $180,000 9.00% 20 1,619.51 0.00% $40,000 13.00% 20 468.63 220000 20 2,088.14 Discount rate 9.00% Net Benefit in taking Loan 1 $4,162.20 PART B Holding period Loan 1 Loan Points $220,000 Loan 2 Loan Points $180,000 $40,000 220000 5 Loan Proceeds Rate Term* Payment $220,000 9.50% 20 2,050.69 0.00% 0.00% 0.00% Loan Proceeds Rate Term* Payment $180,000 9.00% 20 1,619.51 $40,000 13.00% 20 468.63 220000 20 2,088.14 Discount rate 9.00% Net Benefit in taking Loan 1 $1,804.02 PART C - I Holding period Loan 1 Loan Points $220,000 Loan 2 Loan Points $180,000 20 Loan Proceeds Rate Term* Payment $220,000 9.50% 20 2,050.69 0.00% 0.00% Loan Proceeds Rate Term* Payment $180,000 9.00% 20 1,619.51 $40,000 220000 0.00% $40,000 220000 13.00% 10 20 597.24 2,216.75 Discount rate 9.00% Net Benefit in taking Loan 1 ($1,268.97) PART C - II Holding period Loan 1 Loan Points $220,000 Loan 2 Loan Points $180,000 $40,000 220000 5 Loan Proceeds Rate Term* Payment 0.00% $220,000 9.50% 20 2,050.69 Loan Proceeds Rate Term* Payment 0.00% $180,000 9.00% 20 1,619.51 0.00% $40,000 13.00% 10 597.24 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 Purchase price Initial Rent Rental growth rate Property growth rate Insurance Maintenance Expense growth Marginal tax rate Property tax % Selling expenses Loan and equity calculations Annual debt service (payment) Annual loan constant Equity investment $9,580 7.98% $30,000 Summary loan schedule 1 $9,580 118,781 8,361 1,219 Property Data 0 1 $150,000 153,000 12,000 BTCF (owner) 1 2,250 500 500 9,580 12,830 Tax deductions from owning Property taxes Interest Total tax deductions $2,250 8,361 $10,611 $2,295 8,273 $10,568 $150,000 $12,000 2.00% 2.00% $500 $500 2.00% 28.00% 1.50% 7.00% Loan Information: Loan-to-value ratio Loan amount Interest rate Loan term (years) Payments (per year) End of year Payment Balance Interest Principal 2 $9,580 117,474 8,273 1,307 Property value Rents 2 156,060 12,240 Year Property taxes Insurance Maintenance Principal and Interest Cash Outflows before taxes 2 2,295 510 510 9,580 12,895 Tax savings 2,971 Net cost of owning 12,830 2,971 9,859 Net cost of renting $12,000 2,959 Cash Outflows before taxes Tax savings After tax cost 12,895 2,959 9,936 Rents $12,240 Cost of renting Cost of owning After Tax Cash Flow Own vs Rent Net Cash Flow from Owning Before Sale $12,000 $12,240 9,859 9,936 $2,141 $2,304 BT Cash Flow - Sale 1 153,000 10,710 118,781 23,509 AT Cash Flow - Sale Sales price Selling Costs Mortgage balance Benefit from sale (own - rent) 2 156,060 10,924 117,474 27,662 Sales price Less selling costs Less purchase price Gain on sale Exclusion Taxable gain Tax After tax cash flow $153,000 10,710 150,000 (7,710) 0 0 0 23,509 $156,060 10,924 150,000 (4,864) 0 0 0 27,662 Year Sold 1 2 3 4 5 Cash savings and IRR summary Cash flows 0 1 2 -30,000 $25,650 -30,000 2,141 29,966 -30,000 2,141 2,304 -30,000 2,141 2,304 -30,000 2,141 2,304 ersonal Residence Loan Information: 80.00% $120,000 7.00% 30 12 -value ratio 3 $9,580 116,072 8,179 1,402 4 $9,580 114,569 8,077 1,503 5 $9,580 112,958 7,969 1,612 3 159,181 12,485 4 162,365 12,734 5 165,612 12,989 3 2,341 520 520 9,580 12,962 4 2,388 531 531 9,580 13,029 5 2,435 541 541 9,580 13,098 $2,341 8,179 $10,520 $2,388 8,077 $10,465 $2,435 7,969 $10,404 2,946 2,930 2,913 12,962 2,946 10,016 13,029 2,930 10,099 13,098 2,913 10,185 $12,485 $12,734 $12,989 Sale $12,485 10,016 $2,469 $12,734 10,099 $2,635 $12,989 10,185 $2,804 3 159,181 11,143 116,072 31,966 4 162,365 11,366 114,569 36,430 5 165,612 11,593 112,958 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% 9.63% 12.34% 13.71% 34,435 2,469 2,469 39,065 2,635 43,865 Chapter 9 Effective Rents Spreadsheet Limitations: Net Lease with free rent has begins payment year two. Inerest Rate Year 1 Rents Net Lease with Steps Net with CPI Adjustment Gross Lease Gross with Exp. Stop Gross with Exp. Stop &CPI Year 2 Rents Net Lease with Free Rent Data Input Box: 0.1 Adjustments Step Amount 10 Percent CPI Adj. 11 Yr 2 CPI 17.5 CPI Growth 15.5 Yr 1 Expenses 14.5 Expense Growth Expense Stop 14.5 1 1 0.02 0.01 4 0.5 4 Year Net Rent Average Rent Present Value Effective Rent 1 $ NET LEASE with STEPS: 2 3 10.00 11.00 12.00 $ 12.00 44.77 11.81 4 13.00 5 14.00 Year Net Rent Average Rent Present Value Effective Rent 1 0 NET LEASE with FREE RENT: 2 3 14.50 15.50 12.80 45.76 12.07 4 16.50 5 17.50 Year Expected CPI Net Rent Average Rent Present Value Effective Rent NET LEASE with CPI ADJUSTMENT: 1 2 3 2.00% 3.00% 11.00 11.22 11.56 11.68 44.00 11.61 4 4.00% 12.02 5 5.00% 12.62 Year Gross Rent Less Expenses Net Rent 1 GROSS LEASE: 2 17.50 17.50 4.00 4.50 13.50 13.00 3 17.50 5.00 12.50 4 17.50 5.50 12.00 5 17.50 6.00 11.50 Average Rent Present Value Effective Rent 12.50 47.74 12.59 Year Gross Rent Less Expenses Plus Reimbursement Net Rent Average Rent Present Value Effective Rent GROSS LEASE with EXPENSE STOP: 1 2 3 15.50 15.50 15.50 4.00 4.50 5.00 0.00 0.50 1.00 11.50 11.50 11.50 11.50 43.59 11.50 4 15.50 5.50 1.50 11.50 5 15.50 6.00 2.00 11.50 GROSS LEASE with EXPENSE STOP and CPI ADJUSTMENT: Year 1 2 3 4 Expected CPI 2.00% 3.00% 4.00% Gross Rent 14.50 14.79 15.23 15.84 Less Expenses 4.00 4.50 5.00 5.50 Plus Reimbursement 0.00 0.50 1.00 1.50 Net Rent 10.50 10.79 11.23 11.84 Average Rent 11.40 Present Value 42.84 Effective Rent 11.30 5 5.00% 16.64 6.00 2.00 12.64 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 Projected Increase in NOI Operating Expenses Loan: Debt Coverage Ratio Interest Term Payments per year Holding Period Terminal Cap Rate Before-tax Equity Yield 50000 0.03 per year 0.3 of EGI 1.2 0.11 20 years 12 5 years 0.11 0.12 Loan Constant Loan Amount Loan Balance 12.3863% 336,394 305,493 Year 67.02% LTV in year 1 50,000 5 3 53,045 4 54,636 5 56,275 Net Operating Income 2 51,500 Debt Service Before-tax Cash Flow DCR Cash Flows from Sale in Year Sales Price Mortgage Balance Before-tax Cash Flow 41,667 8,333 1.20 5 526,943 305,493 221,450 41,667 9,833 1.24 41,667 11,378 1.27 41,667 12,970 1.31 41,667 14,609 1.35 Present Value of Operations at Present value of Reversion at Total Equity Total Equity Mortgage Total Present Value Check: Cash Flow Summary 12.00% 12.00% 39,910 125,657 165,567 165,567 336,394 501,961 Year BTCF BTIRR 0 (165,567) 12.00% 1 8,333 2 9,833 3 11,378 4 12,970 based on terminal cap rate. 6 57,964 5 236,059 Chapter 10 Highest and Best Use Analysis NOI yr 1 Return or r Growth or g Building Costs Office $500,000 13.00% 3.00% 4,000,000 Data Input Box: Retail $600,000 12.00% 4.00% 4,000,000 Apartment $400,000 12.00% 3.00% 4,000,000 Use Office Retail Apartment Warehouse Year 1 NOI $500,000 600,000 400,000 400,000 HIGHEST and BEST USE ANALYSIS: Implied Property Building R Value Costs 10.00% 5,000,000 4,000,000 8.00% 7,500,000 4,000,000 9.00% 4,444,444 4,000,000 8.00% 5,000,000 4,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 Address City State Total Units Unit Size Building Size (SF) Analysis Begin Date Holding Period Discount Rate Terminal Rate Selling Cost Inputs Name Units Monthly Rent Lease Term (Yrs) Market Monthly Rent Market Rent Increase Laundry Income/unit/year Laundry Income increase Market Vacancy Rate Credit Loss Rate Oakwood 1234 Elm Street Anywhere USA 95 1,100 104,500 1/1/2000 5 11.00% 9.00% 5.00% Apartment Unit Two Bedroom 95 $1,200.00 1 $1,250.00 3.00% $120.00 3.00% 5.00% 1.00% % of EGI 0.00% 0.00% 0.00% 0.00% 0.00% 12.00% 0.00% 0.00% $ Amount $87,000.00 $20,000.00 $0.00 $0.00 $8,000.00 $0.00 $45,000.00 $15,000.00 $ per Unit $0.00 $0.00 $150.00 $550.00 $0.00 $0.00 $0.00 $0.00 Real Estate Taxes Office Expenses Insurance Repairs & Maintenance Advertising Management Utilities Miscellaneous Expenses Year Income: Current Rent Market Rent from lease renewals 1 Outputs 2 3 $0 $1,511,783 $1,368,000 $0 $0 $1,467,750 Laundry Income Potential Gross Income (PGI) Less: Vacancy Less: Credit Loss Effective Gross Income (EGI) Expenses: Real Estate Taxes Office Expenses Insurance Repairs & Maintenance Advertising Management Utilities Miscellaneous Expenses Total Expenses Net Operating Income (NOI) Expenses % of EGI $11,400 $11,742 $1,379,400 $1,479,492 $68,400 $73,388 $13,794 $14,795 $1,297,206 $1,391,310 $12,094 $1,523,877 $75,589 $15,239 $1,433,049 $87,000 $20,000 $14,250 $52,250 $8,000 $155,665 $45,000 $15,000 $397,165 $900,041 30.62% $89,175 $20,600 $14,678 $53,818 $8,240 $166,957 $46,350 $15,450 $415,267 $976,042 29.85% $91,404 $21,218 $15,118 $55,432 $8,487 $171,966 $47,741 $15,914 $427,279 $1,005,770 29.82% Net Operating Income PV Factors Present Value $900,041 0.90090 $810,848 $976,042 0.81162 $792,178 $1,005,770 0.73119 $735,410 Resale Selling Cost Net Resale PV Factor PVResale $12,227,545 $611,377 $11,616,168 0.59345 $6,893,630 Implied Change in Value apter 10 partment Example Change % 2.50% 3.00% 3.00% 3.00% 3.00% 0.00% 3.00% 3.00% 4 $0 $1,557,136 5 6 $0 $0 $1,603,850 $1,651,966 $12,457 $1,569,593 $77,857 $15,696 $1,476,040 $12,831 $13,216 $1,616,681 $1,665,181 $80,193 $82,598 $16,167 $16,652 $1,520,322 $1,565,931 $93,689 $21,855 $15,571 $57,095 $8,742 $177,125 $49,173 $16,391 $439,641 $1,036,400 29.79% $96,032 $22,510 $16,039 $58,808 $9,004 $182,439 $50,648 $16,883 $452,361 $98,433 $23,185 $16,520 $60,572 $9,274 $187,912 $52,167 $17,389 $465,452 $1,067,960 $1,100,479 29.75% 29.72% $1,036,400 0.65873 $682,709 $1,067,960 $1,100,479 0.59345 $633,782 Sum PV NOI $3,654,927 PV Resale $6,893,630 Value $10,548,557 plied Change in Value 15.92% Chapter 11 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 Gross square feet of building Net rentable square feet of building Projected Increase in Market Rent Management costs Estimated increase in CPI Vacancy rate starting year 4 $15.00 100,000 96,000 4.00% 5.00% 4.00% 5.00% per s.f. s.f. s.f. per year of Effective Gross Income per year per year Lease Calculations: Given Lease Data, Solve for N SUMMARY LEASE INFORMATION Tenant Sq. ft. Current Rent per s.f. $14.00 $14.00 $14.00 $14.50 $15.00 $15.00 Current Rent $980,000 0 0 145,000 0 240,000 1,365,000 Tenant 1 Tenant 2 Tenant 3 Tenant 4 Tenant 5 Tenant 6 Total 70,000 0 0 10,000 0 16,000 96,000 Note: Expense stop excludes managemen Summary of Expense Information Dollars Property tax Insurance Utilities Janitorial Maintenance Subtotal (before mgt) Management Total $148,800 14,400 120,000 76,800 67,200 427,200 $68,250 $495,450 per s.f. 1.55 0.15 1.25 0.80 0.70 4.45 0.71 $5.16 Year Tenant 1 Tenant 2 Tenant 3 Tenant 4 Tenant 5 Tenant 6 Total base Tenant 1 Tenant 4 Tenant 6 Total CPI Total Base and CPI 1 980,000 0 0 145,000 0 240,000 1,365,000 19,600 2,900 0 22,500 1,387,500 PROJECTED BASE INCOME 2 980,000 0 0 145,000 0 240,000 1,365,000 39,592 5,858 4,800 50,250 1,415,250 Expense Reinmursements $39,445 0 0 3,135 0 1,816 44,396 1,459,646 0 1,459,646 Tenant 1 Tenant 2 Tenant 3 Tenant 4 Tenant 5 Tenant 6 Total Reimbursements Potential Gross Income Vacancy Effective Gross Income $31,500 0 0 2,000 0 0 33,500 1,421,000 0 1,421,000 SUMMARY OF OPERATING EXPENSES Reimbursable expenses 148,800 14,976 126,000 79,104 69,216 438,096 4.56 Property tax Insurance Utilities Janitorial Maintenance Total before management per s.f. 148,800 14,400 120,000 76,800 67,200 427,200 4.45 Management Total Expenses Non reimbursable expenses 71,050 72,982 Total expenses 498,250 511,078 Year Base income Plus CPI Adjustment Plus Reimbursements Total Potential Income Less Vacancy Effective Gross Income Less operating expenses: Reimbursable expenses Non reimbursable expenses NOI PROJECTED NET OPERATING INCOME 1 2 $1,365,000 $1,365,000 22,500 50,250 $33,500 $44,396 $1,421,000 $1,459,646 0 0 1,421,000 1,459,646 427,200 71,050 $922,750 438,096 72,982 $948,568 Asking price Capitalization rate Average compound increase in NOI $8,500,000 10.86% (1st year NOI / Price) 2.63% er 11 Lease Data, Solve for NOI ginning of year 4. E INFORMATION Remaining term (yrs) 3 3 3 4 5 5 Expense stop per s.f. $4.00 4.00 4.00 4.25 4.45 4.45 CPI adjustment 50.00% 50.00% 50.00% 50.00% 50.00% 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 980,000 0 0 145,000 0 240,000 1,365,000 59,984 8,875 9,696 78,555 1,443,555 4 1,181,107 0 0 145,000 0 240,000 1,566,107 0 11,953 14,690 26,643 1,592,750 5 1,181,107 0 0 175,479 0 240,000 1,596,586 23,622 0 19,784 43,406 1,639,992 6 1,181,107 0 0 175,479 0 291,997 1,648,583 47,717 3,510 0 51,226 1,699,809 mursements $58,570 0 0 5,867 0 6,187 70,625 1,514,180 0 1,514,180 $0 0 0 7,099 0 8,158 15,256 1,608,006 80,400 1,527,606 $8,979 0 0 0 0 10,210 19,189 1,659,181 82,959 1,576,222 $18,334 0 0 1,336 0 0 19,670 1,719,479 85,974 1,633,505 RATING EXPENSES e expenses 163,680 15,575 132,300 81,477 71,292 464,325 4.84 163,680 16,198 138,915 83,921 73,431 476,146 4.96 163,680 16,846 145,861 86,439 75,634 488,460 5.09 163,680 17,520 153,154 89,032 77,903 501,289 5.22 able expenses 75,709 540,034 76,380 552,526 78,811 567,271 81,675 582,964 penses PERATING INCOME 3 $1,365,000 78,555 $70,625 $1,514,180 0 1,514,180 464,325 75,709 $974,146 4 $1,566,107 26,643 $15,256 $1,608,006 80,400 1,527,606 476,146 76,380 $975,080 5 $1,596,586 43,406 $19,189 $1,659,181 82,959 1,576,222 488,460 78,811 $1,008,951 6 $1,648,583 51,226 $19,670 $1,719,479 85,974 1,633,505 501,289 81,675 $1,050,541 (years 1 to 6) Chapter 11 Basic Investment Analysis Spreadsheet Assumptions: 5 year holding period. Purchase price Resale Price Data Input Box: $8,500,000 $9,700,000 Year Purchase price Net Operating Income (NOI) (from Ch11_Lease) Sales Price Cash flow IRR Discount rate Present Value of Cash flows 14.00% BEFORE TAX CASH FLOW FROM OPERATIONS: 0 1 2 ($8,500,000) 922,750 ($8,500,000) 13.46% 922,750 948,568 948,568 $8,336,061.59 lysis OPERATIONS: 3 4 5 974,146 974,146 975,080 975,080 1,008,951 9,700,000 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. Purchase price Building Value Ordinary income tax rate Loan-to-Value Loan Interest Loan term Payments per year Resale Price Holding Period Selling costs Equity discount rate Reinvestment rate Equity Loan Annual Loan Payment Mortgage Balance 8500000 7225000 0.36 0.7 0.1 20 years Building Square Footage: 12 Gross 9700000 Net 5 years 0 of sale price 0.13 0.06 2,550,000 5,950,000 689,025 5,343,245 Data Input Box: Tax Considerations: Depreciation (in years) Capital gains tax rate Depreciation recapture year 5 End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 3 689,025 689,025 5,851,543 5,742,776 590,569 580,259 98,457 108,767 689,025 5,622,620 568,869 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) Less Debt Service (DS) Before Tax Cash Flow $922,750 689,025 $233,725 $948,568 689,025 $259,542 $974,146 689,025 $285,121 Less Tax After-tax Cash Flow 52,893 $180,832 65,899 $193,643 79,207 $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 Accumulated Depreciation Adjusted Basis Capital Gain Depreciation recapture Price appreciation Tax on price appreciation Tax on depreciation recapture Total capital gain tax After-Tax Cash Flow from Sale 8,500,000 926,282 7,573,718 2,126,282 926,282 1,200,000 180,000 231,571 411,571 3,945,184 CASH FLOW SUMMARY BTIRR on Equity Year Before-Tax Cash Flow After-Tax Cash Flow Before-Tax IRR (BTIRR) After-Tax IRR (ATIRR) Effective Tax Rate 0 ($2,550,000) ($2,550,000) 20.08% 15.75% 21.57% Set purchase price (C10) $696,943 equal to Cell D87 to make 2,141,288 "circular" and solve for 2,838,231 value (2,550,000) 288,231 14.73% (also called Adjusted IRR) 1 $233,725 $180,832 2 $259,542 $193,643 AFTER-TAX NET PRESENT VALUE PV of ATCFo PV of ATCFs Total Present Value Less Original Equity Investment After-Tax Net Present Value Modified AT IRR (using reinvestment rate) 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) Profitablility Index (18% discount rate) BT Equivalent Yield 771,455 1.30 24.61% Analysis d in year they are incurred. No PAL limitations considered. 39 0.15 0.25 100000 96000 4 689,025 5,489,883 556,288 132,738 5 689,025 5,343,245 542,388 146,637 OPERATIONS: 975,080 556,288 185,256 233,536 84,073 1,008,951 542,388 185,256 281,306 101,270 $975,080 $1,008,951 689,025 689,025 $286,054 $319,925 84,073 $201,981 101,270 $218,655 3 $285,121 $205,913 4 $286,054 $201,981 5 $4,676,680 $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. Purchase NOI year 1 Growth-NOI Loan-to-Value Loan Interest (Payment) Loan Interest (Accural) Loan Amortization Term Loan Due Payments per year Equity Participation Partic. on Excess over Equity Participation Land Lease Payment Appreciation rate Lender's Equity Ownership Holding Period Selling costs Equity Loan Annual Loan Payment Annual Accrual Amount Mortgage Balance Data Input Box: 1000000 100000 0.03 0.7 0.08 0.08 15 years 15 years 12 0.5 of NOI 100000 0.45 of sales gain 0 0.03 0 5 years 0 of sale price $300,000 700,000 80,275 80,275 551,364 Tax Considerations: Building Value Depreciation Tax rate year 5 End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 80,275 80,275 674,815 647,540 55,090 53,000 25,185 27,275 3 80,275 618,001 50,736 29,539 Year NOI Less Debt Service Less Land Lease Payment Before-tax Cash Flow Equity Participation Before-Tax Cash Flow SUMMARY CASH FLOW INFORMATION: 1 2 3 100,000 103,000 106,090 80,275 80,275 80,275 0 0 0 19,725 22,725 25,815 0 1,500 3,045 19,725 21,225 22,770 NOI Less: Interest Depreciation Participation Less Land Lease Payment Taxable Income Tax (Savings) After-Tax Cash Flow 100,000 55,090 32,727 0 0 12,183 3,411 16,314 103,000 53,000 32,727 1,500 0 15,773 4,417 16,809 106,090 50,736 32,727 3,045 0 19,582 5,483 17,287 CASH FLOW FROM SALE: Sale Price Sales costs Mortgage Balance Before-tax Cash Flow Participation in Gain Before-tax cash flow Sale Price Sales Costs Participation Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale EQUITY ANALYSIS: 1 19,725 1,000,000 163,636 836,364 251,237 70,346 465,891 1,159,274 0 71,673 1,159,274 0 551,364 607,910 71,673 536,237 Year BTCF after Participation BTIRR on Equity Year ATCF ATIRR on Equity Effective Tax Rate 0 (300,000) 18.36% 0 (300,000) 14.07% 23.37% 2 21,225 1 16,314 2 16,809 Year NOI BTIRR on Property Year ATCF-no loan PROPERTY ANALYSIS: 0 1 (1,000,000) 100,000 13.00% 0 (1,000,000) 1 81,164 2 103,000 2 83,324 ATIRR on Property 9.98% Year Debt service Loan balance Participation Loan amount Cash flows to lender Lender's IRR 0 Lender's Yield: 1 80,275 0 2 80,275 1,500 81,775 -700000 -700000 10.17% 80,275 Calculation of Lender's IRR: Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Cash flow -700000 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6689.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6814.56 6943.31 6943.31 6943.31 6943.31 6943.31 6943.31 6943.31 6943.31 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 80,275 586,010 48,284 31,991 5 80,275 551,364 45,629 34,646 4 109,273 80,275 0 28,998 4,636 24,362 5 112,551 80,275 0 32,276 6,275 26,001 109,273 48,284 32,727 4,636 0 23,625 6,615 17,747 112,551 45,629 32,727 6,275 0 27,919 7,817 18,183 3 22,770 4 24,362 5 562,238 3 17,287 4 17,747 5 484,074 3 106,090 4 109,273 5 1,271,825 3 85,548 4 87,840 5 1,179,128 3 80,275 3,045 83,320 4 80,275 4,636 84,911 5 80,275 551,364 77,949 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. Asking Price NOI year 1 Growth-NOI Loan-to-Value Loan Interest (Payment) Loan Interest (Accural) Loan Amortization Term Loan Due Payments per year Equity Participation Partic. on Excess over Equity Participation Land Lease Payment Appreciation rate Lender's Equity Ownership Holding Period Selling costs Equity Loan Annual Loan Payment Annual Accrual Amount Mortgage Balance Data Input Box: 1000000 100000 0.03 0.7 0.1 0.1 15 years 15 years 12 0 of NOI 0 0 of sales gain 7800 0.03 0 5 years 0 of sale price $270,000 630,000 81,240 81,240 512,295 year End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 81,240 81,240 610,900 589,800 62,140 60,140 19,100 21,100 SUMMARY CASH FLOW INFORMATION: 1 2 100,000 103,000 81,240 81,240 7,800 7,800 10,960 13,960 0 0 10,960 13,960 Year NOI Less Debt Service Less Land Lease Payment Before-tax Cash Flow Equity Participation Before-Tax Cash Flow NOI Less: Interest Depreciation Participation Less Land Lease Payment Taxable Income Tax (Savings) After-Tax Cash Flow 100,000 62,140 32,727 0 7,800 (2,668) (747) 11,707 103,000 60,140 32,727 0 7,800 2,332 653 13,307 CASH FLOW FROM SALE: Sale Price Sales costs Mortgage Balance Before-tax Cash Flow Participation in Gain Before-tax cash flow Sale Price Sales Costs Participation Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale EQUITY ANALYSIS: 1 10,960 900,000 163,636 736,364 306,983 1,043,347 0 0 Year BTCF after Participation BTIRR on Equity Year ATCF ATIRR on Equity Effective Tax Rate 0 (270,000) 19.16% 0 (270,000) 14.98% 21.80% 1 11,707 Year NOI BTIRR on Property Year ATCF-no loan ATIRR on Property PROPERTY ANALYSIS: 0 1 (1,000,000) 100,000 11.24% 0 (1,000,000) 7.80% 1 81,164 Year Land cash flows Effective Cost Effective Cost of Sale-Leaseback of Land 0 1 -100,000 10.25% 7,800 Calculation of Land purchaser's IRR: Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Cash flow -100000 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 IRR 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 650.00 116577.41 10.25% Chapter 12 easeback of Land losses used in year they are incurred. ells that do not apply to zero. put Box: Tax Considerations: Building Value Depreciation Tax rate 900000 27.5 years 0.28 5 N INFORMATION: 3 81,240 566,491 57,931 23,309 4 81,240 540,741 55,490 25,750 5 81,240 512,295 52,794 28,446 LOW INFORMATION: 3 106,090 81,240 7,800 17,050 0 17,050 4 109,273 81,240 7,800 20,233 0 20,233 5 112,551 81,240 7,800 23,511 0 23,511 106,090 57,931 32,727 0 7,800 7,632 2,137 14,913 109,273 55,490 32,727 0 7,800 13,255 3,711 16,521 112,551 52,794 32,727 0 7,800 19,230 5,384 18,126 E: 1,043,347 0 512,295 531,052 0 531,052 85,955 445,097 QUITY ANALYSIS: 2 13,960 3 17,050 4 20,233 5 554,563 2 13,307 3 14,913 4 16,521 5 463,223 PERTY ANALYSIS: 2 103,000 3 106,090 4 109,273 5 1,155,898 2 83,324 3 85,548 4 87,840 5 1,047,592 t of Sale-Leaseback of Land 2 7,800 3 7,800 4 7,800 5 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. Asking Price NOI year 1 Growth-NOI Loan-to-Value Loan Interest (Payment) Loan Interest (Accural) Loan Amortization Term Loan Due Payments per year Equity Participation Partic. on Excess over Equity Participation Land Lease Payment Appreciation rate Lender's Equity Ownership Holding Period Selling costs Equity Loan Annual Loan Payment Annual Accrual Amount Mortgage Balance Data Input Box: 1000000 100000 0.03 0.7 0.085 0.085 30 years 15 years 12 0 of NOI 0 0 of sales gain 0 0.03 0.65 5 years 0 of sale price $300,000 700,000 64,589 64,589 668,432 year End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 64,589 64,589 694,708 688,949 59,297 58,829 5,292 5,759 SUMMARY CASH FLOW INFORMATION: 1 2 100,000 103,000 64,589 64,589 0 0 35,411 38,411 0 0 35,411 38,411 100,000 103,000 Year NOI Less Debt Service Less Land Lease Payment Before-tax Cash Flow Equity Participation Before-Tax Cash Flow NOI Less: Interest Depreciation Participation Less Land Lease Payment Taxable Income Tax (Savings) After-Tax Cash Flow 59,297 32,727 0 0 7,976 2,233 33,178 58,829 32,727 0 0 11,443 3,204 35,207 CASH FLOW FROM SALE: Sale Price (net of portion transferred to lender) Sales costs Mortgage Balance Before-tax Cash Flow Participation in Gain Before-tax cash flow Sale Price Sales Costs Participation Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale EQUITY ANALYSIS: 0 1 (300,000) 35,411 18.40% 0 (300,000) 13.06% 29.02% 1 33,178 1,000,000 163,636 836,364 322,910 1,159,274 0 0 Year BTCF after Participation BTIRR on Equity Year ATCF ATIRR on Equity Effective Tax Rate Year NOI BTIRR on Property Year ATCF-no loan ATIRR on Property PROPERTY ANALYSIS: 0 1 (1,000,000) 100,000 13.00% 0 (1,000,000) 9.66% 1 81,164 Year Debt service Loan balance Conversion Loan amount Cash flows to lender Lender's IRR 0 Lender's Yield: 1 64,589 -700000 -700000 10.40% 64,589 Calculation of Lender's IRR: Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Cash flow -700000 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 5382.39 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% apter 12 ertible Loan sses used in year they are incurred. s that do not apply to zero. Box: Tax Considerations: Building Value Depreciation Tax rate 900000 27.5 years 0.28 5 NFORMATION: 3 64,589 682,680 58,320 6,269 4 64,589 675,858 57,766 6,823 5 64,589 668,432 57,163 7,426 W INFORMATION: 3 106,090 64,589 0 41,501 0 41,501 106,090 4 109,273 64,589 0 44,684 0 44,684 109,273 5 112,551 64,589 0 47,962 0 47,962 112,551 58,320 32,727 0 0 15,043 4,212 37,289 57,766 32,727 0 0 18,779 5,258 39,426 57,163 32,727 0 0 22,661 6,345 41,617 405,746 0 0 405,746 0 405,746 90,415 315,331 Y ANALYSIS: 2 38,411 3 41,501 4 44,684 5 453,708 2 35,207 3 37,289 4 39,426 5 356,948 RTY ANALYSIS: 2 103,000 3 106,090 4 5 109,273 1,271,825 2 83,324 3 85,548 4 5 87,840 1,159,059 der's Yield: 2 64,589 3 64,589 4 64,589 5 64,589 0 753,528 818,117 64,589 64,589 64,589 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)". Asking Price NOI year 1 Growth-NOI Loan-to-Value Loan Interest (Payment) Loan Interest (Accural) Loan Amortization Term Loan Due Payments per year Appreciation rate Holding Period Selling costs Equity Loan Annual Loan Payment Annual Accrual Amount Mortgage Balance Data Input Box: 1000000 Tax Considerations: 100000 Building Value 0.03 Depreciation 0.7 Tax rate 0.08 0.1 30 years 15 years 12 0.03 5 years 0 of sale price $300,000 700,000 61,636 73,716 753,972 900000 27.5 0.28 year 5 End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 3 61,636 61,636 61,636 708,758 718,433 729,121 70,394 71,311 72,324 (8,758) (9,675) (10,688) SUMMARY CASH FLOW INFORMATION: 1 2 3 100,000 103,000 106,090 61,636 61,636 61,636 4 61,636 740,928 73,444 (11,807) Year NOI Less Debt Service 4 109,273 61,636 Before-Tax Cash Flow NOI Less: Interest Depreciation 38,364 100,000 70,394 32,727 41,364 103,000 71,311 32,727 44,454 106,090 72,324 32,727 47,636 109,273 73,444 32,727 Taxable Income Tax (Savings) After-Tax Cash Flow (3,121) (874) 39,238 (1,039) (291) 41,655 1,038 291 44,163 3,102 869 46,768 CASH FLOW FROM SALE: Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Before-tax cash flow Sale Price Sales Costs Original Cost Basis Accumulated Depreciation Adjusted Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale 1,000,000 163,636 836,364 322,910 1,159,274 0 1,159,274 0 753,972 405,302 405,302 90,415 314,887 EQUITY ANALYSIS: 1 2 38,364 Year BTCF after Participation BTIRR on Equity Year ATCF ATIRR on Equity Effective Tax Rate 0 (300,000) 19.27% 0 (300,000) 15.25% 20.88% 41,364 3 44,454 1 39,238 2 41,655 3 44,163 Year NOI BTIRR on Property Year ATCF-no loan ATIRR on Property 0 (1,000,000) 13.00% 0 (1,000,000) 9.66% PROPERTY ANALYSIS: 1 2 100,000 103,000 3 106,090 1 81,164 2 83,324 3 85,548 Year Debt service 0 Lender's Yield: 1 61,636 2 61,636 3 61,636 Loan balance Loan amount Cash flows to lender Lender's IRR -700000 -$700,000 10.00% $61,636 $61,636 $61,636 Calculation of Lender's IRR: Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Cash flow -700000 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 IRR 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 5136.35 759108.54 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 47,636 5 456,217 4 46,768 5 364,361 4 109,273 5 1,271,825 4 87,840 5 1,159,059 4 61,636 5 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) Cash Flow Present Value 1,777,383 4,356,755 Year 5 PV of BTCFo PV of BTCFs Total Present Value $772,373 1,777,383 $2,549,756 Ratio of Present Values BTCFo to Total Present Value BTCFs to Total Present Value 30% 70% Calculating Variance of Returns *Numbers from Exhibit 10-11 Scenario Pessimistic Most Likely Optimistic Return (or R) 6.17% 19.64% 28.64% Office Building Probability (P) (Return x Probability) 25% 1.54% 50% 9.82% 25% 7.16% 18.52% 0.64% 8.02% Expected Return Variance Standard Deviation Scenario Pessimistic Return (or R) 10% Apartment Building Probability (P) (Return x Probability) 25% 2.50% Most Likely Optimistic 15% 20% 50% 25% 7.50% 5.00% 15.00% 0.13% 3.61% Expected Return Variance Standard Deviation Scenario Pessimistic Most Likely Optimistic Return (or R) 5.00% 20% 35% Hotel Probability (P) (Return x Probability) 25% 1.25% 50% 10.00% 25% 8.75% 20.00% 1.15% 10.71% Expected Return Variance Standard Deviation SUMMARY Expected Return 18.52% 15.00% 20.00% Property Office Apartment Hotel Risk 8.02% 3.61% 10.71% ted Returns and Variance eturns R - Expected R -12.35% 1.12% 10.12% 18.52% 0.64% 8.02% P x (R-Expected R)2 0.3815% 0.0062% 0.2559% R - Expected R -5.00% P x (R-Expected R)2 0.0625% -3.52% 1.48% 15.00% 0.13% 3.61% 0.0620% 0.0055% R - Expected R -13.52% 1.48% 16.48% 20.00% 1.15% 10.71% P x (R-Expected R)2 0.4571% 0.0109% 0.6788% Chapter 13 Risk and Leverage Pessimistic Year Purchase NOI Resale Total Cash Flow IRR 0 -100,000 1 9,000 -100,000 4.33% Most Likely Year Purchase NOI Resale Total Cash Flow IRR 0 -100,000 1 9,000 -100,000 7.27% Optimistic Year Purchase NOI Resale Total Cash Flow IRR 0 -100,000 1 9,000 -100,000 10.21% 9,000 2 9,270 9,270 3 9,548 9,548 9,000 2 9,000 9,000 3 9,000 9,000 9,000 2 8,730 8,730 3 8,468 8,468 Scenario Pessimistic Most Likely Optimistic Return (or R) 4.33% 7.27% 10.21% Probability (P) 30% 50% 20% No Loan (Return x Probability) 1.30% 3.64% 2.04% 6.98% 0.04% 2.06% Expected Return Variance Standard Deviation Loan to value Interest rate Loan term 70.00% 6.00% 25 years Pessimistic Year Purchase Loan NOI Payment Resale Loan balance Total Cash Flow IRR 0 -100,000 70,000 1 2 3 9,000 -5,476 8,730 -5,476 8,468 -5,476 -30,000 -0.45% 3,524 3,254 2,992 Most Likely Year Purchase Loan NOI Payment Resale Loan balance Total Cash Flow IRR 0 -100,000 70,000 1 2 3 9,000 -5,476 9,000 -5,476 9,000 -5,476 -30,000 10.22% 3,524 3,524 3,524 Optimistic Year Purchase Loan NOI Payment Resale Loan balance Total Cash Flow IRR 0 -100,000 70,000 1 2 3 9,000 -5,476 9,270 -5,476 9,548 -5,476 -30,000 18.59% 3,524 3,794 4,072 Scenario Pessimistic Most Likely Optimistic Return (or R) -0.45% 10.22% 18.59% Probability (P) 30% 50% 20% Loan (Return x Probability) -0.13% 5.11% 3.72% 8.69% 0.49% 6.99% Expected Return Variance Standard Deviation apter 13 nd Leverage stic 4 8,214 8,214 5 7,968 77,286 85,254 6 7,729 kely 4 9,000 9,000 5 9,000 90,000 99,000 6 9,000 tic 4 9,835 9,835 5 10,130 104,335 114,464 6 10,433 R - Expected R -2.64% 0.29% 3.23% P x (R-Expected R) 2 0.02% 0.00% 0.02% stic 4 5 6 8,214 -5,476 2,738 7,968 -5,476 77,286 -62,808 16,970 7,729 kely 4 5 6 9,000 -5,476 3,524 9,000 -5,476 90,000 -62,808 30,716 9,000 tic 4 5 6 9,835 -5,476 4,359 10,130 -5,476 104,335 -62,808 46,181 10,433 R - Expected R -7.43% 3.24% 11.62% P x (R-Expected R) 2 0.17% 0.05% 0.27% Chapter 13 Retail Example Input Assumptions Building Name Purchase price State Building Size (SF) Analysis Begin Date Holding Period Discount Rate Terminal Rate Selling Cost Tenant 1 Drug Store 25,000 $12.00 5 $12.00 3.00% Westgate Shopping Center $ USA $ 8,500,000.00 100,000.00 7/1/2000 5 11.00% 10.50% 3.00% Tenant 2 FoodStore4 60,000 $8.50 14 $9.00 3.00% Name Tenant Size Rent SF Lease Term (Yrs) Market Rent Market Rent Increase Sales Volume/SF Sales Annual Change Breakpoint/SF Overage % MLA Lease Term MLA Tenant Improvement MLA Lease Commissions MLA TI Annual Change Recoveries Lease Only Property Tax Insurance Maintenance Market Vacancy Rate 10 $0.00 0.00% 0.00% 5 $0.00 0.00% 0.00% Net Net Net 5.00% % of EGI $0.00 Net $0.00 Net $0.00 Net Management Fee Property Tax Insurance Maintenance 6.00% 0.00% 0.00% 0.00% $ Amount $0 $40,000 $0 $0 $ per SF $0.00 $0.00 $0.18 $0.75 Restaurant Sales Volume $250.00 $257.50 $0.00 Year Rental Income: Drug Store Rent Drug Store Renewal Rent Drug Store Recoveries Food Store Rent Food Store Renewal Rent Food Store Recoveries Restaurant Rent Restaurant Renewal Rent Restaurant Recoveries Restaurant Overage Rent Vacant Space 1 Rent Vacant Space 1 Renewal Rent Vacant Space 1 Recoveries Vacant Space 2 Rent Vacant Space 2 Renewal Rent Vacant Space 2 Recoveries Total Income Vacant Space Vacancy General Vacancy Effective Gross Income Management Fee Property Tax Insurance CAM Total Expenses NOI Vacant Space TIs In-Line Space TIs Total TIs Vacant Space Leasing Commissoins In-Line Space Leasing Commissions Total Leasing Commissions Cash Flow from Operations Cash flow from Resale Resale Selling Cost Net Resale 1 Outputs 2 $300,000 $300,000 $0 $0 $33,250 $34,198 $510,000 $510,000 $0 $0 $79,800 $82,074 $75,000 $75,000 $0 $0 $6,650 $6,840 $6,250 $8,125 $80,000 $80,000 $0 $0 $6,650 $6,840 $80,000 $80,000 $0 $0 $0 $6,840 $1,177,600 $1,189,915 $80,000 $0 $58,880 $59,496 $1,038,720 $1,130,419 $62,323 $40,000 $18,000 $75,000 $195,323 $843,397 $50,000 $0 $50,000 $12,000 $0 $12,000 $781,397 $67,825 $41,000 $18,540 $77,250 $204,615 $925,804 $50,000 $0 $50,000 $12,000 $0 $12,000 $863,804 3 $300,000 $0 $35,172 $510,000 $0 $84,413 $0 $84,872 $0 $0 $80,000 $0 $7,034 $80,000 $0 $7,034 $1,188,526 $0 $59,426 $1,129,100 $67,746 $42,025 $19,096 $79,568 $208,435 $920,665 $0 $26,523 $26,523 $0 $10,185 $10,185 $883,958 $8,984,768 $269,543 $8,715,225 Total Cash Flow Year Cash flow IRR 0 ($8,500,000) 10.48% 1 $781,397 2 $863,804 Partitioning the IRR: Using IRR as a discount rate: PV of Cash flow from Operations $3,204,921 PV of Cash Flow from Resale 5,295,079 Total PV $8,500,000 37.70% 62.30% 100.00% hapter 13 tail Example ut Assumptions Tenant 3 Restaurant 5,000 $15.00 2 $16.00 3.00% $250.00 3.00% $225.00 5.00% 3 $5.00 4.00% 3.00% Vacant Space # Leases 2 Total Space Space per Lease 5,000 10,000 Rent SF $16.00 Lease Term (Yrs) 3 Market Rent $16.00 Market Rent Increase 3.00% Period 2nd Lease 1 $10.00 5.00% 3.00% $0.00 Net $0.00 Net $0.00 Net $0.00 Net $0.00 Net $0.00 Net $0.00 $0.00 $0.00 Change % 0.00% 2.50% 3.00% 3.00% $0.00 $0.00 $0.00 4 5 6 $0 $347,782 $0 $510,000 $0 $91,841 $0 $92,742 $0 $0 $0 $92,742 $0 $0 $92,742 $0 $1,227,849 $0 $61,392 $1,166,457 $69,987 $45,256 $20,867 $86,946 $223,056 $943,401 $300,000 $300,000 $0 $0 $36,175 $37,206 $510,000 $510,000 $0 $0 $86,820 $89,295 $0 $0 $87,418 $90,041 $0 $0 $0 $0 $0 $0 $87,418 $90,041 $0 $0 $80,000 $0 $0 $90,041 $7,235 $0 $1,195,066 $1,206,623 $0 $0 $59,753 $60,331 $1,135,312 $1,146,292 $68,119 $43,076 $19,669 $81,955 $212,818 $922,494 $0 $27,318 $27,318 $0 $10,490 $10,490 $884,686 $68,778 $44,153 $20,259 $84,413 $217,602 $928,690 $0 $28,138 $28,138 $0 $10,805 $10,805 $889,747 3 $883,958 4 $884,686 5 $9,604,972 Chapter 13 Industrial Example Inputs Building Name Address City State Building Size (SF) Analysis Begin Date Holding Period Discount Rate Terminal Cap Rate Selling Cost Tenant 1 Electric Supply 50,000 $6.00 3 Renew Worthington Distribution Center Anywhere USA 140,000 6/1/2000 5 10.50% 9.75% 3.00% Name Tenant Size Rent SF Lease Term (Yrs) At Expiration Market Leasing Assumption Rent Annual Change 3.00% Tenant Improvement Annual Change Lease Term New Renewal Weighted 5 Renewal Prob. 70.0% Market Rent 1 - Electric Supply Market Rent 1 - Sign Co. Market Rent 1 - Computer Dist $7.10 $7.43 $7.48 Market Rent $355,136 $315,618 $355,520 % of EGI Management Fee Property Tax Insurance Maintenance Roof Repair (year 1) 5.00% 0.00% 0.00% 0.00% $45,000 $ Amount $0 $23,000 $0 $0 Year Electric Supply Rent Electric Supply Market Rent Sign Company Rent Sign Company Market Rent Computer Dist. Rent Computer Dist. Market Rent Total Income Turnover Vacancy Effective Gross Income 1 $300,000 $0 $276,250 $0 $273,125 $0 $849,375 $0 $849,375 Outputs 2 Income: $300,000 $0 $276,250 $0 $273,125 $0 $849,375 $0 $849,375 Expenses: $42,469 $23,575 $21,840 $28,840 $116,724 Cash Flow: $732,651 $0 $0 $0 0 $732,651 0.81898 $600,030 Management Fee Property Tax Insurance CAM Total $42,469 $23,000 $21,000 $28,000 $114,469 NOI Tenant Improvements Leasing Commissions Total TI and LC Capital Costs Cash Flow PV Factors Present Value of Cash Flow Resale Calculations: Resale Selling Cost Net Resale PV Factor PVResale $734,906 $0 $0 $0 $45,000 $689,906 0.90498 $624,350 $9,137,730 $274,132 $8,863,598 0.60700 $5,380,203 Chapter 13 Industrial Example Inputs Tenant 2 Sign Company 42,500 $6.50 2 Vacate Tenant 3 Computer Dist. 47,500 $5.75 4 Market nant Improvement Annual Change 3.00% Leasing Commissions 3.00% 1.00% 1.60% Tenant Improvements $5.00 $2.00 $2.90 TI LC $109,273 $17,757 $225,441 $47,343 $155,039 $28,442 Months Vacant Market Rent 10 $7.00 0 $6.50 3 $6.65 Start Date 4.00 3.00 5.00 4.00 3.83 5.25 Market Turnover Vacate Turnover $88,784 $295,947 $78,904 $263,015 $88,880 $296,267 $ per SF $0.00 $0.00 $0.15 $0.20 Change % 0.00% 2.50% 4.00% 3.00% Outputs 3 Income: $300,000 $0 $0 $315,618 $273,125 $0 $888,743 $263,015 $625,728 Expenses: $31,286 $24,164 $22,714 $29,705 $107,870 Cash Flow: $517,858 $225,441 $47,343 $272,784 0 $245,074 0.74116 $181,640 $817,698 $109,273 $17,757 $127,030 0 $690,669 0.67073 $463,256 $809,055 $155,039 $28,442 $183,480 0 $625,575 0.60700 $379,724 $890,929 $47,194 $24,768 $23,622 $30,596 $126,181 $46,870 $25,388 $24,567 $31,514 $128,339 $51,314 $26,022 $25,550 $32,460 $135,345 $0 $355,136 $0 $315,618 $273,125 $0 $943,879 $0 $943,879 $0 $355,136 $0 $315,618 $0 $355,520 $1,026,274 $88,880 $937,394 $0 $355,136 $0 $315,618 $0 $355,520 $1,026,274 $0 $1,026,274 4 5 6 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: 200000 Tax Considerations: 40000 Depreciable Life (in years) 160000 Capital Gains Rate 0.5 Marginal Tax Rate 0.04 0.03 0.75 150000 0.11 25 12 5 0.06 250000 Purchase Price / Property Value Land Value Building Value Operating Expenses (% of NOI) NOI Growth Property Growth Loan-to-Value Loan Amount / Current Balance Interest Rate Loan Term Remaining (in years) Payments per Year Holding Period / Yrs. Since Purch. Selling Costs Resale Value Year 5 Equity Loan Annual Loan Payment Mortgage Balance 50000 150000 17642.0354 142,432 year 5 End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 3 17,642 17,642 17,642 148,799 147,458 145,963 16,441 16,302 16,146 1,201 1,340 1,496 4 17,642 144,294 15,973 1,669 CASH FLOW FROM OPERATIONS: 1 2 3 Rents 39,000 40,560 42,182 Less Operating Expenses 19500 20280 21091 Net Operating Income 19500 20280 21091 Less Debt Service 17642 17642 17642 Before-Tax Cash Flow $ 1,858 $ 2,638 $ 3,449 $ Year Net Operating Income Less: Interest Depreciation 19,500 16,441 8,421 20,280 16,302 8,421 21,091 16,146 8,421 4 43,870 21935 21935 17642 4,293 21,935 15,973 8,421 Taxable Income (loss) Tax Before-Tax Cash Flow Less Tax After-Tax Cash Flow (5,362) (1,501) (4,443) (1,244) (3,476) (973) (2,460) (689) $ 1,858 2,638 (1,501) (1,244) 3,359 $ 3,882 $ 3,449 4,293 (973) (689) 4,423 $ 4,982 CASH FLOW FROM SALE: Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Original Cost Basis Accumulated Depreciation Adjusted Basis 200,000 42,105 157,895 77,105 250,000 15,000 250,000 15,000 142,432 92,568 Capital Gain Tax from Sale After-Tax Cash Flow from Sale 21,589 70,978 End of Year Before-Tax Cash Flow After-Tax Cash Flow Before-Tax IRR After-Tax IRR 0 (50,000) (50,000) 1 1,858 3,359 18.26% 14.83% 2 2,638 3,882 3 3,449 4,423 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 Building Value (original) 160000 Capital Gains Rate Land Value (original) 40000 Depreciable Life (in yrs) 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 19 0.28 0.28 5 17,642 142,432 15,780 1,862 6 17,642 140,355 15,565 2,077 7 17,642 138,038 15,325 2,317 8 17,642 135,452 15,056 2,586 9 17,642 132,567 14,757 2,885 10 17,642 129,348 14,423 3,219 5 45,624 22812 22812 17642 $ 5,170 22,812 15,780 8,421 6 47,449 23725 23725 17642 6,083 $ 23,725 15,565 8,421 $ 7 48,873 24436 24436 17642 6,794 $ 24,436 15,325 8,421 8 50,339 25170 25170 17642 7,528 $ 25,170 15,056 8,421 9 10 51,849 53,405 25925 26702 25925 26702 17642 17642 8,283 $ 9,060 25,925 14,757 8,421 26,702 14,423 8,421 (1,389) (389) 5,170 (389) $ 5,559 (261) (73) 6,083 (73) 6,156 $ 691 193 6,794 193 6,601 1,692 474 7,528 474 $ 7,054 2,746 769 8,283 769 7,514 3,858 1,080 9,060 1,080 $ 7,980 $ $ Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Org. Cost Basis Accum Dep Adj Basis 200,000 84,211 115,789 156,640 289,819 17,389 289,819 17,389 129,348 143,081 Capital Gain Tax from Sale After-Tax Cash Flow from Sale 43,859 99,222 CASH FLOW SUMMARY: 7 8 6,794 7,528 6,601 7,054 23.06% 15.60% 4 4,293 4,982 5 97,738 76,537 5 (70,978) (70,978) Before-Tax IRR After-Tax IRR 6 6,083 6,156 9 8,283 7,514 years 0.28 0.28 19 If Renovated: NOI Yr 1 After Renovation NOI Growth Terminal Cap Rate Renovation Costs Loan Amount on Renovations Total New Loan Amount Interest Rate Loan Term (in years) Payments per Year Dep. Life of Renovation Holding Period Years Since Purchase IF RENOVATED: Additional Equity Loan Annual Pmt Mortg Bal 45000 0.04 0.1 200000 200000 337500 0.11 15 12 31.5 5 5 4932 337500 46032.18 278,477 in year 5 Yr of Ren. 1 6 46,032 328,130 36,662 9,370 2 7 46,032 317,675 35,578 10,455 3 8 46,032 306,011 34,368 11,664 4 9 46,032 292,997 33,018 13,014 5 10 46,032 278,477 31,512 14,520 6 - $ 7 45000 46800 46032 46032 (1,032) $ 768 45,000 36,662 14,770 46,800 35,578 14,770 8 48672 46032 $ 2,640 48,672 34,368 14,770 9 50619 46032 $ 4,587 50,619 33,018 14,770 10 52644 46032 $ 6,611 52,644 31,512 14,770 11 54749 (6,432) (1,801) (3,548) (993) (466) (131) 2,831 793 6,361 1,781 6,611 1,781 $ 4,830 $ (1,032) 768 2,640 4,587 (1,801) (993) (131) 793 769 $ 1,761 $ 2,770 $ 3,794 Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Org. Cost Basis Accum Dep Adj Basis 400,000 115,957 284,043 547,494 32,850 547,494 32,850 278,477 236,168 Capital Gain 230,601 Tax from Sale After-Tax Cash Flow from Sale 64,568 171,599 10 152,142 107,202 ATCF assuming renovation ATCF assuming no renovation Incremental Cash Flow IRR on Incremental Flows 5 (4,932) 6 769 6,156 (5,387) 37.47% 7 1,761 6,601 (4,840) 8 2,770 7,054 (4,283) 9 3,794 7,514 (3,719) 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 Current NOI Original Building Value Original Land Value Years since purchased Current Depreciable life Financing information Current loan balance Interest rate Remaining term Tax rate Growth rates for next 5 years: NOI Property Value 250000 47449 160000 40000 5 19 142432 11.00% 20 YEARS 28.00% 3.00% 3.00% Loan constant Payment Equity 12.39% $17,642 $107,568 End of year Mortgate balance 1 140355 2 138037 3 135452 4 132567 5 129348 Calulation of After Tax Cash Flow from Sale if Sold Today Sale Price Sale Costs Mortgage balance Before tax cash flow Sale price Sale costs Original cost basis 6.00% 250000 15000 200000 Accumulated depreciation Adjusted basis Capital gain Capital gains tax rate After tax cash flow from reversion 28.00% 42105 157895 77105 YEAR Rent Expenses Net operating income Debt service Before tax C.F. Net Operating Income Interest Depreciaiton Taxable income Tax Before tax C.F. Tax After tax cash flow Calculation of After Tax Cash Flow from Operations if NOT sold 1 2 3 4 5 $47,449 23,725 23,725 17,642 6,083 23,725 15,565 8,421 -261 -73 6,083 -73 6,156 $48,873 24,436 24,436 17,642 6,794 24,436 15,325 8,421 691 193 6,794 193 6,601 $50,339 25,170 25,170 17,642 7,528 25,170 15,056 8,421 1,692 474 7,528 474 7,054 $51,849 25,925 25,925 17,642 8,283 25,925 14,757 8,421 2,746 769 8,283 769 7,514 $53,405 26,702 26,702 17,642 9,060 26,702 14,423 8,421 3,858 1,080 9,060 1,080 7,980 Calculation of After Tax Cash Flow from Sale Sale price Morggage balance Selling expenses B.T.C.F. Sale price Selling expenses Orig. Cost basis Accum Deprec. Adjusted basis Total taxable gain Capital gains tax ATCF - Rev. $257,500 $265,225 $273,182 $281,377 $289,819 140,355 138,037 135,452 132,567 129,348 15,450 15,914 16,391 16,883 17,389 101,695 111,274 121,339 131,928 143,081 257,500 15,450 200,000 50,526 149,474 92,576 25,921 75,774 265,225 15,914 200,000 58,947 141,053 108,259 30,312 80,962 273,182 16,391 200,000 67,368 132,632 124,159 34,765 86,575 281,377 16,883 200,000 75,789 124,211 140,284 39,280 92,648 289,819 17,389 200,000 84,211 115,789 156,640 43,859 99,222 Cash Flow Summary Holding Period 1 2 3 4 5 6 7 8 9 10 Year 0 -70,979 -70,979 -70,979 -70,979 -70,979 -70,979 -70,979 -70,979 -70,979 -70,979 1 81,930 6,156 6,156 6,156 6,156 6,156 6,156 6,156 6,156 6,156 2 87,563 6,601 6,601 6,601 6,601 6,601 6,601 6,601 6,601 3 4 93,628 7,054 7,054 7,054 7,054 7,054 7,054 7,054 100,162 7,514 7,514 7,514 7,514 7,514 7,514 Chapter 14 ginal rate of return (MRR) 6 125757 7 121750 8 117280 9 112292 10 106727 day 250000 15000 142432 92568 21589 70979 rom Operations if NOT sold 6 7 $55,007 27,503 27,503 17,642 9,861 27,503 14,051 8,421 5,032 1,409 9,861 1,409 8,453 $56,657 28,329 28,329 17,642 10,687 28,329 13,635 8,421 6,272 1,756 10,687 1,756 8,930 8 $58,357 29,178 29,178 17,642 11,536 29,178 13,172 8,421 7,586 2,124 11,536 2,124 9,412 9 $60,108 30,054 30,054 17,642 12,412 30,054 12,654 8,421 8,978 2,514 12,412 2,514 9,898 10 61911 30,955 30,955 17,642 13,313 30,955 12,077 8,421 10,457 2,928 13,313 2,928 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 17,911 200,000 92,632 107,368 173,234 48,505 106,340 307,468 18,448 200,000 101,053 98,947 190,073 53,220 114,050 316,693 19,002 200,000 109,474 90,526 207,165 58,006 122,405 326,193 19,572 200,000 117,895 82,105 224,516 62,865 131,465 335,979 20,159 200,000 126,316 73,684 242,136 67,798 141,295 Cash Flow Summary 5 6 7 8 9 10 IRR 15.43% 15.49% 15.54% 15.57% 15.60% 15.61% 15.61% 15.61% 15.60% 15.58% MRR 15.43% 15.56% 15.65% 15.69% 15.71% 15.69% 15.65% 15.58% 15.49% 15.38% 107,202 7,980 7,980 7,980 7,980 7,980 114,793 8,453 8,453 8,453 8,453 122,980 8,930 8,930 8,930 131,818 9,412 9,412 141,363 9,898 151,680 Spreadsheet Limitations: Ten year holding period. RETURN FOR PAST 5 YEARS Data Input Box: 200000 Tax Considerations: 40000 Depreciable Life (in years) 160000 Capital Gains Rate 0.5 Marginal Tax Rate 0.04 0.03 0.75 150000 0.11 25 12 5 0.06 250,000 Purchase Price / Property Value Land Value Building Value Operating Expenses (% of NOI) NOI Growth Property Growth Loan-to-Value Loan Amount / Current Balance Interest Rate Loan Term Remaining (in years) Payments per Year Holding Period / Yrs. Since Purch. Selling Costs Resale Value Year 5 Equity Loan Annual Loan Payment Mortgage Balance 50,000 150,000 17,642 142,432 year 5 End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 1 2 3 17,642 17,642 17,642 148,799 147,458 145,963 16,441 16,302 16,146 1,201 1,340 1,496 4 17,642 144,294 15,973 1,669 CASH FLOW FROM OPERATIONS: 1 2 3 Rents 39,000 40,560 42,182 Less Operating Expenses 19500 20280 21091 Net Operating Income 19500 20280 21091 Less Debt Service 17642 17642 17642 Before-Tax Cash Flow $ 1,858 $ 2,638 $ 3,449 $ Year Net Operating Income Less: Interest Depreciation Taxable Income (loss) 19,500 16,441 8,421 (5,362) 20,280 16,302 8,421 (4,443) 21,091 16,146 8,421 (3,476) 4 43,870 21935 21935 17642 4,293 21,935 15,973 8,421 (2,460) Tax Before-Tax Cash Flow Less Tax After-Tax Cash Flow (1,501) 1,858 (1,501) $ 3,359 $ (1,244) 2,638 (1,244) 3,882 $ (973) 3,449 (973) 4,423 $ (689) 4,293 (689) 4,982 CASH FLOW FROM SALE: Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Original Cost Basis 200,000 Accumulated Depreciation 42,105 Adjusted Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale 250,000 15,000 250,000 15,000 142,432 92,568 157,895 77,105 21,589 70,978 End of Year Before-Tax Cash Flow After-Tax Cash Flow Before-Tax IRR After-Tax IRR CASH FLOW SUMMARY (Past 5 years) 0 1 2 (50,000) 1,858 2,638 (50,000) 3,359 3,882 18.26% 14.83% 3 3,449 4,423 AFTER TAX NET PRESENT VALUE: Discount rate After-Tax Present Value from Operations After-Tax Present Value from Reversion Initial Loan Amount Total Present Value $ After-Tax Present Value from Operations After-Tax Present Value from Reversion Original Equity Investment After-Tax Net Present Value 13.00% 17,988 38,524 150,000 203,675 15,150 38,524 50,000 3,675 $ Chapter 14 Renovation versus no renovation RETURN FOR NEXT 5 YEARS Data Input Box for Yrs 6-10: 250000 200000 160000 40000 0.03 0.5 0.03 289819 0.06 142432 0.11 20 12 10 57,568 142,432 17,642 106,727 19 0.28 0.28 Current Value Original Cost Basis Building Value (original) Land Value (original) NOI Growth Operating Expenses (% of NOI) Property Growth Resale Value Selling Costs Loan Amount / Current Balance Interest Rate Loan Term Remaining (in years) Payments per Year Total Holding Period (in yrs) NO RENOVATION: Equity Loan Annual Pmt Mortg Bal in year 5 17,642 142,432 15,780 1,862 6 17,642 140,355 15,565 2,077 7 17,642 138,038 15,325 2,317 8 17,642 135,452 15,056 2,586 5 45,624 22812 22812 17642 $ 5,170 22,812 15,780 8,421 (1,389) 6 47,449 23725 23725 17642 6,083 $ 23,725 15,565 8,421 (261) 7 $ 8 48,873 50,339 24436 25170 24436 25170 17642 17642 6,794 $ 7,528 24,436 15,325 8,421 691 25,170 15,056 8,421 1,692 (389) 5,170 (389) $ 5,559 (73) 6,083 (73) 6,156 $ 193 6,794 193 6,601 474 7,528 474 $ 7,054 $ Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Org. Cost Basis Accum Dep Adj Basis Capital Gain Tax from Sale After-Tax Cash Flow from Sale 200,000 84,211 115,789 156,640 289,819 17,389 4 4,293 4,982 5 97,738 76,537 5 (70,978) (70,978) Before-Tax IRR After-Tax IRR CASH FLOW SUMMARY ( Next 5 years) 6 7 6,083 6,794 6,156 6,601 23.06% 15.60% 14 no renovation RN FOR NEXT 5 YEARS nput Box for Yrs 6-10: Tax Considerations: Marginal Tax Rate Capital Gains Rate Depreciable Life (in yrs) 0.28 0.28 19 Data Input 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 Loan Annual Pmt Mortg Bal 10 4,932 337,500 46,032 278,477 in year Yr of Ren. 9 17,642 132,567 14,757 2,885 10 17,642 129,348 14,423 3,219 1 6 46,032 328,130 36,662 9,370 2 7 46,032 317,675 35,578 10,455 3 8 46,032 306,011 34,368 11,664 4 9 46,032 292,997 33,018 13,014 9 $ 10 51,849 53,405 25925 26702 25925 26702 17642 17642 8,283 $ 9,060 25,925 14,757 8,421 2,746 26,702 14,423 8,421 3,858 6 45000 46032 $ (1,032) $ 45,000 36,662 14,770 (6,432) 7 46800 46032 768 46,800 35,578 14,770 (3,548) 8 48672 46032 $ 2,640 48,672 34,368 14,770 (466) 9 50619 46032 $ 4,587 50,619 33,018 14,770 2,831 769 8,283 769 7,514 1,080 9,060 1,080 $ 7,980 (1,801) (1,032) (1,801) $ 769 $ (993) (131) 793 $ 768 2,640 4,587 (993) (131) 793 1,761 $ 2,770 $ 3,794 289,819 17,389 129,348 143,081 Sale Price (received by investor) Sales costs Mortgage Balance Before-tax Cash Flow Sale Price Sales Costs Org. Cost Basis Accum Dep Adj Basis 400,000 115,957 284,043 230,601 547,494 32,850 547,494 32,850 278,477 236,168 43,859 99,222 Capital Gain Tax from Sale After-Tax Cash Flow from Sale 64,568 171,599 W SUMMARY ( Next 5 years) 8 7,528 7,054 9 8,283 7,514 10 152,142 ATCF assuming renovation 107,202 ATCF assuming no renovation Incremental Cash Flow IRR on Incremental Flows 5 (4,932) 6 769 6,156 (5,387) 37.47% ovated: 5 5 10 46,032 278,477 31,512 14,520 10 52644 46032 $ 6,611 52,644 31,512 14,770 6,361 11 54749 1,781 6,611 1,781 $ 4,830 7 1,761 6,601 (4,840) 8 2,770 7,054 (4,283) 9 3,794 7,514 (3,719) 10 176,430 107,202 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: Ownership Data: of sales Property Value Today Time Since Purchase Purchase Price Building Value Land Value Resale Value year 15 years Loan-to-Value of payment Loan Amount Interest rate Loan Term Equity Investment Sales Revenue Cost of Goods Sold Additional Overhead Up-Front/Opening Costs Lease Data: Annual Payment Lease Term Operating Expenses Tax Considerations: Corporate tax rate Depreciable Life (in yrs) *No value after 15 years. $1,500,000 50.00% 200,000 2,500,000 200,000 15 50.00% 30.00% 32 $2,000,000 5 1,800,000 1,575,000 225,000 3,000,000 76.06% 1,369,000 10.00% 10 430999.56 OPERATING CASH FLOW: Own $1,500,000 750,000 750,000 200,000 550,000 100,000 0 136,900 50,000 263,100 78,930 184,170 50,000 0 $234,170 Lease $1,500,000 750,000 750,000 200,000 550,000 100,000 200,000 0 0 250,000 75,000 175,000 0 0 $175,000 Difference Own - Lease 0 0 0 0 0 0 (200,000) 136,900 50,000 13,100 3,930 9,170 50,000 0 $59,170 Sales Less: Cost of Goods Sold Gross Income Less: Overhead Net Income (before real estate) Operating Expenses Lease Payments Mortgage Interest Payment Tax Depreciation Taxable Income Less: Tax Income After-Tax Plus: Depreciation Less: Mortage Balance After-Tax Cash Flow Same for own and le difference. 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 Resale Less: Adjusted Basis Capital Gain Less: Tax After-Tax Cash Flow 1,631,000 3,000,000 800,000 2,200,000 660,000 Additional cash flow from $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 owning; IRR on ATCF differences 14.10% SUMMARY A Year Own Lease / Sale-Leaseback Differnce of Own vs. Lease IRR on Own vs. Lease 0 0 496,000 (496,000) 14.10% 1 234,170 175,000 59,170 2 234,170 175,000 59,170 3 234,170 175,000 59,170 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 Less: Mortgage Balance 2,000,000 1,369,000 Before-Tax Cash Flow Resale Less: Adjusted Basis Capital Gain Less: Tax After-Tax Cash Flow 631,000 2,000,000 1,550,000 450,000 135,000 $496,000 SUMMARY AFTER-TAX CASH FLOWS OF OPTIONS: 4 5 6 234,170 234,170 234,170 175,000 175,000 175,000 59,170 59,170 59,170 7 234,170 175,000 59,170 8 234,170 175,000 59,170 9 234,170 175,000 59,170 10 234,170 175,000 59,170 11 234,170 175,000 59,170 12 234,170 175,000 59,170 13 234,170 175,000 59,170 14 234,170 175,000 59,170 15 1,205,170 175,000 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: per year Holding Pd. (after con) of sale price Permanent Financing Loan Fee Loan Fee Amort Loan Amortization Loan Term Interest Rate Payments per Year Construction Loan per GLA Loan Amount per year Term on gross sales % drawn first 4 mos. per GLA % drawn last months per GLA Interest Rate Loan Fee per year Tax Considerations per GLA, yr 2 Marginal Tax Rate per year Capital Gains Rate per GLA Depreciation Capital Improvements per year Capital Improvements Tenant Improvements Tenant Improvements Property Appreciation Selling Costs Gross Building Area Gross Leasable Area Site Acq. & Closing On/Off Site Costs Hard Costs Soft Costs Leasing Comissions Rent Rent Growth Overage Percentage Overage on Excess over Average Sales Sales Growth Yr 2 - Yr 3 Sales Growth Tenant Reinmbursement Tenant Reinb. Growth Expenses Exp. Growth Yr 2 - Yr 3 Expenses Growth Vacancy Yr 2 Vacancy beginning Yr 3 6.00% 2.00% 120,000 110,000 2,500,000 850,000 7,055,500 1,576,786 45,300 15 6.00% 5.00% 200.00 200.27 316% 4.00% 8.39 4.00% 9.68 2.59% 4.16% 30.00% 5.00% Site Acq. & Close On/Off-Site Costs Hard Costs Soft Costs Total Project Costs Total Cost Breakdown 2,500,000 Total Project Costs 850,000 Loan Amount 7,055,500 Equity Needed 1,576,786 $11,982,286 Perm Loan Fee Amount CONSTRUCTION LOAN DRAWS & REPAYMENT: End of Month 0 1 Project Costs $ 1,557,938 Interest 0 $ Ending Balance 1,557,938 2 3 4 5 6 7 8 9 10 11 12 Total 1,557,938 1,557,938 1,557,938 259,656 259,656 259,656 259,656 259,656 259,656 259,656 259,656 $ 8,309,000 $ 15,579 31,315 47,207 63,259 66,488 69,749 73,043 76,370 79,730 83,124 86,552 692,416 3,131,454 4,720,706 6,325,851 6,648,766 6,974,910 7,304,315 7,637,014 7,973,041 8,312,427 8,655,208 9,001,416 Yield to Lender Year of Loan Payment Mortgage Balance Interest Principal 2 1,104,844 8,682,752 1,045,836 59,008 SUMMARY OF PERMANENT LOAN 3 4 1,104,844 1,104,844 8,616,260 8,541,335 1,038,352 1,029,919 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 Year Site Acq. and Closing Site Improvements Hard Costs Soft Costs* *(less interest & loan fees) 0 2,500,000 1 BEFORE AND AFTER-TAX CASH FLOWS: 2 850,000 7,055,500 447,282 262,253 174,835 2,937,088 0 $ 2,937,088 $ 692,416 9,045,198 8,741,760 303,438 Permanent Loan Fee Construction Loan Fee Construction Interest Total Constr. Outflow Less : Total Draws Total Equity Needed Year Rent Overage Tenant Reimbursements 1 OPERATING PERIOD CASH FLOWS 2 3 1,650,000 1,749,000 29,997 124,788 923,000 959,920 PGI Less : Vacancy EGI Total Expenses Net Operating Income Less : Debt Service Before-Tax Cash Flow Net Operating Income Less: Interest Depreciation Capital Imprvmnt Tenant Imprvmnt Amortization: Constr Loan Fee Perm Loan Fee Leasing comm. Taxable Income Taxes After-Tax Cash Flow (303,438) 2,602,997 780,899 1,822,098 1,064,800 757,298 1,104,844 (347,546) 757,298 1,045,836 257,140 257,140 2,833,708 141,685 2,692,022 1,092,402 1,599,620 1,104,844 494,776 1,599,620 1,038,352 257,140 183,671 174,835 0 (174,835) (48,954) (254,484) 26,225 0 (829,043) (232,132) (115,414) 26,225 0 94,231 26,385 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 Accum Dep & Amort Adjusted Basis 11,982,286 2,324,312 9,657,974 6,056,328 1,695,772 5,656,756 Capital Gain Tax from Sale After-Tax Cash Flow from Sale Year Before-Tax Cash Flow After-Tax Cash Flow BTCF IRR ATCF IRR 0 (2,937,088) (2,937,088) 20.95% 17.35% BEFORE and AFTER-TAX CASH FLOW SUMMARY 1 2 (303,438) (347,546) (254,484) (115,414) 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% 10 25 10 0.12 12 $ of loan years years years 8,309,000 12 months 75.00% 25.00% 12.00% 2.00% 28.00% 28.00% 31.5 90.00% 7 10.00% years - S/L of total years - DDB of total $ 11,982,286 8,741,760 3,240,526 $ 262,253 AYMENT: Lender's Cash Flow $ 174,835 0 (1,557,938) Payoff 0 0 0 0 0 0 0 0 0 0 8,741,760 (1,557,938) (1,557,938) (1,557,938) (259,656) (259,656) (259,656) (259,656) (259,656) (259,656) (259,656) 8,741,760 15.35% Yield to Lender AN 5 1,104,844 8,456,908 1,020,417 84,427 6 1,104,844 8,361,773 1,009,709 95,135 ER-TAX CASH FLOWS: 3 4 5 6 ASH FLOWS 4 1,853,940 129,779 998,317 5 1,965,176 134,970 1,038,250 6 2,083,087 140,369 1,079,780 2,982,036 149,102 2,832,934 1,137,847 1,695,087 1,104,844 590,243 1,695,087 1,029,919 257,140 131,194 3,138,396 156,920 2,981,476 1,185,183 1,796,294 1,104,844 691,450 1,796,294 1,020,417 257,140 93,710 3,303,236 165,162 3,138,074 1,234,487 1,903,587 1,104,844 798,743 1,903,587 1,009,709 257,140 66,936 26,225 0 250,609 70,170 520,073 26,225 0 398,802 111,665 579,785 26,225 0 543,576 152,201 646,541 AX CASH FLOW SUMMARY 3 494,776 468,391 4 590,243 520,073 5 691,450 579,785 6 8,151,271 6,303,298 the following reasons: shown above. as wrong in the book. Chapter 17 E Financing Land Develo Type Cluster Standard Creek Price 19,000 45,600 47,500 Totals Number 60 87 10 157 Total 1,140,000 3,967,200 475,000 5,582,200 Available Financing 42% 100% 12% 3 20% TARGET REVENUE: of land acquisition of improvement costs Interest rate points faster than revenue $4,651,833 DRAWS AND UNITS: Month 0 1 2 3 4 5 6 Total Draws Amount 1,000,000 600,000 600,000 600,000 300,000 300,000 300,000 3,700,000 Months 0 1-3 4-6 7-12 13-18 19-24 Cluster 0 2 4 3 1 10 Standard 0 2 3 6 5 16 1,000,000 2,640,000 3,640,000 Total to be financed 448,109 Interest Carry (calc. below) 4,088,109 Total Loan Amount PRESENT VALUES, ACCELERATION, & RESALE PR Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 Draw 1,019,200 655,200 655,200 655,200 218,400 218,400 218,400 Cluster 0 0 0 0 2 2 2 4 4 4 4 4 4 3 Standard 0 0 0 0 2 2 2 3 3 3 3 3 3 6 Creek 0 0 0 0 0 0 0 1 1 1 1 1 1 3 14 15 16 17 18 19 20 21 22 23 24 Total Present Value 3,640,000 3,569,554 PV Draws PV Revenue PV Rev / PV Draw Acceleration Release Price 3 3 3 3 3 1 1 1 1 1 1 54 6 6 6 6 6 5 5 5 5 5 5 90 3 3 3 3 3 2 2 2 2 2 2 36 3,569,554 5,880,209 60.7046% 20.00% 72.8455% Month 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Interest 0 10,192 16,846 23,566 30,354 31,900 33,462 35,040 33,494 31,933 30,356 28,763 27,155 25,530 22,339 19,116 15,861 12,573 9,253 5,899 3,467 1,010 0 0 0 448,109 LOAN SCHEDULE AND LENDER'S IRR Payments Total Principal Interest 1,019,200 665,392 0 10,192 672,046 0 16,846 678,766 0 23,566 248,754 94,116 30,354 250,300 94,116 31,900 251,862 94,116 33,462 35,040 189,617 35,040 33,494 189,617 33,494 31,933 189,617 31,933 30,356 189,617 30,356 28,763 189,617 28,763 27,155 189,617 27,155 25,530 344,632 25,530 22,339 344,632 22,339 19,116 344,632 19,116 15,861 344,632 15,861 12,573 344,632 12,573 9,253 344,632 9,253 5,899 249,131 5,899 3,467 249,131 3,467 1,010 102,005 1,010 0 0 0 0 0 0 0 0 0 4,088,109 4,088,109 448,109 Lender's Yield 15.69% DEVELOPER'S CASH FLOW, NPV AND IRR Quarter Inflow: Sales Loan Draw Interest Draw Total Inflow Outflows: Site Purch. Closing Loan Fee Loan Pmt. Interest Cost Direct Costs Gen & Admin Prop. Tax Sales Exp. Total Outflow Net Cash Net Present Value Internal Rate of Return 0 0 1,019,200 0 1,019,200 1 0 1,965,600 50,604 2,016,204 2 387,600 655,200 95,717 1,138,517 3 780,900 0 100,466 881,366 2,400,000 100,000 122,643 0 19,200 0 50,604 1,965,600 26,250 0 2,042,454 ($26,250) 40,056 16.64% 282,349 95,717 655,200 26,250 19,380 1,078,896 568,850 100,466 26,250 39,045 734,612 2,641,843 ($1,622,643) $59,621 $146,755 15% discount rate Chapter 17 Exhibits Financing Land Development Projects* Land Direct Costs Commission 2,400,000 2,640,000 5% Total to be financed Interest Carry (calc. below) Total Loan Amount Release Prices Cluster Standard 13,840.64 33,217.53 (calculated below) Land and Development Costs Site Site Closing Site Improvements Construction Interest Const. Loan Fee Total Direct Costs Creek 34,601.60 Operating Expenses: Selling Commissions Property Taxes General & Admin. Total Indirect Costs Total Project Costs - Loan Amount Total Equity Required Percent Financed Creek 0 0 1 3 2 Cumulative Units 0 12 60 132 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 96 108 120 132 140 148 156 164 172 180 180 473,100 473,100 473,100 473,100 473,100 342,000 342,000 342,000 342,000 342,000 342,000 6,840,000 5,880,209 2,895,600 3,368,700 3,841,800 4,314,900 4,788,000 5,130,000 5,472,000 5,814,000 6,156,000 6,498,000 6,840,000 .86996 .86135 .85282 .84438 .83602 .82774 .81954 .81143 .80340 .79544 .78757 0 0 0 0 0 0 0 0 0 0 0 3,569,554 411579 407504 403470 399475 395520 283087 280284 277509 274762 272041 269348 5,880,209 ENDER'S IRR Payments Total 10,192 16,846 23,566 124,470 126,017 127,579 224,656 223,111 221,549 219,973 218,380 216,771 370,162 366,971 363,748 360,493 357,205 353,885 255,030 252,598 103,015 0 0 0 4,536,218 Balance 1,019,200 1,684,592 2,356,638 3,035,404 3,190,042 3,346,226 3,503,972 3,349,395 3,193,272 3,035,588 2,876,327 2,715,474 2,553,012 2,233,910 1,911,617 1,586,102 1,257,331 925,272 589,893 346,660 100,996 0 0 0 0 Cash Flow (896,557) (655,200) (655,200) (655,200) (124,284) (124,284) (124,284) 189,617 189,617 189,617 189,617 189,617 189,617 344,632 344,632 344,632 344,632 344,632 344,632 249,131 249,131 102,005 0 0 0 S CASH FLOW, NPV AND IRR 4 780,900 0 86,274 867,174 5 1,419,300 66,985 1,486,285 6 1,419,300 37,687 1,456,987 7 1,026,000 10,375 1,036,375 8 1,026,000 0 1,026,000 568,850 86,274 26,250 43,750 39,045 764,169 $103,005 discount rate 1,033,896 66,985 26,250 70,965 1,198,096 $288,189 1,033,896 37,687 26,250 70,965 1,168,798 $288,189 600,268 10,375 26,250 51,300 688,194 $348,182 0 0 26,250 43,750 51,300 121,300 $904,700 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 Property Taxes General & Admin. Total Indirect Costs Total Project Costs - Loan Amount Total Equity Required Percent Financed 279,110 87,500 210,000 576,610 6,237,363 4,088,109 2,149,253 65.54% Initial Investment Investors Capital Inc. Property Developers Inc. -$45,000,000 -$5,000,000 Year Initial Cash flow from Investment Operations 0 -$50,000,000 1 $1,000,000 2 $2,000,000 3 $5,000,000 4 $6,000,000 5 $6,500,000 Total Cash flow -$50,000,000 $1,000,000 $2,000,000 $5,000,000 $6,000,000 $81,500,000 14.81% IRR Year 1 2 3 4 5 Cash flow from Operations $1,000,000 $2,000,000 $5,000,000 $6,000,000 $6,500,000 Investors Capital Inc. Property Developers return from Inc. return from operations operations $1,000,000 $2,000,000 $2,250,000 $2,250,000 $2,250,000 $0 $0 $250,000 $250,000 $250,000 Remaining operating cash flow to be split $0 $0 $2,500,000 $3,500,000 $4,000,000 Year 0 1 2 3 4 5 IRR Investors Property Developers Capital Inc. Inc. -$45,000,000 -$5,000,000 $1,000,000 $0 $2,000,000 $0 $3,500,000 $1,500,000 $4,000,000 $2,000,000 $70,150,834 $11,349,166 13.22% 26.64% Chapter 18 Investors Capital Inc. return from operations $1,000,000 $2,000,000 $2,250,000 $2,250,000 $2,250,000 Property Developers Inc. return from operations $0 $0 $250,000 $250,000 $250,000 Remaining operating cash flow to be split $0 $0 $2,500,000 $3,500,000 $4,000,000 Investors Capital Investors Capital Inc. from sale for Inc. cash flow for 12% IRR 12% IRR -$45,000,000 $1,000,000 $2,000,000 $3,500,000 $4,000,000 $61,801,668 $66,051,668 12.00% Year 0 1 2 3 4 5 Investors Capital Investors Capital Inc. cash flow Inc. return of initial from Operations investment $1,000,000 $2,000,000 $3,500,000 $4,000,000 $4,250,000 $45,000,000 Property Developers Inc. return of initial investment Remaining cash flow Investors from sale to Capital Inc. be split total cash flow -$45,000,000 $1,000,000 $2,000,000 $3,500,000 $4,000,000 $5,000,000 $8,198,332 $70,150,834 13.22% Property Developers Inc. Check -$5,000,000 $0 $0 $0 $0 $0 $1,500,000 $0 $2,000,000 $0 $11,349,166 $0 26.64% Investors Capital Inc. additional cash flow from Sale Total Cash 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. Property Developers Inc. -$45,000,000 -$5,000,000 Year Initial Investment 0 -$50,000,000 1 2 3 4 5 Cash flow from Operations $1,000,000 $2,000,000 $5,000,000 $6,000,000 $6,500,000 Total Cash flow -$50,000,000 $1,000,000 $2,000,000 $5,000,000 $6,000,000 $81,500,000 14.81% IRR Year 1 2 3 4 5 Cash flow from Operations $1,000,000 $2,000,000 $5,000,000 $6,000,000 $6,500,000 Investors Capital Inc. Property Developers return from Inc. return from operations operations $1,000,000 $2,000,000 $2,250,000 $2,250,000 $2,250,000 $0 $0 $250,000 $250,000 $250,000 Remaining operating cash flow to be split $0 $0 $2,500,000 $3,500,000 $4,000,000 Year 0 1 2 3 4 5 IRR Investors Property Developers Capital Inc. Inc. -$45,000,000 -$5,000,000 $1,000,000 $0 $2,000,000 $0 $3,500,000 $1,500,000 $4,000,000 $2,000,000 $66,051,493 $15,448,507 12.00% 32.94% Chapter 18 Investors Capital Inc. return from operations $1,000,000 $2,000,000 $2,250,000 $2,250,000 $2,250,000 Property Developers Inc. return from operations $0 $0 $250,000 $250,000 $250,000 Remaining operating cash flow to be split $0 $0 $2,500,000 $3,500,000 $4,000,000 Investors Capital Investors Capital Inc. from sale for Inc. cash flow for 12% IRR 12% IRR -$45,000,000 $1,000,000 $2,000,000 $3,500,000 $4,000,000 $53,602,986 $57,852,986 9.38% Year 0 1 2 3 4 5 Investors Capital Investors Capital Inc. cash flow Inc. return of initial from Operations investment $1,000,000 $2,000,000 $3,500,000 $4,000,000 $4,250,000 $45,000,000 Property Developers Inc. return of initial investment Remaining cash flow Investors Property from sale to Capital Inc. Developers be split total cash flow Inc. -$45,000,000 -$5,000,000 $1,000,000 $0 $2,000,000 $0 $3,500,000 $1,500,000 $4,000,000 $2,000,000 $5,000,000 $16,397,014 $66,051,493 $15,448,507 12.00% 32.94% Check $0 $0 $0 $0 $0 $0 Investors Capital Inc. additional cash flow from Sale Total Cash 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: Income Considerations: General Partner Gen Partner Gain Allocation Limited Partner Lim Partner Gain Allocation PGI (year 2) Vacancy & Loss Operating Expense (yr 2) Income Growth Resale Price Selling Costs Tax Considerations: Depreciation General Partner Limited Partner Land Improvements Funding Required Loan Amount Interest Rate Loan Term Payments per Year Holding Period Syndication Expense Points Points Term Points Payment Organization Fee Org Fee Term Org Fee Payment 525,000 $ 3,475,000 4,180,000 3,000,000 12.00% 25 12 5 100,000 60,000 25 2,400 20,000 5 4,000 per year years per year per year Total: Equity Annual Loan Payment General Partner: 1,180,000 379,161 Equity Payment End of Year Payment Mortgage Balance Interest Principal SUMMARY LOAN INFORMATION: 2 3 379,161 379,161 2,979,750 2,956,931 358,910 356,342 20,250 22,819 4 379,161 2,931,218 353,448 25,713 End of Year Payment Balance SUMMARY OF POINTS INFORMATION: 2 3 2,400 2,400 57,600 55,200 4 2,400 52,800 Year Potential Gross Income Less : Vancancy & Loss Effective Gross Income Less : Operationg Expenses Net Operating Income Less : Debt Service DISTRIBUTION OF BEFORE-TAX CASH FLOW: 2 3 4 750,000 772500 795675 37500 38625 39784 712,500 733,875 755,891 249375 256856 264562 463,125 477,019 491,329 379,161 379,161 379,161 Before-Tax Cash Flow Distribution: General Partner BTCF Limited Partner BTCF 83,964 4,198 79,766 97,858 4,893 92,965 112,169 5,608 106,560 Net Operating Income Less : Interest Depreciation Organization Fee Points Taxable Income: Distribution: General Partner BTCF Limited Partner BTCF DISTRIBUTION OF TAXABLE INCOME: 463,125 477,019 358,910 356,342 110,317 110,317 4,000 4,000 2,400 2,400 (12,503) 3,959 (625) (11,878) 198 3,761 491,329 353,448 110,317 4,000 2,400 21,164 1,058 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 Limited Partners' Gain $120,159 $1,081,429 End of Year Equity Plus : Income Less : Loss Plus : Gain from Sale Less : Cash Distribution Total for Year Balance CAPITAL ACCOUNTS PRIOR TO DISTRIBUTION OF CASH FLOW FROM SALE: 1 2 3 Limited Partners 1,121,000 0 3,761 (11,878) 0 (79,766) (91,644) 1,029,356 General Partner (92,965) (89,204) 940,153 1,121,000 $ 1,121,000 Equity Plus : Income Less : Loss Plus : Gain from Sale Less : Cash Distribution Total for Year Balance 59,000 0 (625) (4,198) (4,823) 54,177 198 0 (4,893) (4,695) 49,482 $ 59,000 59,000 End of Year Operation: Before-Tax Cash Flow Taxable Income Taxes After-Tax Cash Flow Reversion: Before-Tax Cash Flow Capital Gains Taxes After-Tax Cash Flow Total After-Tax Cash Flow AFTER-TAX CASH FLOW & INTERNAL RATE OF RETURN: 1 2 3 General Partner $ (59,000) 4,198 (625) (175) 4,373 4,893 198 55 4,837 $ (59,000) $ (59,000) $ 4,373 22.24% $ 4,837 After-Tax Internal Rate of Return Limited Partners Operation: Before-Tax Cash Flow Taxable Income Taxes After-Tax Cash Flow Reversion: Before-Tax Cash Flow Capital Gains Taxes After-Tax Cash Flow Total After-Tax Cash Flow (1,121,000) 79,766 (11,878) (3,326) 83,092 92,965 3,761 1,053 91,912 (1,121,000) $ (1,121,000) $ 83,092 13.15% $ 91,912 After-Tax Internal Rate of Return 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: 59,000 18,958 Limited Partner: Equity 1,121,000 Payment 360,203 5 6 379,161 379,161 2,902,244 2,869,596 350,187 346,512 28,974 32,648 : 5 2,400 50,400 6 2,400 48,000 OW: 5 819545 40977 778,568 272499 506,069 379,161 6 844132 42207 801,925 280674 521,251 379,161 126,909 6,345 120,563 142,091 7,105 134,986 506,069 350,187 110,317 4,000 2,400 39,165 1,958 37,207 521,251 346,512 110,317 4,000 50,400 10,021 501 9,520 ALE: OF CASH FLOW FROM SALE: 4 5 6 20,106 0 (106,560) (86,455) 853,698 37,207 0 (120,563) (83,357) 770,341 9,520 0 1,081,429 (134,986) 955,963 1,726,304 1,058 0 (5,608) (4,550) 44,931 1,958 0 (6,345) (4,387) 40,544 501 0 120,159 (7,105) 113,555 154,100 RATE OF RETURN: 4 5 6 5,608 1,058 296 5,312 6,345 1,958 548 5,797 7,105 501 140 6,964 $ $ 5,312 $ 5,797 $ 154,100 120,159 33,644 120,455 127,419 106,560 20,106 5,630 100,931 120,563 37,207 10,418 110,145 134,986 9,520 2,666 132,320 $ $ 100,931 $ 110,145 $ 1,726,304 1,081,429 302800 1,423,504 1,555,825 Chapter 19 Mortgage Pass Through Security Data Input Box: Number of mortgages in initial pool Average mortgage balance Initial mortgage pool balance Prepayment rate Mortgage rate Servicing and Guarantee Fee Coupon rate Market interest rate 40 $25,000 $1,000,000 10.00% 10.00% 0.5% 9.50% 9.50% Issuance of 100 Mortgage Pass Through Securities (MPT) (d) (e) Total PrincipalGuarantee Principal Principal and and Interest and Pool due to Interest Pmts Pmts to Issuer Service Fees Balance Prepayment to Issuer (b)+(c) (a)x(0.5%) $1,000,000 837,255 100,000 162,745 262,745 5,000 691,873 83,725 145,381 229,107 4,186 562,186 69,187 129,688 198,875 3,459 446,710 56,219 115,476 171,695 2,811 344,142 44,671 102,568 147,239 2,234 253,358 34,414 90,784 125,198 1,721 173,431 25,336 79,927 105,263 1,267 103,692 17,343 69,739 87,082 867 43,946 10,369 59,746 70,115 518 0 0 48,340 48,340 220 Discount rate 9.50% 8.50% 10.50% (a) (b) (c) Year 0 1 2 3 4 5 6 7 8 9 10 Price to Green Value of MPT $1,000,000 $25,000 $1,033,908 $25,848 $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 $75,000 (ooo omitted) 11.00% 10 years $12,735.11 Prepayment rate 0.00% Amount Interest rate Term Annual Payment LOAN SCHEDULE FOR MORTGAGE POOL Year 1 2 3 4 5 6 7 8 9 10 Beg. Bal $75,000.00 70,514.89 65,536.42 60,010.32 53,876.35 47,067.64 39,509.98 31,120.97 21,809.17 11,473.07 Payment $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 Interest 8,250.00 7,756.64 7,209.01 6,601.14 5,926.40 5,177.44 4,346.10 3,423.31 2,399.01 1,262.04 Principal $4,485.11 $4,978.47 $5,526.10 $6,133.97 $6,808.71 $7,557.67 $8,389.01 $9,311.80 $10,336.10 $11,473.07 End Bal $70,514.89 $65,536.42 $60,010.32 $53,876.35 $47,067.64 $39,509.98 $31,120.97 $21,809.17 $11,473.07 $0.00 Amount Rate CLASS Z TRAUNCHE $30,000 11.00% Total Payment Cash Flow ($30,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 11.00% $32,611 Year 0 1 2 3 4 5 6 7 8 9 10 Beg. Bal Interest End Bal $30,000.00 33,300.00 36,963.00 41,028.93 45,542.11 44,067.64 36,509.98 28,120.97 18,809.17 8,473.07 3,300.00 3,663.00 4,065.93 4,513.18 5,009.63 4,847.44 4,016.10 3,093.31 2,069.01 932.04 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 IRR P V at $33,300.00 36,963.00 41,028.93 45,542.11 44,067.64 36,509.98 28,120.97 18,809.17 8,473.07 0.00 9.75% TRAUNCH A Amount Rate $27,000 9.25% Cash Flow 10,282.61 10,418.85 10,570.07 1,072.17 0.00 0.00 0.00 0.00 0.00 0.00 $27,376 YTM CLASS B TRAUNCHE $15,000 10.00% Interest $1,500.00 1,500.00 1,500.00 1,500.00 533.42 0.00 0.00 0.00 0.00 0.00 Principal $0.00 0.00 0.00 9,665.76 5,334.24 0.00 0.00 0.00 0.00 0.00 P V at End Bal $15,000.00 15,000.00 15,000.00 5,334.24 0.00 0.00 0.00 0.00 0.00 0.00 9.50% Cash Flow 1,500.00 1,500.00 1,500.00 11,165.76 5,867.66 0.00 0.00 0.00 0.00 0.00 $15,257 YTM Year 0 1 2 3 4 5 6 7 8 9 10 Beg. Bal $27,000.00 19,214.89 10,573.42 981.39 0.00 0.00 0.00 0.00 0.00 0.00 Interest 2,497.50 1,777.38 978.04 90.78 0.00 0.00 0.00 0.00 0.00 0.00 Principal $7,785.11 8,641.47 9,592.03 981.39 0.00 0.00 0.00 0.00 0.00 0.00 P V at End Bal $19,214.89 10,573.42 981.39 0.00 0.00 0.00 0.00 0.00 0.00 0.00 8.50% Amount Rate Year 0 1 2 3 4 5 6 7 8 9 10 Beg. Bal $15,000.00 15,000.00 15,000.00 15,000.00 5,334.24 0.00 0.00 0.00 0.00 0.00 RESIDUAL CLASS Year 0 1 Total in pool Other Classes Residual ($3,000.00) 952.50 $12,735.11 $11,782.61 2 3 4 5 6 7 8 9 10 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 $12,735.11 IRR 11,918.85 12,070.07 12,237.93 12,351.76 12,405.11 12,405.11 12,405.11 12,405.11 9,405.11 816.26 665.03 497.17 383.34 330.00 330.00 330.00 330.00 3,330.00 20.19% Beginning Balance of Class B Z 10.00% 11.00% 15000 15000 15000 15000 5334 0 0 0 0 0 30000 33300 36963 41029 45542 44068 36510 28121 18809 8473 A Coupon End of Year 0 1 2 3 4 5 6 7 8 9 9.25% 27000 19215 10573 981 0 0 0 0 0 0 Total 72000 67515 62536 57010 50876 44068 36510 28121 18809 8473 a) Weighted Average Coupon when issued (yr 0) 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. 10.14% 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 Rate Year Beginning Interest Principal Balance $0.00 $0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 $0 0.00% Ending Balance $0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 $0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10% Price Increase ($16,500) 1,500.00 1,500.00 1,500.00 11,165.76 5,867.66 0.00 0.00 0.00 0.00 0.00 1 2 3 4 5 6 7 8 9 10 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 Interest rate for tranche Total interest available $20,000,000 6.00% $1,200,000 Floater (F) Inverse floater (IF) Total F / IF Ratio Max Inverse Floater (IF) rate $ Amount Scale $10,000,000 10,000,000 20,000,000 1.00 12.00% Maximum Rate change 0.50 0.50 1.00 6.00% -6.00% Libor 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% (F) Rate 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% 10.00% 11.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% 12.00% (IF) Rate 12.00% 11.00% 10.00% 9.00% 8.00% 7.00% 6.00% 5.00% 4.00% 3.00% 2.00% 1.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% F Interest $0 100,000 200,000 300,000 400,000 500,000 600,000 700,000 800,000 900,000 1,000,000 1,100,000 1,200,000 1,200,000 1,200,000 1,200,000 1,200,000 1,200,000 1,200,000 Rate on Floater and Inverse Floater vs. LIBOR 14.00% 12.00% 12.00% 10.00% 8.00% Rate 6.00% 4.00% 2.00% 0.00% 0% 2% 4% 6% 8% 10% LIBOR Floater Inverse Floater r 20 rse Floater Rate limit 12.00% 0.00% IF Interest $1,200,000 1,100,000 1,000,000 900,000 800,000 700,000 600,000 500,000 400,000 300,000 200,000 100,000 0 0 0 0 0 0 0 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 se Floater vs. LIBOR 12% 14% 16% 18% Inverse Floater Chapter 20 IO/PO MORTGAGE POOL Amount Interest rate TERM Annual Payment 169801.4271 $1,000,000 11.00% 10 years Prepayment LOAN SCHEDULE FOR MORTGAGE POOL Year 1 2 3 4 5 6 7 8 9 10 Beg. Bal 1000000 940199 873819 800138 718351 627569 526800 414946 290789 152974 Payment 169801 169801 169801 169801 169801 169801 169801 169801 169801 169801 Interest 110000 103422 96120 88015 79019 69033 57948 45644 31987 16827 Principal 59801 66380 73681 81786 90783 100769 111853 124157 137815 152974 Interest only and Principal only strips Year 1 2 3 4 5 6 7 8 9 10 Interest $110,000 103,422 96,120 88,015 79,019 69,033 57,948 45,644 31,987 16,827 Principal $59,801 66,380 73,681 81,786 90,783 100,769 111,853 124,157 137,815 152,974 Assuming no prepayment PV @ 11% $461,248 PV @ 11% $538,752 Assuming no prepayment PV @ 10% $476,919 PV @12% $446,425 PV @ 10% $566,437 PV @12% $512,991 Assuming no prepayment Assuming prepayment: MORTGAGE POOL AMOUNT INTEREST RATE TERM ANNUAL PAYMENT 169801.4271 $1,000,000 11.00% 10 PREPAYMENT LOAN SCHEDULE FOR MORTGAGE POOL Year 1 2 3 4 5 6 7 8 9 10 Beg. Bal 1000000 740199 539900 386395 269620 181623 116135 68249 34178 11144 Payment 169801 133681 104914 81999 63732 49142 37433 27929 19958 12370 Interest 110000 81422 59389 42503 29658 19978 12775 7507 3760 1226 Principal 259801 200299 153505 116774 87998 65488 47885 34071 23034 11144 Interest only and Principal only strips Year 1 2 3 4 5 6 7 8 9 10 Interest $110,000 81,422 59,389 42,503 29,658 19,978 12,775 7,507 3,760 1,226 Principal $259,801 200,299 153,505 116,774 87,998 65,488 47,885 34,071 23,034 11,144 PV @ 11% PV @ 11% $276,200 $723,800 Extra Calculations below Present values at 8 percent with PV of interest $296,777 PV of Principal $784,162 PV of Interest Prepayment 0.00% 5.00% 10.00% 15.00% 20.00% 25.00% 30.00% $361 311 271 238 211 189 171 PV of Principal $639 689 729 762 789 811 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 0 1 2 3 4 5 6 7 8 9 10 IRR Interest ($275) 110,000 81,422 59,389 42,503 29,658 19,978 12,775 7,507 3,760 1,226 39974.02% Principal ($725) 259,801 200,299 153,505 116,774 87,998 65,488 47,885 34,071 23,034 11,144 35811.78% IRR of IO Prepayment 0.00% 5.00% 10.00% 15.00% 20.00% 25.00% 30.00% 30.00% 25.32% 20.62% 15.91% 11.19% 6.45% 1.70% IRR of PO 5.38% 6.57% 7.90% 9.37% 10.94% 12.59% 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 940199 873819 800138 718351 627569 526800 414946 290789 152974 0 Prepayment 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 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 740199 539900 386395 269620 181623 116135 68249 34178 11144 0 Prepayment 200000 148040 107980 77279 53924 36325 23227 13650 6836 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 Interest rate 10% Maturity 5 Assets Commercial mortgages Senior Securities: Class A bonds Default property Price $8,000,000 Liabilities $10,000,000 Subordinated securities: Class B bonds Total Net Worth (residual) Total $10,000,000 Total Panel A. No Default or Mortgage Payment End of Period 1 2 3 4 5 Cash Inflow to Pool Senior Subordinated Residual $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $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 Period 1 2 3 4 5 Cash Inflow to Pool Senior Subordinated Residual $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $1,000,000 $480,000 $300,000 $220,000 $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. S&P NCREIF Correlation 7.41% 1.76% -0.0521 Portfolio Variance 0.03% 0.03% 0.03% 0.03% 0.04% 0.05% 0.06% 0.08% 0.10% 0.12% 0.14% 0.17% 0.20% 0.23% 0.27% 0.31% 0.35% 0.40% 0.44% 0.50% 0.55% Return 4.06% 2.32% % S&P % NCREIF 0.00% 100.00% 5.00% 95.00% 10.00% 90.00% 15.00% 85.00% 20.00% 80.00% 25.00% 75.00% 30.00% 70.00% 35.00% 65.00% 40.00% 60.00% 45.00% 55.00% 50.00% 50.00% 55.00% 45.00% 60.00% 40.00% 65.00% 35.00% 70.00% 30.00% 75.00% 25.00% 80.00% 20.00% 85.00% 15.00% 90.00% 10.00% 95.00% 5.00% 100.00% 0.00% Portfolio Stand. Dev 1.76% 1.69% 1.71% 1.82% 1.99% 2.22% 2.48% 2.78% 3.09% 3.42% 3.76% 4.11% 4.47% 4.82% 5.19% 5.55% 5.92% 6.29% 6.66% 7.04% 7.41% Return Return x 100 2.32% 2.32 2.41% 2.41 2.50% 2.50 2.58% 2.58 2.67% 2.67 2.76% 2.76 2.84% 2.84 2.93% 2.93 3.02% 3.02 3.10% 3.10 3.19% 3.19 3.28% 3.28 3.36% 3.36 3.45% 3.45 3.54% 3.54 3.62% 3.62 3.71% 3.71 3.80% 3.80 3.88% 3.88 3.97% 3.97 4.06% 4.06 Exhibit 20-8 Portfolio Returns of NCREIF and S&P 500 21-8 4.50% 4.50 4.00% Portfolio Return (percent) 4.00 3.50% 3.00% 3.50 2.50% 2.00% 3.00 1.50% Portfolio Return (per 1.00% 2.50 0.50% 0.00% 2.00 1% 1% 2% 2% 3% 3% 4% 4% 5% 5% 6% 6% 7% Portfolio standard deviation % Portfolio standard deviation % 7% 8%