{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

# 3 advanced formulas - formula results in the priority of...

This preview shows pages 1–5. Sign up to view the full content.

ADVANCED FINANCIAL MODELING FORMULAS Optional Debt Prepayment / Waterfall Formula =MAX(MIN(D151-E218,E\$241- SUM(E\$222:E224)),0)*\$D\$225 YES / NO TRIGGER = \$D\$225 If \$D\$225= 0 or “NO” there is no prepayment and the value reverts to 0 If \$D\$225 = 1 or “YES” there is prepayment and the value = the output of the min / max function

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
MIN / MAX FUNCTIONS =MAX(MIN(D151-E218,E\$241- SUM(E\$222:E224)),0)*\$D\$225 The amount of optional debt retirement cannot: 1) Be a negative payment - Achieved using a MAX function 2) Exceed the Debt balance - Achieved using a MIN function Further detail on MIN function MIN(D151-E218,E\$241- SUM(E\$222:E224)) D151 – E218 = Revolver Balance – Required Revolver Pmt Required Revolver Payment is 0 in this case.
E\$241 – SUM(E\$222:E224) Where: E\$241 = Cash Available for Debt Repayment + Existing Excess Cash SUM(E\$222:E224) = Required Debt Payments + Previous Optional Prepayments If you highlight the Senior Sub cell this

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: formula results in the priority of paying off the Revolver, then Term Loan, and then the Senior Sub Notes. (Waterfall formula) Average Interest Expense Calculation =\$C104*IF(B7=1, (D160+E160)/2,D160) If average interest formula trigger \$C104 is turned on then multiply the interest rate by an average of this period’s debt balance and the prior period’s debt balance… Else Multiply the interest rate by last period’s debt balance. Calculation Of Fully Diluted Shares Outstanding Using Options Sample Formula: Diluted Shrs =E7+IF(E6>E9,E8-E8*E9/E6,0) Logic If the options are out of the money use current shares outstanding (E7)…else use current shares outstanding (E7) + new net shares Where net new shares = number of vested options (E8) – amount of shares the firm could buyback with the exercise price proceeds (E8*E9/E6)...
View Full Document

{[ snackBarMessage ]}