Pro Forma

Pro Forma Assignment

*price is negotiable

>Assumptions

Assignment #

– Assumptions

0,000

x total net leasable area

8.50%

per sq. ft.

, others

per sq. ft.

0.0%

-10 of lease

2.00% Legal Fees 1.00%

50%

5.0%

75%

1 Market rents

space

0% of market rent – 20% step in year 6 of their lease

15,000 5

$4.31 $64,700

1 sign 5 year lease @ market rent

$0.00

15,000 2 sign 5 year lease @ market rent

$0.00

10,000 3 sign 5 year lease @ market rent

$0.00

4 sign 5 year lease @ market rent

$0.00

Capital Expenses

																			C	3	0	7	1
Buildng Size		Total
	Stabilized NOI
Net Leasible Area	1	8	Gross rent revenues: use average rent of occupied	space
		Vacancy Allowance	Use average vacancy rate for years 1-		10
Sales Price			Bad Debt Allowance		1.00%
Cap Rate at Sale (on stabilized NOI)		8.			5	Expected annual TIs & Leasing costs as % of rent revenue
		Operating	Expenses	Yr 1 Costs	Loan Calculations	1st Mortg.
CAM, Utilities, property tax, & management (all recoverable)				per sq. ft.	$10.00	Min allowed DCR	1.25
Insurance (not receoverable)	$2.00	Max LTV	0.75
Annual increase in expenses , includes	Tis	3.0%	Amortization Period (years)		20
Rate = basis points spread over prime	150
Leasing Expenses
TI (New)	$20.00	Sales Costs
TI (renew)	$5.00	Commission	1.	75%
TI costs allocated to lease	10	0.0%		Legal Fees	0.50%
TI costs allocated to structure
Leasing/brokerage fees	Purchase Costs
% of total net rent paid over years 1-5 of lease	3.50%	Taxes		2.00%
% of total net rent paid over years	6
Leasing costs (renew)	of costs for new
Tax Treatment
CRU Renewals & Vacancies	Marginal Tax Bracket			4
Probability CRU tenant renews	Market value of structure @ Purchase	80.0%	of purchase price
Expected time space is vacant	months	Market value of structure @ Sale	60.0%	of sales price
Revenues
	Year	Per Sq. Ft.	$21.00
Annual Increase in Rents	4.5%
Rent Roll
Tenant	Years left on lease	Current lease terms (renew at same)	Current rent	Current lease TI/sq ft	TIs received for current lease	Leasing Agent Fees for Current Lease
						Law firm	60,000	10 year lease @	9	$18.56	$3.83	$229,800	$136,900
						Mortgage brokers			15,000	Signs 7 year lease@ market rent – 15% step in year 4 of their lease	$21.61		$4.31	$64,700	$40,900
						Engineering firm	Signs 10 year lease@ 95% of market rent – 10% step in year 3 of their lease and again in year 7	$19.63	$76,300
						Tenants w/ leases expiring end of year 0	20,000					sign 5 year lease @ market rent	$18.79			$0.00	$86,200	$57,500
						Tenants w/ leases expiring end of year 1		10,000	$19.21	$77,700	$21,000
						Tenants w/ leases expiring end of year 2	$19.64	$89,300	$29,600
						Tenants w/ leases expiring end of year 3	$20.09	$89,500	$26,400
						Tenants w/ leases expiring end of year 4	35,000	$20.54	$169,800	$116,400
Scheduled	Recoverable
Amount	Years to Amortize (life of system)
Year 3 – Repair, replace with improvement over original	$1,000,000
Year 7 – Repair, replace original with same	$250,000
Recoverable from tenants as straight line expense for life of system
Unrecoverable Capital Expenses
Annual as % of initial structure value	0.25%
repair, same as original, no improvement

&A &P of &N

Q1

Year 1 2 3 4 5 6 7 8 9 10
Tis
Vacancy Allowance
Bad Debt Allowance
Law firm
Mortgage brokers
Engineering firm
Tenants w/ leases expiring end of year 0
Tenants w/ leases expiring end of year 1
Tenants w/ leases expiring end of year 2
Tenants w/ leases expiring end of year 3
Tenants w/ leases expiring end of year 4

Law firm
Mortgage brokers
Engineering firm
Tenants w/ leases expiring end of year 0
Tenants w/ leases expiring end of year 1
Tenants w/ leases expiring end of year 2
Tenants w/ leases expiring end of year 3
Tenants w/ leases expiring end of year 4

(life of lease) – Old Leases

Total

Operating Expense Recovery

Operating Expenses
Recoverable

Operating Expenses

Vacancy Allowance
Bad Debt Allowance

Expenses

Stabilized NOI

IRR on Flow

Payment

Net Operating Income

Amortized Leasing Fees

Capital Expenditures (repair / replacement / renovations / additions)

Structure

, CCA recapture, terminal loss

Capital Gain
Structure capital gain

QUESTION 1 – BASELINE
Purchase Price
Parameters
Rent Growth
Expense Growth
Forecast Market rent psf (per sq ft)
Operating Expenses (psf)
Cleaning & Maintenance
Regular capital replacement (to original)
Insurance
Total
Recoverable Component
New
Renewal
Probability of renewal
Months to find new tenant (Max of 23)
Probability of renew-renew
Probability of renew-vacant or vacant-renew
Probability of vacant-vacant
Months vacant if renew-renew
Months vacant if renew-vacant / vacant-renew
Months vacant if vacant-vacant
Cap rate at sale
Effective Occupied Space (average leased over year)
Total Occupied
Vacancy Rate
Average Rent (psf) by Tenant/Tenant Group
Rent Revenues
Total Rent Revenues
	Operating Expense Recovery
Total Operating Expense Revenues
Leasing Costs
Leasing Fees to Agents
Tenant Improvements (Tis)
Total Leasing+Tis Costs
Leasehold CCA Amount
	Amortized Leasing Fees
Amortized Tis – Old Leases
Amortized Leasing Fees (life of lease) – New Leases
Amortized Tis – New Leases
CASH FLOW CALCULATIONS (NOI & CFO)
Rental Income
Space Rent
Recovery of extra repair/replacement cost (to original quality or less)
Gross Income
Non-recoverable
Other Repair/replacement cost (to original quality or less)
	Net Operating Income
Regular Capital Expenditures
Leasing costs & Tis
Cash From Operations
Stabilized NOI Calculation
Gross potential rent revenues
Operating expense recovery
Potential Gross Income
Effective Gross Income
Operating recoverable
Non-recoverable operating expenses
Stabilized (Annualized) Leasing & Tis
Primary Loan	Payment
Property Before Tax Cash Flow (PBTCF)
Income Tax Owed (No Loss Carry Forward)
Property After Tax Cash Flow (PATCF)
CAPITAL ACCOUNT: Purchase & Reversion
Purchase Price/Sales Price
Costs at Purchase/Sales
Loan Contribution/Repayment
Capital expenditure (improvement)
Capital expenditure recovery (improvement)
Tax on	Capital Gain
Tax on CCA Recapture
CFO + Reversion
		IRR on Flow
BTPCF + Equity Reversion
ATPCF + After Tax Reversion
Debt Calculations
Loan Data – Primary Loan
Outstanding Balance (Beginning of Period)
Interest
Principal
Amortized Fee
Outstanding Balance (End of Period)
Tax Calculations
Taxable Income
Interest+Amortized Fee
Pre-CCA Taxable Income
CCA DEDUCTIONS
Leasehold Deductions
Amortized Tis – Leasehold Improvements
Amount to Deduct
Pre Structure CCA Taxable Income
Structure CCA Deductions
UCC- Start of Period
Intial Structure
Start of Year UCC
New (TIs) – improvement to structure
	Structure
Max CCA Eligible
CCA Used
End of Year UCC
Total CCA taken to date
Adjusted Cost Base (ACB)
Taxable Income (Excld CCA Recapture)\
Income Tax (cash flow only, excld CCA reversion taxes)
CCA Recapture & Capital Gains
Values at Sale
Land (Market=Book Value)
Market value
Book value = UCC
Adjusted Cost Base
Adjust Land/Structure Value (if land capital gain & structure terminal loss)
Adjusted structure value
Adjusted land value
Capital gains/loss, CCA recapture, terminal loss
Land Capital Gain/Loss
	Structure capital gain
Structure recapture
Structure capital gains
Terminal loss
CCA Recapture (taxable as income)
SALES PRICE, Revenue & Capital Gains
Sales Price before adjustment for capital reimbursement
PV of future structure repair/improvement payments
Gross Sales Price
Charges at Sale
Real Estate Commission
Legal Fees
Land capital gain
Sales Charges
Net Capital Gains
Capital Gains Tax (credit)
CALCULATION AREA
Return Calculations
Yield on 10 yr Treasury
Yield on 10 yr Treasury (after tax)
BP spread of desired return
Target IRR

&”Lucida Grande,Regular”&11&K000000C307 Pro-Forma Assignment Answers &”Lucida Grande,Regular”&K000000&A &”Lucida Grande,Regular”&11&K000000&P of &N

The object of this analysis is to give you some experience in creating a pro-forma for

analysing the attractiveness of an individual project. Assumptions necessary for the

proforma analysis are attached; use them to examine a seven-year investment in a higher

quality retail centre. The pro-forma should be in nominal dollars. Use the following rules:

i) use a single discounting rate or IRR; ii) no carry forward of operating losses for use

