Question

Property Assumptions:

Purchase Price:                                                       $4,000000

Year 1 PGI:                                                             $540,000

PGI Growth Rate (Annual):                                   3%

Annual Vacancy and Collection Loss (VCL):        10%

Year 1 Operating Expenses (OER):                       35%

        OPEX growth rate after first year                   2%

Sales Price:                                                             

        -Capitalize HP+1 NOI at 9%                          $3,895,042

Anticipated Holding Period:                                   3 Years

Maximum LTV:                                                     70%

Interest Rate:                                                           5%

Amortization Rate:                                                 30 Years

Payments Per Year:                                                12

Investor Hurdle Rate (Unleveraged):                     15%

1. What is the Loan Amount using the LTV Ratio?
2. What is the Initial Investment (Cash or Equity Investment)?
3. What is the Loan Balance at the end of 3 years?

Answer 1

To answer the three questions, let's first write down the data that is given:

1. Purchase price of the property: $4,000,000
2. Maximum Loan to value (LTV): 70%
3. Interest rate: 5% (assuming it is APR); since amortization payments are made monthly, the applicable rate r = 5% / 12 = 0.417% (rounding off to 3 decimals)
4. Tenure of the loan N: 30 years

Loan Amount:

Loan amount that can be availed = LTV * Property value

In this case, since the maximum LTV is 70%, the loan amount that can be availed would be $4,000,000 * 70% = $2,800,000 (let's call this P)

Initial Investment:

If loan of $2,800,000 is being availed for purchasing the property, then the remaining equity funding required would be $4,000,000 - $2,800,000 = $1,200,000

Loan balance at the end of 3 years:

To find out the loan balance at the end of 3 years, we first have to generate the amortization schedule. The monthly amortization A can be calculated using the below formula:

P *r A= 1- +

Substituting the values in the above formula, we get A = $15,031. Hence, the monthly amortization on a loan of $2,800,000 at 5% interest rate is $15,031. With this we can generate the amortization schedule. The schedule will have the following column items:

Month

Amortization

Interest

Principal

Loan Balance

Since, the loan is being availed for 30 years, i.e., 360 months, the complete schedule will have 360 entries. However, since we need to find out the loan balance at the end of 3 years, we will compute the entries for 30 months. Doing this manually would take a lot of time, so it is better to use Excel for the calculations.

Let's compute the entries for the first month. We know the amortization is $15,031 and this will be the same throughout the schedule.

Interest = r * loan balance at the beginning of the month. Since we are calculating the interest for the first month, the interest would be 0.417% * $2,800,000 = $11,667 (0.417% * $2,800,000 would give you $11,676, but as mentioned above 5%/12 has been rounded off to 3 decimals, hence the difference).

Principal = Amortization - interest for the month. For the first month, it would be $15,031 - $11,667 = $3,364.

Loan balance = Loan at the beginning of the month - Principal for the month. For the first month, it would be $2,800,000 - $3,364 = $2,796,636 (loan balance at the end of the first month).

Repeating this process for the rest of the tenure, we get the following amortization schedule (for the first 36 months only). It can be observed from the schedule that the loan balance at the end of three years is $2,669,621.

Month	Amortization	Interest	Principal	Loan Balance
1	15,031	11,667	3,364	2,796,636
2	15,031	11,653	3,378	2,793,257
3	15,031	11,639	3,392	2,789,865
4	15,031	11,624	3,407	2,786,458
5	15,031	11,610	3,421	2,783,038
6	15,031	11,596	3,435	2,779,603
7	15,031	11,582	3,449	2,776,153
8	15,031	11,567	3,464	2,772,689
9	15,031	11,553	3,478	2,769,211
10	15,031	11,538	3,493	2,765,719
11	15,031	11,524	3,507	2,762,212
12	15,031	11,509	3,522	2,758,690
13	15,031	11,495	3,536	2,755,153
14	15,031	11,480	3,551	2,751,602
15	15,031	11,465	3,566	2,748,036
16	15,031	11,450	3,581	2,744,455
17	15,031	11,435	3,596	2,740,859
18	15,031	11,420	3,611	2,737,249
19	15,031	11,405	3,626	2,733,623
20	15,031	11,390	3,641	2,729,982
21	15,031	11,375	3,656	2,726,326
22	15,031	11,360	3,671	2,722,655
23	15,031	11,344	3,687	2,718,968
24	15,031	11,329	3,702	2,715,266
25	15,031	11,314	3,717	2,711,549
26	15,031	11,298	3,733	2,707,816
27	15,031	11,283	3,748	2,704,067
28	15,031	11,267	3,764	2,700,303
29	15,031	11,251	3,780	2,696,524
30	15,031	11,236	3,795	2,692,728
31	15,031	11,220	3,811	2,688,917
32	15,031	11,204	3,827	2,685,090
33	15,031	11,188	3,843	2,681,246
34	15,031	11,172	3,859	2,677,387
35	15,031	11,156	3,875	2,673,512
36	15,031	11,140	3,891	2,669,621