3 advanced formulas

3 advanced formulas - formula results in the priority of paying off the Revolver then Term Loan and then the Senior Sub Notes(Waterfall formula

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
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.
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
Background image of page 4
Background image of page 5
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

This document was uploaded on 11/02/2011 for the course FINANCE 390 at Rutgers.

Page1 / 5

3 advanced formulas - formula results in the priority of paying off the Revolver then Term Loan and then the Senior Sub Notes(Waterfall formula

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online