2023 Midterm Solved

xlsx

School

Rutgers University *

*We aren’t endorsed by this school

Course

33:390:435

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

Uploaded by MinisterDiscovery7644

Name: Rutgers University 0 Real Estate Finance Midterm Examination 0 PART I - Excel Calculations 1 A Describe the difference between the Excel IRR and XIRR Function. IRR excel function is for annual cash flow while XIRR function is for when the ca B Using Excel Formulas, calculate the IRR and XIRR for the following cash flows as facts below for the purchase of a 10,000 square foot building to be Initial Investment 60 monthly distributions of Sale of investment at the end of month 60 Months Deposit Withdrawal Net Cash Flow 1-Jan $1,000,000 ($1,000,000) 2/1/2023 $7,500 $7,500 3/1/2023 $7,500 $7,500 4/1/2023 $7,500 $7,500 5/1/2023 $7,500 $7,500 6/1/2023 $7,500 $7,500 7/1/2023 $7,500 $7,500 8/1/2023 $7,500 $7,500 9/1/2023 $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 1/1/2024 $7,500 $7,500 2/1/2024 $7,500 $7,500 3/1/2024 $7,500 $7,500 4/1/2024 $7,500 $7,500 5/1/2024 $7,500 $7,500 6/1/2024 $7,500 $7,500

7/1/2024 $7,500 $7,500 8/1/2024 $7,500 $7,500 9/1/2024 $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 1/1/2025 $7,500 $7,500 2/1/2025 $7,500 $7,500 3/1/2025 $7,500 $7,500 4/1/2025 $7,500 $7,500 5/1/2025 $7,500 $7,500 6/1/2025 $7,500 $7,500 7/1/2025 $7,500 $7,500 8/1/2025 $7,500 $7,500 9/1/2025 $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 1/1/2026 $7,500 $7,500 2/1/2026 $7,500 $7,500 3/1/2026 $7,500 $7,500 4/1/2026 $7,500 $7,500 5/1/2026 $7,500 $7,500 6/1/2026 $7,500 $7,500 7/1/2026 $7,500 $7,500 8/1/2026 $7,500 $7,500 9/1/2026 $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 1/1/2027 $7,500 $7,500 2/1/2027 $7,500 $7,500 3/1/2027 $7,500 $7,500 4/1/2027 $7,500 $7,500 5/1/2027 $7,500 $7,500 6/1/2027 $7,500 $7,500 7/1/2027 $7,500 $7,500 8/1/2027 $7,500 $7,500 9/1/2027 $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 ### $7,500 $7,500 1/1/2028 $7,500 $7,500 2/1/2028 1400000 $1,400,000 $850,000

C Assume you borrowed $600,000 under the following terms. Interest Rate Amortization Period Using Excel formulas, what is the monthly and annual payment of this Fixed Rat Mnothly Payment $4,051.24 Annual Payment How much would be due upon sale in Month 60? Number of Payment Payment Interest Principal 0 1 $4,051.24 $3,250.00 $801.24 2 $4,051.24 $3,245.66 $805.58 3 $4,051.24 $3,241.30 $809.95 4 $4,051.24 $3,236.91 $814.33 5 $4,051.24 $3,232.50 $818.74 6 $4,051.24 $3,228.06 $823.18 7 $4,051.24 $3,223.60 $827.64 8 $4,051.24 $3,219.12 $832.12 9 $4,051.24 $3,214.61 $836.63 10 $4,051.24 $3,210.08 $841.16 11 $4,051.24 $3,205.53 $845.72 12 $4,051.24 $3,200.95 $850.30 13 $4,051.24 $3,196.34 $854.90 14 $4,051.24 $3,191.71 $859.53 15 $4,051.24 $3,187.05 $864.19 16 $4,051.24 $3,182.37 $868.87 17 $4,051.24 $3,177.67 $873.58 18 $4,051.24 $3,172.93 $878.31 19 $4,051.24 $3,168.18 $883.07 20 $4,051.24 $3,163.39 $887.85 21 $4,051.24 $3,158.58 $892.66 22 $4,051.24 $3,153.75 $897.49 23 $4,051.24 $3,148.89 $902.36 24 $4,051.24 $3,144.00 $907.24 25 $4,051.24 $3,139.08 $912.16 26 $4,051.24 $3,134.14 $917.10 27 $4,051.24 $3,129.18 $922.07 28 $4,051.24 $3,124.18 $927.06

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

