Ridge Business Center

Ridge Business Center is a warehouse/office property in Charlotte. The property includes a well-maintained building totalling 5
The property has been offered for sale by the current owner at asking price of $5 Million. As a Financial Analyst of XYZ Investo
be a good acquisition for the company. An investment must generate a minimum of 15% IRR for XYZ Investors.
Please build a financial model using the assumptions below to provide an answer to the Management. Required sheets have b

Investment Assumptions
Acquisition Date 1-Jan-21
Holding Period 5 years * time for which the property is owned
Sale Date last day of 60th month
Terminal Cap Rate 7.5% * This rate is used to determine Sale Price by the following formula: Sale Price=
Cost of Sale 2% * 2% of Sale Earnings go to broker/lawyer

Loan Assumptions
Loan Start Date 1-Jan-21
Term of Loan 5 years
LTV 75% * of Price
Interest Rate 4.0%
Loan Fees 1%
Amortization Period 25 years * Calculate monthly loan payments, you will need an amortization schedule

Rent Assumptions
1) Assume rents increase every year on first day of the calendar year
2) Rents shown are Annual rents on a per square feet basis. Convert them to monthly rents and actual $ as and when required
3) NNN leases are triple net leases, tenant has to pay their proportionate share of CAM, taxes and insurance over and above th
4) Consider Suite 1006 as vacant for the entire duration of 5 years

Rent Roll ( As of 31 July, 2020)

Suite Leased Area Status Lease Start Date Lease End Date
(sq. ft) (mm/dd/yy) (mm/dd/yy)
1001 10,000 Occupied 03/01/18 03/31/28
1002 10,000 Occupied 06/01/19 06/30/29
1003 15,000 Occupied 01/01/20 01/31/30
1004 5,000 Occupied 03/01/18 03/31/28
1005 10,000 Occupied 06/01/19 06/30/29
1006 6,000 Vacant
Total 56,000

Expense Assumptions
1) All expenses are paid by landlord
2) All expenses grow 2% annually
Expense Assumptions
Expense Type Annual Expense ($)
CAM $25,000
Taxes $75,000
Insurance $25,000
Utilities $20,000
Repairs $45,000
Total $190,000

Capital Expenditure
Year Annual Amount
($ psf)
2021 $2.00
2022 $1.25
2023 $1.00
2024 $1.00
2025 $1.00
maintained building totalling 50,000 sq ft in leasable area.
nancial Analyst of XYZ Investors, you are expected to identify if the property will
XYZ Investors.
ment. Required sheets have been provided.

ollowing formula: Sale Price= 5th Year NOI/Terminal Cap Rate

an amortization schedule

actual $ as and when required.

d insurance over and above the rent

Annual Rent PSF Rent Increase Date Rent Increase Lease Type
(as of 31 July, 20) (mm/dd/yy) Annual (%)
$12.15 01/01/21 3% NNN
$11.50 01/01/21 3% NNN
$10.75 01/01/21 3% NNN
$12 01/01/21 5% NNN
$12 01/01/21 5% NNN

$10.32
 Test Taker First Name: Amar
Test Taker Last Name: Kendre
Test Taker Email: kendreamar2001@gmail.com
Test Taker Phone Number: 8208820919

Instructions:
1. Answer all the questions below
2. Once you finish make a simple DCF model and debt amortization schedule with the assumptions provided

Qs 1. Please refer to the excel sheet for the case study – Ridge Business Center. Use functions of Excel wherever necessary
Answer: 74%

Qs 2. Investor A & B both invested $100,000 in a private investment and were promised 10% IRR.
- A received $15,000 in Year 1 and $5000 in Year 2
- B received $10,000 in Year 1 and $10,000 in Year 2
Both of them exited the investment at the end of 2nd year. Even though both got 10% IRR on their investments, wha
Answer: The cash flow pattern resulted higher retuen for A in first year while B received less return than A .And in the second

Qs 3. Bond yields are inversely related to Interest Rates. What is the explanation for this relationship?
Answer: It means bond yield and interest rate has an inverse relationship,as when interest rate rises then the bond prices dec
(This interest rate is basically the repo rate governed by central bank of a country(formely the monetary policy))

