Excel Debt Loan Technical Question

HennyHigden's picture
Rank: Chimp | 15

Can anyone model this? I finished B school with Real Estate Masters degree, and am having a lot of trouble.

The following investment on 1/1/2017 with the following terms:

* $100,000,000 Total Loan - interest only payments/non-amortizing
* 24 month term
* 12% interest rate - charged as actual/360 on current outstanding loan balance ("OLB")
* 1% origination fee paid at close
* 1% exit fee paid on maturity
* 12 month extension option at 1% of total loan
* $50MM funded up front on 1/1/17- the rest drawn ratably over the first 20 months
* Full yield maintenance/prepayment for the life of loan or as extended, paid on current OLB
* After 3 months, the loan will be split into an A and B note. The A-Note purchaser will buy a 50% share of the current OLB, and fund future draws pari passu for the remainder of the term. The A-note will also get a 1% origination/exit/extension fee on their $50MM, earn 6% interest on their OLB at actual/360, and have full yield maintenance/prepayment on OLB.
* The retained B-note collects any spread between the total loan and the A-note, in addition to full returns on its retained B-note portion.

Please model out the monthly cash flows of this investment and provide the following within the spreadsheet in a summarized manner:

1) The IRR & total dollar returns of the following, assuming the extension option is not exercised and the loan is held to maturity:
* Total Loan
* A-note
* B-note
* Net return on the Total Loan after a 25% incentive fee, and total dollar amount of incentive fee
* Net return on B-note after a 25% incentive fee, and total dollar amount of incentive fee

2) Please provide the following using the Data Table function comparing the below scenarios to the original terms (one data table should be created for each scenario variation):

A. The gross and net total loan and B-note returns and the amount of the incentive fee if the loan is prepaid in month 21.
B. The gross and net total loan and B-note returns and the amount of the incentive fee if the loan is extended for the full 12 months and repaid in month 36.
C. The gross and net B-note returns and the amount of the incentive fee if the A-note is sold in Month 8 as opposed to Month 3. Show returns for both un-extended and extended terms.
D. The gross and net B-note returns and the amount of the incentive fee if the total loan interest rate is 14.5% and the A-note interest rate is 8%.

Comments (1)

Apr 12, 2018
Comment