29 $4,051.24 $3,119.16 $932.08 30 $4,051.24 $3,114.11 $937.13 31 $4,051.24 $3,109.04 $942.21 32 $4,051.24 $3,103.93 $947.31 33 $4,051.24 $3,098.80 $952.44 34 $4,051.24 $3,093.64 $957.60 35 $4,051.24 $3,088.45 $962.79 36 $4,051.24 $3,083.24 $968.00 37 $4,051.24 $3,078.00 $973.25 38 $4,051.24 $3,072.72 $978.52 39 $4,051.24 $3,067.42 $983.82 40 $4,051.24 $3,062.09 $989.15 41 $4,051.24 $3,056.74 $994.51 42 $4,051.24 $3,051.35 $999.89 43 $4,051.24 $3,045.93 $1,005.31 44 $4,051.24 $3,040.49 $1,010.75 45 $4,051.24 $3,035.01 $1,016.23 46 $4,051.24 $3,029.51 $1,021.73 47 $4,051.24 $3,023.97 $1,027.27 48 $4,051.24 $3,018.41 $1,032.83 49 $4,051.24 $3,012.82 $1,038.43 50 $4,051.24 $3,007.19 $1,044.05 51 $4,051.24 $3,001.54 $1,049.71 52 $4,051.24 $2,995.85 $1,055.39 53 $4,051.24 $2,990.13 $1,061.11 54 $4,051.24 $2,984.39 $1,066.86 55 $4,051.24 $2,978.61 $1,072.64 56 $4,051.24 $2,972.80 $1,078.45 57 $4,051.24 $2,966.95 $1,084.29 58 $4,051.24 $2,961.08 $1,090.16 59 $4,051.24 $2,955.18 $1,096.07 60 $4,051.24 $2,949.24 $1,102.00 Amount Due after making 60 payments $ (543,372.98) 7 What would resulting IRR be assuming this "Leveraged" acquisition IRR 11.82% D If the tenant in the above example were paying $10 NNN rent in m 4 "Going In" Cap Rate

4 If the Base Rent grew at 2.5% Per year what would be the rent in y 4 At the date of sale what would be the Sales Capitalization Rate? (Hint: you sell the cash flows that are "In Place")

ash flow is irregular ssuming the e leased to a third party tenant; $1,000,000 $7,500 1,400,000

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

IRR 1.19% XIRR 15.26%

6.50% 25 years te Mortgage? $49,188.89 Balance $ (600,000.00) $ (599,198.76) $ (598,393.17) $ (597,583.23) $ (596,768.89) $ (595,950.15) $ (595,126.97) $ (594,299.33) $ (593,467.21) $ (592,630.58) $ (591,789.42) $ (590,943.70) $ (590,093.40) $ (589,238.50) $ (588,378.97) $ (587,514.78) $ (586,645.90) $ (585,772.33) $ (584,894.02) $ (584,010.95) $ (583,123.10) $ (582,230.44) $ (581,332.95) $ (580,430.59) $ (579,523.35) $ (578,611.19) $ (577,694.09) $ (576,772.02) $ (575,844.96)

$ (574,912.88) $ (573,975.75) $ (573,033.54) $ (572,086.23) $ (571,133.79) $ (570,176.18) $ (569,213.39) $ (568,245.39) $ (567,272.14) $ (566,293.63) $ (565,309.81) $ (564,320.66) $ (563,326.15) Cash Flow To Calculate the IRR $ (562,326.26) Initial DP $ (400,000.00) $ (561,320.95) Total Interest ($186,447.56) $ (560,310.20) Balance After 60 $ (543,372.98) $ (559,293.97) Sale at the end of 60 1,400,000 $ (558,272.23) $ (557,244.96) $ (556,212.13) $ (555,173.70) $ (554,129.65) $ (553,079.94) $ (552,024.55) $ (550,963.44) $ (549,896.58) $ (548,823.95) $ (547,745.50) $ (546,661.21) $ (545,571.05) $ (544,474.98) $ (543,372.98) n? month 1 what would be the

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

year 4?

Name: Rutgers U Real Esta Midterm E 34 0 POINTS Part II - Cas 1 5 5 2 3 3

2 3 4 4 5 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

University ate Finance Examination sh Flow and Ownership Rights Number of APT Vacancy Rate Rent / Month Expense Ratio Gross Rental Income Vacancy Loss Effective Gross Income Operating Expenses Net Operating income Net operting Income in the first year is (b) What is the estimated value of the property if the current market cap rate is 8%? Est. Value of Property When performing your due diligence on this project you are considering ownership rights: (a) Which legal document would allow you to understand the previous ownership of this property and any potential claims others may have on it? Property Deeds (b) Name the three kinds of deeds that the seller may be able to offer and which of these is most beneficial to you as prospective new owner. You are thinking about purchasing a small apartment building in Fair Lawn, New Jersey. The property contains 10 apartments of similar size and con- figuration that rent for $2,000 a month. Based on historic vacancy rates and some market analysis, you expect a vacancy rate of 5% per year. (a) If expenses are estimated to be 40% of effective gross income, what is the net operating income generated by the property in the first year of ownership?

