'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate Loan Percentages by Maturity Date in Excel
Question details
The user needs to calculate the percentage of total loan balances that fall into specific maturity date categories.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grouping loan balances by the time remaining until maturity (e.g., within 1 year, 1-2 years, etc.) and determining what percentage each group represents of the total loan portfolio.
- Observed behavior
- Needs the correct combination of SUMIFS and EDATE functions to dynamically aggregate loan balances across specific future timeframes based on today's date.
Ensure your maturity dates are formatted as valid dates in Excel and that there are no blank cells or text values in your loan balance column.
Use SUMIFS and EDATE Formulas to Calculate Percentages
Use SUMIFS to aggregate loan balances matching specific date ranges dynamically calculated by EDATE, then divide by the total loan amount to get the percentage.
The EDATE function returns a date that is a specified number of months before or after a starting date. Combined with the TODAY() function, you can dynamically create date thresholds (e.g., exactly 12 months from today).
By placing these EDATE thresholds inside a SUMIFS function, you can sum only the loan balances whose maturity dates fall within the defined periods.
Select an empty cell and enter the formula: =SUMIFS(B2:B100,A2:A100,"<"&EDATE(TODAY(),12))/SUM(B2:B100). This sums balances in B2:B100 where dates in A2:A100 are strictly less than 12 months from today, dividing the result by the total loan value.
In the next cell, enter: =SUMIFS(B2:B100,A2:A100,">="&EDATE(TODAY(),12),A2:A100,"<"&EDATE(TODAY(),24))/SUM(B2:B100). This isolates the 12-to-24-month window.
For loans maturing far in the future, enter: =SUMIFS(B2:B100,A2:A100,">"&EDATE(TODAY(),36))/SUM(B2:B100).
Highlight the cells containing your formula results, navigate to the Home tab on the Excel ribbon, and click the '%' (Percent Style) icon to format the decimal outputs as percentages.
Calculate Loan Maturity Percentages in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SUMIFS, EDATE, and TODAY(), allowing you to easily calculate loan balances, track maturity dates, and manage complex financial portfolios with high efficiency.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the workbook containing your loan balances and maturity dates.
- 2. Enter the Formula: Input the SUMIFS and EDATE formulas in the summary cells exactly as you would in Microsoft Excel.
- 3. Apply Percentage Formatting: Select the results, go to the Home tab on the top ribbon, and click the Percent icon to display the values accurately.
Frequently Asked Questions
Why is my EDATE function returning a #VALUE! error?
EDATE requires a valid starting date format. If your referenced date cells are stored as plain text rather than date values, EDATE will fail. Select your date column, go to 'Data' > 'Text to Columns', and click 'Finish' to convert text to valid dates.
Can I use EOMONTH instead of EDATE for loan maturity calculations?
Yes, if you want to group maturity dates up to the very last day of a specific month. While EDATE calculates exactly X months from the specific starting date, EOMONTH returns the final day of that future month, which is often preferred for month-end financial reporting.
How do I make the total sum dynamic if I add more rows later?
Instead of limiting your range to B2:B100, you can format your data as an Excel Table (Ctrl+T). This allows your SUMIFS and SUM formulas to automatically expand and include new loan entries without requiring manual formula updates.
