In this article we are going to conduct an investment analysis on a 140 unit apartment building acquisition. We’ll walk through the process of forecasting cash flows and also explain the calculations needed to determine investment value. Read on as we take a deep dive into the world of apartment investing.

## Apartment Investment Case Study Objectives

First, before we jump into the details of this investment analysis, let’s quickly go over our objectives. Here’s what we’ll accomplish in this case study:

- Forecast the before tax cash flows over a 5 year holding period for a 140 unit apartment building.
- Calculate the maximum supportable loan amount based on the debt service coverage ratio and the loan to value ratio.
- Calculate the gross rent multiplier.
- Calculate the cash on cash return.
- Calculate the debt service coverage ratio.
- Complete a discounted cash flow analysis to determine the levered and unlevered internal rate of return (IRR) and net present value (NPV).
- Stress test the vacancy rate to analyze how it impacts cash flow.
- Stress test the vacancy rate to analyze how it impacts the debt service coverage ratio.
- Stress test the loan interest rate to analyze how it impacts the debt service coverage ratio.

## Apartment Investment Case Study Scenario

An investor is considering buying an apartment building with 140 units offered for sale at $16,500,000. The subject apartment building has the following unit mix:

Additionally, the following assumptions are also being made by the investor in order to construct a 5-year cash flow proforma:

**Vacancy and Credit Loss **In the current market, vacancy and credit losses are running at 9%. Due to the improving market conditions as well as the investor’s prior experience leasing and operating multifamily buildings, it’s expected that vacancy will steadily decline over the next 5 years to 5%.

**Potential Rental Income **Potential rental income is based on the above unit mix. The 1-bedroom and studio rental rates are expected to increase at 2% annually. The 2-bedroom units are also expected to increase at 2% annually.

**Financing **After a preliminary discussion with a relationship manager at a local bank it’s determined that a loan can be extended based on the lesser of a 1.25x debt service coverage ratio or 80% loan to value. Additionally, assuming the underwriting process doesn’t reveal any red flags, it’s expected that the loan will be based on a 20 year amortization and a 6% interest rate.

**Operating Expenses **The following table breaks out historical operating expenses for the property as well as projected increases over the holding period.

**Reserves for Replacement **In addition to the above operating expenses a reserve for replacement of $250 per unit will also be included in this analysis.

**Sales Price and Cost of Sale **The projected sale price is estimated by applying a conservative 3% annual growth rate to the acquisition price of the property over the 5 year holding period. Additionally, a 6% cost of sale is factored into the net sales proceeds to account for selling costs.

**Acquisition Costs**

In addition to the $16,500,000 purchase price, an additional $50,000 is factored in to account for closing costs.

**Discount Rate **For the purposes of this case study we’ll assume that the investor’s discount rate, or required rate of return, is 15%.

## Apartment Investment Proforma

Using the above assumption we can now build a proforma for the proposed apartment investment property. This can be accomplished in Excel in a few hours, or in our case we did this entire analysis in less than 10 minutes using PropertyMetrics.

Now that we have a 5 year proforma, let’s take a look at what the maximum supportable loan amount is based on these cash flows. Using the above 1.25x DSCR and 80% LTV assumptions we get the following:

As shown above, the maximum loan analysis based on year 1 proforma NOI is about $12,250,000. Assuming we can get this loan amount approved at the 6% rate amortized over 20 years, this is the updated proforma:

You’ll notice that the debt we added to the property reduced our cash flow significantly, however, this also reduces our equity requirement and therefore improves yield. We’ll discuss this in more detail below, but first let’s take a look at some quick ratios:

As shown above, you can see that the Year 1 cash on cash return is 6.14%. At first glance this is well below our target rate of return of 15%, However, because the cash on cash return only takes into account a single year instead of the entire holding period, the IRR and NPV below will be much more relevant for our purposes.

The gross rent multiplier is 7.58x, which taken by itself doesn’t mean much. However, assuming we have some other submarket data to compare this to we can check whether or not it’s in line with comparable properties. The important take away here is to check whether or not the gross rent multiplier is abnormal, and if so, to further investigate why.

The debt service coverage ratio is in line with the bank’s requirement of 1.25x and improves over the holding period. This is partially due to our assumption of increasing occupancy over our investment horizon, as well as increasing rental rates. We’ll stress test these assumptions further below, but at first glance the DSCR is adequate for this deal.

Finally, the breakeven occupancy on this property is just under 79%. This means total vacancy can go up to 21% and the property will still produce enough cash flow to cover expenses and debt service. Good to know.

## Apartment Discounted Cash Flow Analysis