Grant Deed Quitclaim Deed Warranty Deed Warranty Deed is the best for a buyer (c ) what is a future estate in real property and give one example. A future estate in real property means you have a right to own or use a property, but you can't do so u For example, if you inherit a house, but you can't live in it until the current tenant m You are contemplating using your own money or borrwing 65% of this purchase from a local lender. (a) Lender is offering either recourse loan or a nonrecourse loan. Which would you choose and why? I would choose a nonrecourse loan as this is less risky for the borrower (b) Assuming the value calculated in 1 (b) above: (i) what amount could you borrow? Amount you could borrow (ii) If lender requires the Net Operating Income in 1(a)to be at a minimum the same as the debt paym loan, would you be able to meet this requirement if the loan amortized over 30 years at 7% Would the Net Operating Income justify the loan if it amortized over 10 years instead? (in (ii) above please show the ratio of Net Operating Income / Annual Debt Payments)

10 5% $2,000 40% $20,000 $1,000 $19,000 $7,600 $11,400 $136,800 $1,710,000

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

until a specific event happens in the future. moves out, your right to live there is a future estate. $1,111,500 ment on this

Name: Rutgers University Real Estate Finance Midterm Examination 33 0 POINTS PART III - Mortgage Markets / Commercial Leasing 5 1 In general, would a falling rate of interest cause the price of a Mor or decrease? Explain why this would be true. When interest rates fall, the price of a Mortgage Pass-Through security typica leading to higher prepayments. Investors then find these older, higher-yield 5 2 Name one type of institutional investor that would be interested i and what goals they could be trying to meet from their company p One type of institutional investor that might be interested in investing in mortgage-bac not only for financial return but also to meet other company goals. These goals may include achieving a deg long-term liabilities with long-term assets (such as mortgages), and providing a source of income to pay reti 3 If a tenant is the sole tenant in a 600 square foot building with the tenant pay in year 3 of their lease? Year 1 Year 2 Year 3 Utilities 4,000 4,120 4,244 CAM 3,500 3,605 3,713 Taxes 2,800 2,884 2,971 Insurance 1,400 1,442 1,485 11,700 12,051 12,413 2 (A) If they were NNN? 2 (B) Gross? 2 (C) Modified Gross? (assume $19.50/SF base stop, incre 2 4 If an investor in a pool of residential mortgages wishes to realize a of prepayments of the underlying mortgages impact the required The expectation of prepayments on mortgages in a pool can affect the requir Prepayments can disrupt expected cash flows and may require a larger upfro

2 5 Name the two mortgage entities created by the US Gov't that issu Fannie Mae (Federal National Mortgage Association) Freddie Mac (Federal Home Loan Mortgage Corporatio 2 6 Describe the the Secondary Mortgage Market. The secondary mortgage market is where existing mortgages and influences mortgage availability and pricing. Government agencies and p 6 7 Briefly describe the three ways that an appraiser estimates the val Income Approach: This approach evaluates the property's value based on its income-gen Sales Comparison Approach: Appraisers compare the subject property to recent sales of Cost Approach: This approach calculates the property's value by determining the cost to EXTRA CREDIT - Hint: Refer to the CBRE Appraisal in Canvas 2 1 What is the appraised value of the Denholtz Properties project Cel 1 What is meant by stabilization? 2 What is the value estimated at stabilization?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

rtgage Pass Through security to increase ally increases. This happens because as rates drop, homeowners refinance their mortgages, securities more attractive, increasing demand and driving up their prices. in investing in mortgage backed securities persepctive other than financial return. cked securities is a pension fund. Pension funds often invest in these securities gree of safety and stability in their investment portfolio, matching tirement benefits to their members. e following expenses how much would the ease by 3% annually) a specific rate of return would the expectation investment amount and why? red investment amount for an investor seeking a specific rate of return. ont investment or lead to lower returns due to reinvestment risks.

ue most of the residential mortgages. on) are bought and sold. It helps lenders get cash for making more loans, spreads risk, private investors are major players in this market. lue of commercial properties. nerating potential. Appraisers analyze rental income, expenses, and market rental rates to calculate the prope f similar properties in the market. They make adjustments for differences in size, location, condition, and other o replace or reproduce it, adjusted for depreciation. It is particularly useful for new or unique properties with li lebration Business Center at the time of acquisition?

erty's present value. r factors to estimate the property's value. imited comparable sales data.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version