Qs 4. What is DSCR? Why is it important to lenders?

Answer: Debt-service coverage ratio is basically a measure of the cash flow available to pay current debt obligations.
It helps the lenders to determine whether the businness has enough profit to pay back the debt or not.

Qs 5. Our client is a real estate private equity company that focuses on multifamily acquisitions. A property we are looking
What is the cap rate?
Answer: 5.33%

Qs 6. Describe what a promote structure is (also known as a waterfall) for sponsors and investors in real estate?
Answer: It is basically a chart showing that how an initail vaue is affected by a series of intermediate positive or negative valu
 Test Questions

of Excel wherever necessary, no macros should be used for the case study. Calculate Levered IRR on the investment using the data provid

RR on their investments, what could be the possible reason for B’s unhappiness?
rn than A .And in the second year A got additional 5000 but B received the same as the 1st year.

ses then the bond prices decreses and when interest rate falls then the value of bond prices increases.
y the monetary policy))

nt debt obligations.
he debt or not.

s. A property we are looking at has a net operating income (NOI) of $567,678. The asking price is $10,650,567.

ors in real estate?

ate positive or negative values.It signifies the retuen thinvestor would be getting with the revenue generated by the business.
ment using the data provided.

y the business.
You must calculate the sections mentioned below on this page, you can add assumptions or other information yo

Purchase Price
Net Sale Price
Net Cash Flow Before Debt (yearly)
Net Cash Flow After Debt (yearly)

Sources and Uses of Cash Flow

Sources Total %
Debt $ 3,750,000.00 75%
Equity $ 1,250,000.00 25%
Total $ 5,000,000.00 100%

Uses Total %
Acquisition $ 5,000,000.00 92.81%
Loan Fee $ 37,500.00 0.7%
Capital Expenditure $ 350,000.00 6.50%
Total $ 5,387,500.00 100%

Purchase Price $ 5,000,000.00

Net Sale Price $ 81,020,186.18

Cash Flow Initial Investment Year 2021 Year 2022

Cash flow Before Debt $ -5,000,000.00 $ 3,514,590.00 $ 4,232,963.70
Cash Flow After Debt $ -5,000,000.00 $ 3,364,590.00 $ 4,082,963.70
ptions or other information you deem relevant to this dashboard page

Returns & Yields

Equity Multiple 4
Levered IRR 74%
Levered Profit $ 493,505.71
Return on Equity 39.48%

Year 2023 Year 2024 Year 2025

$ 4,624,501.73 $ 5,150,038.01 $ 5,404,513.96
$ 4,474,501.73 $ 5,000,038.01 $ 5,254,513.96
Analysis Date 1-Jan-21
1/1/2021
Month 0 Month 1 Month 2 Month 3
Sections required
Income $ 577,750.00 $ 598,682.50 $ 598,682.50 $ 598,682.50
Expenses $ 190,000.00 $ 193,800.00 $ 193,800.00 $ 193,800.00
NOI $ 387,750.00 $ 404,882.50 $ 404,882.50 $ 404,882.50
Cash flow Before Debt $ 292,882.50 $ 292,882.50 $ 292,882.50
Cash Flow After Debt $ 280,382.50 $ 280,382.50 $ 280,382.50
Interest(4% of 3750000=150000 yearly)

Working Notes
Suite Leased Area Year 0 Year 1 Year 2 Year 3 Year 3
1001 10,000 $12.15 $ 12.51 $ 12.89 $ 13.28 $ 13.67
1002 10,000 $11.50 $ 11.85 $ 12.20 $ 12.57 $ 12.94
1003 15,000 $10.75 $ 11.07 $ 11.40 $ 11.75 $ 12.10
1004 5,000 $12 $ 12.60 $ 13.23 $ 13.89 $ 14.59
1005 10,000 $12 $ 12.60 $ 13.23 $ 13.89 $ 14.59
1006 6,000 $0.00 $ - $ - $ - $ -
 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10