Screening this property with the above ratios is a good starting point, but ultimately a full discounted cash flow analysis should be completed to determine IRR and NPV:

As you can see above, the levered internal rate of return comes in at 18.65%. While the above Year 1 cash on cash return didn’t meet our required rate of return of 15%, the full discounted cash flow analysis shows that the yield on this investment comfortably exceeds our target return. In fact, the net present value tells us that we can pay about $650,000 over the asking price and we’ll still achieve our target yield. This can come in handy during negotiations, especially in a competitive bidding situation.

## Apartment Investment Sensitivity

So after a quick first pass it appears that this potential acquisition meets our target yield based on some reasonable assumptions. However, what if our assumption of a declining vacancy rate, from 9% in Year 1 to 5% in Year 5, turns out to be overly optimistic? Let’s take a look at what we deem to be a worst case scenario – that the market vacancy rate actually deteriorates after we acquire the property to 15%, rather than the current 9%. How will this impact our IRR?

While a worse than expected vacancy rate does reduce our cash flow, it turns out that we’d still achieve our target yield of 15%. What about the debt service coverage ratio? Will a higher than expected vacancy rate violate our 1.25x DSCR loan covenant? Let’s take a look at what happens to the debt service coverage ratio as we move from a 6% vacancy rate all the way up to a 20% vacancy rate:

As shown above, in Year 1 we actually can’t support a 1.25x DSCR requirement at a vacancy rate of 10%. While this does improve over the holding period, this could be problematic for us if the market turns out worse than expected. This could also be discovered during the loan underwriting process, resulting in a lower loan amount or stronger loan covenants. This information might come in handy during negotiations.

Next, let’s take a look at how sensitive our DSCR is to our loan interest rate. This is useful in understanding how much wiggle room there is in negotiating the interest rate with our bank, as well as understanding how capital market conditions might affect the buyer of our property at the end of the holding period.

As shown above, 6% appears to be the upper limit loan interest rate based on Year 1 cash flow. Once the rate gets beyond 6% it starts eating into our 1.25x DSCR requirement. However, in subsequent years, assuming we hit our projections, the property can support a much higher interest rate based on the same loan amount. This provides some cushion toward the end of our holding period, in case the then prevailing market conditions change and interest rates rise.

## Conclusion

While there are several different angles you can look at when underwriting a potential acquisition, this simple case study illustrates a few core concepts. First, sizing up a loan amount based on proforma cash flow. Second, calculating and interpreting several quick but useful ratios. Third, understand whether or not an acquisition meets a target yield. And finally, understanding how changes in our assumptions affect our resulting cash flow and underwriting ratios. While there are several additional layers of analysis we can dive into for a property like this, the above analysis gives us a good starting point for screening this particular property.

You mention that there are “several additional layers of analysis” which could be done. Could you give a brief overview of what these would be?

You could stress test different variables, create multiple scenarios (best case, worst case, most likely case), look at MIRR, capital accumulation, etc. You could even “score” the risk factors associated with the property to come up with a risk-adjusted IRR, which could then be used to make comparisons to other properties. Then, you could also do this all on an after-tax basis…

Is it appropriate to use the same discount rate for both levered and unlevered cash flows? Wouldn’t debt and equity command different required rates of returns and the allocation between the two sources of financing alter the discount rate accordingly? Thanks guys!

Yes it’s common to use a higher discount rate in the leveraged scenario due to the increased risk that leverage adds to a deal.

That’s not correct. The WACC, or weighted avg cost of capital would decrease as you add leverage since debt is cheaper than equity. However, considering the LTV, if per cent leverage is greater than market expectation, say 65%, then yes the equity yield would increase, as captured in an alpha risk that would be weighted by the equity, which would drive up WACC, but it wouldn’t be 1:1

The lower discount rate that results from increased debt is one of the commonly cited limitations of using WAAC as a discount rate. It doesn’t reflect the risk of the project itself. The Warren Buffet method would actually be to simply use the same discount rate for every deal you analyze. Then, based on your reasoned analysis of risk, you’d adjust the calculated present value by a margin of safety. At the end of the day the discount rate is subjective. It’s what you want to earn and it’s likely different for each investor.

Why do people say this. ” “what you want to earn and it’s likely “Different for each investor””. Off course it is. However, it seems to me that every investor wants to _earn_ the maximum that they can. That is no different for any investor. Would you rather earn 5%, 10%, 15%, or 20%.

It’s really about opportunity cost. However, if you input the rate you want to earn then your present value will reflect this. If you want to earn 20% but the resulting present value is insulting to the seller, then it’s not realistic. More here:

