Question

The specifics of the opportunity are as follows:

Assume 100% occupancy

Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80%

Loan Terms: fully amortized over 30 years at 4.25% APR paid monthly

The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because the tenants in that unit are responsible for year round cosmetic maintenance of the walkways and greenspaces and also minor emergency repairs. You plan to invest an additional $150,000 in paid in capital for cosmetic updates on the property and closing fees. The tax rate on the property is 1.75% of the purchase price. For the sake of simplicity will assume that taxes and rents are constant.

3. Under the original loan conditions, how much do you have to pay extra each period to make the loan pay off in 20 years? What is the interest cost savings from doing that?

Instructions: Set up a spreadsheet for valuing this opportunity using Excel functions to answer the questions given

Answer 1

Purchase price	2250000
Additional investment	150000
Total value	2400000
Loan amount(2400000*80%)	1920000

Monthly payment against the loan amount of $ 1920000

at a monthly interest rate of 4.25%/12 =0.003542

for a period of 30*12=360 months

is calculated using the PV of ordinary annuity formula

PV of Loan=Mthly.PMT*(1-(1+mthly int.rate)^-no.of mths)/mthly.int.rate

ie.1920000=Mthly.payment*(1-1.003542^-360)/0.003542

Solving the above, we get, the equal mthly.payment as

$9,446

So, calculating from above,
Total payments (9446*360)	3400560
PV of Loan	1920000
So,total amount paid towards interest=	1480560
(3400560-1920000)

3..

to make the same loan pay off in 20 years,ie. 20*12=240 mths.

using the same formula as above, (at the same 4.25% p.a.)

1920000=Mthly.payment*(1-1.003542^-240)/0.003542

Solving the above,mthly. Payment comes to

$11,890

Amount to be paid extra each period in the latter case works out to

11890-9446=

2444

Total payments (11890*240)	2853600
PV of Loan	1920000
So,total amount paid towards interest=	933600
(2853600-1920000)

So, savings in interest expenses :
30 yrs.-360 mthly.payments	1480560
20 yrs.-240 mthly.payments	933600
Savings in interest costs	546960

CORRECTED SOLUTION:

Total value=Purchase price=	2250000
Loan amount(2250000*80%)	1800000
Monthly payment against the loan amount of $ 1800000
at a monthly interest rate of 4.25%/12 =0.003542
for a period of 30*12=360 months
is calculated using the PV of ordinary annuity formula
PV of Loan=Mthly.PMT*(1-(1+mthly int.rate)^-no.of mths)/mthly.int.rate
ie.1800000=Mthly.payment*(1-1.003542^-360)/0.003542
Solving the above, we get, the equal mthly.payment as
$8,855
So, calculating from above,
Total payments (8855*360)	3187800
PV of Loan	1800000
So,total amount paid towards interest=	1387800
(3187800-1800000)
3..
to make the same loan pay off in 20 years,ie. 20*12=240 mths.
using the same formula as above, (at the same 4.25% p.a.)
1800000=Mthly.payment*(1-1.003542^-240)/0.003542
Solving the above,mthly. Payment comes to
$11,147
Amount to be paid extra each period in the latter case works out to
11147-8855=
2292
Total payments (11147*240)	2675280
PV of Loan	1800000
So,total amount paid towards interest=	875280
(2675280-1800000)
So, savings in interest expenses :
30 yrs.-360 mthly.payments	1387800
20 yrs.-240 mthly.payments	875280
Savings in interest costs	512520