• 067 05/02/2017
    In response to subscriber email traffic, I thought I would post a couple examples of monthly debt schedules. This post includes an Excel template with two examples (available for download at the bottom of this post). The first it labeled "Senior Term Loan," and the second is labeled "Subordinated Notes."

    Senior Term Loan: This example debt schedule provides the option to project interest payments at LIBOR + a fixed rate, and includes mandatory quarterly principal payments. There is a row for the cash flow sweep, but it is included as a manual input in this model (for a dynamic version in please see the leveraged buyout video series).

    Subordinated Notes: This example debt schedule includes a toggle that permits a portion of the interest be paid in kind (Payment-in-Kind or PIK). This feature can be activated or deactivated for any monthly period with a simple "Y" / "N" input.

    Formulas and Functions: As you explore the template, you may notice functions that have not been explained previously on ASM. For a video explanation please see the following:


    Template available for download: Download Template
     

    Click HERE to have updates like this one sent directly to your inbox.

     

     

    Monthly Debt Schedule Examples in Microsoft Excel Financial Templates.

     

 



  • 066 05/02/2017

    One of the challenges you will encounter building monthly debt schedules is the need to calculate and show interest expense in each month, and then reflect the payment of interest at the end of the quarter. This video provides a helpful approach.

    Click on the icon in the lower right-hand corner of the video player to expand to full screen.


    The template used in the video is available for download: Download Template

    What you will learn in this video:

    1. How to use =MOD() in an =IF() statement to calculate quarterly interest payments.
    2. Multiple uses of the =OR() function.

 



  • 065 04/28/2017

    This video explains how the =SUM and =OFFSET functions can be combined to write a formula that will sum a specific number of cells. This is especially useful when you are working with monthly data, and showing quarterly and annual periods in a financial model. The video also explains why using the =SUM formula for this purpose can lead to errors.

    Click on the icon in the lower right-hand corner of the video player to expand the view to full screen.


    The template used in the video is available for download: Download Template

    What you will learn in this video:

    1. Why =SUM() has limitations.
    2. How the =OFFSET() function can be used to return arrays.
    3. How to effectively use =SUM and =OFFSET together.
    4. How to use F9 to audit formulas.

 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.