http://www.propertymetrics.com/blog/npv-discount-rate/

Then you go to another investment. It seems you are focusing completely on present value of certain markets. Most all markets are different; it’s just a matter of finding the right one.

Right, “going to another investment” is the concept of opportunity cost. If you can easily source similar investments that produce a 20% return, then it’d make sense to pass on a deal that only provides a 10% return.

That’s my point. Not necessarily getting a 20% return, but everyone wants the maximum return they can get. And, of course, we all know that we are looking for that 20% or more return, but we never know for certain that any particular investment will generate it.

I guess where we are on a different page here; you are looking at exact numbers (metrics), and I’m thinking more about the actual market, but when someone tells me “what they want to earn (and it’s different for every investor) they are stating the obvious.

I guess you guys are just showing “property metrics” and how the calculations are done; I’m just trying to be more realistic about what actually happens on the open market.

How do you get, $12,253,889 in year 1 of the max loan analysis?

And just to note, the 1-bedroom, you use a 2% increase when you said you would use.

Check this out for an explanation of the calculation:

http://www.propertymetrics.com/blog/how-commercial-real-estate-loan-underwriting-works/

It’s a few thousand dollars higher in the actual calculation, but we round down which is what lenders will do.

tks Robby, I got the cheat sheet to work this time, i am still digesting this

Can you confirm the calculation for the 6.14% C-o-C return in Year 1, please?

Are you including the additional $50,000 in closing costs?

is it not (noi for Y1 + closing costs?)/(20% deposit that is put down) =($1,316,860+50K)/$3,300,00= 40% but this is not right.

And $1,316,860/6.14% = 21 odd mill so not sure how you are getting this 6.14% value.

have tried the cheat sheet, but no joy yet.

What is the definition of cash on cash return?

Can you not just answer the question?

The cash on cash return is defined as annual before tax cash flow divided by total cash invested.

In this case is it for 1st year:

annual before tax cash flow = rent collected from tennent?

total cash invested = 20% deposit that is put down + closing costs (+ principal and interest costs for the year)?

have I got that right??

You got the definition right. To get total cash invested, first take the $16,500,000 purchase price plus $50,000 in closing costs, which totals $16,550,000 in acquisition costs. Then, subtract out the loan proceeds of $12,250,000 to get your total required equity contribution of $4,300,000.

Now, you simply take year 1 before tax cash flow of $263,706 and divide it by your $4,300,000 equity contribution.

If I do or don’t include it the answer still doesn’t come to 6.14%… Surely the author knows the answer?!??

The cash on cash return is defined as annual before tax cash flow divided by total cash invested. So, let’s calculate these two numbers.

To get total cash invested, first take the $16,500,000 purchase price plus $50,000 in closing costs, which totals $16,550,000 in acquisition costs. Then, subtract out the loan proceeds of $12,250,000 to get your total required equity contribution of $4,300,000.

Now, you simply take year 1 before tax cash flow of $263,706 and divide it by your $4,300,000 equity contribution.

Legend – many thanks

hi Robby, can you esplain why you use $4,300,000 as opposed to 20% of $16,550,000 = $3,310,000? As I thought it was 20% equity/deposit you had to put up or have I missed something? many thanks

Take a look here for how bank sizes a loan:

http://www.propertymetrics.com/blog/how-commercial-real-estate-loan-underwriting-works/

This same max loan analysis is used in the case study above.

Could anyone please write the formula for reversion amount?

Sales Price and Cost of Sale

The projected sale price is estimated by applying a conservative 3% annual growth rate to the acquisition price of the property over the 5 year holding period. Additionally, a 6% cost of sale is factored into the net sales proceeds to account for selling costs.

Formula please if u can

This is good stuff and very practical real world examples. RE: the reversion, in the example the going in/out cap rate are the same. I typically load the going in cap rate +/- 50 basis points and apply (exit cap) to reversion NOI? I haven’t used the method in the example by growing purchase price 3%. Just curious as I know that markets to vary in methodology and the yields do differ between the two methods.

re the bank loan figure:

1,053,488 is availble for debt service, with 1.25 being 1,316,860

why do you use 1,053,154, as the total for the bank loan in year 1-5

Read the section above on the maximum loan analysis. The max loan amount is rounded down.

In the bank Loan, how did we get the 11,453,354 in year 5?

