In: Finance
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 (nominal rate) 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.
2. If you can lower your monthly interest charge by 50 basis points (to 3.75%) by paying 3.0 “points” at closing, should you do it? Explain why or why not and demonstrate how the values change in the spreadsheet by showing values with and without “points”.
Instructions: Set up a spreadsheet for valuing this opportunity using Excel functions to answer the questions given
Please find below details
Property Value | 2250000 |
Loan To Value Ratio | 0.8 |
Loan Value | 1800000 |
There are two options
1. 4.25% APR (assuming interest of 4.25% as no closing fee is defined) paid monthly
2. 3.75% interest charge with closing fee of 3.0 points.
1. With 4.25% of APR the equivalent monthly repayment will be 4.25%/12 = 8854.92$ = emi
formula : emi = P * r * (1+r)n/((1+r)n -1)
here r = 4.25%/12, P = 1800000, n =360 = 30 years
2. Option 2 involves closing fee of 3% = 54000$ and interest rate of 3.75%. hence the EMI becomes = 8336.08$ (refer formula above).
After plotting the cashflows of debt repayment of 8336.08$ and initial inflow of (1800000 - 54000) the IRR of the cash flow beacome 0.3334% monthly or 4.00% annually. This is APR of the option 2.
Hence it is advisable to go ahead with Option 2 as APR of Option 2 (4.00%) is lower than that of Option 1 (4.25%)
the required repayment Schedule for option 2 and option 1 is provided below:
*Excel sheet cannot be attached hence only first 2 years repayment sheet is provided
Option 2 | Option 1 | |||||||
Month | Opening Balance | Interest | EMI Payment | Closing Balance | Opening Balance | Interest | EMI Payment | Closing Balance |
1 | 1800000.00 | 5625.00 | 8336.08 | 1797288.92 | 1800000.00 | 6375.00 | 8854.92 | 1797520.08 |
2 | 1797288.92 | 5616.53 | 8336.08 | 1794569.37 | 1797520.08 | 6366.22 | 8854.92 | 1795031.38 |
3 | 1794569.37 | 5608.03 | 8336.08 | 1791841.32 | 1795031.38 | 6357.40 | 8854.92 | 1792533.87 |
4 | 1791841.32 | 5599.50 | 8336.08 | 1789104.74 | 1792533.87 | 6348.56 | 8854.92 | 1790027.51 |
5 | 1789104.74 | 5590.95 | 8336.08 | 1786359.61 | 1790027.51 | 6339.68 | 8854.92 | 1787512.27 |
6 | 1786359.61 | 5582.37 | 8336.08 | 1783605.90 | 1787512.27 | 6330.77 | 8854.92 | 1784988.12 |
7 | 1783605.90 | 5573.77 | 8336.08 | 1780843.59 | 1784988.12 | 6321.83 | 8854.92 | 1782455.04 |
8 | 1780843.59 | 5565.14 | 8336.08 | 1778072.65 | 1782455.04 | 6312.86 | 8854.92 | 1779912.98 |
9 | 1778072.65 | 5556.48 | 8336.08 | 1775293.04 | 1779912.98 | 6303.86 | 8854.92 | 1777361.92 |
10 | 1775293.04 | 5547.79 | 8336.08 | 1772504.75 | 1777361.92 | 6294.82 | 8854.92 | 1774801.83 |
11 | 1772504.75 | 5539.08 | 8336.08 | 1769707.75 | 1774801.83 | 6285.76 | 8854.92 | 1772232.67 |
12 | 1769707.75 | 5530.34 | 8336.08 | 1766902.01 | 1772232.67 | 6276.66 | 8854.92 | 1769654.40 |
13 | 1766902.01 | 5521.57 | 8336.08 | 1764087.49 | 1769654.40 | 6267.53 | 8854.92 | 1767067.01 |
14 | 1764087.49 | 5512.77 | 8336.08 | 1761264.19 | 1767067.01 | 6258.36 | 8854.92 | 1764470.46 |
15 | 1761264.19 | 5503.95 | 8336.08 | 1758432.06 | 1764470.46 | 6249.17 | 8854.92 | 1761864.70 |
16 | 1758432.06 | 5495.10 | 8336.08 | 1755591.08 | 1761864.70 | 6239.94 | 8854.92 | 1759249.72 |
17 | 1755591.08 | 5486.22 | 8336.08 | 1752741.22 | 1759249.72 | 6230.68 | 8854.92 | 1756625.48 |
18 | 1752741.22 | 5477.32 | 8336.08 | 1749882.45 | 1756625.48 | 6221.38 | 8854.92 | 1753991.95 |
19 | 1749882.45 | 5468.38 | 8336.08 | 1747014.76 | 1753991.95 | 6212.05 | 8854.92 | 1751349.08 |
20 | 1747014.76 | 5459.42 | 8336.08 | 1744138.10 | 1751349.08 | 6202.69 | 8854.92 | 1748696.86 |
21 | 1744138.10 | 5450.43 | 8336.08 | 1741252.45 | 1748696.86 | 6193.30 | 8854.92 | 1746035.24 |
22 | 1741252.45 | 5441.41 | 8336.08 | 1738357.78 | 1746035.24 | 6183.87 | 8854.92 | 1743364.20 |
23 | 1738357.78 | 5432.37 | 8336.08 | 1735454.07 | 1743364.20 | 6174.41 | 8854.92 | 1740683.70 |
24 | 1735454.07 | 5423.29 | 8336.08 | 1732541.28 | 1740683.70 | 6164.92 | 8854.92 | 1737993.70 |