Modeling Tests - Free Sample

Tests Are in the comments

Tests are in the comments






Tests are in the comments

Testa are in the commenfs

Comments (37)

  • Analyst 2 in RE - Comm

Also interested - thank you!

comrade1, what's your opinion? Comment below:

Also interested - thank you!

Crumbpicker, what's your opinion? Comment below:

Thank you for sharing!

Most Helpful
  • Analyst 1 in IB - Gen

Modeling Exercise 1

All inputs below should be flexible assumptions

Development Program
* 200,000 SF office building
* Land purchase price: $20M ($100 per FAR)
* Closing Costs: 1% of purchase price
* Hard Costs: $300 psf
* Soft Costs: (excluding TI's, LC's and Debt): 15% of hard costs
* TI's: $60 psf - paid at tenant occupancy
* LC's: $18 psf - paid six months before tenant occupancy

Construction & Lease-up
* 24 Month Construction Period, beginning at land close date
* Costs spent evenly over construction period
* 2 Tenant Lease-up of equal size (one tenant at construction completion; one 6 months after completion)
* Lease up to 95%
* Rent $4.25 NNN
* Free Rent: 3 months free
* Annual rental bumps: 3%
* Annual Operating Expenses during Lease-Up: $16 psf

Debt Assumptions
* 60% LTC
* Rate: 5% all-in interest rate
* All equity drawn first; then debt
* Use available cash flow to offset debt costs, as available

Hold Period:
* 5 years after stabilization
* Exit Cap Rate: 5.5%
* Transaction Fees: 1.5%

Joint Venture Structure
* LP invests 95% of required equity / GP invests 5%
* GP receives a 20% promoted interest over a 12% 
IRR to the LP

Required Output
* Required Project Equity, Net Profit, IRR and ROC (Return on Capital)
* Required LP (after promote) Equity, Net Profit, IRR and ROC (Return on Capital)

  • 5
  • Analyst 1 in IB - Cov

Does anyone have solutions to these?

  • Analyst 1 in IB - Gen

Best bet is to use a free modeling service (i think someone mentioned Kahr real estate) and learn the concepts there and then apply them to these tests. If all you ever do is look at test prompts + the answer key, you'll never learn anything.

Save these and use them as timed tests of your skills. Keep in mind most of these are administered over a short time period - they're not meant to be complicated at all

  • Analyst 1 in IB - Gen


-        E-mail the responses no later than 4.5 hours after receiving the test.

Case Study:

Certain affiliates of [REPE Fund] are considering the acquisition of a 350,000 SF, Class A office campus in suburban Dallas, TX (the "Property").  Completed in 2002, the Property is comprised of 5 office buildings, each with 70,000 NRSF.  Only one of the five buildings is currently occupied, leased on a NNN basis to Tech Corp., a major credit rated tenant, with a remaining lease term of 15 years with an annual rent of $1.12 million ($16.00 per SF), escalating at 2.5% annually.

[REPE Fund]'s business plan is to lease the four remaining buildings and sell the property three to five years after the acquisition.  According to a market study prepared by a national research firm, it is likely to take 24 months to complete the lease-up. Today, average market rents are $13.50 per SF (NNN). Typical lease terms are for five ayears with tenant improvement allowances of $25 psf, leasing commissions of $5 psf and lease rates increase 3.0% annually. Local leasing brokers expect tenants to lease an entire building at a time. Assume expenses for the property of $8.0 psf and a property management fee of 3.0% of EGR. Market vacancy is 12.5%.

According to discussions with several local brokers, based on the age and quality of the building, likely tenant profile, and its location, the Property is likely to sell for a 7.50% cap rate and similar stabilized properties in the surrounding submarket generally trade in the $140 to $160 per square foot range.

The negotiated purchase price for the Property is $37.5 million.  In addition, [REPE Fund] is expected to incur closing costs (for legal counsel and third-party consultants) of $500k.  [REPE Fund] has obtained several loan quotes to finance the acquisition of the Property.  The most favorable quote was 65% LTV with 65% of future funding for TI / LC / Cap Ex, fixed rate of 4.50% (actual / 360), five year term, interest only for the first 30 months with a 25 year amortization thereafter.

Please create a monthly cash flow model, which reflects the assumptions and business plan outlined above and calculates the levered and unlevered returns of the proposed investment to [REPE Fund]. Create a summary page for key assumptions, annual cash flows, return metrics (Profit, IRR, Investment Multiple), any other metrics you would use to evaluate an investment, and a sensitivity analysis.

  • 2
lebronsthebest, what's your opinion? Comment below:

Have a completed model for this? Would like to check my answers

  • Analyst 1 in IB - Gen

Post your model in a drop box / google drive and I'll give you comments

  • Analyst 1 in IB - Gen
[Comment removed by mod team]
  • Analyst 1 in IB - Gen

fund itsts evaluating a 125 unit stabilized apartment building located in a thriving emerging submarket of DC as a potential acquisition. Your task is to put together a 1 page financial model in excel which evaluates the investment opportunity. The key assumptions are stated on the next page. Please make sure your analysis clearly indicates which cells are assumptions vs. formulas in your spreadsheet. Your base case underwriting should solve for a purchase price which results in a 15.0% levered IRR over a 5 year hold period. The cash flows should show how you get from Effective Gross Revenue down to NOI, down to your unlevered and levered cash flows.