fwiw, I get $11,402,191 in year 5. $1,053,154 (int and principal) + $10,349,037 remaining principal at end of year 5 (using monthly paydown). I am using ppmt and ipmt excel formulas, which kick out sum to the correct debt service (using monthly paydown, eg here is the ppmt formula: = ppmt(rate/12, year*12, Amortization years*12, original principal, 0)*12. There is at least one other calculation error in their version of the spreadsheet, this may be another.

Check your spreadsheet. If you use a financial calculator you’ll get $1,053,154 in annual payments with a balance at the end of year 5 of $10,400,201. Summing these two figures together gets you to the $11,453,354 shown above.

N = 240

i = 6%/12

PV = $12,250,000

Thanks, appreciate it. I’m new to the ppmt and ipmt functions. I used the cumprinc excel function and come up with your number. For some reason I can’t get the ppmt function, used every year and then summed, to reconcile with the cumprinc number. I’ll figure it out. Thanks for pointing this out. I’m an old guy trying to go back in time and figure out excel functions I haven’t used in over a decade.

There is calculation error in Property investment performa.

Where?

Cash flow is absolutely vital. So much so that my Company works with a lender who will do deals on apartments, self storage units, Industrial buildings, retail buildings and etc No tax returns required.

Do you offer an excel template that lays all of this out?

We don’t have an Excel template for this, but the entire analysis was done using our software here:

https://www.propertymetrics.com/software/real-estate-analysis/

Does the software include a mortgage calculator based on inputs e.g. loan amount, rate, amort schedule, etc? Or is the debt service something we have to plug in?

Yes, we have extensive loan functionality built into the software.

How is the reversion calculated? I couldn’t get the 17,980,341 number.

It did state that there is a 3% growth from the acquisition price and a 6% cost of sale. So would the calculation be 16,500,000*(1+0.09)=17,985,000? Also, there is a 50,000 closing cost. I was wondering where this would fit into the proforma? Would this go into reversion as well?

Close. If you grow the $16,500,000 purchase price by 3% over 5 years, you get (1.03)^5 times $16,500,000, which is $19,128,022. This is the projected sale price at the end of year 5. Then, if you net out the assumed 6% selling cost, you get reversion figure 17,980,341.

Thank you very much. Understood completely.

I can not get to 187.3% cash on cash return on the 5th year of the investment. I getting 210.66%. I am considering 17,980,000.00 for sales proceeds and a remaining bank debt of 10,456,538.00 at the end of 5th year. Could someone show me the step by step calc to get to 187.3%? Thanks

Why are you getting 1,054M as a Debt Service Payment. It should be 1,134,826. I am using the constant and the I multiply for the Loan Amount 1,665,0000 * 80%. If you could please explain why are you using 1,054M

How are you getting to 1.054M as a the Debt Service Payment. It should be $1,134,826.80. The LTV is 80% and the constant 8.60%. That does not give me 1.054M. Also, the rent bumps on the 1bed and 1 bath are on the assumptions 1% but on the proforma you made it 2%

.

The 1.054M is just the amount available to pay debt service. You get that by dividing the NOI by the debt service coverage ratio of 1.25. This tells you the max amount your debt service can be while still providing the 1.25x DSCR cushion. Also, on the rent bumps, the studio units increase at 1% but the other units increase at 2%. See this for more:

https://www.propertymetrics.com/blog/how-commercial-real-estate-loan-underwriting-works/

I just noticed that error as well – 2%inc on the 1br/1ba vs preivously stated 1%. PM’s response doesnt address it but I guess I will just make it 2% in my model and consider it an error.

We updated the setup assumptions to reflect this.

Great case study.

How do I get “Max Loan using 1.25x DSCR”?

https://ckx564arsfh1icytr1p1ix7z-wpengine.netdna-ssl.com/wp-content/uploads/2014/04/apartment_investing_loan_underwriting.png

The 1.25x DSCR is given in the assumptions. In practice this will be driven by bank loan policy and market conditions.

Great Case Study. How is Max Loan Using 1.25x DSCR calculated? I couldn’t back into it.

I am unable to back into the max loan amount, is there any way you can expand on it and perhaps show thte math. After running a PV calculation on excel I obtained $12,083,424.36. This is using a 5% rate 20yr per and Available for DS pmt of $1053,488.

Thank you.

Check this:

https://www.propertymetrics.com/blog/how-commercial-real-estate-loan-underwriting-works/

your PV formula is wrong, you need to divide your interest rate by 12, times the amortization period by 12, and divide your annual payment by 12, in this case it is 1,053,488. remember your loan payment is made monthly, therefore you should always divide everything by 12 in Excel.

Can you back into the NPV formula for us to see how you obtained $650670 using 15% discrate. Even by hardcoding my model I obtain $569,181.

Thanks!