Question

In: Finance

Underwriting information: 200 unit apartment building 1,000 per month average rent 8% vacancy 40% total operating...

Underwriting information: 200 unit apartment building 1,000 per month average rent 8% vacancy 40% total operating expense ratio 3% growth rate Purchase price is $20 million Exit cap rate is 7.5% Unlevered discount rate is 8% Assume 5 year holding period

2. Assume the above but now with the following loan information: Loan principal of $10 million 5% interest rate 30 year term with amortization 2% loan fees

Calculate levered cash flows

Calculate net sales proceeds after debt repayment

What are net loan proceeds?

What is monthly loan payment?

What is effective annual interest rate?

What is your required equity investment if you buy the asset for $20 million?

What is your equity dividend rate?

What is the DSCR?

If the required return (discount rate) increases to 11%, what is your NPV?

Levered IRR?

Solutions

Expert Solution

Year 0 1 2 3 4 5
1.Purchase price -20000000
2.Potential Gross Income(200*1000*12)*1.03 after yr.1 2400000 2472000 2546160 2622545 2701221
3.Vacancy loss(8%*rent) -192000 -197760 -203693 -209804 -216098
4.Effective Gross Income 2208000 2274240 2342467 2412741 2485123
5.Opg. Expense(40%*PGI) -960000 -988800 -1018464 -1049018 -1080488
6.Net operating Income(NOI) 1248000 1285440 1324003 1363723 1404635
7.Annual debt service(ADS)(53684.61*12) -496689 -489141 -481207 -472866 -464099
8.Levered cash flows 751310.6 796298.9 842796.7 890857.3 940536.3
9.Net sales proceeds after debt repayment 9545542
10.Total cashflows -20000000 751310.6 796298.9 842796.7 890857.3 10486078
11.Equity dividend rate(lev. Cash flows/10.2mln.)     -----------ANS. 7 7.37% 7.81% 8.26% 8.73% 9.22%
12.DSCR (NOI/ADS)------------ANS. 8 2.51 2.63 2.75 2.88 3.03
13.Total cash flows(same as 11) -20000000 751310.6 796298.9 842796.7 890857.3 10486078
14.PV F at 11%(1/1.11^Yr. n) 1 0.900901 0.811622 0.731191 0.658731 0.593451
15.PV at 11%(13*14) -20000000 676856.4 646294.1 616245.7 586835.3 6222977
16. NPV at 11%(sum row 15)---------Ans.9 -11250792
17. Levered IRR(of row 13)------------Ans.10 -8%
2. Net sales proceeds after debt repayment
Sales price(1404635/7.5%) 18728467
Mortgage balance (EOY 5) -9182925
9545542
3..Loan fees 10000000
Loan fees at 2% 200000
Net loan proceeds 9800000
4. Monthly loan payment
PV/ Annuity Factor for 360 months at 5% 12=0.4167% p.m.
10000000/((1-1.004167^-360)/0.004167)=
53684.61
5.Effective interest for the net loan proceeds=
9800000=(53684.61*(1-(1+r)^-360)/r)
ie. r= 0.4198% p.m.
(1+0.4198%)^12-1=
5.16%
Effective annual interest rate,when settled at EOY 5
9800000=(53684.61*(1-(1+r)^-60)/r)+(9182925/(1+r)^60)
0.4655% p.m.
ie. (1+0.4655%)^12-1=
5.73% p.a.
6.required equity investment if you buy the asset for $20 million =20 mln-9.8(net loan proceeds )=10.2 mln.
Year 0 1 2 3 4 5
1.Purchase price -20000000
2.Potential Gross Income(200*1000*12)*1.03 after yr.1 2400000 2472000 2546160 2622545 2701221
3.Vacancy loss(8%*rent) -192000 -197760 -203693 -209804 -216098
4.Effective Gross Income 2208000 2274240 2342467 2412741 2485123
5.Opg. Expense(40%*PGI) -960000 -988800 -1018464 -1049018 -1080488
6.Net operating Income(NOI) 1248000 1285440 1324003 1363723 1404635
7.Annual debt service(ADS)(53684.61*12) -964730 -1024750 -1044220 -1064687 -1086201
8.Levered cash flows 283270.1 260690.3 279783 299036.4 318434.3
9.Net sales proceeds after debt repayment 9545542
10.Total cashflows -20000000 283270.1 260690.3 279783 299036.4 9863976
11.Equity dividend rate(lev. Cash flows/10.2mln.)     -----------ANS. 7 2.78% 2.56% 2.74% 2.93% 3.12%
12.DSCR (NOI/ADS)------------ANS. 8 1.29 1.25 1.27 1.28 1.29
13.Total cash flows(same as 11) -20000000 283270.1 260690.3 279783 299036.4 9863976
14.PV F at 11%(1/1.11^Yr. n) 1 0.900901 0.811622 0.731191 0.658731 0.593451
15.PV at 11%(13*14) -20000000 255198.3 211582.1 204574.9 196984.6 5853790
16. NPV at 11%(sum row 15)---------Ans.9 -13277871
17. Levered IRR(of row 13)------------Ans.10 -12%

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Underwriting information: 200 unit apartment building 1,000 per month average rent 8% vacancy 38% total operating...
Underwriting information: 200 unit apartment building 1,000 per month average rent 8% vacancy 38% total operating expense ratio Replacement Reserves of $250 per unit per year 3% growth rate Purchase price is $20 million Exit cap rate is 7.5% Sales costs are 2% Unlevered discount rate is 8% Assume 5 year holding period Part 1 - Prepare 5 year pro forma operating statement What is cap rate on the purchase price? Determine the sales price at the end of year...
A 100 unit apartment building is for sale. It rents for 500/unit per month. Operating expenses...
A 100 unit apartment building is for sale. It rents for 500/unit per month. Operating expenses for the building are 200,000 per year and property taxes are 10,000 a year. Lisa wants to buy it and thus needs to arrange a mortgage loan of 3,000,000 at j4 = 10% amortized over 25 years with monthly payments. The banker assessed the lending value of the property to be 4,000,000. The banker requires a minimum DCR of 1.15, (a) Does Lisa qualify...
I wish to show that the average apartment rent in Atlanta is over $1200 per month....
I wish to show that the average apartment rent in Atlanta is over $1200 per month. I took a simple random sample of 81 and found the sample mean was $1250 with a standard deviation of $36. Let alpha=.01 Critical value = _____ 1) 1.28 2) 12.5 3) 1.65 4) 2.33
You are buying a mismanaged 64-unit apartment building for $3,000,000.  Rents are $800 per unit per month...
You are buying a mismanaged 64-unit apartment building for $3,000,000.  Rents are $800 per unit per month and expenses are $500 per unit per month, but the property is 10% vacant. You think you can turn the property around in three years by increasing rents $75 per year at each unit, keeping operating expenses constant.  Vacancy will decrease to 7.5% in year 2 and 5% in year 3.    The bank requires you put 25% down. Terms include a 25-year amortization at 4.0%.   You...
Part1) Bob's Apartment Building has 100 units that rent for $1,110 per month each. What is...
Part1) Bob's Apartment Building has 100 units that rent for $1,110 per month each. What is the Gross Potential Income for Bob's Apartment Building? Part2) Bob's Apartment Building has gross potential income of $1,000,000. The expected vacancy rate is 13%. Collection losses are 5% (after allowing for vacancies). Incidental income is $50,000 per year. What is the Effective Gross Income for Bob's Apartment Building? Part3) Bob's Apartment Building has effective gross income of $500,000. Operating expenses are 38% of revenue....
The business manager of a 90 unit apartment building is trying to determine the rent to...
The business manager of a 90 unit apartment building is trying to determine the rent to be charged. From past experience with similar buildings, when rent is set at $400, all the units are full. For every $20 increase in rent, one additional unit remains vacant. What rent should be charged for maximum total revenue? What is that maximum total revenue? To help solve the above scenario, perform an internet search for Profit Parabola or Applications of Quadratic Functions. List...
Five years ago, the average apartment rent in southwest Virginia was $895 per month. A recent...
Five years ago, the average apartment rent in southwest Virginia was $895 per month. A recent study of 180 randomly selected apartments found that the average rent was $915, with a standard deviation of $227.50. Do the data enable us to conclude that rents have increased? At the 98% degree of confidence, can it be concluded that the average rent has increased?
Five years ago, the average apartment rent in southwest Virginia was $895 per month. A recent...
Five years ago, the average apartment rent in southwest Virginia was $895 per month. A recent study of 180 randomly selected apartments found that the average rent was $915, with a standard deviation of $227.50. Do the data enable us to conclude that rents have increased? The computed test statistic
A real estate office handles a 60-unit apartment complex. When the rent is $530 per month,...
A real estate office handles a 60-unit apartment complex. When the rent is $530 per month, all units are occupied. For each $25 increase in rent, however, an average of one unit becomes vacant. Each occupied unit requires an average of $30 per month for service and repairs. What rent should be charged to obtain a maximum profit?
The business manager of a 90 unit apartment building is trying to determine the rent to be charged.
The business manager of a 90 unit apartment building is trying to determine the rent to be charged. From past experience with similar buildings, when rent is set at $400, all the units are full. For every $20 increase in rent, one additional unit remains vacant. What rent should be charged for maximum total revenue? What is that maximum total revenue? To help solve the above scenario, perform an internet search for Profit Parabola or Applications of Quadratic Functions. List...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT