PLEASE SHOW EXCEL EQUATION STEPS OF HOW TO SOLVE THIS PROBLEM. THANK YOU QUESTIONS: A) What is the yield to the lender and the investor's after-tax IRR if 90% of the loan must be drawn during the first four months and 10% during the last 8 months. B) Repeat (A) assuming that 60% of the loan is drawn the first 4 months and 40% the last 8 months.
Financing Proposed Projects with Construction Loan
Spreadsheet Limitations: Twelve month draw period, where first % draw is only for 4 months. All yellow colored cell
are data input cells. Set all data input cells that do not apply equal to zero (i.e. if there is constant growth,
set the growth for "Yr2-Yr3" to zero.
Data Input Box:
Property Appreciation	6.00%	per year	Holding Pd. (after con)	5	years
Selling Costs	2.00%	of sale price	Permanent Financing
Gross Building Area	120,000		Loan Fee	3.00%	of loan
Gross Leasable Area	110,000		Loan Fee Amort	10	years
Site Acq. & Closing	2,500,000		Loan Amortization	25	years
On/Off Site Costs	850,000		Loan Term	10	years
Hard Costs	7,055,500		Interest Rate	0.12
Soft Costs	1,576,786		Payments per Year	12
Leasing Comissions	45,300		Construction Loan
Rent	15	per GLA	Loan Amount	$ 8,309,000
Rent Growth	6.00%	per year	Term	12	months
Overage Percentage	5.00%	on gross sales	% drawn first 4 mos.	75.00%
Overage on Excess over	200.00	per GLA	% drawn last months	25.00%
Average Sales	200.27	per GLA	Interest Rate	12.00%
Sales Growth Yr 2 - Yr 3	316%		Loan Fee	2.00%
Sales Growth	4.00%	per year	Tax Considerations
Tenant Reinmbursement	8.39	per GLA, yr 2	Marginal Tax Rate	28.00%
Tenant Reinb. Growth	4.00%	per year	Capital Gains Rate	28.00%
Expenses	9.68	per GLA	Depreciation
Exp. Growth Yr 2 - Yr 3	2.59%		Capital Improvements	31.5	years - S/L
Expenses Growth	4.16%	per year	Capital Improvements	90.00%	of total
Vacancy Yr 2	30.00%		Tenant Improvements	7	years - DDB
Vacancy beginning Yr 3	5.00%		Tenant Improvements	10.00%	of total
Total Cost Breakdown
Site Acq. & Close	2,500,000		Total Project Costs	$ 11,982,286
On/Off-Site Costs	850,000		Loan Amount	8,741,760
Hard Costs	7,055,500		Equity Needed	3,240,526
Soft Costs	1,576,786
Total Project Costs	$11,982,286		Perm Loan Fee Amount	$ 262,253
CONSTRUCTION LOAN DRAWS & REPAYMENT:
End of Month	Project Costs	Interest	Ending Balance	Payoff	Lender's Cash Flow
0					$ 174,835
1	$ 1,557,938	0	$ 1,557,938	0	(1,557,938)
2	1,557,938	15,579	3,131,454	0	(1,557,938)
3	1,557,938	31,315	4,720,706	0	(1,557,938)
4	1,557,938	47,207	6,325,851	0	(1,557,938)
5	259,656	63,259	6,648,766	0	(259,656)
6	259,656	66,488	6,974,910	0	(259,656)
7	259,656	69,749	7,304,315	0	(259,656)
8	259,656	73,043	7,637,014	0	(259,656)
9	259,656	76,370	7,973,041	0	(259,656)
10	259,656	79,730	8,312,427	0	(259,656)
11	259,656	83,124	8,655,208	0	(259,656)
12	259,656	86,552	9,001,416	8,741,760	8,741,760
Total	$ 8,309,000	$ 692,416
			Yield to Lender		15.35%
SUMMARY OF PERMANENT LOAN
Year of Loan	2	3	4	5	6
Payment	1,104,844	1,104,844	1,104,844	1,104,844	1,104,844
Mortgage Balance	8,682,752	8,616,260	8,541,335	8,456,908	8,361,773
Interest	1,045,836	1,038,352	1,029,919	1,020,417	1,009,709
Principal	59,008	66,492	74,925	84,427	95,135
SUMMARY OF DEPRECIABLE COSTS
On/Off Site Improvements	850,000
Hard Costs	7,055,500
Soft Costs	1,094,398	(Does not include leasing commissions, Permanent loan fees or construction loan fees)
Total Depreciable Costs	8,999,898
BEFORE AND AFTER-TAX CASH FLOWS:
Year	0	1	2	3	4	5	6
Site Acq. and Closing	2,500,000
Site Improvements		850,000
Hard Costs		7,055,500
Soft Costs*		447,282
*(less interest & loan fees)
Permanent Loan Fee	262,253
Construction Loan Fee	174,835
Construction Interest		692,416
Total Constr. Outflow	2,937,088	9,045,198
Less: Total Draws	0	8,741,760
Total Equity Needed	$ 2,937,088	$ 303,438
OPERATING PERIOD CASH FLOWS
Year	1	2	3	4	5	6
Rent		1,650,000	1,749,000	1,853,940	1,965,176	2,083,087
Overage		29,997	124,788	129,779	134,970	140,369
Tenant Reimbursements		923,000	959,920	998,317	1,038,250	1,079,780
PGI		2,602,997	2,833,708	2,982,036	3,138,396	3,303,236
Less: Vacancy		780,899	141,685	149,102	156,920	165,162
EGI		1,822,098	2,692,022	2,832,934	2,981,476	3,138,074
Total Expenses		1,064,800	1,092,402	1,137,847	1,185,183	1,234,487
Net Operating Income		757,298	1,599,620	1,695,087	1,796,294	1,903,587
Less: Debt Service		1,104,844	1,104,844	1,104,844	1,104,844	1,104,844
Before-Tax Cash Flow	(303,438)	(347,546)	494,776	590,243	691,450	798,743
Net Operating Income		757,298	1,599,620	1,695,087	1,796,294	1,903,587
Less:
Interest		1,045,836	1,038,352	1,029,919	1,020,417	1,009,709
Depreciation
Capital Imprvmnt		257,140	257,140	257,140	257,140	257,140
Tenant Imprvmnt		257,140	183,671	131,194	93,710	66,936
Amortization:
Constr Loan Fee	174,835
Perm Loan Fee		26,225	26,225	26,225	26,225	26,225
Leasing comm.	0	0	0	0	0	0
Taxable Income	(174,835)	(829,043)	94,231	250,609	398,802	543,576
Taxes	(48,954)	(232,132)	26,385	70,170	111,665	152,201
After-Tax Cash Flow	(254,484)	(115,414)	468,391	520,073	579,785	646,541
AFTER-TAX CASH FLOW FROM SALE
Sale Price (received by investor)		16,035,002
Sales costs		320,700
Mortgage Balance		8,361,773
Before-tax Cash Flow		7,352,528
Original Cost Basis	11,982,286
Accum Dep & Amort	2,324,312
Adjusted Basis		9,657,974
Capital Gain		6,056,328
Tax from Sale			1,695,772
After-Tax Cash Flow from Sale			5,656,756
BEFORE and AFTER-TAX CASH FLOW SUMMARY
Year	0	1	2	3	4	5	6
Before-Tax Cash Flow	(2,937,088)	(303,438)	(347,546)	494,776	590,243	691,450	8,151,271
After-Tax Cash Flow	(2,937,088)	(254,484)	(115,414)	468,391	520,073	579,785	6,303,298
BTCF IRR	20.95%
ATCF IRR	17.35%
Note: The calculation of taxable income differs slightly from the book for the following reasons:
1) Leasing commissions were not amortized in the book as they should be as shown above.
2) The amount in the book for depreciation of tenant improvements in year 5 was wrong in the book.