Reference no: EM133715898
It is December 31, 2019. You are considering purchasing a property offered for sale at 1540 Georgesville Road, Columbus, Ohio. A brochure is enclosed.
Price: You tried to negotiate the price down but failed. There are no transaction costs.
Rent: Rent is paid at the end of each month. The first payment will be on January 31.
Financing: You start discussing different financing structures with your local bank. Loan payments are at the end of the month.
1. The loan officer examines the rent schedule and notices that the rent is guaranteed until the end of August 2031.
She is offering you to use the entire rent amount during the guaranteed rent period to pay off the loan. In other words, the bank will collect the entire rent payment during the guaranteed period (you get zero income).
The loan is fully amortized over the loan term (i.e., the balance is $0 at the end of the term).
The interest rate on the loan is 5.1%.
What maximum amount would the bank be willing to lend you under these conditions?
HINT: Set up the cash flow schedule and amortization table. Then, vary the loan amount until it is fully amortized over the life of the loan.
Check the LTV of each loan amount to ensure you are using the correct interest rate.
2.What is the monthly IRR for the lender on this loan?
Enter your response as XX.xx%, e.g., for an answer of 5.13% (or 0.0513) type "5.13".
3.Now, suppose that you plan to sell the property in August 2031. Let's find the anticipated sales price, based on the following assumptions:
The tenant renews the rental contract as specified in the rent schedule at $10,212 monthly.
The exit cap rate is 6.90% + 0.5% = 7.40%
For simplicity, assume that the sales price can be calculated as (monthly NOI * 12 / Cap rate)
Calculate the sales price that you will expect to receive in August 2031.
4.Assume that the anticipated sales price is $1,700,000. Assume also the bank lent you $1,050,000 and that the loan was completely paid off using the rent payments until Aug 2031. This means the cash flows to the investor (you) are the net purchase amount (purchase price minus the loan amount; in negative sign), and the sales price in 2031.
What is the leveraged annual IRR to the investor ("cash-on-cash")?
Note: When calculating the IRR, Excel might show an Error. You may need to "guess" the IRR: "=IRR(CELLRANGE, 0.01).
After you calculate the monthly IRR, use the following formula: AnnualIRR=(1+MonthlyIRR)12-1
Enter your response as XX.xx%, e.g., for an answer of 5.13% (or 0.0513) type "5.13".
5. You convinced the loan officer to allow $1,000,000 of unpaid balance to remain outstanding in August 2031. The balance will be paid once you sell the property.
The loan officer agrees but increases the interest rate on this loan to 5.4%.
Recalculate the maximum amount that the lender will lend.
6. What will be the lender's IRR in this case?
Enter your response as XX.xx%, e.g., for an answer of 5.13% (or 0.0513) type "5.13".
7.What is the buyer's IRR with the higher loan amount? Assume that you borrowed $1,580,000 and that you will owe $1,000,000 in Aug 2031. This amount will be paid out of the sales price. As before, assume that the sales price will be $1,700,000.