Estate Inventory Spreadsheet From Scanned Statements

Build an estate inventory spreadsheet by converting each scanned statement, deed, title and bill the family hands over, then entering one line per asset with its value on the date of death and the page that proves it. Keep debts on a second sheet, and check every figure against the scan before the inventory is filed.

The spreadsheet is a working file. The court's own form or rule decides what the filed inventory must contain, and that differs from state to state. What the workbook gives you is a place where every number has a source page beside it, so the form can be filled in once and defended later.

Account titles tell you which statement belongs on which line. Search the converted pages for the decedent's name and for the account the personal representative will list on the probate inventory. Copy a beneficiary designation when the page prints one. Estate administration compares those lines with the court form, so confirm the date-of-death value on the image before you type it.

What records does a personal representative gather first?

A personal representative first gathers whatever shows what the decedent owned and owed on the date of death: account statements, deeds, vehicle titles, share certificates, insurance and retirement paperwork, tax returns, and the unpaid bills. They may arrive as paper, phone photographs or downloaded PDFs, and each one becomes a source for a line of the inventory.

The duty behind the list comes from state law. Florida's statute is one example of the wording: under section 733.604(1)(a) of the Florida Statutes, "a personal representative shall file a verified inventory of property of the estate, listing it with reasonable detail and including for each listed item its estimated fair market value at the date of the decedent's death." Your state's statute, deadline, valuation standard and form may differ on every point, so read the provision and the court form that govern your estate before you design anything.

A log for the pile

Give every item a number as it comes in, before any conversion. A plain intake log has five columns: item number, what it is, who handed it over, the date received, and the file name of the scan. A family that delivers a grocery bag of envelopes in March and a second bag in May will not remember which bag held the credit union statement, and the log will.

Sort the pile into the groups that drive the workbook:

The last group matters to the estate but holds no inventory figures. The post on OCR for a handwritten will covers the will itself and typed probate court records. Papers a client assembled before death for benefits planning or a guardianship are a different file with a different purpose, described in the post on elder law document OCR, though the account statements in that file are a good lead to what existed at death.

Tax records earn their place as a cross-check. A year-end interest or dividend form names a payer. Every payer on the last return should correspond to an account on the inventory, or to a note explaining why the account no longer exists.

Date-of-death balances from scanned account statements

A date-of-death balance comes from the statement whose period includes the date of death: take the opening balance, apply every transaction dated on or before the death, and the result is the balance to report. A statement's closing balance is the figure for the cycle's end date, which need not be the day the decedent died.

Working one account

1. Find the right statement. Read the period dates in the header. You want the cycle that contains the date of death, plus the one before it for the opening balance check.
2. Convert the transaction table. Upload the scan and export the table to Excel. In Schema Law the Excel export places each table on its own sheet with the rest of the text on another, and every plan includes it. The plans and their page limits are on the pricing page.
3. Prove the conversion. Add the opening balance and all the converted transactions. The total must equal the closing balance printed on the page. If it does not, a row was dropped, split or misread, and you find it before going further.
4. Cut at the date of death. Filter to transactions dated on or before the death and total again from the opening balance.
5. Record the result on the inventory with the statement's file name and page.

A fictional example: Eleanor Voss died on March 14. Her checking statement runs from February 21 to March 20.

The page prints 19,289.64. The inventory figure is 18,574.76. The 1,104.00 deposit that arrived after death is not part of the date-of-death balance. Record it for the accounting, and leave its treatment to the lawyer. The method for turning a scanned statement into a clean workbook, with the running-balance test in more detail, is in the post on converting scanned bank statements to Excel.

Checks written before death

A check the decedent wrote on March 12 that cleared on March 17 raises a question the statement cannot answer by itself: is the balance reported before or after that check? For federal estate tax purposes, 26 CFR § 20.2031-5 includes cash "deposited with a bank" in the gross estate and allows the balance remaining after outstanding checks to be returned in the circumstances that section sets out, "but only if the obligations are not claimed as deductions from the gross estate." A probate inventory follows your state's law, which may treat the point differently. Flag every such check in a column of its own so the lawyer can decide once and apply the decision to all of them.

Brokerage and retirement statements