Please make sure to calculate:

 Unlevered IRR and Cash-on-Cash Returns

 Levered IRR and Cash-on-Cash Returns

 Equity Multiple

 Going-in Cap Rate and Stabilized Cap Rate

 Purchase Price PSF

 Sale Price PSF


Additionally please include a sensitivity table which evaluates the levered IRR and equity multiple at different exit cap rates and purchase prices. Assume there are no transaction / sales costs as a part of this analysis. You will have 45 minutes to complete the analysis. Please complete as much as you can during that time period.

  • 3
Tony Spumoni, what's your opinion? Comment below:

Thanks for posting. Is this answer posted somewhere? I solved for a purchase price of $54,897,076.

007en, what's your opinion? Comment below:

Very Interested

Regards, 007
  • 1
  • Analyst 1 in IB - Gen




loli, what's your opinion? Comment below:

Thank you interested!!

McFate, what's your opinion? Comment below:

Anonymous Debt Fund - Simple Test

You are an institutional real estate investor who has the opportunity to purchase a 85% leased, 326,642
square feet office building in Irvine, CA. Comparable properties have sold between $270 and $300 per
square foot (PSF), and you think a fair price for this property is $300 PSF.

Based on your underwriting, you estimate that the property's Net Operating Income (NOI) will be
$6,500,000 in Year 1 and will grow by 6% in Year 2, 5% in Year 3, 4% in Year 4, and 3% thereafter. You
estimate that it will cost $0.75 per square foot per year in capital costs (tenant improvements, leasing
commissions, and other capital costs) to keep the building fully leased. Your plan is to sell the building
after 5 years (assume you can sell the property at a 7.25% cap rate and total sales costs of 2.00% of the
gross price).

Your plan is to leverage the investment with a bank loan. A regional bank has provided you with a quote
for a 65% loan to value (LTV), five-year, 30-year amortization loan at a 3.25% rate with a 1.00%
origination fee.

1) What is the proposed investment's unleveraged IRR?
2) What is the proposed investment's leveraged IRR?
3) Bonus: What is the Bank's Year 1 Debt Yield?
4) Bonus: What is the IRR of the bank's loan?
5) Bonus: Explain why the IRR of the bank's investment is different, if at all, from the quoted 3.25%

  • 2
jdbjd, what's your opinion? Comment below:

Ut expedita nemo natus doloremque voluptatem. Ipsum praesentium qui modi laboriosam eveniet nisi. Et sint culpa ut quo distinctio illum velit eaque. Deleniti repellendus qui unde velit eius ut. Id aut qui ad rem.

Harum sint qui velit autem nihil. Tempora aut eius dolor consequatur inventore. Vel laboriosam modi illo voluptatem minima.

Consequatur mollitia fuga ipsa neque. Voluptatem molestias qui earum ut. Porro harum animi alias ea quia. Sunt dignissimos nulla odit quia in doloremque facilis porro. Numquam autem quo velit et laboriosam consectetur assumenda.

thesmartadvisory, what's your opinion? Comment below:

Odit dolorem ut et tempora aut et nostrum. At debitis dicta ea beatae velit ipsum. Architecto quasi est voluptate voluptatem corrupti. Sunt voluptatem id qui velit veniam sed illo. Ut rerum adipisci ea magni in veniam placeat aut.

Quidem repudiandae ducimus qui nam unde natus. Ex occaecati unde et placeat commodi autem. Tenetur mollitia eos et dolores exercitationem est maxime. Tempore autem exercitationem magni velit nihil.

Ut eveniet doloribus cum consequatur praesentium quasi. Voluptates vero nesciunt et et sequi perferendis. Ullam modi odio numquam ratione. Illum at doloremque ex numquam ea.

Tenetur deleniti alias temporibus rerum. Unde eum id vel quia. Deserunt exercitationem laudantium voluptate veniam delectus expedita.

[Comment removed by mod team]
Start Discussion

Career Advancement Opportunities

March 2023 Investment Banking

  • Lazard Freres (+ +) 99.5%
  • Lincoln International (= =) 99.1%
  • Jefferies & Company (▽02) 98.6%
  • Financial Technology Partners (▽01) 98.2%
  • William Blair (▲11) 97.7%

Overall Employee Satisfaction

March 2023 Investment Banking

  • William Blair (▲05) 99.5%
  • Lincoln International (▲11) 99.1%
  • Canaccord Genuity (▲17) 98.6%
  • Stephens Inc (▲10) 98.1%
  • Financial Technology Partners (▲04) 97.7%

Professional Growth Opportunities

March 2023 Investment Banking

  • Financial Technology Partners (▲05) 99.5%
  • Lincoln International (▲01) 99.1%
  • Lazard Freres (▲14) 98.6%
  • Jefferies & Company (▽03) 98.1%
  • William Blair (▲03) 97.7%

Total Avg Compensation

March 2023 Investment Banking

  • Director/MD (6) $592
  • Vice President (27) $425
  • Associates (141) $260
  • 3rd+ Year Analyst (9) $194
  • 2nd Year Analyst (86) $170
  • 1st Year Analyst (265) $170
  • Intern/Summer Associate (45) $165
  • Intern/Summer Analyst (194) $92