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

Watch on YouTube

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