Question

1. You are advising a group of investors who are considering the purchase of a shopping center complex. They would like to finance 75% of the purchase price. A loan has been offered to them with the following terms: The contract interest rate is 10% and will be amortized with monthly payments over 25 years. The loan has a lockout provision that prevents it from being repaid before year 5. The property is expected to cost $5 million. NOI is estimated to be $475,000, including overages for the first year and to increase at the rate of 3% annually for the next 5 years. The property is expected to be worth $6 million at the end of 5 years. The improvement represents 80% of cost, and depreciation will be over 39 years. Assume a 28% tax bracket for both income and capital gains tax and a holding period of 5 years. a. Compute the BTIRR and ATIRR on the equity investment. b. What is the BEIR? c. Is there positive financial leverage?

Answer 1

Soln : Step 1: Please calculate the amortized schedule for 5 years using 25 years = 300 months of payment on monthly basis for amount = 75%*5 mn = 3.75 million for 10% interest = 10/12 = 0.83% on monthly basis.

Using PMT formula in excel we get EMI = 33970.59, please refer table for interest calculation:

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60
3750000	37,47,279	37,44,536	37,41,770	37,38,981	37,36,168	37,33,333	37,30,473	37,27,590	37,24,682	37,21,751	37,18,795	37,15,814	37,12,809	37,09,778	37,06,722	37,03,641	37,00,534	36,97,401	36,94,243	36,91,057	36,87,846	36,84,607	36,81,341	36,78,049	36,74,728	36,71,381	36,68,005	36,64,601	36,61,169	36,57,708	36,54,218	36,50,699	36,47,151	36,43,574	36,39,966	36,36,329	36,32,661	36,28,962	36,25,233	36,21,473	36,17,681	36,13,858	36,10,003	36,06,116	36,02,196	35,98,244	35,94,258	35,90,240	35,86,188	35,82,102	35,77,983	35,73,828	35,69,640	35,65,416	35,61,157	35,56,863	35,52,533	35,48,167	35,43,764	35,39,325
Payment	? 33,970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59	33970.59
Interest	31,250	31,227	31,204	31,181	31,158	31,135	31,111	31,087	31,063	31,039	31,015	30,990	30,965	30,940	30,915	30,889	30,864	30,838	30,812	30,785	30,759	30,732	30,705	30,678	30,650	30,623	30,595	30,567	30,538	30,510	30,481	30,452	30,422	30,393	30,363	30,333	30,303	30,272	30,241	30,210	30,179	30,147	30,115	30,083	30,051	30,018	29,985	29,952	29,919	29,885	29,851	29,817	29,782	29,747	29,712	29,676	29,641	29,604	29,568	29,531
Principal paid	2,721	2,743	2,766	2,789	2,812	2,836	2,859	2,883	2,907	2,932	2,956	2,981	3,005	3,031	3,056	3,081	3,107	3,133	3,159	3,185	3,212	3,239	3,266	3,293	3,320	3,348	3,376	3,404	3,432	3,461	3,490	3,519	3,548	3,578	3,607	3,638	3,668	3,698	3,729	3,760	3,792	3,823	3,855	3,887	3,920	3,952	3,985	4,018	4,052	4,086	4,120	4,154	4,189	4,224	4,259	4,294	4,330	4,366	4,403	4,439

Steo 2: We will drw the P& L statement of the project :

Year	0	1	2	3	4	5
NOI		475000	489250	503927.5	519045.325	534616.6848
Cost	1250000.00
Depreciation		128205.13	128205.13	128205.13	128205.13	128205.13
Interest		373461.32	369881.62	3,65,927.07	3,61,558.439	3,56,732
Property Value	5000000.00	4871794.87	4743589.74	4615384.62	4487179.49	4358974.36
PBT		-26666.45	-8836.75	9795.30	29281.76	49679.21
Capital Gain						16,41,026
Total income		-26666.45	-8836.75	9795.30	29281.76	1690704.85
Tax		0.00	0	0	1,000.68	4,73,397.36
PAT		-26666.45	-8836.75	9,795	28,281	12,17,307.49

To calculate BTIRR for equity we have cash flows :

0	1	2	3	4	5
-1250000.00
	-26,666	-8,837	9795.297439	29281.75766	1690704.847

Using IRR in excel we get BTIRR = 6.20%

Similarly for ATIRR cash flows are :

-1250000.00
	-26,666	-8,837	9795.297439	28281.07608	1217307.49

We will get ATIRR = -0.48%

b) BEIR or Break even interest rates are where theinterest on borrowed funds has no effect on the return of invested fund, can be calculated sing this = After tax IRR on equity/(1-investor tax rate) = -0.48%/(1-0.28) = -0.67%

c ) As we have seen the BEIR is negative, hence, it is actually a negative leverage and not positive.