Using Excel for Debt and Loan Management

Author: David H. Ringstrom

In this course, 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 course 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 course 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.

Publication Date: February 2025

Designed For
Professionals seeking to build debt and loan related spreadsheets more effectively.

Topics Covered

• Crafting self-resizing formulas with the new Spilled Range Operator in in Excel 2021 and Microsoft 365
• Pairing the EOMONTH and SEQUENCE functions together to create a dynamic column of period end dates
• Jump-starting spreadsheet projects using free, prebuilt templates in Excel
• Seeing dynamic array formulas in action by resizing an amortization table based on changing the loan term
• Exploring Excel’s Goal Seek feature, which can be used to solve for a single missing input
• 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
• Avoiding the need to write repetitive formulas using Excel’s Data Table feature
• Calculating the principal portion of a loan paid during a specific time period by way of the CUMPRINC function
• Matching the IPMT and SEQUENCE functions to create a dynamic column of interest paid amounts
• Using Excel's Solver feature to find the amounts that match a total, such as which checks or invoices make up a particular total
• Managing risk by creating a debt coverage ratio calculator

Learning Objectives

• Identify the character that represents the spilled range operator in Excel
• Identify the arguments for the EOMONTH function in Excel
• Recognize which version of a formula correctly utilizes the PMT function to calculate a monthly loan payment amount
• Identify the function that calculates the total interest between two periods
• Identify the character that represents the spilled range operator in Excel

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended

Advance Preparation
None