The finance director wants a simple number: how much pledged money should come in each month for the next year? Altru has the information. Each pledge holds an installment schedule with dates and amounts. What Altru doesn’t obviously give you is one report that lays those future installments out by month. In most databases you build the projection yourself, from a query plus a little spreadsheet work.
Below is how to build one you can trust, and the traps that make pledge forecasts drift from reality.
Start by checking what you already have
Before you build anything, look through the standard revenue and pledge reports in your Altru database. Some organizations have a report that covers part of this, such as outstanding pledge balances or past-due installments. Run a couple against a known pledge and compare the output to the installment schedule on that pledge record. If a report already shows future installments by date, you may only need to export it and group it by month.
If nothing fits, or you can’t tell whether a report shows remaining balances or future installments, ask Blackbaud support whether a standard projection report exists for your version. Don’t guess. A balance report and an installment projection answer different questions, and finance will notice when the totals don’t match.
Build the projection from an installment-level query
The dependable approach is a revenue query that returns one row per installment, not one row per pledge. A pledge-level query gives you the total and the balance. It can’t tell you when the balance is due.
What the query needs
- Filter: revenue type is pledge, and the pledge still has a balance. Exclude pledges that are fully paid or written off.
- Installment fields: installment date, installment amount, and the unpaid balance on that installment if your query view exposes it. The exact field names depend on your query views, so look under the revenue and installment areas and test with a pledge you know well.
- Context fields: constituent name and lookup ID, pledge date, total pledge amount, designation or fund, and campaign or appeal if finance reports by them.
- Date filter: installment date from the first of this month through whatever horizon finance wants, usually 12 or 24 months.
Run it and check the row count against a few pledges by hand. A three-year pledge paid quarterly should come back as however many quarterly rows are left, not one row and not the full original twelve.
Turn rows into a monthly forecast
Export to Excel or Google Sheets and add a column for the month of the installment date. A pivot table with month as rows and the sum of unpaid installment amounts as values gives you the projection. Add fund or designation as columns if finance needs restricted and unrestricted money split out.
Where pledge projections go wrong
The query is the easy part. The judgment calls are what make the numbers useful:
- Past-due installments. An installment that was due last month and hasn’t been paid won’t appear if your date filter starts today. Decide whether to drop it, roll it into the current month, or report it on its own line. A separate “past due” line is usually the most honest choice.
- Partial payments. If a donor paid part of an installment, using the full installment amount overstates what’s coming. Use the unpaid balance on the installment if you can get it. If you can’t, mark partially paid installments in your review.
- Prepayments. Donors sometimes pay ahead. Depending on how the payment was applied, future installments may already be covered. Spot-check a few large donors who pay early.
- Schedule changes. When staff change a payment plan, make sure they actually edit the installment schedule on the pledge. A new plan that only lives in a note never reaches the projection.
- Write-offs. A pledge everyone knows won’t be paid stays in the forecast until someone writes it off. Go through the oldest past-due pledges with your development lead before each forecast.
- Cash versus recognition. This is a cash-timing projection. Your auditors may book pledge revenue on a different schedule. Label the report clearly so nobody compares it to the income statement and finds a “discrepancy.”
Keep it current without rebuilding it every month
Save the query and give it a clear name. Each month, re-run it, paste the results into the same sheet, and refresh the pivot. Keep the previous month’s projection on another tab. Comparing what you forecast for a month with what actually came in is the quickest way to find bad schedules and donors who need a reminder.
For large installments due in the next 30 to 60 days, pull a short list from the same query for the gift officer. The forecast is more accurate when someone makes the reminder call before the installment slips.
Automating the export
The manual export works, but it’s one more monthly chore, and the sheet is out of date as soon as a payment posts. If your projection lives in Google Sheets, Data Bridge can sync the results of an Altru query to that sheet. The pivot table then sits on top of current installment data instead of last month’s export. The query and the judgment calls above stay the same. You just stop copying and pasting.
Quick answers
Can I project installments with a pledge-level query?
Only roughly. You can divide the balance by the remaining months, but that ignores the actual schedule and gives you wrong timing for quarterly or annual plans. An installment-level query is worth the extra setup.
Should recurring gifts be included?
Keep them separate. Recurring gifts are not a legal commitment the way a pledge is, and finance usually wants them forecast differently. If you include them, put them on their own line.
