Excel - Simplify Loan Service Fee Calculation in Excel - No More 360-Row Tables! - Episode 1954
2,648 views · Published 5 June 2015 · 15:20 · Indexed 27 October 2026
Channel: MrExcel.com · 2015 · Education
Microsoft Excel Tutorial: Calculate the Loan Service Fee for all months without building the amortization table. Welcome to Episode 173 of the Dueling Excel Masterclass! 📈 In this episode, Bill Jelen and Mike Girvin take on a fascinating Excel challenge - calculating loan service fees. But here's the twist: he does it with a single, powerful formula, avoiding the need for a 360-row amortization table. Here's what you'll discover in this episode: Understanding how to calculate a monthly service fee based on an annual percentage. Breaking down the complex logic behind loan service fee calculation. Utilizing Excel functions and array formulas to streamline the process. Bill's approach is not just mathematically intriguing but also a practical solution for those who don't want to manage extensive tables. Learn how to craft a single, efficient formula that handles everything. 🚀 By the end of this episode, you'll have a deep understanding of complex calculations and a game-changing Excel formula in your toolkit. Mike Girvin then improves the formula, using SUMPRODUCT instead of SUM to avoid having to press Ctrl+Shift+Enter. Don't forget to like, subscribe, and share this video with fellow Excel enthusiasts. If you have questions or need further insights, leave a comment below. Table of Contents (00:00) Sum Each Month's Loan Servicing Fee (00:49) Monthly Service Fee is 0.0025% OF Loan Balance (01:33) Avoid 360 row Amortization Table? (02:45) State the Logic of the Amortization Table (03:28) VBA to loop through all months (04:59) Cumulative Principal (06:28) Using ROW to force an array (07:59) Combining formulas (11:01) Using ROW and INDIRECT for an array (12:38) SUMPRODUCT instead of SUM to avoid Ctrl Shift Enter (13:53) INDIRECT is Volatile and Slow (14:24) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #excelformulasandfunctions #exceldynamicarray #loancalculation This video answers these common search terms: what is sumproduct in excel how to use sumproduct in excel using row and indirect functions in excel performing a calculation for 360 months in a single formula array formulas in excel A loan service fee is .25% of the unpaid principal balance. Is there a way to write a single formula to total all of the service fees, *without* having the complete amortization table? Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153780/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920