A brokerage statement lists positions and a market value for each as of the statement date. For an inventory you need the positions held on the date of death and a value for that date under the standard your jurisdiction uses. The federal estate tax standard is one reference point: 26 CFR § 20.2031-2(b)(1) says that where there is a market for stocks or bonds, "the mean between the highest and lowest quoted selling prices on the valuation date is the fair market value per share or bond." That is a different number from the closing price on a month-end statement.

Convert the positions table for the share counts, then ask the institution for a date-of-death valuation letter. Where a letter exists, it is the source for the value and the statement is the source for the holding. Put both file names on the line.

Digits deserve a second reader. The post on why OCR gets numbers wrong covers swapped digits and lost decimal points, and a balance is exactly where one of them does harm.

Deeds, vehicle titles and stock certificates as inventory lines

Deeds, vehicle titles and stock certificates identify an asset and its owner but state no current value, so each becomes an inventory line whose description comes from the document and whose value comes from an appraisal, a valuation service or a transfer agent. Convert them for the identifying text, then verify that text character by character.

Deeds

From a deed you need the names of the grantees exactly as written, the words describing how they hold title, the legal description, and the recording information. Copy the legal description from the converted text into the workbook's notes, then read it against the image, because a bearing or lot number with one wrong digit describes different land. The post on closing documents explains what OCR does with deeds, legal descriptions and recording stamps.

Record the vesting words verbatim in their own column. Whether a property passes through the estate at all can turn on those words, and that is the lawyer's reading to make from the exact text.

Vehicle titles

A certificate of title is a state form, so the layout depends on the state that issued it. Look for the vehicle identification number, year, make, owner names, and any lienholder. The number is the part to protect. The federal rule at 49 CFR § 565.13 says in paragraph (b) that "Each VIN shall consist of seventeen (17) characters", and paragraph (g) limits each character to a numeral or a letter from a set that leaves out I, O and Q. That gives you two free tests on a converted title for a vehicle covered by the rule: count the characters, and treat any I, O or Q as a misread to correct from the image.

Capture how the owners' names are joined on the title and whether a lien is printed or stamped as released. Do not interpret either one in the workbook. Enter what the page shows.

Share certificates and bonds

On a paper certificate, look for the issuer, the registered owner, the number of shares, the certificate number, and any identifying number for the security. Engraved borders, coloured backgrounds and ornate type are hard on OCR, so plan to key these few fields by hand and use the converted text only as a draft.

A certificate proves what was issued on its date. Whether the company still exists under that name, whether the shares were split, and what they are worth now are questions for the transfer agent. Put the certificate on the inventory as a line with a blank value and a note of the inquiry sent, so it is not forgotten.

What columns does an estate inventory workbook need?

An estate inventory workbook needs columns that identify the asset, state how it was owned, give its value on the date of death, and point to the page that supports each of those. Add a column for who verified the line. Everything else is convenience.

Sheets

Keep at least four sheets: Inventory for the lines in the table, Debts for claims and bills, Sources for the intake log, and Working sheets, one per converted statement, holding the exported tables and the arithmetic. The inventory value for an account is then a cell reference into its working sheet, and anyone can follow the figure back to the rows that produced it.

Rules that keep the file trustworthy

• One asset, one line. A brokerage account with twelve positions is one line on the inventory if your form reports the account, or twelve if it reports holdings. Decide from the form, then be consistent.
• Never type over a converted figure. Correct it in the working sheet with a note of the page you corrected it from.
• Mask account numbers to the last four digits in the inventory sheet. The full number stays on the source scan.
• Do not delete a line for an asset that turns out to pass outside the estate. Mark it and leave it, so the question is answered once.

The filed inventory may need a layout, grouping or attachment schedule that this workbook does not have. Treat the workbook as the source and build the court's form from it.

Creditor claims, funeral bills and final medical bills

Creditor claims, funeral bills and final medical bills go on a debts sheet, one row per obligation, with the creditor, the amount, the dates the debt was incurred, and the page that shows each. Dates matter as much as amounts, because a state's priority rules can turn on when a service was provided.

