01/21/2025
11 am pt / 12 pm mt / 1 pm ct / 2 pm et
*approximate length: 45 minutes to 1 hour
presenter: david ringstrom, cpa
in this presentation, excel expert david h. ringstrom, cpa, will delve into the capabilities of excel 2021 and excel for microsoft 365, focusing on dynamic array functions for debt and loan management. he will introduce the pmt, impt, and ppmt functions to create a self-resizing amortization table, utilizing techniques such as the spilled range operator, sequence, and eomonth functions. david will also demonstrate the goal seek feature for optimal decision-making and compute debt service for multiple loans using cumipmt, cumprinc, and datedif functions. join us for this session to enhance your excel skills and financial modeling techniques.
david is the author of “exploring microsoft excel's hidden treasures: turbocharge your excel proficiency with expert tips, automation techniques, and overlooked features”. he demonstrates every technique at least twice: first, on a powerpoint slide with numbered steps, and second, in the subscription-based excel for microsoft 365. david draws your attention to any differences in excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. the handouts include an excel workbook with most of the examples he uses during his demonstrations.
excel for microsoft 365 is a subscription-based product that receives periodic feature updates. conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
who should attend: professionals seeking to build debt and loan related spreadsheets more effectively.
topics typically covered:
- exploring the dynamic arrays functionality available in excel 2021 and microsoft 365
- jump-starting spreadsheet projects using free, prebuilt templates in excel
- determining the principal portion of a single loan payment by way of the ppmt function
- avoiding the need to write repetitive formulas using excel’s data table feature
- exploring excel’s goal seek feature, which can be used to solve for a single missing input
- matching the ipmt and sequence functions to create a dynamic column of interest paid amounts
- pairing the eomonth and sequence functions together to create a dynamic column of period end dates
- identifying the interest portion of a single loan payment by way of the ipmt function
- contrasting traditional static amortization tables with a dynamic amortization now possible in excel 2021 and microsoft 365
- utilizing the ppmt and sequence functions together to return a dynamic column of principal paid amounts
- seeing dynamic array formulas in action by resizing an amortization table based on changing the loan term
- integrating the sequence function within sumif to create a dynamic running balance column for an amortization table
learning objectives:
- identify the character that represents the spilled range operator in excel
- identify the function that returns the principal portion of a single loan payment based upon a constant interest rate and constant payments
- recognize which version of a formula correctly utilizes the pmt function to calculate a monthly loan payment amount
level: intermediate
david h. ringstrom, cpa, is an author and nationally recognized instructor who teaches scores of webinars each year. his excel courses are based on 25 years of consulting and teaching experience. david’s mantra is “either you work excel, or it works you,” so he focuses on what he sees users don’t, but should, know about microsoft excel. his goal is to empower you to use excel more effectively.