My yield model survived exactly one real tape

Spent about six weeks building a spreadsheet for performing notes. Remaining term, rate, purchase price as a percent of balance, yield to maturity, and a column where I'd stress the collateral value down 20 percent and see what recovery looked like. It was clean. I was proud of it.

Then somebody actually sent me a tape. Nineteen loans. Half the fields I'd built columns for weren't on it. There was no origination date on eleven of them, so I couldn't compute how seasoned they were without asking. Two had a payment amount that didn't match the rate and balance, which turned out to be because they'd been modified and nobody had put the mod on the tape. Property values were all listed as "BPO" with a number and no date, and one of those dates, when I finally got it, was from 2019.

My model wanted inputs the market doesn't hand you. The real work was six emails asking for the origination dates and the mod documents and a current value opinion, and then waiting eleven days, and then finding out four of the nineteen had already been sold.

I'm still in the analysis stage and I'm trying to stop. But the actual lesson is that the spreadsheet was the easy half and I did the easy half first because it felt like progress.