Introduction to Waterfall Calculations

Waterfall calculations are used to allocate cash flow among two or more partners based upon agreed-upon return parameters.

In this practical webinar, you will learn how to assemble a waterfall calculation from scratch. Among other things, you will learn concepts such as using range names (cell address references can be used in lieu of range names if desired). You will also learn the differences between the IRR versus XIRR worksheet functions for computing investment returns and other worksheet functions including MIN, ROUND, DATE, SUMIF, and more. Extensive check figures are also incorporated into the model to ensure data integrity and ease of use in following the calculations through the tiers.

Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Prerequisites: None

Field of Study: Computer Software and Applications

Program Level: Intermediate

Credit Type: Group Internet-Based for the Live Program

You will learn the following:

• Summarizing the investment parameters to be incorporated into the waterfall calculation.
• Building a waterfall calculation from scratch, starting with a blank worksheet.
• Using range names to streamline formulas and bookmark key inputs within a workbook.
• Utilizing Excel’s Name Manager feature to review and manage named ranges within a workbook.
• Building a series of dates with the Fill Series command.
• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP.
• Building a series of self-updating dates with Excel’s DATE function.
• Saving time writing formulas by choosing named ranges with the Use In Formula command.
• Incorporating decisions into calculations with Excel’s IF function.
• Saving time by using the AutoSum formula to sum multiple columns at once with a mouse click or a keyboard shortcut.
• Computing the internal rate of return for a series of cash flows with the IRR function.
• Understanding how XIRR provides more accurate return calculations than IRR, as well as computing returns on irregular cash flow distributions when needed.
• Maximizing worksheet cells by using custom number formats to display text within cells that contain numeric values.
• Returning the smallest value from two or more amounts by way of the MIN function.
• Applying formatting to multiple locations within a worksheet by way of the Format Painter feature.
• Using Excel’s ROUND function to round numbers to the nearest 10, 100, 1000, or beyond.

Upon course completion, you will be able to:

• Identify the worksheet function that enables you to calculate a return for a series of cash flows distributed at irregular dates.
• Define the purpose of the MIN function.
• Recall the arguments for the IF worksheet function.
• Recall the keyboard shortcut that enables you to transfer the contents of a cell across a row of adjacent cells that you’ve selected.
• State which keyboard shortcut allows you to toggle the Bold settings for a cell or selection of text on or off.
• Recall the location of the Name Manager command within Excel’s ribbon interface.
• Recall which menu contains the Fill Series command.
• State the purpose of the Use In Formula command.
• State which keyboard shortcut displays the Format Cells dialog box.
• Recall the arguments for the MIN function.
• Identify the location of the Format Painter command within Excel’s menu structure.
• Name the arguments for the ROUND function.
• State which character signifies an absolute reference within a formula.
• Recall the character that you can use in lieu of the CONCATENATE worksheet function.