CD Portfolio Optimization

Excel-based forecasting and scenario modeling tool that projects interest income and models rate changes for a $24M CD investment portfolio.

Excel Power Query Financial Modeling Forecasting Dashboard

Click to zoom

Client: Regional Credit Union
Type: Investment Forecasting & Scenario Modeling (Excel + Power Query)

Overview

I developed a forecasting and scenario modeling system for a regional credit union managing approximately $24 million in certificate of deposit (CD) investments.

The credit union relied heavily on interest income from its CD portfolio but lacked a clear way to forecast future income streams or visualize how reinvestment strategies would impact long-term performance.

The objective was to transform static monthly statements into a forward-looking financial planning tool.

The Challenge

CD portfolio with staggered maturity dates
Monthly and quarterly interest payment schedules
Ongoing reinvestment of matured funds
No consolidated forecasting model
Manual review of PDF statements without structured projections

Leadership needed visibility into:

Future interest income
Maturity concentration risk
Impact of changing interest rates

The Solution

I built a structured Excel-based system that:

Ingested monthly PDF statements using Power Query
Transformed and normalized investment data
Forecasted scheduled interest payments for the next 36 months
Modeled reinvestment assumptions under “normal” rate conditions

The system generated interactive dashboards to provide immediate, decision-ready insight.

Key Features

Portfolio Forecast Dashboard

Month-by-month projected interest payments (36-month horizon)
Reinvestment modeling based on existing rates
Visualization of CD maturity distribution
Breakdown of payment frequency (monthly, quarterly, etc.)
Distribution of CDs by investment term length

Scenario Modeling Dashboard

Adjustable projected interest rates by term (12, 24, 30, 36 months, etc.)
Dynamic recalculation of reinvested fund performance
Immediate visualization of portfolio income impact
Forward-looking rate sensitivity analysis

Technical Highlights

Power Query ingestion and transformation of PDF statements
Structured Excel Tables for portfolio data normalization
Forecast modeling using time-series projections
Dashboard-driven visualization with dynamic recalculation

Impact

Provided leadership with clear forward visibility into income streams
Enabled proactive reinvestment planning
Quantified interest rate sensitivity across the portfolio
Transformed static statements into a strategic financial planning tool

This project reflects my ability to convert raw financial documents into structured, decision-support systems that enhance forecasting, risk awareness, and executive planning.