Real Estate Finance
Chapter 2
ARGUS Instructions for (Revised) Willow Brook Apartments
22 September 2008
Your company is considering the acquisition of Willow Brook Apartments, a 220-unit garden apartment complex in Phoenix, AZ. You are responsible for the feasibility analysis. The analysis start date is March 1, 2008. The purchase price of the property is $17.25 million and your company expects to hold the property for 5 years. The current rent roll is provided in Exhibit 2.1. In addition to rental income, the property produces $100/month for each of 120 garage spaces and $30/month for each of 70 carport spaces. The garage and carport revenues do not depend on occupancy. The laundry facility on the property produces $10/unit/month. Laundry revenues depend on occupancy.
Exhibit 2.1
Current Rent Roll for Willow Brook Apartments
|Unit Description |# Units |Square Feet per unit|Total Square Feet |Monthly Rent |Rent / sf / month |
|1 Bed / 1 Bath |50 |650 |32,500 |$ 611 |$ 0.94 |
|1 Bed / 1 Bath / Den |40 |800 |32,000 |736 |0.92 |
|2 Bed / 1 Bath |45 |950 |42,750 |855 |0.90 |
|2 Bed / 2 Bath |35 |1,100 |38,500 |968 |0.88 |
|2 Bed / 2 Bath / Den |30 |1,200 |36,000 |1,044 |0.87 |
|3 Bed / 2 Bath |20 |1,353 |27,060 |1,150 |0.85 |
|ToTotals |220 | |208,810 | | |
The Phoenix apartment market is segmented, with an excess demand for smaller, one bedroom, units and an excess supply of larger, three bedroom units. The current occupancy status and market leasing assumptions are provided in Exhibit 2.2. It will take two months to absorb the currently vacant one bedroom units, four months to absorb the currently vacant two bedroom units, and six months to absorb the currently vacant three bedroom units. The currently vacant units do not require any preparation expenditures. The property loses 2% of potential gross revenue in collection losses annually.
Exhibit 2.2
Current Occupancy and Leasing Assumptions for Willow Brook Apts.
|Unit Description |# Units |# Occupied |Renewal |Renewal |Months |Prep Cost |Lease Cost |
| | | |Prob. |Base Rent |Vacant | | |
|1 Bed / 1 Bath |50 |50 |0.9 |$ 630 |2 |$ 150 |0.5 |
|1 Bed/1 Bath /Den |40 |35 |0.9 |750 |2 |$ 150 |0.5 |
|2 Bed/1 Bath |45 |40 |0.8 |870 |4 |$ 160 |0.5 |
|2 Bed/2 Bath |35 |30 |0.8 |980 |4 |$ 160 |0.5 |
|2 Bed/2 Bath /Den |30 |27 |0.8 |1,055 |4 |$ 160 |0.5 |
|3 Bed/2 Bath |20 |15 |0.6 |1,150 |6 |$ 170 |0.5 |
|Totals |220 | | | | | | |
The property's current expenses are provided in Exhibit 2.3. Income and expenses are expected to increase at 3% per year, the expected rate of inflation, for the duration of a five-year holding period. Income and expenses will be adjusted for inflation at the end of each fiscal year (e.g. end of February). Marketing costs are $0.16 per square foot and the owner must pay a leasing commission equal to one-half of one month's rent when a vacant apartment is rented. Preparation costs average $150/unit for the one-bedroom apartments, $160/unit for the two-bedroom apartments and $170/unit for the three-bedroom apartments.
Exhibit 2.3
Annual Expenses for Willow Brook Apartments
|Expense |For Property |Per Unit |Per Square Foot |Percent Fixed |
|Property Taxes |$ 300,000 | | |100 |
|Hazard Insurance |65,000 | | |100 |
|Administration | | |$ 0.10 |50 |
|Repairs/Maintenance | | |0.45 |50 |
|Marketing | | |0.16 |100 |
|Salaries | | |0.81 |30 |
|Utilities | | |0.38 |20 |
|Management |4% of EGI | | | |
|Structural Reserves | |$ 100/unit | | |
|Reserves for Replacements | |$ 200/unit | | |
You estimate the future selling price by capitalizing NOI, net of average preparation and leasing costs, at 7.5%. In addition, the owner will have to pay a 2% sales commission when the property is sold in five years. Finally, if you decide to purchase the property you need to select from three financing alternatives:
A. An 80% loan/value ratio loan with a 6.5% annual interest rate, monthly mortgage payments, a 30 year amortization schedule, 2% financing costs and a 3% prepayment penalty.
B. A 75% loan/value ratio loan with a 6.25% annual interest rate, a 30 year amortization period, monthly interest only payments for three years, 2% financing costs and a 2% prepayment penalty.
C. An 85% loan/value ratio loan with a constant monthly mortgage payment of $103,631.50, a 7% annual interest rate, two discount points, 2% other financing costs, and a 2% prepayment penalty.
1. Compute the property (e.g. unlevered) NPV discounting expected cash flows at 9% annually.
2. Compute the (after debt) before tax equity NPV discounting expected cash flows at 12% annually for the 80% LTV; at 11% annually for the 75% LTV; and at 13% annually for the 85% LTV.
First compute the expected cash flows and investment returns for loan option A and check your answers against the screen shots provided here. Then compute the answers for loan options B and C. Should your company purchase the property? Which financing option would you recommend? Why?
Willow Brook Apartments
ARGUS Inputs
1. Create a new folder on your computer’s hard drive. I created a folder on my C:\ drive called ARGUS_FILES.
2. Start ARGUS.
Don’t connect to the Community and select ‘New Property From Scratch.’
Exhibit 2.4
First Argus screen shot
[pic]
Change the default folder to the folder you created. For me, it is C:\ARGUS_FILES.
Call the new file Willow Brook Apartments
Your screen should look something like this:
Exhibit 2.5
Saving the new file
[pic]
3. Click [Save] to save the file and select Standard ARGUS valuation methods.
Exhibit 2.6
Standard ARGUS selection
[pic]
Then your screen should look like this:
Exhibit 2.7
Property Description Tab
[pic]
ARGUS Inputs
In Text/Drop Down Box Enter Comments
Property Description
Property Name: Willow Brook The project name and address will appear on
Address: ARGUS output.
Address2:
City: Phoenix
State: AZ Zip:
Zip:
In Text/Drop Down Box Enter Comments
Property Description
Portfolio:
Property Type: Apartment
Property Reference
Property version
Click [OK] to accept the Apartment template. This cannot be changed.
Your screen should look something like this:
Exhibit 2.8
Confirmation of Property Type
[pic]
Click [Yes]
(With the exception of the dates), your screen should look like this:
Exhibit 2.9
Executive Dashboard
[pic]
You are now ready to begin entering property information in ARGUS. ARGUS is a menu driven program, like Microsoft Word or Excel. The top of the screen contains menu options and each menu contains sub-menus. The basic ARGUS menus for apartments are:
File Property Apartment Market Yield Reports Options Help
The sub-menus for each of these choices are provided in the table on the next page:
Overview of ARGUS Menus (Apartments)
File Property Apartment Market
New ► Description… Rent Roll… Ctrl+R Market Leasing Assumptions…
Open… Timing… Unit Groups… Renewal Probability…
Close Area Measures… Market Rent…
Inflation Rates… Months Vacant…
Copy ► Preparation Cost…
Send To ► Miscellaneous Revenues… Leasing Cost…
Import/Export ► Apartment Operating Expenses…
OpenARGUS ► Capital Expenditures… Rent Abatements…..
INSIGHT Reporting…. Development Costs ► Security Deposits….
Batch Processing… Escrow ►
Global Categories
Language Settings General Vacancy Loss…
User Identification…. Credit & Collection Loss…
1. Willow Brook Apartments
Budgeting Account Codes…
Exit
Yield Reports Options Help
Property Purchase…. Property Level… System… Contents and Index
Property Resale…. Depreciation & Taxes… Simplify… ARGUS Updates
Direct Capitalization…. Partner Distributions… Reports… ARGUS Community
Debt Financing… Tracking ► Calculation… Check In License Key
Present Value Discounting… Presentation Rent Roll… Input… ARGUS Training
PV by Source….. Individual Unit… OpenARGUS…. E-Mail ARGUS ►
Depreciation & Taxes… Market Leasing… About ARGUS….
Value Added Taxes…. Supporting Schedules ►
Partner Equity Contributions… Graph…
Cash Flow Distributions… Report Writer…
Resale Distributions… Sensitivity Matrix….
Partner Groups… Input Assumptions…
Partner Levels…
Sensitivity Matrix… Report Packages…
Willow Brook Apartments
ARGUS Inputs
Property Input Comments
Description…
Timing (tab)
Analysis Start Date: 3/08 Begin the analysis in March 2008.
Reporting Start Date: 3/08 Start reporting with the same date.
Years to Report or End Date: 5 The holding period is 5 years.
Area Measures (tab)
Property Size
[Edit]
Size: 220 There are 220 apartment units.
[OK]
Alt. Prop. Size
[Edit]
Size: 208,810 There are 208,810 sq. ft. of rentable space.
[OK]
[New]
Name Garage Spaces Create a new space category for garage
Determined by: Measurement spaces.
Size: 120 There are 120 garage spaces.
[OK]
[New]
Name Carport Spaces Create a new category for carport spaces.
Determined by: Measurement
Size: 70 There are 70 carport spaces.
[OK]
Property Inflation (tab)
Based On: None Can be based on a saved parameter file.
Adjustments:
Inflation Month: Analysis Start Automatically starts @ your start date
For the Year Ending:
General 3 For subsequent years hit [Extend]
Miscellaneous Revenues Leave the rest blank--assumes
Operating Expenses all revenues and expenses increase
Capital Expenses at the 3% overall rate of inflation.
Market Rent
Leasing Costs
Land Costs
Hard Costs
Soft Costs
[OK]
Property Input Comments
Description…
Additional Data Can add descriptions to line items.
Comments Will appear on the top of each page.
Input Preferences Used to label area measurements,
Output Preferences currency, symbols and displays.
[OK] To exit Property Description Window
Property Input Comments
Miscellaneous Revenues….
[Insert] Line 1: Garage
Name Garage Label the garage revenues
Acct Code Leave blank
Amount 100 $100/garage space/month.
Units $/Area
Area Garage Spaces
Frequency /Month
% Fixed 100 Assumes all the spaces are leased.
Inflation Retain the general 3% inflation rate.
Ref Acct
Notes
[Insert] Line 2: Carport
Name Carport
Acct Code
Amount 30 $30/carport space/month.
Units $/Area
Area Carport Spaces
Frequency /Month
% Fixed 100 Assumes all the spaces are leased.
Inflation Retain the general 3% inflation rate.
Ref Acct
Notes
[Insert] Line 3: Laundry
Name Laundry
Acct Code
Amount 10
Units $/Area
Area Property Size $10 per unit per month.
Frequency /Month
% Fixed 0 Income depends on occupancy.
Inflation Retain the general 3% inflation rate.
Ref Acct
Notes
The completed Miscellaneous Revenues Window should look like this:
Exhibit 2.10
Miscellaneous Revenues
[pic]
[Close] Close Miscellaneous Revenues window.
Property Input Comment
Apartment Operating Expenses….
[Insert] Line 1: Property Taxes
Name Property Taxes
Acct Code
Amount 300000 $300,000 per year
Units $ Amount
Area
Frequency /Year
% Fixed 100
Inflation Retain the general 3% inflation rate.
Ref Acct
Notes
[Insert] Line 2: Insurance
Name Insurance
Acct Code
Amount 65000 $65,000 per year.
Units $ Amount
Area
Frequency /Year
% Fixed 100
Inflation Use 3% general inflation.
Ref Acct
Notes
[Insert] Line 3: Administration
Name Administration
Acct Code
Amount 0.1 $ 0.10 per square foot.
Units $/Area
Property Input Comment
Apartment Operating Expenses….
Line 3: Administration (continued)
Area Alt. Prop. Size
Frequency /Year
% Fixed 50
Inflation
Ref Acct
Notes
[Insert] Line 4: Repairs
Name Repairs
Acct Code
Amount 0.45 $ 0.45 per square foot.
Units $/Area
Area Alt. Prop. Size
Frequency /Year
% Fixed 50
Inflation
Ref Acct
Notes
[Insert] Line 5: Marketing
Name Marketing
Acct Code
Amount 0.16 $ 0.16 per square foot.
Units $/Area
Area Alt. Prop. Size
Frequency /Year
% Fixed 100
Inflation
Ref Acct
Notes
[Insert] Line 6: Salaries
Name Salaries
Acct Code
Amount 0.81 $ 0.81 per square foot.
Units $/Area
Area Alt. Prop. Size
Frequency /Year
% Fixed 30
Inflation
Ref Acct
Notes
[Insert] Line 7: Utilities
Name Utilities
Acct Code
Amount 0.38 $ 0.38 per square foot.
Property Input Comment
Apartment Operating Expenses….
Line 3: Utilities (continued)
Units $/Area
Area Alt. Prop. Size
Frequency /Year
% Fixed 20
Inflation
Ref Acct
Notes
[Insert] Line 8: Management Fees
Name Management Fees
Acct Code
Amount 4 4% of Effective Gross Revenue.
Units % of EGR
Area
Frequency
% Fixed
Inflation
Ref Acct
Notes
The Willow Brook Operating Expenses Window should look like this:
Exhibit 2.11
Operating Expenses Window
[pic]
[Close] Close Apartment Operating Expenses
window.
Property Input Comments
Capital Expenditures….
[Insert] Line 1: Structural Reserves
Name Structural Reserves
Acct Code
Amount 100 $100/unit/year.
Units $/Area
Area Property Size
Frequency /Year
% Fixed 100
Inflation
Ref Acct
Notes
[Insert] Line 2: Replacements
Name Replacements
Acct Code
Amount 200 $200/unit/year.
Units $/Area
Area Property Size
Frequency /Year
% Fixed 100
Inflation
Ref Acct
Notes
The Willow Brook Capital Expenses Window should look like this:
Exhibit 2.12
Capital Expenditures Window
[pic]
[Close] Close Capital Expenditures window.
Development Costs ►
Land/Acquisition Costs… This is an existing property;
Hard/Construction Costs… there are no development costs.
Soft/Development Costs…
Property Input Comments
Development Costs ► [continued]
S-Curves…
Reference Dates…
Escrow ► For property taxes, insurance, etc.
Escrow Contributions… There are no required escrow accounts
Escrow Distributions… for this property.
Escrow Balance…
General Vacancy Loss…
Method: No General Vacancy Vacancy will be done with leasing.
Primary Rate:
Overrides Affect Primary Rate As:
Available Unit Groups:
Overridden Unit Groups:
[OK] To exit General Vacancy Loss….
Credit and Collection Loss…..
Method: Percent of Potential Gross Revenue
Primary Rate: 2 2% of gross revenue is lost.
Overrides Affect Primary Rate As: Replace This is irrelevant for this example.
Available Unit Groups:
Overridden Unit Groups:
[OK] To exit the Credit and Collection Loss
window.
We are going to input the Market Leasing Assumptions BEFORE the rent roll, so we can refer to the market leasing assumptions when we input the rent roll.
Market Input
Market Leasing Assumptions… See Exhibit 2.13 for the 1B/1B window.
[New]
Category: 1B/1B New Market Renewal Market
Renewal Probability 90
Market Rent 630 611
Months Vacant 2 0
Preparation Costs 150
Leasing Costs 0.5
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
The Willow Brook Market Leasing Assumptions Window for 1B/1B should look like this:
Exhibit 2.13
Market Leasing Category Window
[pic]
[OK] To exit 1B/1B Market Leasing Assumptions
Market Input
Market Leasing Assumptions for 1B/1B/D
[New]
Category: 1B/1B/D New Market Renewal Market
Renewal Probability 90
Market Rent 750 736
Months Vacant 2 0
Preparation Costs 150
Leasing Costs 0.5
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
[OK]
Market Input
Market Leasing Assumptions for 2B/1B
[New]
Category: 2B/1B New Market Renewal Market
Renewal Probability 80
Market Rent 870 855
Months Vacant 4 0
Preparation Costs 160
Leasing Costs 0.5
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
[OK]
[New]
Category: 2B/2B New Market Renewal Market
Renewal Probability 80
Market Rent 980 968
Months Vacant 4 0
Preparation Costs 160
Leasing Costs 0.5
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
[OK]
[New]
Category: 2B/2B/D New Market Renewal Market
Renewal Probability 80
Market Rent 1,055 1,044
Months Vacant 4 0
Preparation Costs 160
Leasing Costs 0.5
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
[OK]
[New]
Category: 3B/2B New Market Renewal Market
Renewal Probability 60
Market Rent 1,150 1,150
Months Vacant 6 0
Preparation Costs 170
Leasing Costs 0.5
Market Input
Market Leasing Assumptions for 3B/2B (continued)
Rent Abatements 0
Security Deposit None None
NON-WEIGHTED ITEMS
Term Lengths 12
[OK]
The Market Leasing Assumptions window should look like this:
Exhibit 2.14
List of Market Leasing Categories
[pic]
[Close]
Now we can refer to the Market Leasing Assumptions in the Rent Roll.
Apartment Input Comments
Rent Roll Ctrl+R
Apartment Rent Schedule See Exhibits 2.15 and 2.16 for the
completed rent roll window.
[Insert] One Bedroom/One Bath
Unit Type Description 1/1
Unit Size 650
Total Units 50
Current Rent 611
Apartment Input Comments
Rent Roll Ctrl+R
Apartment Rent Schedule: One Bedroom/One Bath (continued)
Current Abatements
Current Term 12
Current Occupancy 50
Market Leasing (Use dropdown box) 1B/1B
Maximum Occupancy
Mos. to Absorb
Begin Absorb
Prep Costs
Lease Costs
Abatements
Security Deposit
Absorb Term
[Insert] One Bedroom/One Bath/Den
Unit Type Description 1/1/D
Unit Size 800
Total Units 40
Current Rent 736
Current Abatements
Current Term 12
Current Occupancy 35
Market Leasing (Use dropdown box) 1B/1B/D
Maximum Occupancy 40
Mos. to Absorb 2
Begin Absorb
Prep Costs 150
Lease Costs .5
Abatements
Security Deposits
Absorb Term 12
[Insert] Two Bedrooms/One Bath
Unit Type Description 2/1
Unit Size 950
Total Units 45
Current Rent 855
Current Abatements
Current Term 12
Current Occupancy 40
Market Leasing (Use dropdown box) 2B/1B
Maximum Occupancy 45
Mos to Absorb 4
Begin Absorb
Prep Costs 160
Lease Costs 0.5
Apartment Input Comments
Rent Roll Ctrl+R
Apartment Rent Schedule: Two Bedroom/One Bath (continued)
Abatements
Security Deposit
Absorb Term 12
[Insert] Two Bedrooms/Two Baths
Unit Type Description 2/2
Unit Size 1,100
Total Units 35
Current Rent 968
Current Abatements
Current Term 12
Current Occupancy 30
Market Leasing (Use dropdown box) 2B/2B
Maximum Occupancy 35
Mos to Absorb 4
Begin Absorb
Prep Costs 160
Lease Costs 0.5
Abatements
Security Deposit
Absorb Term 12
[Insert] Two Bedrooms/Two Baths/Den
Unit Type Description 2/2/D
Unit Size 1,200
Total Units 30
Current Rent 1,044
Current Abatements
Current Term 12
Current Occupancy 27
Market Leasing (Use dropdown box) 2B/2B/D
Maximum Occupancy 30
Mos to Absorb 4
Begin Absorb
Prep Costs 160
Lease Costs 0.5
Abatements
Security Deposit
Absorb Term 12
[Insert] Three Bedrooms/Two Baths
Unit Type Description 3/2
Unit Size 1,353
Total Units 20
Current Rent 1,150
Current Abatements
Apartment Input Comments
Rent Roll Ctrl+R
Apartment Rent Schedule: Three Bedrooms/Two Baths (continued)
Current Term 12
Current Occupancy 15
Market Leasing (Use dropdown box) 3B/2B
Maximum Occupancy 20
Mos to Absorb 6
Begin Absorb
Prep Costs 170
Lease Costs 0.5
Abatements
Security Deposit
Absorb Term 12
[Close]
The Willow Brook Rent Roll Window should look like Exhibits 2.15 and 2.16.
Exhibit 2.15
Left Side of Rent Roll Window
[pic]
Exhibit 2.16
Right Side of Rent Roll Window
[pic]
Now you will input the purchase price, required yields and loan terms.
Yield Input
Property Purchase....
Purchase Price & Current Value
Property Purchase Price
• Initial Amount: 17,250,000
Direct Cap Value as of Analysis Start
Current Value for IRR Calculations
Inflate Purchase Price at:
• Current Amount 17,250,000
Cumulative costs
Direct Cap Value as of Reporting Start
[OK]
Yield Input
Property Resale….
Option for Resale Calculation (Use drop down box) CAP Cash Flow Adjusted for
Average PCs & LCs
Direct Resale Amount
Cap Rate: 7.5
Resale Commission as Percent of Price: 2
( Apply Rate to Following Year Income
( Calculate Resale for All Years
[OK]
Yield Input
Debt Financing…
[New] First Lien
Note Name: 80% L/V
Input Currency: Input
Start Date Leave blank for analysis start date.
( Amortize Start Leave blank to begin amort. @ start
Term Length (drop down box) 30
% of Price or PV (drop down box) 80
Rate Charged 6.5
( Calculate This Note
[Advanced…]
Advanced Debt – 80%L/V
Rate Paid
Rate on Accrual
Payments
Payments/Year 12
Delay Leave blank--no delay
Interest Calculation: USA Ordinary annuity
Reduction from Cash Flow Leave default--no reductions in CFs
Reduce (Interest) using 0 % of NOI Less P&I
Other Payments: No
Yield Input
Debt Financing…
[Advanced…]
Advanced Debt – 80%L/V
Call or Balloon: Leave blank--no balloon
Prepay Penalty 3 3% prepayment penalty
Maximum Draw: Leave blank--no draw
Points and Fees: 2 Two percent financing fee
% of Init. Principal (use drop down box) The fee is a percent of the loan.
[OK] To exit advanced window menu
[OK] To exit first lien window
[OK] To exit debt financing window
Yield Input
Present Value Discounting…..
Discount Rate and Method Tab
Primary Discount Rate: 9
Discount Rate Range
Number of Rates 7
Increment between Rates: 0.5
Discount Method: Annually (Endpoint on Cash Flows and Resale)
Present Value As Of Tab
Analysis Discount Period
Start Date: 3/08 Entered by ARGUS
End Date: 2/13 Entered by ARGUS
Length: 5 years Entered by ARGUS
Secondary Discount Period Leave blank
Start Date:
End Date:
Length:
Advanced Tab
Unleveraged Discount Rates
Cash Flow Rate: 9 Entered by ARGUS.
Resale Rate: 9 Use the same discount rate.
Leveraged Discount Rates
Cash Flow Rate: 12 The levered required yield is 12%.
Resale Rate: 12 On reversion cash flows too.
[OK]
Yield Input
PV by Source…
Depreciation & Taxes… Leave default--before tax analysis
Value Added Taxes Leave blank
Partner Equity Contributions… Leave blank--no partnership
Cash Flow Distributions
Resale Distributions
Partner Groups…
Partner Levels…
Sensitivity Matrix…
Reports Input
Property Level…..
Method of Iteration Prior to producing any reports, you must "solve"
the analysis. There are three solution methods:
• "Quick Calc"
○ Modified Iteration to Specified Tolerance
○ Full Iteration to Specified Tolerance
“Quick Calc” will suffice, hit [Calculate]
Reports Comment
( Schedule of Cash Flow from Operations See Exhibits 2.17a and 2.17b
on pages 26 and 27.
Schedule of Expense Reimbursement Revenue
( Individual Loan & Debt Service Summary See Exhibit 2.18 on page 28.
( Sources and Uses of Capital--Cash Returns See Exhibit 2.19 on page 29.
( Prospective Resale & IRR Summary See Exhibit 2.20 on page 30.
( Prospective Present Value Summary-Unleveraged See Exhibit 2.21 on page 31.
-Leveraged See Exhibit 2.22 on page 32.
( Property Summary Report See Exhibit 2.23 on page 33.
Frequency
• Annually
Monthly
Quarterly
Monthly with Annual Sum
[View]
The following Report tabs should look like these exhibits:
Exhibit 2.17a
Top portion of Cash Flow tab for 80% L/V Loan
[pic]
Exhibit 2.17b
Bottom portion of Cash Flow tab for 80% L/V Loan
[pic]
Exhibit 2.18
Loan Summary tab for 80% L/V Loan
[pic]
Exhibit 2.19
Sources and Uses tab for 80% L/V Loan
[pic]
Exhibit 2.20
Resale tab for 80% L/V Loan
[pic]
Exhibit 2.21
Detailed PV – Unleveraged for 80% L/V Loan
[pic]
Exhibit 2.22
Detailed PV – Leveraged for 80% L/V Loan
[pic]
Exhibit 2.23
Property Summary Tab for 80% L/V Loan
Compare results with answers below:
[pic]
Property Summary Tab Compare results with Answer Sheet
Property Summary Tab…
Unlevered Annual IRR = 8.84%
Unlevered PV = $17,138,626
Leveraged Annual IRR = 13.51%
Value of Equity Interest = $3,671,632
Exhibit 2.24
Executive Dashboard for 80% L/V Loan
[pic]
NEW MODEL FOR FINANCING OPTION B
To compute expected cash flows for the 75% L/V loan, begin by making a copy of the ARGUS worksheet you just created.
FILE
Copy
Duplicate File….
File name: Willow Brook with Loan Option B
[Save]
ARGUS will ask ‘Do you want to open the newly created file?
Click [Yes]; then go to the Yield Menu to change the terms of the loan and the required yield on equity.
Yield Input
Debt Financing…
[Edit] First Lien
Note Name: 75% L/V
Input Currency: Input
Start Date Leave blank for analysis start date.
( Amortize Start 03/11 Three years interest only—loan
amortization will begin in March 2011.
Term Length (drop down box) 30
% of Price or PV (drop down box) 75
Rate Charged 6.25
( Calculate This Note
[Advanced…]
Advanced Debt – First Lien
Rate Paid
Rate on Accrual
Payments
Payments/Year 12
Delay Leave blank--no delay
Interest Calculation: USA Ordinary annuity
Reduction from Cash Flow Leave default--no reductions in CFs
Reduce (Interest) using 0 % of NOI Less P&I
Other Payments: No
Call or Balloon: Leave blank--no balloon
Prepay Penalty 2 2% prepayment penalty
Maximum Draw: Leave blank--no draw
Points and Fees: 2 Two percent financing costs
% of Init. Principal (use drop down box) Financing costs as a percent of the loan.
[OK] To exit advanced window menu
[OK] To exit first lien window
[OK] To exit debt financing window
Now change the equity investor’s required yield to 11%.
Yield Input
Present Value Discounting…..
Advanced Tab
Unleveraged Discount Rates
Cash Flow Rate: 9 Entered by ARGUS.
Resale Rate: 9 Use the same discount rate.
Leveraged Discount Rates
Cash Flow Rate: 11 The levered required yield is 11%.
Resale Rate: 11 On reversion cash flows too.
[OK]
Now compute the new expected cash flows and investor returns.
Reports Input
Property Level…..
Method of Iteration Prior to producing any reports, you must "solve"
the analysis. There are three solution methods:
• "Quick Calc"
○ Modified Iteration to Specified Tolerance
○ Full Iteration to Specified Tolerance
[Calculate]
Reports Comments
( Schedule of Cash Flow from Operations See Exhibits 2.25a and 2.25b.
Schedule of Expense Reimbursement Revenue
( Individual Loan & Debt Service Summary See Exhibits 2.26a and 2.26b.
Sources and Uses of Capital--Cash Returns
Prospective Resale & IRR Summary
( Prospective Present Value Summary-Unleveraged
-Leveraged See Exhibit 2.27 on page 41.
( Property Summary Report See Exhibit 2.28 on page 42.
Frequency
• Annually
Monthly
Quarterly
Monthly with Annual Sum
[View]
The following Report tabs should look like these exhibits:
Exhibit 2.25a
Top portion of Cash Flow tab for 75% L/V Loan
[pic]
Exhibit 2.25b
Bottom portion of Cash Flow tab for 75% L/V Loan
[pic]
Exhibit 2.26a
Top of Loan Summary tab for 75% L/V Loan
[pic]
Exhibit 2.26b
Bottom of Loan Summary tab for 75% L/V Loan
[pic]
Exhibit 2.27
Detailed PV – Leveraged for 75% L/V Loan
[pic]
Exhibit 2.28
Property Summary Tab for 75% L/V Loan
Compare results with answers below
[pic]
Property Summary Tab for the 75% L/V Loan Compare results with Answer Sheet
Property Summary Tab…
Unlevered Annual IRR = 8.84% (same as before)
Unlevered PV = $17,138,626 (same as before)
Leveraged Annual IRR = 13.76% (vs. 13.51%)
Value of Equity Interest = $ 4,793,487
Exhibit 2.29
Executive Dashboard for 75% L/V Loan
[pic]
NEW MODEL FOR FINANCING OPTION C
To compute expected cash flows for the 85% L/V loan, begin by making a copy of the ARGUS worksheet you just created.
FILE
Copy
Duplicate File…..
File name: Willow Brook with Loan Option C
[Save]
ARGUS will ask ‘Do you want to open the newly created file?
Click [Yes]; then go to the Yield Menu to change the terms of the loan and the required yield on equity.
Yield Input
Debt Financing…
[Edit] First Lien
Note Name: 85% L/V
Input Currency: Input
Start Date Leave blank for analysis start date.
( Amortize Start Leave blank to start amortization
Payment (drop down box) $103,631.50
% of Price or PV (drop down box) 85
Rate Charged 7.00
( Calculate This Note
[Advanced…]
Advanced Debt – First Lien
Rate Paid
Rate on Accrual
Payments
Payments/Year 12
Delay Leave blank--no delay
Interest Calculation: USA Ordinary annuity
Reduction from Cash Flow Leave default--no reductions in CFs
Reduce (Interest) using 0 % of NOI Less P&I
Other Payments: No
Call or Balloon: Leave blank--no balloon
Prepay Penalty 2 2% prepayment penalty
Maximum Draw: Leave blank--no draw
Points and Fees: 4 2% financing costs and two points
% of Init. Principal (use drop down box) Financing costs as a percent of the loan.
[OK] To exit advanced window menu
[OK] To exit first lien window
[OK] To exit debt financing window
Now change the equity investor’s required yield to 13%.
Yield Input
Present Value Discounting…..
Advanced Tab
Unleveraged Discount Rates
Cash Flow Rate: 9 Entered by ARGUS.
Resale Rate: 9 Use the same discount rate.
Leveraged Discount Rates
Cash Flow Rate: 13 The levered required yield is 13%.
Resale Rate: 13 On reversion cash flows too.
[OK]
Now recompute the expected cash flows and investor returns.
Reports Input
Property Level…..
Method of Iteration Prior to producing any reports, you must "solve"
the analysis. There are three solution methods:
• "Quick Calc"
○ Modified Iteration to Specified Tolerance
○ Full Iteration to Specified Tolerance
[Calculate]
Reports
( Schedule of Cash Flow from Operations See Exhibits 2.30a and 2.30b.
Schedule of Expense Reimbursement Revenue
( Individual Loan & Debt Service Summary See Exhibits 2.31a and 2.31b.
Sources and Uses of Capital--Cash Returns
Prospective Resale & IRR Summary
( Prospective Present Value Summary-Unleveraged
-Leveraged See Exhibit 2.32 on page 50.
( Property Summary Report See Exhibit 2.33 on page 51.
Frequency
• Annually
Monthly
Quarterly
Monthly with Annual Sum
[View]
Illustrate the following Report tabs:
Exhibit 2.30a
Top portion of Cash Flow tab for 85% L/V Loan
[pic]
Exhibit 2.30b
Bottom portion of Cash Flow tab 85% L/V Loan
[pic]
Exhibit 2.31a
Top of Loan Summary tab for 85% L/V Loan
[pic]
Exhibit 2.31b
Bottom of Loan Summary tab for 85% L/V Loan
[pic]
Exhibit 2.32
Detailed PV for 85% L/V Loan
[pic]
Exhibit 2.33
Property Summary Tab for 85% L/V Loan
Compare results with answers below
[pic]
Solutions for 85% L/V loan
Property Summary Tab…
Unlevered Annual IRR = 8.84% (same as before)
Unlevered PV = $17,138,626 (same as before)
Leveraged Annual IRR = 11.3% (vs. 13.51% for the 80% L/V loan)
Value of Equity Interest = $2,377,985
Exhibit 2.34
Executive Dashboard for 85% L/V Loan
[pic]
Which financing option is best? Take the 75% L/V Loan
Answer: Cash 75% L/V 80% L/V 85% L/V
Price $17,250,000 $17,250,000 $17,250,000 $17,250,000
Loan Amount $0 $12,937,500 $13,800,000 $14,662,500
Equity Contribution (excl. fees) $17,250,000 $4,312,500 $3,450,000 $2,587,500
Required Yield 9% 11% 12% 13%
PV of Equity Cash Flows $17,138,626 $4,793,487 $3,671,632 $2,377,985
NPV of Equity Cash Flows -$111,374 +$480,987 +$221,632 -$209,515
Expected IRR 8.84% 13.76% 13.51% 11.30%
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- real estate open house ideas
- real estate license in nevada
- nevada real estate license search
- nevada real estate license lookup
- real estate license lookup nv
- vanguard real estate index admiral
- nevada real estate division
- real estate marketing plan template
- nevada real estate division license lookup
- real estate investment spreadsheet template
- real estate excel spreadsheet templates
- real estate gadsden