$ 598,682.50 $ 598,682.50 $ 598,682.50 $ 598,682.50 $ 598,682.50 $ 598,682.50 $ 598,682.50

$ 193,800.00 $ 193,800.00 $ 193,800.00 $ 193,800.00 $ 193,800.00 $ 193,800.00 $ 193,800.00
$ 404,882.50 $ 404,882.50 $ 404,882.50 $ 404,882.50 $ 404,882.50 $ 404,882.50 $ 404,882.50
$ 292,882.50 $ 292,882.50 $ 292,882.50 $ 292,882.50 $ 292,882.50 $ 292,882.50 $ 292,882.50
$ 280,382.50 $ 280,382.50 $ 280,382.50 $ 280,382.50 $ 280,382.50 $ 280,382.50 $ 280,382.50

Year 4 Year 5
$ 14.09 $ 14.51
$ 13.33 $ 13.73
$ 12.46 $ 12.84
$ 15.32 $ 16.08
$ 15.32 $ 16.08
$ - $ -
 Month 11 Month 12 Month 13 Month 14 Month 15 Month 16 Month 17

$ 598,682.50 $ 598,682.50 $ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98

$ 193,800.00 $ 193,800.00 $ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00
$ 404,882.50 $ 404,882.50 $ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98
$ 292,882.50 $ 292,882.50 $ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98
$ 280,382.50 $ 280,382.50 $ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98
 Month 18 Month 19 Month 20 Month 21 Month 22 Month 23 Month 24

$ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98 $ 620,422.98

$ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00 $ 197,676.00
$ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98 $ 422,746.98
$ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98 $ 352,746.98
$ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98 $ 340,246.98
 Month 25 Month 26 Month 27 Month 28 Month 29 Month 30 Month 31

$ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66

$ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52
$ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14
$ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14
$ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14
 Month 32 Month 33 Month 34 Month 35 Month 36 Month 37 Month 38

$ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66 $ 643,004.66 $ 690,831.94 $ 690,831.94

$ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52 $ 201,629.52 $ 205,662.11 $ 205,662.11
$ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14 $ 441,375.14 $ 485,169.83 $ 485,169.83
$ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14 $ 385,375.14 $ 429,169.83 $ 429,169.83
$ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14 $ 372,875.14 $ 416,669.83 $ 416,669.83
 Month 39 Month 40 Month 41 Month 42 Month 43 Month 44 Month 45

$ 690,831.94 $ 690,831.94 $ 690,831.94 $ 690,831.94 $ 690,831.94 $ 690,831.94 $ 690,831.94

$ 205,662.11 $ 205,662.11 $ 205,662.11 $ 205,662.11 $ 205,662.11 $ 205,662.11 $ 205,662.11
$ 485,169.83 $ 485,169.83 $ 485,169.83 $ 485,169.83 $ 485,169.83 $ 485,169.83 $ 485,169.83
$ 429,169.83 $ 429,169.83 $ 429,169.83 $ 429,169.83 $ 429,169.83 $ 429,169.83 $ 429,169.83
$ 416,669.83 $ 416,669.83 $ 416,669.83 $ 416,669.83 $ 416,669.83 $ 416,669.83 $ 416,669.83
 Month 46 Month 47 Month 48 Month 49 Month 50 Month 51 Month 52

$ 690,831.94 $ 690,831.94 $ 690,831.94 $ 716,151.52 $ 716,151.52 $ 716,151.52 $ 716,151.52

$ 205,662.11 $ 205,662.11 $ 205,662.11 $ 209,775.35 $ 209,775.35 $ 209,775.35 $ 209,775.35
$ 485,169.83 $ 485,169.83 $ 485,169.83 $ 506,376.16 $ 506,376.16 $ 506,376.16 $ 506,376.16
$ 429,169.83 $ 429,169.83 $ 429,169.83 $ 450,376.16 $ 450,376.16 $ 450,376.16 $ 450,376.16
$ 416,669.83 $ 416,669.83 $ 416,669.83 $ 437,876.16 $ 437,876.16 $ 437,876.16 $ 437,876.16
 Month 53 Month 54 Month 55 Month 56 Month 57 Month 58 Month 59

