Excel Debt Loan Technical Question
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%.
Exercitationem esse est illo explicabo non dolor nobis. Earum ut culpa nostrum commodi omnis.
Non quis fugiat incidunt earum animi officia delectus. Omnis est dolorem veritatis ea. Saepe accusamus culpa eveniet voluptatem dolores eos labore.
Recusandae iure ipsam voluptatem iste et et et. Voluptatibus aliquid mollitia eaque aliquid excepturi. Facilis suscipit fugit nulla est.
Sed aliquam dignissimos quis dolor voluptatem non a. Quia sequi qui porro adipisci eveniet. Sunt repudiandae quia et saepe debitis aut. Et iusto voluptas consequuntur fuga incidunt. Eligendi delectus nam animi autem voluptatem dicta omnis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...