Spreadsheet risk
When a spreadsheet stops being adequate for investment accounting
Start with the honest part: spreadsheets are not the enemy. A small portfolio of plain bullet maturities, held to maturity, bought near par, maintained by someone who understands the accounting, can be tracked correctly in a workbook for years. Telling you otherwise would be a sales pitch, and you would be right to discount it.
What follows is the other half of that sentence. There are specific, mechanical ways a securities workbook drifts away from the right answer, and they are different from generic spreadsheet risk. This page walks through seven of them, shows the arithmetic on two, describes what reviewers actually test on investments, and gives you a threshold test you can run on your own file this afternoon. Then it covers how to move off a workbook without breaking your audit trail, because the transition is where most of the damage happens.
What reviewers actually test on the investment portfolio
It helps to argue from the published expectations rather than from fear. For federally insured credit unions, the NCUA Other Supervisory Committee Audit Minimum Procedures Guide describes audit evidence that cash and investment accounts were reconciled and confirmed directly with the institution or the safekeeper, and that material general ledger accounts were reconciled with their supporting subsidiary ledgers. The same guide sets out sample-based testing on investments: select a sample of at least ten securities, compare accrued interest receivable to the terms of the security, recalculate the receivable, and recalculate the most recent interest coupon received against the credit entry on the general ledger accrued interest account. That guide is written for the supervisory committee, internal auditor, or other qualified person performing the audit rather than for an examiner. Underneath all of it, the Federal Credit Union Act at 12 U.S.C. 1761b(19) directs the board to establish and maintain a system of internal controls consistent with the regulations of the Board, and the NCUA Examiner's Guide is where the supervisory expectations around those controls are published. Confirm any specific procedure against the version of the guidance in effect for your cycle.
Read that last sentence again, because it is the whole point. The test is a recalculation from the security's own terms, traced to your ledger, on a sample the reviewer picks. Nothing there prohibits a spreadsheet. What it requires is that your numbers be reproducible from source terms, reconciled to the ledger, and supported by a subsidiary record that someone other than the author can follow. Community banks face their own examiner expectations and report their securities on the FFIEC call report schedules at both amortized cost and fair value by classification (see the FFIEC 051 Schedule RC-B instructions, and confirm against the version in effect for your filing). Different supervisor, same underlying demand for holding-level support.
Seven failure modes specific to a securities workbook
Generic spreadsheet risk articles will tell you about broken formulas and version confusion, and they are not wrong. These seven are the ones that only happen to investment portfolios, which means they are also the ones your close-process checklist probably does not cover.
1. Straight-line standing in for the effective-interest method
The most common shortcut, because straight-line is the one a spreadsheet can do without a schedule.
- Under the interest method, periodic income is the carrying amount multiplied by the effective yield locked at purchase, and the premium or discount amortization is the difference between that and the coupon. The amount changes every period. Straight-line divides the premium by the number of periods and books the same number every time.
- The two answers differ, always, and the difference grows with term and with the size of the premium or discount. A straight-line workbook on a premium bond amortizes too fast in the early years, which understates both carrying value and interest income, then reverses in the later years.
- US GAAP describes the interest method for this, and a straight-line approximation is a materiality judgment rather than a free choice. The problem with a workbook is not that it uses straight-line; it is that nobody ever computes how far off it is, so the materiality judgment is asserted rather than measured.
2. A frozen amortized cost that never rolls forward
The quiet one, and the one that does the most damage over time.
- A holding is entered at cost when it is purchased, and then the basis column never moves again, because the person who built the workbook intended to add the amortization schedule later.
- Everything downstream inherits the error: the balance sheet carrying amount, interest income, the call-report amortized cost column, and the gain or loss when the security eventually matures, is called, or is sold.
- It is invisible until disposal, because a static number never looks wrong. Then the entire cumulative error surfaces at once as a gain or loss that nobody can explain.
3. Factor paydowns applied by hand, late, or not at all
The failure mode unique to mortgage-backed and CMO positions.
- Current face is original face multiplied by the current factor. The factor changes monthly, it arrives from the custodian or the data provider on the provider's schedule, and it has to be applied in the period it belongs to.
- Miss a month and the position is overstated. Apply the right factor in the wrong period and the principal paydown, the interest accrual on the reduced balance, and the amortization all land in the wrong month, which is worse than being late because it corrupts two periods instead of one.
- CMO tranches make it harder, because the principal distribution depends on the structure rather than on a single pool factor. A workbook column cannot express a payment waterfall.
4. Realized gain or loss computed from a stale basis
The consequence of failure modes 1 and 2 arriving at the same time.
- Gain or loss on a call, a maturity, or a sale is proceeds less amortized cost at that date. If the basis has been frozen or approximated all along, the gain or loss is wrong by exactly the amount of accumulated error, and it hits income in a single period.
- Callable holdings purchased at a premium have their own rule. Under US GAAP as amended by FASB ASU 2017-08, for callable debt securities held at a premium with explicit, noncontingent call features callable at fixed prices on preset dates, the premium is amortized to the earliest call date rather than to maturity. Discounts continue to be amortized to maturity. Confirm how it applies to each holding with your auditors.
- A workbook that amortizes every premium to maturity will therefore carry a callable premium bond above where it should be, and will book a loss at the call that should not exist.
5. No audit trail, no version control, no access control
The control weakness, stated plainly.
- A workbook cannot tell you who changed a yield, when, or why. It cannot show that the month you closed in April is the same month you are showing an examiner in September.
- Access is file-level rather than function-level. Anyone who can open the file can change a rate, a factor, or a prior-period figure, and nothing records it.
- This is the point where the conversation stops being about accuracy and becomes about internal controls, which is a board-level responsibility rather than an accounting preference.
6. Key-person dependency on whoever built it
The risk that only shows up when it is too late to fix cheaply.
- The formulas encode decisions that were never written down: which day-count convention, how a stub period was handled, why one holding is amortized differently, which tab feeds the entry.
- When that person retires or leaves, the institution inherits a file it cannot fully explain, and explaining it is exactly what a reviewer will ask for.
- The practical test: could a competent accountant who has never seen the file reproduce last month's entry from it, using only the file and the source documents? If not, the knowledge is in a person, not in a control.
7. A tie-out performed against a number the workbook produced
The one that looks like a control and is not.
- If the journal entry came from the workbook, then agreeing the general ledger balance back to the workbook proves only that the entry posted. It cannot detect an error in the workbook itself, because both sides came from the same place.
- A real reconciliation compares against an independent source: the custodian or safekeeping statement, the coupon actually received, the factor published by the provider, and the terms on the security itself.
- This is why the confirmation requirement exists in audit procedures. Independence of the evidence is the entire point of the exercise.
What the drift actually looks like, with the arithmetic
Vendors like to imply the numbers are catastrophic. Usually they are not, and pretending otherwise makes the rest of the argument suspect. Here are two worked illustrations. Both are hypothetical examples with assumptions stated, not measurements of any institution. Run the same arithmetic on your own largest holding, because the answer depends entirely on your portfolio.
Straight-line versus the interest method. Assume a non-callable bullet, $1,000,000 par, 5.00% coupon paid semiannually, ten years to maturity, purchased at 107.00 for a premium of $70,000. That price implies an effective yield of roughly 4.14%. In the first year, straight-line amortizes $7,000 of premium while the interest method amortizes about $5,782, so the workbook understates first-year interest income by roughly $1,218. The gap in carrying value widens to about $3,571 at the five-year point before closing back to zero at maturity. Now change the assumptions: on a five-year bullet with the same 5.00% coupon bought at 103.00, the peak carrying-value gap is only about $802. On a $5,000,000 position of the ten-year bond, the first-year income difference is about $6,092 and the peak carrying-value difference about $17,857. Same method, very different materiality, which is precisely why the judgment has to be computed rather than assumed.
A stale factor. Assume a mortgage-backed position with $2,000,000 original face. The workbook is still carrying the factor from three months ago, 0.812376, while the current factor is 0.784512. Current face is overstated by $55,728, and every derived figure for that holding, including accrued interest, amortization, and the reported balance, is computed off a face amount that no longer exists. One holding, one missed input, three periods of downstream error.
The honest threshold test
There is no portfolio size that makes a spreadsheet automatically wrong. There is a set of questions that tells you which side of the line you are on. Answer them about your actual file, not about the file you intend to build.
Can it be reproduced?
Could a competent accountant who has never opened your workbook reproduce last month's amortization and accrual entries from it, using only the file and the source documents? If the answer requires a phone call to one specific person, you have a key-person control, not a documented one.
Is the method the interest method?
If it is straight-line, has anyone computed the difference against the interest method for the largest positions, in writing, this year? Approximation is a decision. Undocumented approximation is a gap.
Do factors post in the right period?
If you hold mortgage-backed positions, is there a defined step that applies the published factor for the correct period every month, and evidence it happened? Or does it happen when someone remembers?
Is the tie-out independent?
Does the reconciliation compare the ledger against custodian statements, actual coupons received, and published factors? Or does it compare the ledger against the same workbook that generated the entry?
Can you show a prior period unchanged?
If an examiner asks what the portfolio looked like at the close you signed six months ago, can you produce it and prove nothing has been edited since? A saved copy in a folder is evidence of intent, not integrity.
Who can change a number?
Is there any restriction on who can alter a yield, a factor, or a closed period, and any record when they do? File permissions on a shared drive are not access control over accounting functions.
If you answered badly to three or more
That does not mean anything is wrong with your numbers today. It means the process depends on care rather than on controls, and care does not survive a retirement, a busy quarter, or a portfolio that grows more complex. The reasonable response is a dated plan, not a panic. Fix the highest-value failure mode first: for most institutions that is either the amortization method on the largest premium positions or the factor process on mortgage-backed holdings.
How to move off a workbook without breaking the audit trail
The transition is riskier than the workbook. A rushed mid-period cutover with hand-typed opening balances replaces a documented process with an undocumented one, and that is a worse position than where you started. The sequence below is deliberately unexciting.
1
Pick a closed period boundary
The cutover date is the last day of a period you have already closed and reconciled. Never mid-period, never during a quarter-end. The opening balances then have a source document behind them and a signature on it.
2
Rebuild basis from source, not from the workbook
For each holding, pull acquisition date, original cost, and the price or yield at purchase from the trade confirmation or safekeeping record. Where the workbook and the source disagree, the source wins and the difference gets documented.
3
Load and reconcile before you rely on it
Import the holdings, then reconcile totals against the last closed report: par, amortized cost, accrued interest receivable, and current face. Investigate every difference. A tolerance you cannot explain is an error you have not found yet.
4
Run one period in parallel
Close the same period in both places and compare the entries line by line. Differences are expected where the method changed, and each one should be explainable in a sentence. Keep that comparison as evidence.
5
Document the break
Write down the cutover date, what carried over, what was rebuilt from source, what could not be recovered, and why. A disclosed discontinuity with a memo behind it is a normal accounting event. An undisclosed one is a finding.
What has to carry over
The identifier for each holding (CUSIP, or an account number where no CUSIP exists), acquisition date, original cost, current amortized cost, the effective yield or the price that produced it, accrued interest receivable, classification, factor history and current face for mortgage-backed positions, call and step schedules, and any prior realized amounts you will need for the year. Amortized cost is path dependent, which means it cannot be re-derived from today's price and a coupon. If you are also moving off an older desktop tracker rather than only a workbook, the mechanics are covered on replacing a legacy investment tracking system. In FI Investment Tracker, spreadsheet exports load through the import center shown above, with a preview before anything commits, duplicate detection, blocked-row quarantine, retention of the source file hash, and an audit event written when the batch commits, so the migration itself produces evidence.
Is it wrong to keep investment accounting in a spreadsheet?
No. A small, simple, stable portfolio of bullet maturities held to maturity can be tracked correctly in a workbook by someone who knows what they are doing. What changes the answer is complexity, turnover, staffing, and the level of evidence you are expected to produce. The failure modes on this page are the specific things that break, so you can test your own workbook rather than take a vendor's word for it.
Do examiners prohibit spreadsheets for investment accounting?
We are not aware of any rule that prohibits spreadsheets, and we will not claim one. What supervisory material describes is the outcome: a system of internal controls, general ledger accounts reconciled to supporting subsidiary ledgers, accounts confirmed with the safekeeper, and testing that recalculates accrued interest and the most recent coupon against the ledger. A workbook that cannot demonstrate those things is the problem, not the file format.
When is the right time to move off a spreadsheet?
At a closed period boundary, and never in the middle of a close or a quarter-end. The cutover date should be the last day of a period you have already closed and reconciled, so the opening balances in the new system have a source document behind them and the reconciliation is a comparison rather than a reconstruction.
What data has to carry over when you leave a workbook?
The identifier (CUSIP, or an account number for holdings without one), acquisition date, original cost, current amortized cost, the effective yield or the price that produced it, accrued interest receivable, classification, factor history and current face for mortgage-backed positions, and call and step schedules. Amortized cost is path dependent, so it cannot be re-derived from a current price and a coupon alone.
Where our job ends and yours begins
Your institution files its own reports and remains responsible for its filings. Nothing here is regulatory, accounting, tax, or legal advice, and the regulatory material linked above should be read in the version currently in effect for your institution. Classification elections, day-count conventions, fair-value sources, and materiality judgments belong to you and your auditors. Software can make the calculation consistent, the record traceable, and the evidence retrievable. It cannot make those decisions for you.
If the workbook has outgrown itself
FI Investment Tracker is a local-first securities subledger: effective-interest amortization, factor-based paydowns, month-end close with a GL tie-out, and call-report support, with an audit trail and on-device restore points. Your portfolio data stays in an encrypted database on your own machine. Published pricing, self-serve checkout, and your own data as the evaluation.