$ 716,151.52 $ 716,151.52 $ 716,151.52 $ 716,151.52 $ 716,151.52 $ 716,151.52 $ 716,151.52

$ 209,775.35 $ 209,775.35 $ 209,775.35 $ 209,775.35 $ 209,775.35 $ 209,775.35 $ 209,775.35
$ 506,376.16 $ 506,376.16 $ 506,376.16 $ 506,376.16 $ 506,376.16 $ 506,376.16 $ 506,376.16
$ 450,376.16 $ 450,376.16 $ 450,376.16 $ 450,376.16 $ 450,376.16 $ 450,376.16 $ 450,376.16
$ 437,876.16 $ 437,876.16 $ 437,876.16 $ 437,876.16 $ 437,876.16 $ 437,876.16 $ 437,876.16
 Month 60

$ 716,151.52
$ 209,775.35
$ 506,376.16
$ 450,376.16
$ 437,876.16
* Summarize the monthly Cash flow Annually here
Year 1 Year 2 Year 3 Year 4
Income $ 7,184,190.00 $ 7,445,075.70 $ 7,716,055.97 $ 8,289,983.33
Expenses $ 2,325,600.00 $ 2,372,112.00 $ 2,419,554.24 $ 2,467,945.32
NOI $ 4,858,590.00 $ 5,072,963.70 $ 5,296,501.73 $ 5,822,038.01
Cash flow Before Debt $ 3,514,590.00 $ 4,232,963.70 $ 4,624,501.73 $ 5,150,038.01
Cash Flow After Debt $ 3,364,590.00 $ 4,082,963.70 $ 4,474,501.73 $ 5,000,038.01
Interest(4% of 3750000=150000 yearly)
 Year 5
$ 8,593,818.20
$ 2,517,304.23
$ 6,076,513.96
$ 5,404,513.96
$ 5,254,513.96
Amoritization of Loan
Number of periods 60
Interest rate (annual) 4%
Interest rate (monthly) 0.33%
Loan Amount ($) $ 3,750,000.00

Monthly payment ($) $ 68,994.29

Period Payment Interest

1 $ 68,994.29 $ 12,375.00
2 $ 68,994.29 $ 12,188.16
3 $ 68,994.29 $ 12,000.70
4 $ 68,994.29 $ 11,812.62
5 $ 68,994.29 $ 11,623.92
6 $ 68,994.29 $ 11,434.60
7 $ 68,994.29 $ 11,244.65
8 $ 68,994.29 $ 11,054.07
9 $ 68,994.29 $ 10,862.87
10 $ 68,994.29 $ 10,671.04
11 $ 68,994.29 $ 10,478.57
12 $ 68,994.29 $ 10,285.47
13 $ 68,994.29 $ 10,091.73
14 $ 68,994.29 $ 9,897.35
15 $ 68,994.29 $ 9,702.33
16 $ 68,994.29 $ 9,506.67
17 $ 68,994.29 $ 9,310.36
18 $ 68,994.29 $ 9,113.40
19 $ 68,994.29 $ 8,915.80
20 $ 68,994.29 $ 8,717.54
21 $ 68,994.29 $ 8,518.62
22 $ 68,994.29 $ 8,319.05
23 $ 68,994.29 $ 8,118.83
24 $ 68,994.29 $ 7,917.94
25 $ 68,994.29 $ 7,716.38
26 $ 68,994.29 $ 7,514.17
27 $ 68,994.29 $ 7,311.28
28 $ 68,994.29 $ 7,107.73
29 $ 68,994.29 $ 6,903.50
30 $ 68,994.29 $ 6,698.60
31 $ 68,994.29 $ 6,493.03
32 $ 68,994.29 $ 6,286.77
33 $ 68,994.29 $ 6,079.84
34 $ 68,994.29 $ 5,872.22
35 $ 68,994.29 $ 5,663.92
36 $ 68,994.29 $ 5,454.93
37 $ 68,994.29 $ 5,245.25
38 $ 68,994.29 $ 5,034.88
39 $ 68,994.29 $ 4,823.81
40 $ 68,994.29 $ 4,612.05
41 $ 68,994.29 $ 4,399.59
42 $ 68,994.29 $ 4,186.42
43 $ 68,994.29 $ 3,972.56
44 $ 68,994.29 $ 3,757.99
45 $ 68,994.29 $ 3,542.71
46 $ 68,994.29 $ 3,326.72
47 $ 68,994.29 $ 3,110.01
48 $ 68,994.29 $ 2,892.60
49 $ 68,994.29 $ 2,674.46
50 $ 68,994.29 $ 2,455.60
51 $ 68,994.29 $ 2,236.03
52 $ 68,994.29 $ 2,015.72
53 $ 68,994.29 $ 1,794.70
54 $ 68,994.29 $ 1,572.94
55 $ 68,994.29 $ 1,350.45
56 $ 68,994.29 $ 1,127.22
57 $ 68,994.29 $ 903.26
58 $ 68,994.29 $ 678.56
59 $ 68,994.29 $ 453.12
60 $ 68,994.29 $ 226.93
 Principal Residual Debt