Florida's statute shows why. Section 733.707(1) of the Florida Statutes orders payment by class. Class 2 is "Reasonable funeral, interment, and grave marker expenses, whether paid by a guardian, the personal representative, or any other person, not to exceed the aggregate of $6,000." Class 4 is "Reasonable and necessary medical and hospital expenses of the last 60 days of the last illness of the decedent, including compensation of persons attending the decedent." Under wording like that, the sheet needs the date of each medical service, the amount of the funeral invoice and a record of who paid it. Other states draw the classes differently, and whether any claim is valid or timely is a legal decision. The debts sheet only has to hold the facts that decision needs.

Columns for the debts sheet:

• Creditor name and mailing address, as printed
• Account or invoice number, last four digits
• Type: funeral, medical, credit card, loan, tax, utility, other
• Service or charge dates, first and last
• Amount billed, and amount after insurance where the page shows it
• Paid by whom and when, if already paid
• Claim filed with the court? Date and docket reference
• Source file and page
• Checked by / date

Medical bills

A final illness can produce several kinds of paper for the same charge: the provider's statement, an itemized bill, an insurer's explanation of benefits, and later a collection letter. Enter the obligation once and list the other documents as supporting pages on the same row. Converting an itemized bill to a table lets you total the charges by date of service. The post on converting medical records to a spreadsheet covers billing tables and their codes.

Credit card and loan statements

A credit card or loan statement needs the same cut at the date of death as a bank statement. The balance on that date is the figure to enter, so charges posted after it need a separate look: an automatic renewal, a card used by a family member, interest accrued after death. Convert the statement that spans the date, cut the transactions at the date, and put later items in the notes column with a flag.

Letters from collectors

A collection letter may name a creditor the family never mentioned. Log it in the intake sheet the day it arrives, convert it so the creditor name and reference number are searchable, and add a row. Whether the letter counts as a claim under your state's procedure is for the lawyer.

How do you carry an estate inventory forward into an accounting?

Carry an estate inventory forward by treating each inventory value as the opening figure of the accounting, then adding a ledger of everything received and paid out since the date of death. The inventory lines keep their numbers, and each later transaction refers to the line it changes.

What an accounting must show, the period it covers and the form it takes are set by your court's rules, so obtain the form first and name your ledger columns to match it. The mechanics of the working file stay the same whatever the form.

The ledger sheet

Add a sheet named Ledger with one row per transaction on the estate's own account: date, payee or payer, amount in or out, a category, the inventory or debt line it relates to, and the statement page. Fill it by converting the estate account's statements each month, with the same proof that opening balance plus transactions equals closing balance.

Tag every row with one of a small set of categories, for example:

• Collection of an inventory asset, such as closing the decedent's account into the estate account
• Income received after death, such as interest, dividends or rent
• Sale proceeds, with the inventory line sold
• Payment of a debt on the debts sheet
• Administration expense
• Distribution to a beneficiary

The reconciliation

At any date, the workbook should satisfy one equation: inventory values, plus receipts, plus gains on sale, minus losses on sale, minus disbursements and distributions, equals the assets still on hand. Build that as a small table on its own sheet, with each term a sum over tagged ledger rows. When it does not balance, the difference is the amount of a missing or mis-tagged row.

Take a fictional estate whose inventory lists a checking account at 18,574.76 on the date of death. A deposit of 1,104.00 lands in that account three days later. The ledger shows the 1,104.00 as a receipt after death, in whichever category the lawyer assigns, and it shows the account's closing transfer into the estate account as a collection of the inventory line. The 18,574.76 is never entered as a receipt, because it is already the opening figure.

When the inventory itself changes

Assets can surface late. Florida's statute is one example. Section 733.604(2) of the Florida Statutes provides that a personal representative who "learns of any property not included in the original inventory" or that a value or description "is erroneous or misleading" must file "a verified amended or supplementary inventory". Add late assets as new lines with new numbers and a note of the date discovered. Never renumber. An accounting that cites line 14 has to mean the same asset next year.

Keep every scan the workbook cites for as long as the file is open. Schema Law lets you delete a document whenever you choose, and its security commitments on the homepage say what deletion removes, so decide where the scans of record live before you clear anything from a conversion tool.