Case Study 2 Complete

.xlsx

School

Rutgers University *

*We aren’t endorsed by this school

Course

33:390:435

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

16

Uploaded by MinisterDiscovery7644

Report
Using the results (Cassidy version!) in Case Study 1 populate the attached model. Things to consider / questions: 1 Sources and Uses at time of Purchase - Going in Cap of 9.5% on Year 1 Income (includi What income stream are you purchasing? Start with Value by capitalizing tha this will be your purchase price Assume there are 3% closing costs associated with the purchase Working Capital equals amount to fund the second part in 3 below How much is the $/PSF of fully capitalized acquisition? 2 Calculate Debt Assume Debt at 65% LTV (Purchase Price only!), 5% interest Populate amortization table on a 25 year schedule Annual Debt Service What is the constant? Debt Service Coverage Ratio? Debt Yield? 3 Income from operations Using the results from Case Study 1 (Reviewed in class and posted on Canvas to populate the annual cash flows for years 1- 5 including NOI, Leasing Costs and Debt service Assuming you must always have $50,000 working Capital each year (Ending c but any remaining cash is distributed to investor. 4 Reversion Calculate the sale of the property assuming an 8% Capitalization Rate Assume there are 2.5% costs of sale Calculate the Internal Rate of Return to investor Calculate the Equity Multiple to Investor What would the IRR and Equity Multiple be if the deal were unlevered? What would the sale price be if you could sell at a 5 Cap?
ing new lease) at income s) cash balance)
Student Name RU ID # Income Analysis Sources and Uses Total SF 60,000 Sources $ / SF First Mortgage 65% of Purchase Price $ 3,338,393 $ 56 Equity 2,025,959 34 $ 5,364,352 $ 89 Uses Purchase Price $ 5,135,989 $ 86 Closing Costs 154,080 3 Working Capital 74,283 1 Other - - $ 5,364,352 $ 89 Loan to Value 65.0% Debt Yield 14.6% Debt Service Coverage Ratio 2.08 Interest Rate 5.00% Constant of 7.02% Amortization Period 25 Cash Flows Year 1 Year 2 Year 3 Year 4 Year 5 (From Case Study 1) Net Operating Income $ 487,919 $ 509,880 $ 522,439 $ 538,366 $ 509,090 Debt Service (234,191) (234,191) (234,191) (234,191) (234,191) Leasing Costs (278,011) - - - (137,429) Cash Flow Before Distributions (24,283) 275,689 288,248 304,175 137,470 Distributions to Investor (275,689) (288,248) (304,175) (137,470) Beginning Cash 74,283 50,000 50,000 50,000 50,000 Ending Cash $ 50,000 $ 50,000 $ 50,000 $ 50,000 $ 50,000 (Must always have $50,000 working capital)
Denholtz Associates RU ID # Investor Analysis Sale In Place sale adjusted NOI $ 535,242 Cap Rate 8.0% Gross Sale Price 112 $ 6,690,525 Add: Cash on hand 50,000 Less: Closing Costs 2.5% (167,263) First Mortgage (2,957,155) Sponsor Promote 0% - Net Sale Proceeds $ 3,616,107 Investor Analysis Total Operating Sales Initial Investment (2,025,959) Year 1 - - - Year 2 275,689 275,689 - Year 3 288,248 288,248 - Year 4 304,175 304,175 - Year 5 3,753,577 137,470 3,616,107 1,005,582 3,616,107 Internal Rate of Return 19.8% Return Multiple 2.28
Page 5 of 16 LOAN AMORTIZATION SCHEDULE ENTER VALUES LOAN SUMMARY Loan amount $3,338,393 Scheduled payment $19,515.91 Annual interest rate 5.00% Scheduled number of payments 300 Loan period in years 25 Actual number of payments 300 Number of payments per year 12 Total early payments $0.00 Start date of loan 4/1/2019 Total interest $2,516,380.79 Optional extra payments $ - LENDER NAME PRINCIPAL INTEREST 1 4/1/2019 $3,338,392.85 $19,515.91 $0.00 $19,515.91 $5,605.94 $13,909.97 $3,332,786.91 $13,909.97 2 5/1/2019 $3,332,786.91 $19,515.91 $0.00 $19,515.91 $5,629.30 $13,886.61 $3,327,157.61 $27,796.58 3 6/1/2019 $3,327,157.61 $19,515.91 $0.00 $19,515.91 $5,652.76 $13,863.16 $3,321,504.85 $41,659.74 4 7/1/2019 $3,321,504.85 $19,515.91 $0.00 $19,515.91 $5,676.31 $13,839.60 $3,315,828.54 $55,499.34 5 8/1/2019 $3,315,828.54 $19,515.91 $0.00 $19,515.91 $5,699.96 $13,815.95 $3,310,128.58 $69,315.29 6 9/1/2019 $3,310,128.58 $19,515.91 $0.00 $19,515.91 $5,723.71 $13,792.20 $3,304,404.87 $83,107.50 7 10/1/2019 $3,304,404.87 $19,515.91 $0.00 $19,515.91 $5,747.56 $13,768.35 $3,298,657.32 $96,875.85 8 11/1/2019 $3,298,657.32 $19,515.91 $0.00 $19,515.91 $5,771.51 $13,744.41 $3,292,885.81 $110,620.26 9 12/1/2019 $3,292,885.81 $19,515.91 $0.00 $19,515.91 $5,795.55 $13,720.36 $3,287,090.25 $124,340.61 10 1/1/2020 $3,287,090.25 $19,515.91 $0.00 $19,515.91 $5,819.70 $13,696.21 $3,281,270.55 $138,036.82 11 2/1/2020 $3,281,270.55 $19,515.91 $0.00 $19,515.91 $5,843.95 $13,671.96 $3,275,426.60 $151,708.78 12 3/1/2020 $3,275,426.60 $19,515.91 $0.00 $19,515.91 $5,868.30 $13,647.61 $3,269,558.30 $165,356.39 13 4/1/2020 $3,269,558.30 $19,515.91 $0.00 $19,515.91 $5,892.75 $13,623.16 $3,263,665.55 $178,979.55 14 5/1/2020 $3,263,665.55 $19,515.91 $0.00 $19,515.91 $5,917.31 $13,598.61 $3,257,748.24 $192,578.16 15 6/1/2020 $3,257,748.24 $19,515.91 $0.00 $19,515.91 $5,941.96 $13,573.95 $3,251,806.28 $206,152.11 16 7/1/2020 $3,251,806.28 $19,515.91 $0.00 $19,515.91 $5,966.72 $13,549.19 $3,245,839.56 $219,701.30 17 8/1/2020 $3,245,839.56 $19,515.91 $0.00 $19,515.91 $5,991.58 $13,524.33 $3,239,847.98 $233,225.64 18 9/1/2020 $3,239,847.98 $19,515.91 $0.00 $19,515.91 $6,016.55 $13,499.37 $3,233,831.43 $246,725.00 19 10/1/2020 $3,233,831.43 $19,515.91 $0.00 $19,515.91 $6,041.61 $13,474.30 $3,227,789.82 $260,199.30 20 11/1/2020 $3,227,789.82 $19,515.91 $0.00 $19,515.91 $6,066.79 $13,449.12 $3,221,723.03 $273,648.42 21 12/1/2020 $3,221,723.03 $19,515.91 $0.00 $19,515.91 $6,092.07 $13,423.85 $3,215,630.97 $287,072.27 22 1/1/2021 $3,215,630.97 $19,515.91 $0.00 $19,515.91 $6,117.45 $13,398.46 $3,209,513.52 $300,470.73 23 2/1/2021 $3,209,513.52 $19,515.91 $0.00 $19,515.91 $6,142.94 $13,372.97 $3,203,370.58 $313,843.71 24 3/1/2021 $3,203,370.58 $19,515.91 $0.00 $19,515.91 $6,168.53 $13,347.38 $3,197,202.04 $327,191.08 25 4/1/2021 $3,197,202.04 $19,515.91 $0.00 $19,515.91 $6,194.24 $13,321.68 $3,191,007.80 $340,512.76 PMT NO PAYMENT DATE BEGINNING BALANCE SCHEDULED PAYMENT EXTRA PAYMENT TOTAL PAYMENT ENDING BALANCE CUMULATIVE INTEREST Table values calculated fo Enter the info the left and w loan history p
Page 6 of 16 PRINCIPAL INTEREST PMT NO PAYMENT DATE BEGINNING BALANCE SCHEDULED PAYMENT EXTRA PAYMENT TOTAL PAYMENT ENDING BALANCE CUMULATIVE INTEREST 26 5/1/2021 $3,191,007.80 $19,515.91 $0.00 $19,515.91 $6,220.05 $13,295.87 $3,184,787.76 $353,808.62 27 6/1/2021 $3,184,787.76 $19,515.91 $0.00 $19,515.91 $6,245.96 $13,269.95 $3,178,541.80 $367,078.57 28 7/1/2021 $3,178,541.80 $19,515.91 $0.00 $19,515.91 $6,271.99 $13,243.92 $3,172,269.81 $380,322.50 29 8/1/2021 $3,172,269.81 $19,515.91 $0.00 $19,515.91 $6,298.12 $13,217.79 $3,165,971.69 $393,540.29 30 9/1/2021 $3,165,971.69 $19,515.91 $0.00 $19,515.91 $6,324.36 $13,191.55 $3,159,647.32 $406,731.84 31 10/1/2021 $3,159,647.32 $19,515.91 $0.00 $19,515.91 $6,350.71 $13,165.20 $3,153,296.61 $419,897.03 32 11/1/2021 $3,153,296.61 $19,515.91 $0.00 $19,515.91 $6,377.18 $13,138.74 $3,146,919.43 $433,035.77 33 12/1/2021 $3,146,919.43 $19,515.91 $0.00 $19,515.91 $6,403.75 $13,112.16 $3,140,515.68 $446,147.93 34 1/1/2022 $3,140,515.68 $19,515.91 $0.00 $19,515.91 $6,430.43 $13,085.48 $3,134,085.25 $459,233.42 35 2/1/2022 $3,134,085.25 $19,515.91 $0.00 $19,515.91 $6,457.22 $13,058.69 $3,127,628.03 $472,292.10 36 3/1/2022 $3,127,628.03 $19,515.91 $0.00 $19,515.91 $6,484.13 $13,031.78 $3,121,143.90 $485,323.89 37 4/1/2022 $3,121,143.90 $19,515.91 $0.00 $19,515.91 $6,511.15 $13,004.77 $3,114,632.76 $498,328.65 38 5/1/2022 $3,114,632.76 $19,515.91 $0.00 $19,515.91 $6,538.28 $12,977.64 $3,108,094.48 $511,306.29 39 6/1/2022 $3,108,094.48 $19,515.91 $0.00 $19,515.91 $6,565.52 $12,950.39 $3,101,528.96 $524,256.68 40 7/1/2022 $3,101,528.96 $19,515.91 $0.00 $19,515.91 $6,592.87 $12,923.04 $3,094,936.09 $537,179.72 41 8/1/2022 $3,094,936.09 $19,515.91 $0.00 $19,515.91 $6,620.35 $12,895.57 $3,088,315.74 $550,075.29 42 9/1/2022 $3,088,315.74 $19,515.91 $0.00 $19,515.91 $6,647.93 $12,867.98 $3,081,667.81 $562,943.27 43 10/1/2022 $3,081,667.81 $19,515.91 $0.00 $19,515.91 $6,675.63 $12,840.28 $3,074,992.18 $575,783.55 44 11/1/2022 $3,074,992.18 $19,515.91 $0.00 $19,515.91 $6,703.44 $12,812.47 $3,068,288.74 $588,596.02 45 12/1/2022 $3,068,288.74 $19,515.91 $0.00 $19,515.91 $6,731.38 $12,784.54 $3,061,557.36 $601,380.56 46 1/1/2023 $3,061,557.36 $19,515.91 $0.00 $19,515.91 $6,759.42 $12,756.49 $3,054,797.94 $614,137.05 47 2/1/2023 $3,054,797.94 $19,515.91 $0.00 $19,515.91 $6,787.59 $12,728.32 $3,048,010.35 $626,865.37 48 3/1/2023 $3,048,010.35 $19,515.91 $0.00 $19,515.91 $6,815.87 $12,700.04 $3,041,194.48 $639,565.41 49 4/1/2023 $3,041,194.48 $19,515.91 $0.00 $19,515.91 $6,844.27 $12,671.64 $3,034,350.21 $652,237.06 50 5/1/2023 $3,034,350.21 $19,515.91 $0.00 $19,515.91 $6,872.79 $12,643.13 $3,027,477.43 $664,880.18 51 6/1/2023 $3,027,477.43 $19,515.91 $0.00 $19,515.91 $6,901.42 $12,614.49 $3,020,576.00 $677,494.67 52 7/1/2023 $3,020,576.00 $19,515.91 $0.00 $19,515.91 $6,930.18 $12,585.73 $3,013,645.83 $690,080.41 53 8/1/2023 $3,013,645.83 $19,515.91 $0.00 $19,515.91 $6,959.05 $12,556.86 $3,006,686.77 $702,637.26 54 9/1/2023 $3,006,686.77 $19,515.91 $0.00 $19,515.91 $6,988.05 $12,527.86 $2,999,698.72 $715,165.13 55 10/1/2023 $2,999,698.72 $19,515.91 $0.00 $19,515.91 $7,017.17 $12,498.74 $2,992,681.55 $727,663.87 56 11/1/2023 $2,992,681.55 $19,515.91 $0.00 $19,515.91 $7,046.41 $12,469.51 $2,985,635.15 $740,133.38 57 12/1/2023 $2,985,635.15 $19,515.91 $0.00 $19,515.91 $7,075.77 $12,440.15 $2,978,559.38 $752,573.52 58 1/1/2024 $2,978,559.38 $19,515.91 $0.00 $19,515.91 $7,105.25 $12,410.66 $2,971,454.13 $764,984.19 59 2/1/2024 $2,971,454.13 $19,515.91 $0.00 $19,515.91 $7,134.85 $12,381.06 $2,964,319.28 $777,365.25 60 3/1/2024 $2,964,319.28 $19,515.91 $0.00 $19,515.91 $7,164.58 $12,351.33 $2,957,154.70 $789,716.58 61 4/1/2024 $2,957,154.70 $19,515.91 $0.00 $19,515.91 $7,194.43 $12,321.48 $2,949,960.26 $802,038.05 62 5/1/2024 $2,949,960.26 $19,515.91 $0.00 $19,515.91 $7,224.41 $12,291.50 $2,942,735.85 $814,329.56
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help