Homework about excel loan(WAC, SDA, CDR, SDA) – Assignment Help


FOR ANY SPREADSHEET, YOU MUST HIDE REPETITIVE ROWS THAT DO NOT ILLUSTRATE ANYTHING SUBSTANTIALLY NEW. Do not make any one spreadsheet printout more than 2 pages max (you will generally need only one). Do not continue table columns across several pages (best to print in landscape mode). FAILURE TO DO ALL THIS WILL RESULT IN A LOSS OF FIVE (5) POINTS. 41 points total.

ANNOTATE any formulae that involve more than addition and subtraction (e.g., using a pen or Office’s comment feature – just one period will suffice, of course)

  • [22pts]
    • [20pts] Consider a 100,000,000 CMBS pass-through (PT) security consisting of fresh 15 year fixed rate loans that fully amortize over a period of 30 years, and have a WAC of 7% with fees amounting to 0.5%. For purposes of this HW, a CMBS is like a RMBS, except that all loans have a prepayment lock-out (assume for the entire term), but default occurs according to the standard SDA function. Additionally, since the loans do not fully amortize, there is generally a balloon loss – quoted as a fraction of the outstanding balance at mortgage maturity. To simplify, assume that there is no recovery (unrealistic, of course). Assume that the PT is sold at 94.345. Allow for loans to default at the PSA’s SDA CDR, and allow for balloon risk. The current (corresponding maturity) Treasury yield is 5%. Create a table and graph of the spread (in bps) of cash flow yield to Treasury versus SDA, for 0% and 10% balloon loss. In the spreadsheet that you hand-in, show the situation for 100 SDA and 10% balloon loss. Use par pricing. See notes below for further assumptions and hints.
    • [2pts] Why is there generally a large fraction of outstanding principal that defaults at maturity?
  • [8pts] Consider the following par yield curve for semiannual bonds, all quoted as BEY:
    • [6pts] What are the corresponding zero rates?
    • [2pts] What is today’s lockable rate for a 6 month loan 1 year from now?
  • [10pts] Stratify the following Agency mortgages at a deal coupon of 4%. What are the initial pool and PO principals, and what are the initial notional principal and coupon of the IO?

Maturity [yr]

Par Yield









Balance (mln $)














Notes for question 1:

  1. Note that there is no messy tranching going on here.
  2. Allow for a general SDA; see the graph and spreadsheet in your lecture notes.
  3. Here is one way to get the CDR (as there are better versions, feel free to use your own, extra points if you can show me that the formula below is wrong); “A13” refers to the month and “SDA” to the SDA factor: =(IF(A13<=30,A13*0.006/30,0)+IF(AND(A13>30,A13<=60),0.006,0)+IF(AND(A13>60,A13<=120),0.006-(0.006-0.0003)/(120-60)*(A13-60),0)+IF(A13>120,0.0003,0))*SDA/100
  4. Assume that loss recovery is zero – any loan that defaults is a completely written down.
  5. Defaults work much like prepayment, with one exception: the monthly default happens just before the payment (why pay interest and principal if you are defaulting anyway). So take default = beginning balance × MDR and then apply the appropriate pool survival factor to get the realized “mortgage payment.”
  6. Make sure you create a column of the PT cash flows, including the initial investment at par, so that you can calculate the CF yield.
  7. Balloon losses are quoted as a fraction of the final outstanding principal.
  8. In your graph, show a range of SDA’s of 0 to 1,000. You will find that you do not have to compute very many values in that range.
  9. Display all cash flows to the nearest dollar (use format → cell).

The post Homework about excel loan(WAC, SDA, CDR, SDA) appeared first on ESSAY PAPER.

Homework about excel loan(WAC, SDA, CDR, SDA) was first posted on February 2, 2021 at 11:44 pm.
©2019 "ESSAY PAPER". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement. Please contact me at ALL ANSWERS

Place your order
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more