$ 56,619.29 $ 3,693,380.71
$ 56,806.13 $ 3,636,574.58
$ 56,993.59 $ 3,579,580.99
$ 57,181.67 $ 3,522,399.32
$ 57,370.37 $ 3,465,028.95
$ 57,559.69 $ 3,407,469.26
$ 57,749.64 $ 3,349,719.62
$ 57,940.21 $ 3,291,779.41
$ 58,131.42 $ 3,233,647.99
$ 58,323.25 $ 3,175,324.75
$ 58,515.72 $ 3,116,809.03
$ 58,708.82 $ 3,058,100.21
$ 58,902.56 $ 2,999,197.66
$ 59,096.93 $ 2,940,100.72
$ 59,291.95 $ 2,880,808.77
$ 59,487.62 $ 2,821,321.15
$ 59,683.93 $ 2,761,637.22
$ 59,880.88 $ 2,701,756.34
$ 60,078.49 $ 2,641,677.85
$ 60,276.75 $ 2,581,401.10
$ 60,475.66 $ 2,520,925.43
$ 60,675.23 $ 2,460,250.20
$ 60,875.46 $ 2,399,374.74
$ 61,076.35 $ 2,338,298.39
$ 61,277.90 $ 2,277,020.49
$ 61,480.12 $ 2,215,540.37
$ 61,683.00 $ 2,153,857.36
$ 61,886.56 $ 2,091,970.80
$ 62,090.78 $ 2,029,880.02
$ 62,295.68 $ 1,967,584.34
$ 62,501.26 $ 1,905,083.08
$ 62,707.51 $ 1,842,375.57
$ 62,914.45 $ 1,779,461.12
$ 63,122.07 $ 1,716,339.05
$ 63,330.37 $ 1,653,008.69
$ 63,539.36 $ 1,589,469.33
$ 63,749.04 $ 1,525,720.29
$ 63,959.41 $ 1,461,760.88
$ 64,170.48 $ 1,397,590.40
$ 64,382.24 $ 1,333,208.16
$ 64,594.70 $ 1,268,613.46
$ 64,807.86 $ 1,203,805.60
$ 65,021.73 $ 1,138,783.87
$ 65,236.30 $ 1,073,547.57
$ 65,451.58 $ 1,008,095.99
$ 65,667.57 $ 942,428.42
$ 65,884.27 $ 876,544.15
$ 66,101.69 $ 810,442.46
$ 66,319.83 $ 744,122.63
$ 66,538.68 $ 677,583.95
$ 66,758.26 $ 610,825.69
$ 66,978.56 $ 543,847.13
$ 67,199.59 $ 476,647.53
$ 67,421.35 $ 409,226.18
$ 67,643.84 $ 341,582.34
$ 67,867.07 $ 273,715.28
$ 68,091.03 $ 205,624.25
$ 68,315.73 $ 137,308.52
$ 68,541.17 $ 68,767.35
$ 68,767.35 $ -