You are considering the purchase of an apartment complex. The following assumptions are made: The purchase...
You are considering the purchase of an apartment complex. The
following assumptions are made:
The purchase price is $2,000,000
There are 30 units and the market rent is $850/month
Market rents are expected to increase 4% per year
Vacancy and collection loss is 10%
Real Estate Taxes are expected to be $20,000 in year 1 and increase 5% per year
Insurance is expected to be $10,000 in year 1 and increase 7% per year
Utilities are expected to be 9% of EGI each year
Repairs and Maintenance costs are expected to be 7% of EGI each year
Grounds and Security costs are expected to by 6% of EGI each year
The market value of the investment is expected to increase 6% each year
Selling expenses will be 5%
The holding period is 5 years
80% of the purchase price can be borrowed on a 30-year, monthly payment mortgage
The annual interest rate on the loan will be 8%.
Loan origination fees will be 1% of the loan amount (paid in the year the loan is taken out - Year 0)
There are no prepayment penalties if you pay the loan early.
Tenant Improvements are expected to be $3,000/year
Leasing Commissions are expected to be $1,000/year
A roof repair totaling $15,000 will be completed in year 3
The required rate of return for the investor is 12%.
Assume taxes are 30% of BTCF.
Assignment:
Fill out a pro forma income statement. Calculate the monthly mortgage payment to find debt service. What is the IRR and NPV of the property? (CF0 = equity investment + loan origination fees)
Homework Answers
| Year | 0 | 1 | 2 | 3 | 4 | 5 |
| 1.Purchase price | -2000000 | |||||
| 2.Loan origination fee(2000000*80%*1%) | -16000 | |||||
| Rental revenues | 306000 | 318240 | 330969.6 | 344208.4 | 357976.7 | |
| Less:Vacancy & collection loss(10%) | -30600 | -31824 | -33097 | -34420.8 | -35797.67 | |
| 3.Effective gross income(EGI) | 275400 | 286416 | 297872.6 | 309787.5 | 322179 | |
| Less: Expenses | ||||||
| Real Estate taxes | -20000 | -21000 | -22050 | -23153 | -24310 | |
| Insurance | -10000 | -10700 | -11449 | -12250 | -13108 | |
| Utilities(EGI*9%) | -24786 | -25777 | -26809 | -27881 | -28996 | |
| Rep.& Maint,(EGI*7%) | -19278 | -20049 | -20851 | -21685 | -22553 | |
| Gr.& Sec.costs(EGI*6%) | -16524 | -17185 | -17872 | -18587 | -19331 | |
| Tenant improvement costs | -3000 | -3000 | -3000 | -3000 | -3000 | |
| Leasing commn. | -1000 | -1000 | -1000 | -1000 | -1000 | |
| Roof repair | -15000 | |||||
| Int.on mortgage(as per amortsn.table) | -128158 | -127052 | -125854 | -124555 | -123148 | |
| 4.Total expenses | -222746 | -225764 | -243885 | -232111 | -235446 | |
| 5.BTCF(EGI less expenses total--3+4) | 52654 | 60652 | 53988 | 77676 | 86734 | |
| 6. Tax at 30%(5*30%) | -15796 | -18196 | -16196 | -23303 | -26020 | |
| 7. ATCF(5+6) | 36857 | 42457 | 37792 | 54373 | 60713 | |
| Calculation of NPV & IRR | ||||||
| 8.Mkt. value less selling exp.at end of Yr.5(2000000*1.06^5)*(1-5%) | 2542629 | |||||
| 9.Total annual CFs(1+2+7+8) | -2016000 | 36857 | 42457 | 37792 | 54373 | 2603342 |
| 10.PV F at 12%(1/1.12^Yr.n) | 1 | 0.89286 | 0.79719 | 0.71178 | 0.63552 | 0.56743 |
| 11. PV at 12%(9*10) | -2016000 | 32908 | 33846 | 26899 | 34555 | 1477206 |
| 12.NPV(sum of row 11) | -410584 | |||||
| 13.IRR(of Row 9) | 7% | |||||
| Using the PV of ordinary annuity formula,at r=8%/12=0.0067 p.m.we find the mthly.pmt.on the mortgage , For 360 mths. |
| (2000000*80%)=Pmt.*(1-1.0067^-360)/0.0067 |
| Pmt.=(2000000*80%)/((1-1.0067^-360)/0.0067)= |
| 11784.88 |
| Mth. | Mthly.pmt. | Tow,Int. | Tow. Loan | Loan | Annual Int. |
| 0 | 1600000 | ||||
| 1 | 11784.88 | 10720 | 1064.88 | 1598935 | |
| 2 | 11784.88 | 10712.87 | 1072.015 | 1597863 | |
| 3 | 11784.88 | 10705.68 | 1079.197 | 1596784 | |
| 4 | 11784.88 | 10698.45 | 1086.428 | 1595697 | |
| 5 | 11784.88 | 10691.17 | 1093.707 | 1594604 | |
| 6 | 11784.88 | 10683.85 | 1101.035 | 1593503 | |
| 7 | 11784.88 | 10676.47 | 1108.412 | 1592394 | |
| 8 | 11784.88 | 10669.04 | 1115.838 | 1591278 | |
| 9 | 11784.88 | 10661.57 | 1123.314 | 1590155 | |
| 10 | 11784.88 | 10654.04 | 1130.84 | 1589024 | |
| 11 | 11784.88 | 10646.46 | 1138.417 | 1587886 | |
| 12 | 11784.88 | 10638.84 | 1146.044 | 1586740 | 128158.4 |
| 13 | 11784.88 | 10631.16 | 1153.723 | 1585586 | |
| 14 | 11784.88 | 10623.43 | 1161.453 | 1584425 | |
| 15 | 11784.88 | 10615.65 | 1169.235 | 1583255 | |
| 16 | 11784.88 | 10607.81 | 1177.068 | 1582078 | |
| 17 | 11784.88 | 10599.93 | 1184.955 | 1580893 | |
| 18 | 11784.88 | 10591.99 | 1192.894 | 1579701 | |
| 19 | 11784.88 | 10583.99 | 1200.886 | 1578500 | |
| 20 | 11784.88 | 10575.95 | 1208.932 | 1577291 | |
| 21 | 11784.88 | 10567.85 | 1217.032 | 1576074 | |
| 22 | 11784.88 | 10559.69 | 1225.186 | 1574849 | |
| 23 | 11784.88 | 10551.49 | 1233.395 | 1573615 | |
| 24 | 11784.88 | 10543.22 | 1241.659 | 1572373 | 127052.1 |
| 25 | 11784.88 | 10534.9 | 1249.978 | 1571123 | |
| 26 | 11784.88 | 10526.53 | 1258.353 | 1569865 | |
| 27 | 11784.88 | 10518.1 | 1266.784 | 1568598 | |
| 28 | 11784.88 | 10509.61 | 1275.271 | 1567323 | |
| 29 | 11784.88 | 10501.06 | 1283.815 | 1566039 | |
| 30 | 11784.88 | 10492.46 | 1292.417 | 1564747 | |
| 31 | 11784.88 | 10483.8 | 1301.076 | 1563446 | |
| 32 | 11784.88 | 10475.09 | 1309.793 | 1562136 | |
| 33 | 11784.88 | 10466.31 | 1318.569 | 1560817 | |
| 34 | 11784.88 | 10457.48 | 1327.403 | 1559490 | |
| 35 | 11784.88 | 10448.58 | 1336.297 | 1558154 | |
| 36 | 11784.88 | 10439.63 | 1345.25 | 1556808 | 125853.6 |
| 37 | 11784.88 | 10430.62 | 1354.263 | 1555454 | |
| 38 | 11784.88 | 10421.54 | 1363.337 | 1554091 | |
| 39 | 11784.88 | 10412.41 | 1372.471 | 1552718 | |
| 40 | 11784.88 | 10403.21 | 1381.667 | 1551337 | |
| 41 | 11784.88 | 10393.96 | 1390.924 | 1549946 | |
| 42 | 11784.88 | 10384.64 | 1400.243 | 1548546 | |
| 43 | 11784.88 | 10375.26 | 1409.625 | 1547136 | |
| 44 | 11784.88 | 10365.81 | 1419.069 | 1545717 | |
| 45 | 11784.88 | 10356.3 | 1428.577 | 1544288 | |
| 46 | 11784.88 | 10346.73 | 1438.149 | 1542850 | |
| 47 | 11784.88 | 10337.1 | 1447.784 | 1541402 | |
| 48 | 11784.88 | 10327.4 | 1457.484 | 1539945 | 124555 |
| 49 | 11784.88 | 10317.63 | 1467.249 | 1538478 | |
| 50 | 11784.88 | 10307.8 | 1477.08 | 1537001 | |
| 51 | 11784.88 | 10297.9 | 1486.976 | 1535514 | |
| 52 | 11784.88 | 10287.94 | 1496.939 | 1534017 | |
| 53 | 11784.88 | 10277.91 | 1506.969 | 1532510 | |
| 54 | 11784.88 | 10267.81 | 1517.065 | 1530993 | |
| 55 | 11784.88 | 10257.65 | 1527.23 | 1529465 | |
| 56 | 11784.88 | 10247.42 | 1537.462 | 1527928 | |
| 57 | 11784.88 | 10237.12 | 1547.763 | 1526380 | |
| 58 | 11784.88 | 10226.75 | 1558.133 | 1524822 | |
| 59 | 11784.88 | 10216.31 | 1568.573 | 1523253 | |
| 60 | 11784.88 | 10205.8 | 1579.082 | 1521674 | 123148 |
| 628767.1 | 628767.1 |
Add Answer to:
You are considering the purchase of an apartment complex. The
following assumptions are made:
The purchase...
Five years ago you borrowed $230,000 to finance the purchase of a $290,000 house. The interest...
Five years ago you borrowed $230,000 to finance the purchase of a $290,000 house. The interest rate on the old mortgage is 5.5%. Payment terms are being made monthly to amortize the loan over 30 years. You have found another lender who will refinance the current outstanding loan balance at 3.5% with monthly payments for 25 years. There are no prepayment penalties associated with either loan. You feel the appropriate refinancing cost is 5% of the new loan amount. a....
You are considering the purchase of a small existing office building for $2,575,000 today. Below, you...
You are considering the purchase of a small existing office building for $2,575,000 today. Below, you are given the information you need to analyze the investment and decide how to proceed. Remember: Your submission for this assignment should be calculated in Microsoft Excel. Please show all your formulas in the spreadsheet. I can only give you partial credit if I see how you did each calculation. Your expectations for this stabilized property include the following: first-year gross potential income of...
Property Assumptions: Purchase Price:  ...
Property Assumptions: Purchase Price: $4,000000 Year 1 PGI: $540,000 PGI Growth Rate (Annual): 3% Annual Vacancy and Collection Loss (VCL): 10% Year 1 Operating Expenses (OER): 35% OPEX growth rate after first year 2% Sales Price: -Capitalize HP+1 NOI at 9% $3,895,042 Anticipated Holding Period: 3 Years Maximum LTV: 70% Interest Rate: 5% Amortization Rate: 30 Years Payments Per Year: 12 Investor Hurdle Rate (Unleveraged): ...
You have issued a 30 year a fixed rate mortgage loan to finance the purchase of...
You have issued a 30 year a fixed rate mortgage loan to finance the purchase of a home. Interest rates decline every year for four years after you have issued the mortgage loan. You are now locked into paying an interest rate that is above the market rate. There is no way out of this financial contract until the maturity date of the mortgage (thirty years from origination). True or False A tenant should carefully negotiate with the landlord the...
You are considering refinancing your home. Under the original terms of your mortgage you borrowed $250,000...
You are considering refinancing your home. Under the original terms of your mortgage you borrowed $250,000 to be repaid with equal monthly payments over 30 years. The APR on the loan was 4.5%. You have 20 years of payments remaining. If you now can refinance the remaining loan with no prepayment penalties at a 3.5% APR by how much will your monthly payment drop? What is the present value of your savings if your opportunity cost is represented by an...
please show all work! 8. *Eight years ago you borrowed $200,000 to finance the pur- chase...
please show all work!
8. *Eight years ago you borrowed $200,000 to finance the pur- chase of a $240,000 home. The interest rate on the old mort- gage loan is 6 percent. Payments are being made monthly to amortize the loan over 30 years. You have found another lender who will refinance the current outstanding loan balance at 4 percent with monthly payments for 30 years. The new lender will charge two discount points on the loan. Other refinancing costs...
please help with explanation An investor would like to purchase a new apartment property for $2...
please help with explanation
An investor would like to purchase a new apartment property for $2 million. However, she faces the decision of whether to use 70 percent or 80 percent financing. The 70 percent loan can be obtained at 10 percent interest for 25 years. The 80 percent loan can be obtained at 11 percent interest for 25 years. NOI is expected to be $190,000 per year and increase at 3 percent annually, the same rate at which the...
You want to purchase an office building in Brooklyn. The property contains 27,500 square feet of...
You want to purchase an office building in Brooklyn. The property contains 27,500 square feet of rentable space and is currently occupied by multiple tenants each with differing maturities on their respective leases. No lease is currently shorter than 1 year. The annual rent in the 1st year of ownership is $42.50/sq ft. The vacancy rate is 6.5%. You expect to incur collection losses (from tenant default)on 1.5% of the square feet during your first year. 1. What is the...
Name APPRAISAL PROBLEM Estimate the present market value of a 10 unit apartment house given the...
Name APPRAISAL PROBLEM Estimate the present market value of a 10 unit apartment house given the following information pertaining to the property. Each apartment has 1,000 square feet of living area, 3 bedrooms, and kitchen appliances Tenants pay their own utilities The value of the land is $250,000 The estimate replacement value is $150 per square foot for this type of construction. Total operating expenses insurance and property taxes for the year are expected to be $25,000 Rental income, $6,000...
Part II -Mortgage Refinance Suppose your friend April is considering to refinance her mortgage. She bought...
Part II -Mortgage Refinance Suppose your friend April is considering to refinance her mortgage. She bought her house 60 months ago. The amount of loan equals 154,000. She paid cash to cover the 5% down payment plus all required closing costs (closing costs include application fee, appraisal fee, loan origination fees and other costs, usually about 3%-5% of the loan amount). Since she had a decent credit history and relatively stable income, her mortgage rate was 5.25% for 30 years...
please show all work!
8. *Eight years ago you borrowed $200,000 to finance the pur- chase of a $240,000 home. The interest rate on the old mort- gage loan is 6 percent. Payments are being made monthly to amortize the loan over 30 years. You have found another lender who will refinance the current outstanding loan balance at 4 percent with monthly payments for 30 years. The new lender will charge two discount points on the loan. Other refinancing costs...
please help with explanation
An investor would like to purchase a new apartment property for $2 million. However, she faces the decision of whether to use 70 percent or 80 percent financing. The 70 percent loan can be obtained at 10 percent interest for 25 years. The 80 percent loan can be obtained at 11 percent interest for 25 years. NOI is expected to be $190,000 per year and increase at 3 percent annually, the same rate at which the...
Name APPRAISAL PROBLEM Estimate the present market value of a 10 unit apartment house given the following information pertaining to the property. Each apartment has 1,000 square feet of living area, 3 bedrooms, and kitchen appliances Tenants pay their own utilities The value of the land is $250,000 The estimate replacement value is $150 per square foot for this type of construction. Total operating expenses insurance and property taxes for the year are expected to be $25,000 Rental income, $6,000...
Part II -Mortgage Refinance Suppose your friend April is considering to refinance her mortgage. She bought her house 60 months ago. The amount of loan equals 154,000. She paid cash to cover the 5% down payment plus all required closing costs (closing costs include application fee, appraisal fee, loan origination fees and other costs, usually about 3%-5% of the loan amount). Since she had a decent credit history and relatively stable income, her mortgage rate was 5.25% for 30 years...
Most questions answered within 3 hours.
-
a 5.00 kg ball moving to the right at a velocity of + 2.00 m /...
asked 1 second from now -
A charged paint is spread in a very thin uniform layer over the
surface of a...
asked 2 minutes ago -
The rate constant for a certain reaction is k =
8.50Ć10ā3 sā1 . If the initial...
asked 5 minutes ago -
The lifetime of a physical mechanism is given as f(x) = {(c/x^2
if 1< x <...
asked 11 minutes ago -
Please help me with this problem below
What is wrong with the following code?
if income...
asked 20 minutes ago -
The following information for the past year is available from
Thinnews Co., a company that uses...
asked 23 minutes ago -
Suppose you work for Allstate, and insurance company and the
data shows the following for auto...
asked 23 minutes ago -
The fresh fruit market and frozen dinner market are
currently in equilibrium. Fresh fruit is a...
asked 29 minutes ago -
(1 point) Golf-course designers have become concerned that old
courses are becoming obsolete since new technology...
asked 27 minutes ago -
The active retelling of dominant cultural stories is known
preserves which concept?
hegemony
heterodoxy
doxa
myth
asked 31 minutes ago -
1. Which of the following statements is true of money?
It is anything accepted in exchange...
asked 31 minutes ago -
BASED ON THE AIRBNB, ETSY, UBER: GROWING FROM ONE THOUSAND TO
ONE MILLION CUSTOMERS HARVARD CASE:...
asked 57 minutes ago