against future income; iii) assume year 0 as the year of acquisition followed by 10 full

years of income, with sale at end of year 10, iv) the purchase includes the maximum

allowable loan, and vi) unless otherwise indicated, all leases are 5 year net leases, vii)

sale price based on estimated year 11 stabilized NOI, viii) passive owner & only one

property. For stabilized income: i) Calculate gross potential rent using current average

rent per occupied sq ft., ii) use actual operating costs, iii) vacancy allowance use the

average occupancy rate in the building over the holding period years 1-10, iii) deduct

estimated average Tis and leasing costs. Assume all TIs and leasing costs are deductible

over the life of the lease.

READ THE ASSUMPTIONS CAREFULLY

1. Purchase Price (150 points – approximately)

Find the acquisition price that gives you a total before tax unleveraged return (CFO and

capital revenues and expenditures) of 600 bp over the yield of a Government of Canada

benchmark bond yield – 10 year (as reported Friday Feb. 4, 2013,

http://www.bankofcanada.ca).

a. Take the maximum “allowable” 1st mortgage.

b. Assume that your three listed primary tenants renew, while all other tenants renew with

the assumed renewal probability. If there is no renewal use the assumed months vacant.

c. Lease terms by tenant are in the rent roll.

d. You have scheduled recoverable capital expenses. These are assumed to be added to

the UCC

All further questions refer to the after tax leveraged return for total cash flow (CFO

and capital revenues and expenditures) that came out of your calculation in 1.

2. Sensitivity Analysis (60 points).

Test for the effect on your returns for a change in each of the following variables (these

are independent not cumulative). Provide the change in basis points (bp) in your return

and the before and after values in your lowest year’s CFO (lowest year is lowest year

after change)

a. Rate of rent appreciation falls to 3.5%

b. Rate of expense appreciation increases to 4%

c. Cap rate at sale is 9.5%

d. Months vacant increases to 6 months

e. Probability of renewal falls to 50%

f. New tenant TIs increase by 5$ psf

3. Downturn (40 Points)

How do your return annual CFO, and sales price change if all of the following changes

occur that create a downturn during your holding period over years 3-6?

a. Market rents fall by 5% per annum for years 3-4, and are then flat year 5

b. Market rents rise by 13% % per annum years 6-8 and then resume the assumed growth

rate

c. Renewal probability falls to 50% for years 3-5 and returns to the original assumption in

year 6

d. Time vacant rises to 11 months for years 3-4 and then 9 months in year 5 before

returning to the original assumption for years 6 and on

4. Main Tenant Leaves (80 Points)

Your largest tenant goes bankrupt at the end of year 3.

a. What is your return and cash flow over years 4-6 if you take the 60,000 square feet and

lease it out to smaller tenants as follows:

i. They will all sign 5 years standard leases

ii. The space is vacant for 6 months, then you sign new tenants at a rate of 10,000 sq ft

per quarter

b. How long (months) could you wait to get a new main tenant and get the same return as

in a? What would be the cash flows years 4-6 if you do this?

c. If you double TIs for the new main tenant, how would your answer in “b” change?

Q1 Purchase price (rounded to $000) _______________________

Q2a bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q2b bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q2c bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q2d bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q2e bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q2f bp change in IRR ______ Q1 AFTCF_________ New AFTCF_______ Year _____

Q3 bp change in IRR ______ New Sales Price __________

Year 3 Initial AFTCF_________ Year 3 New ATCF _________

Year 4 Initial AFTCF_________ Year 4 New ATCF _________

Year 5 Initial AFTCF_________ Year 5 New ATCF _________

Year 6 Initial AFTCF_________ Year 6 New ATCF _________

Year 7 Initial AFTCF_________ Year 7 New ATCF _________

Year 8 Initial AFTCF_________ Year 8 New ATCF _________

Year 9 Initial AFTCF_________ Year 9 New ATCF _________

Year 10 Initial AFTCF_________ Year 10 New ATCF _________

Q4a IRR ______

Year 4 Initial AFTCF_________ Year 4 New ATCF _________

Year 5 Initial AFTCF_________ Year 5 New ATCF _________

Year 6 Initial AFTCF_________ Year 6 New ATCF _________

Q4b Months ______

Year 4 Initial AFTCF_________ Year 4 New ATCF _________
Year 5 Initial AFTCF_________ Year 5 New ATCF _________

Year 6 Initial AFTCF_________ Year 6 New ATCF _________

Q4c Months ______

Year 4 Initial AFTCF_________ Year 4 New ATCF _________
Year 5 Initial AFTCF_________ Year 5 New ATCF _________
Year 6 Initial AFTCF_________ Year 6 New ATCF _________

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Pro Forma ”

Get high-quality paper

NEW! AI matching with writer

Still stressed from student homework?

Get quality assistance from academic writers!

Order now